Back to all posts

Design and Manage SQLite Databases with a Modeling Tool

For developers who keep their data in a SQLite file and want to design and change its tables on a diagram, rather than by editing CREATE TABLE statements.

On this page

DbSchema opens a SQLite file as an ER diagram, and you design the schema on that diagram: create a table, add a column, draw a foreign key. While DbSchema is connected, each change that you confirm runs in the file at once. SQLite's ALTER TABLE can't add a foreign key to a table that already exists, so DbSchema rebuilds that table and keeps its rows. The free Community Edition connects, draws the diagram and edits the schema.

To follow the steps on the same tables, run this script in an empty SQLite file before you connect DbSchema, such as with the sqlite3 shell:

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total       INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO orders VALUES (10, 1, 250), (11, 2, 90), (12, 3, 40);

The column orders.customer_id holds customer numbers, but it declares no foreign key. Order 12 points at customer 3, who isn't in customers.

Connect DbSchema to the SQLite file

A SQLite database is one file, so the connection asks for the file and nothing else: no host, no user, no password.

  1. Choose File → New Model, keep Connect to Database selected, and click SQLite.
  2. Choose New Connection on the right, and press Connect to SQLite.
  3. In the File field, press Choose... and pick your file. For a new database, press Create New instead.
  4. Press Connect. In Reverse Engineer the Database, press Load.
The DbSchema Connect to SQLite dialog with a database file in the File field, next to the Choose... and Create New buttons

DbSchema downloads the SQLite JDBC driver on the first connection, then draws the tables of the file on a diagram. DbSchema stores what it read in a model, which is DbSchema's own copy of the schema. On the diagram, orders has no line to customers, because orders.customer_id declares no foreign key. How to Create ER Diagrams for SQLite shows how to draw such a line in the model alone.

Create a table on the diagram

SQLite has no ENUM type. In SQLite 3.50.4, CREATE TABLE t (method ENUM('card', 'cash')) fails with near "'card'": syntax error. A column that takes a few fixed values gets a CHECK constraint instead, as method does in this table:

CREATE TABLE payments (
  id       INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders (id),
  method   TEXT NOT NULL CHECK (method IN ('card', 'cash'))
);

To draw this table in DbSchema instead of typing the statement, follow the table documentation:

  1. Click an empty spot on the diagram, and choose Table → Create Table in the toolbar, or press Ctrl+T.
  2. Type payments in Table Name. In the Columns tab, press Add for each column. In the column dialog of id, tick Primary Key.
  3. In the Check Constraints tab, press Add and type method IN ('card', 'cash').
  4. Press OK. DbSchema creates the table in the file.
  5. Drag payments.order_id onto orders.id, and press OK in the New Foreign Key dialog. The table exists by now, so DbSchema adds the foreign key by rebuilding the table.
The DbSchema Edit Table dialog of payments on the Check Constraints tab, with the condition method IN ('card', 'cash')

SQLite now refuses a method outside the list:

INSERT INTO payments VALUES (1, 10, 'voucher');

SQLite 3.50.4 rejects the row with CHECK constraint failed: method IN ('card', 'cash').

Add a foreign key to a table that exists

SQLite's ALTER TABLE renames a table, and adds, renames or drops a column. Since version 3.53.0 it also sets and drops NOT NULL. Most other changes, such as adding a foreign key or changing a column's data type, need a new table. SQLite's documentation gives the steps: create the new table, copy the rows into it, drop the old table, and rename the new one.

DbSchema runs those steps for you, in one transaction, and recreates the table's indexes and triggers. For payments, the rebuild copied no rows, because the table was still empty. To give orders its missing foreign key:

  1. Drag orders.customer_id onto customers.id.
  2. Press OK in the New Foreign Key dialog.

Before DbSchema commits the rebuild, it runs SQLite's foreign_key_check on the table. Order 12 points at a customer who doesn't exist, so DbSchema rolls the rebuild back, and the file stays as it was.

The DbSchema Error From the Database dialog after the refused foreign key: the database refused one statement of the commit, and the dialog lists the statements that rebuild the orders table

To find such rows before you add the foreign key, look for orders whose customer is missing:

SELECT o.id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;

Order 12 is the only one:

idcustomer_id
123

Delete the order, or update it to a customer who exists. Here it is deleted:

DELETE FROM orders WHERE id = 12;
SELECT * FROM orders;

Two orders remain:

idcustomer_idtotal
101250
11290

The New Foreign Key dialog stays open after the refusal. Press OK in it again, and DbSchema adds the foreign key. If you change a column's data type in the column dialog, DbSchema rebuilds the table the same way.

DbSchema diagram of customers, orders and payments, with a foreign key line from orders to customers and one from payments to orders

Design offline and apply the changes later

To collect several changes and review them before they reach the file, choose Database → Offline (connect later). Your edits then change only the model. Save the model with File → Save Model to File. Saving the model needs DbSchema Pro.

Which DbSchema actions change the model, which change the SQLite file, and which change both

When you connect again, DbSchema asks how to bring the model and the file together. Choose Compare, then decide difference by difference. DbSchema lists each difference in the Synchronize Model and Database dialog, where you choose which side changes: the model or the file. Commit in the database runs the statements, and Generate SQL script writes them to a SQL Editor or a file instead. Schema synchronization needs DbSchema Pro.

Document the schema and keep the model in Git

Choose File → Export As... → HTML5/PDF Documentation, pick HTML5, PDF or Markdown, and press Generate. The HTML5 documentation is one file that opens in any browser, with the diagram, a search box and the columns of every table. In the Community Edition, DbSchema documents a diagram of up to 12 tables, with "DbSchema Trial" across the diagram image. DbSchema Pro documents a diagram of any size.

DbSchema saves the model as a .dbs file, which you can keep in a Git repository. Choose File → Git Collaboration to commit, push and pull it from DbSchema, and a colleague who pulls it opens the same diagram. A commit or a pull leaves the SQLite file unchanged. Git Collaboration needs DbSchema Pro.

Query and edit the data

The SQL Editor runs SQL in the file, in every edition: press Editor in the toolbar and choose SQL Editor, or press Ctrl+E. DbSchema Pro adds three tools that start from the diagram:

  • The Data Explorer shows and edits rows, and follows foreign keys. Right-click customers and choose Data Explorer, click Ada, and open her orders with the arrow next to the table name.
  • The Query Editor builds a SELECT with joins as you tick columns and follow foreign keys.
  • The Data Generator, in Database → Data Generator, fills tables with rows. A foreign key column takes its values from the primary key of the table it points to.

The SQL Editor, the Data Explorer and the Data Generator change the file, never the model.

Download DbSchema, connect it to your SQLite file, and drag a column onto another to add the foreign keys that the file is missing. The free Community Edition connects, draws the diagram, edits the schema and runs the SQL Editor. Saving the model, schema synchronization, Git Collaboration, the Data Explorer, the Query Editor and the Data Generator need DbSchema Pro. Every new installation starts with a 15-day Architect trial, which includes them. The same walkthrough exists for MySQL and Apache Derby.

Open your SQLite file as a diagram

DbSchema connects to a SQLite file, draws it as an ER diagram and edits the schema, in the free Community Edition. Saving the model, schema synchronization, Git, the Data Explorer, the Query Editor and the Data Generator are in DbSchema Pro.