SQLite Schema Diagram Generator

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!

(offtopic warning)

A properly normalised database can wind up with a lot of small tables connected by a complex network of foreign key references

I think the last time I properly normalized a database was at a university. Avoidng lots of small tables and complex networks would be the main reason.

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

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

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.

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.

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

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

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.

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;

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

WWW SQL Designer, your online SQL diagramming tool

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.

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/

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.

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

https://github.com/tedivm/paracelsus

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

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

This seems really dangerous to use given it's AGPL license. IANAL but besides the inherent infectious nature of the .sql file itself, wouldn't the output .svg (or whatever) files that you produce by running this code _also_ be AGPL licensed?

Also checkout https://www.schemacrawler.com/

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.

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

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.

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!

I was just looking for something like this. Ended up using DbVisualiser, which is far too heavy and complex for the simple task I wanted it for. This looks much neater.