How to export database and table schemas in SQLite
In relational databases, the database schema defines the structure of the database and its component parts like tables, fields, and indexes. Extracting and exporting this information is useful in many scenarios, including backups, migrating to new environments, visualizing data structures, and managing these structures within a codebase.
In this short guide, we'll discuss how to export SQLite database schemas using the
sqlite3 command. The same command that you can use to manage your databases can be used to export database data and structures. We'll focus on extracting the data structures themselves in this guide.
The basic command needed to export the database schema from SQLite looks like this:
sqlite3 DATABASE_FILE.sqlite '.schema' > schema.sql
DATABASE_FILE.sqlite is the SQLite database file that contains your data and structures. The
'.schema' component is the command that tells SQLite to export the database schema without any accompanying data. The
schema.sql file is the target file that will received the exported database structures.
The above command can be executed from the command line. You can perform this same procedure interactively within the
First, open the SQLite database file with the
Next, set the output so that command results are sent to a file instead of displayed:
Finally, output the schema by typing:
You can now optionally change the output back to standard out by typing:
For the remainder of the guide, we'll use the command line to demonstrate additional functionality, but be aware that you can replicate this interactively if necessary using this method.
Exporting the schema of a specific database
To export only the schema related to a specific database, you can use wildcards to select all components that belong to the database with the following syntax:
sqlite3 DATABASE_FILE.sqlite '.schema DATABASE.*' > database_schema.sql
For example, if you have a database file called
sales.sql and want to export only the
VENDORS database, you can type:
sqlite3 sales.sqlite '.schema VENDORS.*' > vendors_db_schema.sql
Export specific tables
You can also export specific tables by including the table name after
sqlite3 DATABASE_FILE.sqlite '.schema TABLE' > table_schema.sql
An alternative to this approach is to use a wildcard match instead of the specific name. The
schema command uses LIKE pattern matching for this, which means that the percent character (
%) is used to match zero or more characters and the underscore (
_) can stand in for exactly one character.
For example, to export all of the tables that start with
inventory, you could type:
sqlite3 DATABASE_FILE.sqlite '.schema inventory%' > inventory_schemas.sql
This syntax does not allow for specifying multiple patterns or multiple specific tables at once, so may have to run multiple commands export the objects you require or dump everything and manually manipulate the exported schemas.
Include database statistics in the schema dump
You can use the
.fullschema command in place of the
.schema command to also include all of the statistics tables that SQLite uses internally to decide on query plans, etc. This can be useful information when trying to debug why a query executed in a specific way:
sqlite3 DATABASE_FILE.sqlite '.fullschema' > schema_with_statistics.sql
Note that the
.fullschema command does not allow you to filter by table name.
Being able to export your schemas allows you to save your database structures outside of the database itself. This is helpful when setting up new environments, evolving your schema as your needs change, and visualizing the structure of the information you are storing.