SQLite - ATTACH Database

SQLite - Attaching Databases

The ATTACH DATABASE statement allows you to work with multiple database files within a single database connection. This is incredibly useful for tasks like migrating data, querying across different datasets, or accessing an archive.

Syntax

The syntax requires you to provide the path to the database file and an alias to refer to it.

ATTACH DATABASE 'path/to/database.db' AS AliasName;

After this command, you can access tables in the attached database by prefixing the table name with the alias (e.g., AliasName.table_name). The original database is referred to by the alias main.

Practical Example: Querying an Archive

Imagine you have current_sales.db for this year’s sales and archive_sales.db for all previous years. You want to find the total sales for a specific product across both databases.

First, connect to your primary database:

$ sqlite3 current_sales.db

Next, attach the archive database:

sqlite> ATTACH DATABASE 'archive_sales.db' AS archive;

You can verify the attached databases with the .databases command.

sqlite> .databases
seq  name       file
---  ---------  -----------------------
0    main       /path/to/current_sales.db
2    archive    /path/to/archive_sales.db

Now you can run a query that combines data from both. Assuming both have a sales table with product_id and amount columns:

sqlite> SELECT SUM(total) FROM (
   ...>    SELECT SUM(amount) AS total FROM main.sales WHERE product_id = 123
   ...>    UNION ALL
   ...>    SELECT SUM(amount) AS total FROM archive.sales WHERE product_id = 123
   ...> );

Detaching a Database

Once you are finished, you can detach a database using its alias.

sqlite> DETACH DATABASE archive;

Important Note on Reserved Names

The database names main and temp are reserved. main refers to the primary database you connected to, and temp is used for temporary tables. You cannot attach a database using these aliases.

sqlite> ATTACH DATABASE 'test.db' AS main;
Error: database main is already in use