SQLite
How to export database and table schemas in SQLite
Introduction
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.
You can use Prisma Client to manage PostgreSQL databases from within your JavaScript or TypeScript applications. To learn how to use Prisma with SQLite, check out Prisma's SQLite database connector page.
Basic usage
The basic command needed to export the database schema from SQLite looks like this:
Here, the 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.
Executing interactively
The above command can be executed from the command line. You can perform this same procedure interactively within the sqlite3 shell.
First, open the SQLite database file with the sqlite3 command:
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:
For example, if you have a database file called sales.sql and want to export only the VENDORS database, you can type:
Export specific tables
You can also export specific tables by including the table name after .schema:
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:
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:
Note that the .fullschema command does not allow you to filter by table name.
Conclusion
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.
You can use Prisma Client to manage PostgreSQL databases from within your JavaScript or TypeScript applications. To learn how to use Prisma with SQLite, check out Prisma's SQLite database connector page.
