7 ms·
SQLite Schema Diagram Generator
- idoubtit 3y agoI 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.
- deleted 3y ago[deleted]
- olejorgenb 3y agoRegardless, the implementation in 128 lines of SQL combined with graphviz is cool.
- vidarh 3y agoAnd the sql is mostly comments. The actual SQL is ~50 lines with plenty of whitespace.
- alexvoda 3y agoAs 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 3y agoYou might try https://schemaspy.org/ 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 3y ago> 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 3y ago(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 3y agoHuh. 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 3y agoDon't. The benefits far outweigh the costs until you reach enormous scale.
- krisoft 3y agoYour 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 3y agoTheoretically you gain denormalization benefits at scale so I’m reading it as arguing for normalization.
- alexvoda 3y agoWhat 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.
- lovasoa 3y agoYou 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://sql.ophir.dev (https://github.com/lovasoa/SQLpage#sqlpage https://github.com/lovasoa/SQLpage#sqlpage)
- vidarh 3y agoI 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.
- littlecranky67 3y agoLove this! 5mins after visiting the page it is built into my gitlab CI pipeline :)
- gchaincl 3y agocurious to know what are you using it for? do you upload a diagram on every push?
- littlecranky67 3y agono, 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 3y agoThanks 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 3y agoHaha, 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 3y agoAuthor 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!
- meitham 3y agoThe 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.
- alexvoda 3y agoReally 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.
- deleted 3y ago[deleted]
- vsnf 3y agoIt 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 3y agoIf 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 3y agoThis 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 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. :)
- pjmlp 3y agoI love that it is written in SQL, people keep forgeting it isn't only for queries.
- ramses0 3y ago[flagged]
- deleted 3y ago[deleted]
- Hendrikto 3y agoI 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 3y agoIs there a similar one for Postgres? I’d love to use it.
- Vuizur 3y agoYou can use DBeaver to get a good diagram for almost all RDBMs.
- ccakes 3y agotbls[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 https://github.com/k1LoW/tbls
- thristian 3y agoYears 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.html https://www.postgresql.org/docs/current/information-schema.h...
- dmfay 3y agoMy 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 https://gitlab.com/dmfay/pdot
- gcanyon 3y agoI 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 3y agoI’d love to see it!
- gcanyon 3y agoOkay, I'll package it up and post it.
- codetrotter 3y agoThank you
- willlma 3y agoCheck out https://azimutt.app/ https://azimutt.app/
- tempaccount1234 3y agoAzimutt is an online-only, non free tool. If you don’t want to publish your design or need to analyze someone else’s design, this is a no-go for me. (Skimming it it promises to be able to work only in the browser if you want to, personally I wouldn’t risk it) Check out https://schemaspy.org/ https://schemaspy.org/, which creates a documentation locally, if the original project here doesn’t work for you.
- jll29 3y agoThis 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?
- martinsnow 3y agoInstall a newer version
- codetrotter 3y agoAre 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/ https://brew.sh/
- pmarreck 3y agorun `which sqlite`
- zoomablemind 3y agoHad this error with v3.33. Does work with sqlite3 v3.40 and likely higher too.
- thristian 3y agoThanks 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 3y agoI 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 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 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 https://sqlite.org/changes.html#version_3_16_0 [2]: https://sqlite.org/changes.html#version_3_37_0 https://sqlite.org/changes.html#version_3_37_0
- bbkane 3y agoI love the diagram maker, and this looks like it has fewer dependencies than https://schemaspy.org/ https://schemaspy.org/ (which is still FANTASTIC for larger databases).
- tedivm 3y agoParacelsus creates diagrams in markdown or dot format for SQLAlchemy defined databases. https://github.com/tedivm/paracelsus https://github.com/tedivm/paracelsus
- andrewl 3y agoThis 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 3y agoA 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 3y agoSo 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.
- yencabulator 3y ago> So far as I can tell, GraphViz does not allow you to specify a paper size. Set the size of the graph in inches: https://www.graphviz.org/docs/attrs/size/ https://www.graphviz.org/docs/attrs/size/
- eigenvalue 3y agoCool, 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 3y agoSuch 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 3y agoI built something similar for Prisma recently: https://github.com/irreducible-io/prisma2d2/ https://github.com/irreducible-io/prisma2d2/
- mingodad 3y agoThere is also https://github.com/ondras/wwwsqldesigner https://github.com/ondras/wwwsqldesigner : WWW SQL Designer, your online SQL diagramming tool
- karmakaze 3y agoI'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 3y agoAlso checkout https://www.schemacrawler.com/ https://www.schemacrawler.com/
- zoomablemind 3y agoTried 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 3y agoHuh. 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 https://sqlite.org/syntax/foreign-key-clause.html [2]: https://sqlite.org/foreignkeys.html https://sqlite.org/foreignkeys.html
- thristian 3y agoI have pushed an update which should fix this issue.
- zoomablemind 3y agoJust tested the fix on the Fossil's db. The arrows are displayed correctly. Thanks!
- willlma 3y ago> 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/ https://azimutt.app/
- gmbuell 3y agoThis 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 3y agoAGPL, nor any other GPL variant, does not cover the output of programs. Don’t fall for the FUD, and stop spreading it.
- gmbuell 3y agoGoogle (as in, the corporation, not the search results) says anything derived from AGPL code may be covered by the AGPL license. It's enough of a threat that internal use of AGPL licensed tools is forbidden at both Google and Apple and probably others. Source: https://opensource.google/documentation/reference/using/agpl-policy https://opensource.google/documentation/reference/using/agpl...
- teddyh 3y agoProgram output is not derived from the source code of the program. Google and other lockdownopolists being weird about AGPL is well-known.
- wallymathieu 3y agoI did something related to this https://github.com/wallymathieu/mejram 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 3y agoWhat 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.
- aabbcc1241 3y agoI made a similar FOSS [1] that can generate plain text erd from a live database, and also a visualizer based on the plain text. It supports sqlite, mysql, mssql, postgres. And the visualizer is hosted on a CDN without login requirement. [1] https://quick-erd.surge.sh https://quick-erd.surge.sh