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. DbSchema reads the same file and draws it: every table a box, every foreign key a line between two columns, laid out on a canvas you can rearrange. Connecting to the database and getting that diagram are in the free Community Edition.

What an ER diagram shows

An entity relationship diagram puts four things on one canvas: the tables, the columns inside them with their data types, the primary and foreign keys, and the lines that connect a child table to its parent. Reading a schema this way answers questions that a list of CREATE TABLE statements makes you work for: which table sits at the center of everything, which tables nothing points at, and where a relationship you expected is missing because nobody ever wrote the REFERENCES clause.

The diagram is also the thing you hand to somebody else. A colleague who has never opened the database can follow it, which is harder to say about a DDL dump.

Turn an existing SQLite database into a diagram

DbSchema starts at the Welcome Screen. Choose Connect to a Database, pick SQLite from the list of database types, and DbSchema opens the Connection Dialog set up for SQLite. Give DbSchema the path to your .db file, click Test Connection to check that the file opens, then Connect. 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.

What happens next is a read. DbSchema reverse-engineers the tables, columns, indexes and foreign keys out of the file and builds them into its own design model, an XML .dbs file that lives beside your code and is independent of the database. Nothing is written back to the SQLite file by this step.

A schema of any size arrives with its tables on top of each other, so select everything with Ctrl+A and use Diagram → Auto Arrange, which lays the boxes out with a graph algorithm. Drag the boxes afterwards to put the tables you care about together. Turning on Show Column Types from the Diagram menu adds the data type next to each column name, which is worth doing on 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

The other direction starts with an empty canvas. Design from Scratch on the Welcome Screen opens a project with no connection behind it, and that page marks it as a Pro feature. Every new installation of DbSchema comes with a 15-day trial of all Pro features and no credit card to enter.

Right-click the canvas and choose New Table, then double-click the new table header and DbSchema opens 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 made so far lives in the .dbs model file and in no database at all. Schema → Create or Upgrade Schema in 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 of designing a schema this way, read how to design a schema visually, or watch How to Design a Database: Conceptual, Logical and Physical Explained.

What you can do with the diagram

Split a large schema across several diagrams

One canvas per subsystem beats one canvas with everything on it. A DbSchema project holds as many diagrams as you want, added from the Diagram menu or from the plus tab above the canvas, and the same table can appear on several of them. Each diagram keeps its own layout and its own list of visible tables, while the schema underneath is shared, so a column you add on one diagram is the same column everywhere.

Generate documentation people can click through

Diagram → Export HTML5 or 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. For a slide or a ticket, File → Export has DbSchema write the current diagram out as a PNG or an SVG image. Generating documentation is a Pro feature.

Write down why a table looks the way it does

Each table and column has a Description field, and what you type there travels: it appears in every documentation format DbSchema generates, and in the HTML5 output it also shows as a mouse-over tooltip on the table or column name. Floating notes and callouts go on the canvas itself from the Insert menu, next to the tables they explain, and they are saved in the project file.

Keep the model and the database in step

A database and a diagram drift apart the moment somebody runs a migration. Schema → Compare Model with Database has DbSchema list what differs, table by table and column by column, and for each difference you choose whether to update the model or push the change to the database. Schema → Synchronize Model with Database turns the differences you accepted into a SQL script, which DbSchema shows you before anything runs and executes when you click Execute. Schema synchronization is a Pro feature.

Put the design in Git

The .dbs file DbSchema saves is XML, so a diff over it reads like a diff over code, and it belongs in the repository next to the application that uses the database. DbSchema has a Git dialog of its own, opened from the Model menu with Git — Collaborative Design, where you clone a repository, then stage, commit and push the model file without leaving the application. Saving the design to a .dbs file is a Pro feature.

Build a query by clicking on the diagram

Click a table header and DbSchema opens the Query Builder with that table loaded. Click the small arrow beside a column to follow a foreign key and DbSchema adds the related table and the join between them. The label on the connecting line switches the join between INNER JOIN, LEFT JOIN and EXISTS. Ticking a column adds it to the SELECT list. Right-clicking a column gives you a Filter for the WHERE clause, or an Aggregate such as SUM or COUNT once Group By mode is on. The SQL appears at the bottom of the builder and changes as you click. The Query Builder is a Pro feature.

Why use DbSchema for SQLite

SQLite has no server and no catalog browser of its own, so whatever you use to look at the file is the only view you get. What DbSchema adds, and which edition covers it:

Reading table definitions is a poor way to learn a database you did not write, and it gets worse as the schema grows. Point DbSchema at the file and the structure is in front of you in a minute. Download DbSchema at https://dbschema.com/download.html, connect to your SQLite file, and run Auto Arrange on what comes back: connecting, reverse engineering and the diagram are in the free Community Edition, and documentation, synchronization and the Query Builder are in Pro, with a 15-day trial in every new installation.