Share on


This document describes how you can export data from and import data into a SQLite database. You can learn more about this topic in the official SQLite docs.

Data export with sqlite3

sqlite3 is a native SQLite command line utility you can use for various workflows across your SQLite database. To see all the options for this command, run:

sqlite3 --help

Exporting data is often done with the .dump command within the sqlite3 prompt.

To export data, you need to enter the sqlite3 prompt and point it to the location of your SQLite database file (this will sometimes have a .db suffix):

sqlite3 ./dev.db

Once you're in the prompt, you can export data as follows:

.output ./backup.sql

Alternatively, you can export a specific table by adding the table name after the .dump command in the prompt. For example the following command only dumps the users table:

.output ./backup_users.sql
.dump users

If you want to exclude all data and only export the database schema (DDL), you can use .schema instead of .dump:

.output ./backup_schema.sql

Importing data from SQL files

After having used the .dump command inside the sqlite3 prompt to export your SQLite database as an SQL file, you can restore the state of the database by feeding the SQL file back into sqlite3 using the .read command.

Before you can use the .read command, you need to enter the sqlite3 prompt and point it to your SQLite database file:

sqlite3 ./restore.db

Now you can import the data from your SQL files as follows:

.read ./backup.sql


Exporting data from SQLite and ingesting it again to recreate your data structures and populate databases is a good way to migrate data, as well as back up and recover. Understanding how the .dump and .read commands work together to accomplish this task will help you transfer data across the boundaries of your databases.

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.