SQLite’s ATTACH DATABASE command lets you treat multiple independent database files as if they were a single, unified database.
-- Create first database
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE
);
INSERT INTO users (username) VALUES ('alice');
INSERT INTO users (username) VALUES ('bob');
-- Create second database
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
price REAL
);
INSERT INTO products (product_name, price) VALUES ('laptop', 1200.50);
INSERT INTO products (product_name, price) VALUES ('keyboard', 75.20);
Now, let’s attach the second database to the first. We’ll give it an alias, store, so we can refer to it.
-- Attach the second database file
ATTACH DATABASE 'store.db' AS store;
-- Now query across both databases
SELECT u.username, p.product_name
FROM users u
JOIN store.products p ON 1=1; -- A simple join to demonstrate cross-database access
This query shows alice and bob paired with each available product. The store.products syntax is key here: alias.table_name.
The primary use case is logically grouping related data that might be managed separately. Imagine a main application database and a separate logging database, or an e-commerce site where product catalogs might reside in distinct files for performance or administrative reasons. You can also use it for data migration or complex reporting where data is spread across files.
Internally, when you ATTACH DATABASE, SQLite essentially opens the specified file and maps its tables into the current database connection’s namespace under the given alias. The attached database remains accessible for the duration of the connection or until you DETACH it.
-- Detach the database when done
DETACH DATABASE store;
The DETACH command removes the alias and unmounts the database file from the current connection.
When you query, you can explicitly qualify table names with the alias, like store.products. If you don’t qualify a table name and there’s no ambiguity (i.e., a table with that name only exists in the main database), it will be resolved from the main database. If a table name exists in both the main database and an attached database, you must qualify it with the alias to avoid ambiguity or to select from the specific database you intend.
The most surprising true thing about ATTACH DATABASE is that it does not create a new physical database file or merge existing ones; it simply establishes a new logical namespace within your current SQLite connection that points to an existing, separate database file. The data remains physically segregated on disk.
You can attach multiple databases to a single connection, each with its own unique alias. This allows for complex data integration scenarios where data is naturally distributed across several files.
A common pitfall is forgetting to DETACH a database when it’s no longer needed. While SQLite will eventually clean up on connection close, explicitly detaching releases the file handle and resources associated with the attached database, which can be important in long-running applications.
The next concept you’ll likely encounter is how ATTACH DATABASE interacts with transactions, particularly when performing operations that span multiple attached databases.