How to Create ER Diagrams for SQLite with a Free Tool

A SQLite file arrives with thirty tables in it, and .schema prints them one after another in the order they were created, with the foreign keys buried inside the CREATE TABLE text. An ER diagram turns that text into a picture: every table a box, every foreign key a line between two columns, on a canvas you can rearrange. DbSchema reads the file and draws that diagram.

What the SQLite file records for the diagram

The lines of an ER diagram come from the REFERENCES clauses in the schema, and SQLite records those clauses whether or not it acts on them: PRAGMA foreign_keys is off by default and has to be set for each connection, as the SQLite foreign key documentation states. Three tables of a music library show what that means, where tracks.album_id was written without its clause:

CREATE TABLE artists (
  artist_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL
);

CREATE TABLE albums (
  album_id  INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  artist_id INTEGER REFERENCES artists (artist_id)
);

CREATE TABLE tracks (
  track_id INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  album_id INTEGER
);

pragma_foreign_key_list returns the foreign keys of one table, and joined to the table list in sqlite_master it returns every line the diagram will draw:

SELECT m.name AS child_table, f."from" AS child_column,
       f."table" AS parent_table, f."to" AS parent_column
FROM sqlite_master AS m
JOIN pragma_foreign_key_list(m.name) AS f
WHERE m.type = 'table'
ORDER BY m.name;

On SQLite 3.50.4 it returns one row:

tracks is missing from the result, so no line will join it to albums. A pair of tables that should be joined by a line and is not tells you the clause was never written, which a run of CREATE TABLE statements hides. The list answers which table points at which, but reading it is a poor way to learn a database you did not write, and it gets worse as the schema grows.

Draw the diagram in DbSchema

1. Open DbSchema and choose Connect to Database on the Welcome Screen.
2. Pick SQLite from the list of database types, which opens the Connection Dialog set up for SQLite.
3. Give DbSchema the path to your .db file, click Test Connection to check that the file opens, then Connect.
4. DbSchema reverse-engineers the tables, columns, indexes and foreign keys, and draws them.
5. Choose Auto Arrange Tables from the Diagram menu, which lays the boxes out by their foreign keys.

Connecting, reverse-engineering and the diagram are in the free Community Edition. Picking SQLite is all the driver setup there is, because DbSchema downloads the right JDBC driver from its own repository, as the Driver Manager page describes. The read builds DbSchema's own design model, an XML .dbs file independent of the database, and nothing is written back to the SQLite file.

View → Diagram Display → Column Data Type puts the data type next to each column name. It is worth turning on for a SQLite schema, where the declared types are the only record of what the columns were meant to hold.

Connecting DbSchema to SQLite for the first time is covered step by step on the SQLite page.

Design a new SQLite schema from scratch

Design from Scratch on the Welcome Screen opens a project with no connection behind it. Saving it to a file needs Pro, which the trial in every new installation covers.

Right-click the canvas and choose Create Table, then double-click the table header to open the table dialog, where you fill in the columns, their types, the primary key and any indexes. To relate two tables, drag a column in the child table onto the column it references in the parent, and DbSchema draws the foreign key line on the diagram.

Everything you have built so far lives in the .dbs model file and in no database at all. Database → Create or Upgrade Model in the Database is the step that changes that: DbSchema connects, generates the DDL for what the model holds, shows it to you, and executes it when you click Execute.

For a longer walkthrough, read how to design a schema visually.

Hand the diagram to somebody else

File → Export As... → HTML5/PDF Documentation opens the documentation dialog, and DbSchema writes the schema out as HTML5, PDF or Markdown. The HTML5 output opens in any browser with no server behind it, carries the diagram as a vector image, and gives you a searchable table list with the full column details behind each table.

Anything you type into the Comment box of a table or column travels with it, into every documentation format and, in the HTML5 output, into a mouse-over tooltip on the table or column name. Generating documentation is a Pro feature.

Download DbSchema at https://dbschema.com/download.html, connect to your SQLite file, and run Auto Arrange Tables on what comes back. Connecting, reverse engineering and the diagram are in the free Community Edition, and saving the model to a file, documentation and synchronization are in Pro, with a 15-day trial in every new installation.