SQLite Schema Diagram Generator

[thristian]

Author here, I didn't bother submitting this to HN because I figured it would be too niche and trivial to get much attention. Evidently I was wrong!

[alexvoda]

Really love that you used Kunth's "Literate programming" to document the code. You don't see it often but I find it really nice when seeing it.

[keepamovin]

This is very cool. Reminds me of my days 13 years ago using dot to draw complex planaarized graph diagrams before switching to physics / springs models, graph embeddings, and other cool things.

Nice to see a really good use for dot.

I created a fork on GitHub as a fork there'll be easier for me to come back to, find, organize and use (and may be for others too): https://github.com/o0101/sqlite-schema-diagram

I hope you don't mind? If you don't want ur code there let me know and I'll sadly but obediently take it down and just link to it from someplace on there I can readily find. :)

[meitham]

The fact you achieved this with a query and graphiz is impressive! I wonder how much tweaks this query needs to make it work with DuckDB.

[tejtm]

Likewise, the niche pressure for me came from SQLite being agnostic to a canonical form for SQL `.schema`. I did not need to get into parsing every flavor.

https://github.com/TomConlin/SQLiteViz

[ncruces]

I know this is terribly unfortunate, and supporting the monopoly, but consider a GitHub mirror.

I just mirrored it myself to keep tabs on it, because otherwise I'll forget it.

Very interesting approach.

[NortySpock]

Very nice, I've been doing some similar things at work to help map out a data warehouse migration project.

Have you considered outputting to a MermaidJS format?

https://mermaid.js.org/syntax/entityRelationshipDiagram.html

[_ache_]

Thank you. Interesting little tool.

[chiph]

A place I worked at during the dot-com era had a large format printer[0] and the DBAs would occasionally print database schema diagram posters that they would hang on the walls. It was amazingly useful, especially as we staffed up and had a lot of new employees.

@thristian - can you specify a paper size?

[0] That once the marketing department found out about, was always out of ink.

[thristian]

So far as I can tell, GraphViz does not allow you to specify a paper size. However, if you render to SVG, you can open the result in Inkscape and rearrange things fairly easily. That's not quite as convenient as having it done automatically, but GraphViz can struggle with laying out a complex schema even when assuming infinite space - some amount of hand-tweaking is going to be necessary regardless.

[gcanyon]

I built a similar tool for my own use that:

1. Takes in a .dot file 2. Presents a simple UI for selecting which tables/relationships you want in the final diagram 3. Lets you highlight a table and add all directly related tables to the selected tables 4. Lets you select two tables and adds the tables for the shortest route between the tables 5. Lets you assign colors to tables/relationships for the final diagram 6. Optionally shows only key fields in the final diagram 7. Generates the necessary graph source and copies it to the clipboard, and loads either of two GraphViz pages to let you paste the source and see the graph.

If that would be of interest to anyone I'd be happy to post it.

[codetrotter]

I’d love to see it!

[mrbuttons454]

Yes please!

[zoomablemind]

Tried it on SQLite's own Fossil repo, which is a kind of SQLite db too.

The resulting diagram shows no relationship arrows.

Turns out the Fossil's schema uses REFERENCES clause with a table name only; I guess, this points to table's primary key by default. Apparently, the diagram generator requires explicit column names.

[thristian]

Huh. The syntax diagram in the documentation[1] suggests this is possible, but the documentation on foreign keys[2] does not mention that syntax, or how it's interpreted.

I think I can fix this.

[1]: https://sqlite.org/syntax/foreign-key-clause.html

[2]: https://sqlite.org/foreignkeys.html

[thristian]

I have pushed an update which should fix this issue.

[littlecranky67]

Love this! 5mins after visiting the page it is built into my gitlab CI pipeline :)

[franga2000]

Thanks for the idea! I have a repo that (ab)uses Gitlab CI to periodically produce an SQLite database from a bunch of other data sources and this is a great addition to the README

[gchaincl]

curious to know what are you using it for? do you upload a diagram on every push?

[andrewl]

This seems very clever. I’ve enjoyed abusing SQL, too. And note that abuse is the developer’s term for how what he’s doing in sqlite-schema-diagram.sql. I’m not trying to be insulting. I actually do like it.

[whartung]

What I like here is the "unix style do one thing" part of here's a simple ("simple") SQL script that pumps into GraphViz which does all of the heavy lifting.

[idoubtit]

I remember writing a script for doing this, more than 10 years ago. I haven't used it much, and not for many years.

The problem is that a fully automatic schema is only readable for very small databases. So small that very soon you can keep the structure in your head. For larger databases, the automatic schema will be awful. Even with just 20 tables, graphviz (dot | neato) will make a mess when some tables are referenced everywhere (think of `created_by` and `updated_by` pointing to `user_id`).

When I need a map of a large database, I usually create a few specialized diagrams with dbeaver, then combine them into a PDF file. Each diagram is a careful selection of tables.

[olejorgenb]

Regardless, the implementation in 128 lines of SQL combined with graphviz is cool.

[bbkane]

You might try https://schemaspy.org/ - it generates a website with ER diagrams that only go one or two relationships out, but they have clickable table names to get to the next diagram

[alexvoda]

As with all such tools the issue is the automatic layout algorithm.

I find that almost all layout algorithms for database diagrams are rather poor.

[lovasoa]

You can do this a little bit easier using SQLPage [1], without GraphViz, and you'll get an always up to date schema:

    select 'table' as component, 'Foreign keys' as markdown;

    select *, (
      select
        group_concat(
          printf('[%s.%s](?table=%s)', fk."table",fk."to",fk."table"),
          ', '
      )
      from pragma_foreign_key_list($table) fk
      where col.name = fk."from"
    ) as "Foreign keys"
    from pragma_table_info($table) col;

[1] https://sql.ophir.dev (https://github.com/lovasoa/SQLpage#sqlpage)

[vidarh]

I don't see how this is in any way comparable - it looks like it'd just produces a table rather than a diagram? You can indeed do that too with a single sqlite query as well if it's not the diagram you want.

Nor how running some other tool that runs a web service qualifies as "easier" than running a query using sqlite itself, and a command line tool that's trivially scriptable.

[pjmlp]

I love that it is written in SQL, people keep forgeting it isn't only for queries.

[bbkane]

I love the diagram maker, and this looks like it has fewer dependencies than https://schemaspy.org/ (which is still FANTASTIC for larger databases).

[tedivm]

Paracelsus creates diagrams in markdown or dot format for SQLAlchemy defined databases.

https://github.com/tedivm/paracelsus

[mingodad]

There is also https://github.com/ondras/wwwsqldesigner :

WWW SQL Designer, your online SQL diagramming tool

[karmakaze]

I've used this in the past and is one of my first ways of approaching a new codebase. It's great at loading a schema and letting me lay out the tables. I'll sometimes make many different subset diagrams. I hacked it a bit for working with MySQL schema and inferring 'foreign keys' by naming convention as they are often not enforced by the db schema.

[jll29]

This is a cool idea, I'm glad someone poasted it here.

However, SQLite3 on the Mac gave me:

  Error: near line 2: no such table: pragma_table_list

Somewhere it is written that pragma_table_list was only made available as of 3.16, but I am actually using

  sqlite --version
  3.35.4 2021-04-02 15:20:15 5d4c65779dab868b285519b19e4cf9d451d50c6048f06f653aa701ec212df45e

Anyone seen this?

[KAKAN]

Such an awesome find, I'm thinking of sticking this to my CI Pipeline now! :D

I use SQLite for a gameserver, having 3 different databases for different stuff. And this would be a lifesaver for others working on anything requiring the main database which has a lot of relations, thanks to normalizing it and having a lot of different but related data. Thank you for this!

[abi]

Is there a similar one for Postgres? I’d love to use it.

[thristian]

Years ago I wrote something similar for PostgreSQL. Unlike SQLite, it supports[1] the much richer "information_schema" database schema that's defined by the SQL standard. As long as you can figure out how it represents tables, columns, and primary and foreign keys, it shouldn't be too difficult to adapt this SQL query to fit. After all, reshaping relational data to extract the information you need is what SQL is for.

[1]: https://www.postgresql.org/docs/current/information-schema.h...

[ccakes]

tbls[1] is a similar tool in this space that does a great job and is a simple single binary

Covers a lot of different platforms incl Postgres

[1] https://github.com/k1LoW/tbls

[Vuizur]

You can use DBeaver to get a good diagram for almost all RDBMs.

[dmfay]

My project pdot^1 has a full-ERD mode but it's honestly less useful than the semi-interactive/contextual mode of navigating schema subgraphs in a database of any size. pdot can output mermaid and render other graphs too, like trigger cascades and grant inheritance.

^1 https://gitlab.com/dmfay/pdot

[willlma]

> Lots of database management tools include some kind of schema diagram view, either automatically generated or manually editable so you can get the layout just right. But it's usually part of a much bigger suite of tools, and sometimes I don't want to install a tool, I just want to get a basic overview quickly.

An old colleague of mine created an interactive web app that does this. We use it internally and I find it super useful. Supports SQLite, among others: https://azimutt.app/

[irreducible]

I built something similar for Prisma recently: https://github.com/irreducible-io/prisma2d2/

[wallymathieu]

I did something related to this https://github.com/wallymathieu/mejram Main reason I did it was that I've worked on some old databases that do not have a nice normalised schema. Some of the foreign keys have been missing. Using dot-render can give you nicer graphs compared to some of the built in tools like SQL Server Management Studio.

[eigenvalue]

Cool, I recently made a similar tool for generating diagrams like that for SQLalchemy data models. Can definitely be useful for understanding a complex schema.