Have you ever opened the SQL script you used to build a SQLite database and wondered how it got so long? I like building databases with SQL scripts, but after a few rounds of changes, I might have one CREATE TABLE statement followed by three ALTER TABLE statements. Add a few views and a growing list of INSERT statements, and it takes some digging to see what the database looks like today.

DB Browser for SQLite can generate a fresh SQL script from the database you already have. The export gives you the current table definitions, with those added columns already in place. You can also include the data, so the same script can recreate the database in a new file. That’s what I mean by a full SQL dump.

Export the database to a SQL file

1. Open your database in DB Browser for SQLite and save any pending changes.
2. Go to File > Export > Database to SQL file…
3. Choose Select All to export the whole database.
4. Leave Export schema only unchecked so the export includes the data.
5. Save the file as database_export.sql.

Open the exported file in a text editor and take a look. You’ll see the SQL that defines your database and the INSERT statements that load its rows. If you’re cleaning up a small set of seed data by hand, you can also combine several inserts into one INSERT with multiple rows in the VALUES list.

Rebuild the database in a new file

1. Click New Database and give the new file a name such as database_test.sqlite. If DB Browser opens the Edit table definition dialog, cancel it. Your script will create the tables.
2. Go to the Execute SQL tab, open database_export.sql, and run the whole script.
3. Check the execution log for errors and click Write Changes if the button is enabled.
4. In Database Structure, confirm that the tables and views you expect are present. In Browse Data, compare a few key tables and their row counts with the original.

When does the order of inserts matter?

Suppose you have an Organizations table, an Initiatives table, and a linking table called Organization_Initiative_Link. Each row in the linking table refers to one organization and one initiative. If foreign keys are enabled and checked immediately, both referenced rows have to exist before SQLite will accept the linking row.

That’s something to watch for if you rearrange the exported INSERT statements. Follow the foreign-key relationships when deciding which rows to load first. After the rebuild, run this in the Execute SQL tab:

PRAGMA foreign_key_check;

If the check runs successfully and returns no rows, SQLite found no violations of the foreign keys defined in the database.

What if you only want the table definitions?

Repeat the export with Export schema only checked and save it as schema.sql. I find this handy when I want a clean script for creating an empty database. I can keep a small set of sample rows in a separate seed.sql file, run the schema first, and load the sample data when I need it.