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.
- Choose File → New Model, keep Connect to Database selected, and click SQLite.
- Choose New Connection on the right, and press Connect to SQLite.
- In the File field, press Choose... and pick your file. For a new database, press Create New instead.
- Press Connect. In Reverse Engineer the Database, press Load.
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:
- Click an empty spot on the diagram, and choose Table → Create Table in the toolbar, or press Ctrl+T.
- Type
paymentsin Table Name. In the Columns tab, press Add for each column. In the column dialog ofid, tick Primary Key. - In the Check Constraints tab, press Add and type
method IN ('card', 'cash'). - Press OK. DbSchema creates the table in the file.
- Drag
payments.order_idontoorders.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.
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:
- Drag
orders.customer_idontocustomers.id. - 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.
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:
| id | customer_id |
|---|---|
| 12 | 3 |
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:
| id | customer_id | total |
|---|---|---|
| 10 | 1 | 250 |
| 11 | 2 | 90 |
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.
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.
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
customersand 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.