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. :)

https://github.com/TomConlin/SQLiteViz

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

Very interesting approach.

I see the solution in creating small single/few page(s) landing site and linking to the code and releases, being it to self/hosted Gitea, Forgejo, Gitlab, GitHub...

But for this niche purpose, GitHub is my (last) social media, and GitHub stars are my bookmarks.

So, yeah, I agree, but your suggestion does little for me to not forget it when I'm looking for something SQLite related, and definitely doesn't help me follow project updates (like a proper GitHub mirror would).

I'm sorry.

[1]: https://news.ycombinator.com/item?id=39800533

Well, as long as you're not brothered with it, great! I know I'm not adding much value, I just wanted to “bookmark it” for myself.

Have you considered outputting to a MermaidJS format?

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

@thristian - can you specify a paper size?

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

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/

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.

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.

I think I can fix this.

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

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

WWW SQL Designer, your online SQL diagramming tool

    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;

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.

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.

Actually, SchemaSpy gives you a full diagram of the entire schema as well: it gives it to you with a truncated columns list and a full columns list per table. The "Relationships" option at the top of the page is where the full diagram is accessed.

The one & two relations out limited views are if you're getting to the diagram from the scope of a specific table... it will show you one and two relations away from the current table when using that perspective. And, as you say, you can navigate the relationships that way.

What I really like about SchemaSpy (I use it with PostgreSQL) is that I can `COMMENT ON` database objects like tables and columns using markdown and SchemaSpy will render the Markdown in it's output. Simple markdown still looks decent when viewed from something like psql, too, so it's a nice way to have documentation carried with the database.

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

https://github.com/tedivm/paracelsus

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!

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 had a different problem in the past with the SQLite that ships with macOS, and have been using SQLite from homebrew since.

So if it’s the one that comes with macOS that gives you this problem that you are having, try using SQLite from homebrew instead.

https://brew.sh/

What output do you get when you run these commands?

    $ sqlite3 --version
    -- Loading resources from /home/st/.sqliterc
    3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit)
    $ sqlite3 :memory: -init /dev/null "select * from pragma_table_list();"
    -- Loading resources from /dev/null
    main|sqlite_schema|table|5|0|0
    temp|sqlite_temp_schema|table|5|0|0

[1]: https://sqlite.org/changes.html#version_3_16_0

[2]: https://sqlite.org/changes.html#version_3_37_0

Does work with sqlite3 v3.40 and likely higher too.

It turns out that 3.37.0 is the version that added the `table_list` pragma. I've added that requirement to the README.

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

Covers a lot of different platforms incl Postgres

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

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

> 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.

As long as you are doing OLTP using an RDBMS, I believe the proper way to "denormalize" is to just use materialized views and therefore sacrifice a bit of write performance in order to gain read performance. For the OLAP scenario you are ingesting data from the OLTP which is normalized therefore it's materialized views with extra steps.

If you are forced to use a document database you have to denormalise because joining is hard.

So if by scale you mean using a document database, sure, but otherwise, especially on SSDs, RDBMSs usually benefit from normalization, by having less data to read, especially if old features (by today's standards) like join elimination are implemented. Normalization also enables vertical partitioning.

There was an argument to be had about RDBMSs on HDDs because HDDs heavily favour sequential reads rather than random reads. But that was really the consequence of the RDBMS being a leaky abstraction over the hardware.

Document databases have a better scalability story but not because of denormalization. Instead it's usually because of sacrificing ACID guarantees, choosing Availability and Lower Latency over Consistency from the CAP (PACELC) theorem.

> Don't.