How to Export Database and Table Schemas in SQLite?

Exporting database schemas in SQLite is an important task for database management, enabling functions like data backup, recovery, migration, and auditing.

In this article, We will go through the process of exporting database and table schemas in SQLite by understanding various examples to manage SQLite databases effectively.

How to Export Database and Table Schemas?

When working with SQLite databases, we may need to export the schema of our database, including table and index structures, without exporting the actual data. This is useful for tasks like replicating the database in another environment or visualizing the schema. SQLite provides several approaches to export database and table schemas, each suited to different requirements.

1. Export the Schemas of all Tables in a Specific Database
2. Export the Schema of a Single Table in a Database
3. Export the Schemas of Multiple Tables in a Database

Imagine we have a database called gfg, with three tables: users, courses, and instructors. Here’s what these tables look like:

CREATE TABLE users
(
    id int NOT NULL,
    name varchar2(255) NOT NULL,
    gender varchar2(10) NOT NULL
)

CREATE TABLE courses
(
    id int NOT NULL,
    name varchar2(255) NOT NULL,
    price int NOT NULL
)

CREATE TABLE instructors
(
    id int NOT NULL,
    name varchar2(255) NOT NULL,
    age int NOT NULL
)

1. Export the Schemas of all Tables in a Specific Database

To export the schema of a specific table in a SQLite database, we can use the following command:

Syntax:

sqlite3  [DATABASE_FILE.db]   '.schema'  >  [EXPORT_FILE.sql]

Paramaters:

DATABASE_FILE.db : name of our sqlite3 database file
EXPORT_FILE.sql : SQL file that will store the exported table structures.

For our example, we have assumed there is `gfg` database present in memory, and we store the exported structure into a file named `schema.sql`. The command to export the entire database is as follows:

sqlite3 gfg.db '.schema' > schema.sql

Output:

Export entire database schema

From the image above we can see that we have the table structures i.e. DDL statements of all the tables present in the `gfg` database, but it does not contain any INSERT clauses i.e. DML statements.

2. Export the Schema of a Single Table in a Database

Syntax to export the schema of particular table within a particular database file is as follows:

Syntax:

sqlite3 [DATABASE_FILE.db]  '.schema TABLE' > [EXPORT_FILE.sql]

Paramaters:

DATABASE_FILE.db : name of our sqlite3 database file
TABLE : name of the table whose schema is the be exported
EXPORT_FILE.sql : SQL file that will store the exported table structures.

For our example, we already have assumed there is `gfg` database present in memory, and we just want to export the schema of `users` table and we store the exported structure into a file named `table_schema.sql`. The command to export the only `gfg` users is as follows:

sqlite3 gfg.db '.schema users' > table_schema.sql

Output:

Export a particular table schema

3. Export the Schemas of Multiple Tables in a Database

The approach to export schema of multiple databases is not same as done above. We follows the below steps:

Step 1: Start the sqlite3 session on `gfg` database by typing sqlite3 gfg.db in our terminal. This opens an sqlite3 shell where we can write sqlite3 commands.

sqlite3 gfg.db

Step 2: Set the output of the commands to be written to a file instead of being displayed inside the shell. In this example, we are redirecting the output to be written to multiple_table_schema.sql file.

.output multiple_table_schema.sql

Step 3: Using the `sqlite_schema` table which stores the metadata of all the objects of our currently opened database.

We will export users and `courses` table schemas. Below command selects SQL(i.e. DDL) for multiple tables is as follows:

SELECT sql FROM sqlite_schema where type='table' and (name='users' or name='courses');

NOTE: The sqlite_schema table contains one row for each database object like tables, indexes, viewes, and triggers present within the schema.

Output:

Export multiple table schemas

Conclusion

Overall, exporting database schemas in SQLite is a fundamental aspect of database management, serving various purposes such as data backup, recovery, migration, and auditing. By utilizing the approaches discussed in this article, users can effectively export database and table schemas in SQLite, enabling them to replicate databases in different environments and visualize schema structures.