Share on

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.

Basic usage

The basic command needed to export the database schema from SQLite looks like this:

sqlite3 DATABASE_FILE.sqlite '.schema' > schema.sql

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:

sqlite DATABASE_FILE.sqlite

Next, set the output so that command results are sent to a file instead of displayed:

.output schema.sql

Finally, output the schema by typing:

.schema

You can now optionally change the output back to standard out by typing:

.output

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 .schema:

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.

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.

About the Author(s)
Justin Ellingwood

Justin Ellingwood

Justin has been writing about databases, Linux, infrastructure, and developer tools since 2013. He currently lives in Berlin with his wife and two rabbits. He doesn't usually have to write in the third person, which is a relief for all parties involved.