Importing and exporting data in SQLite

Overview

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
.dump
.exit

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
.exit

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

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
.exit

Conclusion

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.