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 tosqlite3_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
mainandtempaliases 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
mainis 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()andSQLITE_LIMIT_ATTACHED.
Use Cases of SQlite Attach Database
There are several useful cases for attaching SQLite databases:
Access Multiple Related 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.
