# How to export database and table schemas in SQLite

> Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt.

Learn how to export your database objects from SQLite for analysis, migrating, backups, and more.

Canonical URL: https://www.prisma.io/dataguide/sqlite/exporting-schemas
Authors: Justin Ellingwood
Content revision: d63a2962984e4d7c087fa26169ff1009afa41a14fcb2bf7e1baeeeb77cc2fed6
Source revision: 1fe7ef3d9287b7c3aadd4cd0d2fe478dafd71727

## Introduction

In relational databases, the [database schema](https://www.prisma.io/dataguide/intro/database-glossary#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:

```command
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:

```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:

```command
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:

```command
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`:

```command
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](https://sqlite.org/lang_expr.html#like) 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:

```command
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:

```command
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.