[HN Gopher] SQLite Schema Diagram Generator
___________________________________________________________________
SQLite Schema Diagram Generator
Author : tempodox
Score : 463 points
Date : 2024-03-23 07:20 UTC (1 days ago)
(HTM) web link (gitlab.com)
(TXT) w3m dump (gitlab.com)
| idoubtit wrote:
| 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 wrote:
| Regardless, the implementation in 128 lines of SQL combined
| with graphviz is cool.
| vidarh wrote:
| And the sql is mostly comments. The actual SQL is ~50 lines
| with plenty of whitespace.
| alexvoda wrote:
| 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.
| bbkane wrote:
| 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
| sbuttgereit wrote:
| > ER diagrams that only go one or two relationships out
|
| 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.
| rplnt wrote:
| (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.
| ThalesX wrote:
| Huh. Just these days I'm building an MVP and was thinking that
| denormalized would probably be better. But I just can't give
| normalization up.
| petepete wrote:
| Don't. The benefits far outweigh the costs until you reach
| enormous scale.
| krisoft wrote:
| Your message is ambigous. One can read it as "don't give up
| on normalisation" but one can also read it as "don't
| normalise". Which of the meanings did you intend?
| ThalesX wrote:
| Theoretically you gain denormalization benefits at scale
| so I'm reading it as arguing for normalization.
| alexvoda wrote:
| What exactly do you mean by scale?
|
| 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.
| Hendrikto wrote:
| > But I just can't give normalization up.
|
| > Don't.
| MurrayHill1980 wrote:
| I learned more from YouTube videos on database normal
| forms and useful problems they solve than I did in grad
| school.
| lovasoa wrote:
| 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 wrote:
| 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.
| lovasoa wrote:
| Indeed, it's not the same thing, but it's comparable. This
| lets you quickly navigate the tables in your SQLite database.
| lgas wrote:
| If quickly navigating the tables is your goal VisiData
| would probably be a better option.
| littlecranky67 wrote:
| Love this! 5mins after visiting the page it is built into my
| gitlab CI pipeline :)
| gchaincl wrote:
| curious to know what are you using it for? do you upload a
| diagram on every push?
| littlecranky67 wrote:
| no, in my case only when the Migrations/ folder changes (you
| can specify that in .gitlab-ci.yml or using come commandline-
| fu). I'm using EF core as an ORM, thats why it is also easy
| to create an empty SQLite DB from the sources.
| franga2000 wrote:
| 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
| littlecranky67 wrote:
| Haha, I'm abusing gitlab pipeline minutes to run a periodical
| cypress task to test signup+login in production on my pet-
| project :) Scheduled pipelines for the win!
| thristian wrote:
| 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 wrote:
| 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.
| vsnf wrote:
| It also adds a severe amount of visual clutter between the
| code. Individual preference I'm sure, but I'd prefer less
| comments, I think. Or maybe my IDE just needs to collapse
| comments inside functions automatically.
| thristian wrote:
| If this were commonly-used code at the core of a project, I
| would definitely agree. However, this is the kind of thing
| that gets stuck into a CI pipeline and not looked at for
| months or years, and it's written in two languages
| (GraphViz and very SQLite-specific SQL) that probably most
| people don't use regularly. I gave it the comments I'll
| wish it had the next time I come back to it, to figure out
| how it works.
| keepamovin wrote:
| 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. :)
| thristian wrote:
| It's such a small script, most of the repository is
| documentation, so I expected (hoped) it would get copied to a
| lot of places. It's fine. :)
| ncruces wrote:
| 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.
| vanous wrote:
| My opinion will probably not be popular, but by making the
| mirror you are helping with creating this monopoly.
|
| 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...
| ncruces wrote:
| It's not unpopular: I know. I mean, I mentioned the
| monopoly in my comment for a reason.
|
| 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.
| thristian wrote:
| Somebody upthread[1] also made a GitHub mirror. I appreciate
| that different people have different comfort-levels with the
| centralisation of services like GitHub, but luckily it's
| really easy for people to copy a Git repo to a host they're
| more comfortable with, like GitHub or SourceHut or even
| making a local clone.
|
| [1]: https://news.ycombinator.com/item?id=39800533
| ncruces wrote:
| I think I did it first... or I just missed it.
|
| 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.
| NortySpock wrote:
| 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
| thristian wrote:
| I have not considered outputting to MermaidJS, but (from a
| quick glance at that documentation) it looks like the same
| "SQL template" technique should work. Actually doing it is
| left as an exercise for the reader. :)
| _ache_ wrote:
| Thank you. Interesting little tool.
| tejtm wrote:
| 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
| meitham wrote:
| 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.
| pjmlp wrote:
| I love that it is written in SQL, people keep forgeting it isn't
| only for queries.
| Hendrikto wrote:
| 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.
| abi wrote:
| Is there a similar one for Postgres? I'd love to use it.
| Vuizur wrote:
| You can use DBeaver to get a good diagram for almost all RDBMs.
| ccakes wrote:
| 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
| thristian wrote:
| 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...
| dmfay wrote:
| 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
| gcanyon wrote:
| 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 wrote:
| I'd love to see it!
| gcanyon wrote:
| Okay, I'll package it up and post it.
| codetrotter wrote:
| Thank you
| willlma wrote:
| Check out https://azimutt.app/
| mrbuttons454 wrote:
| Yes please!
| gcanyon wrote:
| Okay, I'll package it up and post it.
| jll29 wrote:
| 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?
| codetrotter wrote:
| Are you using SQLite that ships with macOS, or SQLite installed
| from homebrew?
|
| 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/
| pmarreck wrote:
| run `which sqlite`
| martinsnow wrote:
| Install a newer version
| zoomablemind wrote:
| Had this error with v3.33.
|
| Does work with sqlite3 v3.40 and likely higher too.
| thristian wrote:
| Thanks for looking into it!
|
| It turns out that 3.37.0 is the version that added the
| `table_list` pragma. I've added that requirement to the
| README.
| thristian wrote:
| I have not seen that, and that's very puzzling.
|
| 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 e876e51a0
| ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1
| (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
|
| EDIT: The ability to use pragmas as table-valued functions was
| added in version 3.16.0[1], but the table_list pragma was first
| added in 3.37.0[2], which is newer than your sqlite3 version.
|
| [1]: https://sqlite.org/changes.html#version_3_16_0
|
| [2]: https://sqlite.org/changes.html#version_3_37_0
| bbkane wrote:
| 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 wrote:
| Paracelsus creates diagrams in markdown or dot format for
| SQLAlchemy defined databases.
|
| https://github.com/tedivm/paracelsus
| andrewl wrote:
| 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.
| chiph wrote:
| 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 wrote:
| 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.
| eigenvalue wrote:
| 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.
| KAKAN wrote:
| 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!
| irreducible wrote:
| I built something similar for Prisma recently:
| https://github.com/irreducible-io/prisma2d2/
| mingodad wrote:
| There is also https://github.com/ondras/wwwsqldesigner :
|
| WWW SQL Designer, your online SQL diagramming tool
| karmakaze wrote:
| 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.
| eloh wrote:
| Also checkout https://www.schemacrawler.com/
| zoomablemind wrote:
| 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 wrote:
| 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 wrote:
| I have pushed an update which should fix this issue.
| zoomablemind wrote:
| Just tested the fix on the Fossil's db. The arrows are
| displayed correctly. Thanks!
| willlma wrote:
| > 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/
| gmbuell wrote:
| 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?
| teddyh wrote:
| AGPL, nor any other GPL variant, does _not_ cover the output of
| programs. Don't fall for the FUD, and stop spreading it.
| wallymathieu wrote:
| 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.
| whartung wrote:
| 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.
___________________________________________________________________
(page generated 2024-03-24 23:01 UTC)