SQLite Attach Database

Ray Ferrell

September 15, 2023

SQLite attach database allows you to attach one or more database files to a database connection in addition to the main database. This allows you to access multiple databases through one database connection.

Syntax of SQLite Attach Database

The syntax for attaching a database is:

ATTACH DATABASE 'filename' AS 'alias';

filename – The path and name of the database file to attach. This follows the same rules as the filename argument to sqlite3_open().
alias – The alias or name to use for accessing the attached database.

For example:

ATTACH DATABASE 'data.db' AS 'data';

This would attach the data.db file and allow accessing it using the data alias.

Accessing Attached Databases

Once a database is attached, you can access its tables by prefixing the table name with the alias:

SELECT * FROM data.table1;

If a table name is unique across all attached databases, you can access it without the alias prefix.

Detaching Databases

To detach an attached database, use the DETACH DATABASE statement:

DETACH DATABASE 'alias';

Detached databases are no longer accessible.

Notes on SQLite Attach Database

The main and temp aliases are reserved and cannot be attached or detached.
Attached databases are automatically detached when a connection is closed.
Transactions involving multiple attached databases are atomic, assuming main is not an in-memory database and not in WAL mode.
There is a limit on the maximum number of attached databases, configurable using sqlite3_limit() and SQLITE_LIMIT_ATTACHED.

Use Cases of SQlite Attach Database

There are several useful cases for attaching SQLite databases:

If you have multiple databases with related data, attaching allows seamless querying across them as if they were one database. This can simplify access without having to open multiple connections.

Test Schemas or Queries

Attach a separate test database and safely try out schema changes or complex queries before modifying the main database. The test database can then be detached without affecting the main data.

Import or Copy Data Between Databases

Attaching a database provides a simple way to import or copy data between SQLite databases. SELECT queries with INSERT can transfer tables or selected data from one attached database to another.

Temporary Storage

Attach a database to store ephemeral data like sessions or caching temporarily. The temporary database can be detached and discarded easily.

Database Backups

An existing database can be attached to access older versions of data. By attaching backups as read-only, you can query historical data.

Database Migrations

When doing major schema changes, attach the old database to gradually migrate the data to the new schema on the main database.

Conclusion

The ATTACH DATABASE statement is a very powerful SQLite feature. It enables working with multiple databases through a single connection for easier access and atomic, cross-database transactions.

Common use cases are accessing related data, testing queries safely, data import/export, temporary storage, and database migrations. But it can support many other creative uses as well. The ability to attach and detach databases provides tremendous flexibility.