SQLite ATTACH Database
The ATTACH DATABASE statement connects a second (or third, or more) SQLite database file to your current session. Once attached, you query tables from all connected databases in a single SQL statement — no copying, no imports, no temporary merges needed.
The Multi-Database Analogy
Without ATTACH: With ATTACH: ────────────────────────────────── ────────────────────────────────── You have two filing cabinets You open both cabinets at once in separate rooms. and work from the same desk. To compare records, you must You query both directly and photocopy from one room and combine results in a single carry papers to the other. SQL statement.
ATTACH Syntax
ATTACH DATABASE 'filename.db' AS alias; -- filename : path to the SQLite file -- alias : name you use to reference this database in queries DETACH DATABASE alias; -- disconnect when done
Basic Example
-- Session starts with school.db open $ sqlite3 school.db -- Attach a second database sqlite> ATTACH DATABASE 'library.db' AS lib; -- Now you have two databases in one session: sqlite> .databases main: /home/user/school.db r/w lib: /home/user/library.db r/w -- Query library tables using "lib." prefix sqlite> SELECT * FROM lib.books LIMIT 3; -- Query school tables (main database) without prefix sqlite> SELECT * FROM students LIMIT 3; -- OR explicitly: sqlite> SELECT * FROM main.students LIMIT 3;
Referencing Tables Across Databases
-- Syntax: database_alias.table_name SELECT * FROM main.students; -- main database SELECT * FROM lib.books; -- attached database -- You can also omit "main." for the primary database SELECT * FROM students; -- same as main.students
JOIN Across Two Databases
-- school.db has: students (id, name, book_id)
-- library.db has: books (id, title, author)
-- Find each student's borrowed book
SELECT s.name AS student,
b.title AS book,
b.author AS written_by
FROM main.students s
JOIN lib.books b ON s.book_id = b.id
ORDER BY s.name;
student │ book │ written_by
────────┼────────────────────────┼────────────
Alice │ Atomic Habits │ James Clear
Bob │ Deep Work │ Cal Newport
Carol │ Thinking Fast and Slow │ Kahneman
Copy Data Between Databases
-- Copy all books from lib into main database ATTACH DATABASE 'library.db' AS lib; INSERT INTO main.books SELECT * FROM lib.books; -- Copy only specific rows INSERT INTO main.archive_books SELECT * FROM lib.books WHERE year < 2000;
Attach Multiple Databases
-- Attach up to 10 databases (SQLite default limit) ATTACH DATABASE 'sales_2022.db' AS s2022; ATTACH DATABASE 'sales_2023.db' AS s2023; ATTACH DATABASE 'sales_2024.db' AS s2024; -- Query all three years in one UNION SELECT '2022' AS year, SUM(amount) AS revenue FROM s2022.orders UNION ALL SELECT '2023', SUM(amount) FROM s2023.orders UNION ALL SELECT '2024', SUM(amount) FROM s2024.orders; year │ revenue ─────┼────────── 2022 │ 450000 2023 │ 620000 2024 │ 390000
ATTACH with In-Memory Database
-- Attach a temporary in-memory database for scratch work ATTACH DATABASE ':memory:' AS temp_work; CREATE TABLE temp_work.staging ( id INTEGER, data TEXT ); INSERT INTO temp_work.staging SELECT id, name FROM main.students; -- Process in temp_work, then write results back to main INSERT INTO main.processed SELECT id, UPPER(data) FROM temp_work.staging; -- temp_work disappears when you DETACH or close the session DETACH DATABASE temp_work;
Transactions Across Attached Databases
-- A BEGIN transaction covers ALL attached databases
BEGIN;
UPDATE main.inventory SET stock = stock - 5 WHERE product_id = 1;
INSERT INTO lib.shipment_log (product_id, qty, shipped_at)
VALUES (1, 5, datetime('now'));
COMMIT; -- both changes commit together
-- or ROLLBACK; to undo both
DETACH DATABASE
-- Remove an attached database from the session DETACH DATABASE lib; -- Verify it is gone sqlite> .databases main: /home/user/school.db r/w -- lib is no longer listed -- Cannot detach "main" (the primary database) DETACH DATABASE main; -- Error: cannot detach database main
Checking Attached Databases
-- Shell command sqlite> .databases -- SQL equivalent SELECT * FROM pragma_database_list; seq │ name │ file ────┼──────┼──────────────────────────── 0 │ main │ /home/user/school.db 2 │ lib │ /home/user/library.db
Attach Limitations
LIMITATION NOTES ────────────────────────────────────────────────────────────────── Max 10 databases per session Configurable at compile time Cannot attach same file twice Each file gets one alias Cannot detach main database Only attached databases ATTACH is session-scoped Must re-attach every new session Cross-DB views not persistent Views stay in one database Foreign keys do not cross databases FK must be in the same database
Practical Pattern: Yearly Archive Strategy
-- Keep current year in main.db, archive older years separately
-- 1. Move old data to archive
ATTACH DATABASE '2023_archive.db' AS arch2023;
INSERT INTO arch2023.orders
SELECT * FROM main.orders WHERE strftime('%Y', order_date) = '2023';
DELETE FROM main.orders WHERE strftime('%Y', order_date) = '2023';
DETACH DATABASE arch2023;
-- 2. Later, when you need historical data:
ATTACH DATABASE '2023_archive.db' AS arch2023;
SELECT * FROM main.orders
UNION ALL
SELECT * FROM arch2023.orders
ORDER BY order_date DESC;
ATTACH in Python
import sqlite3
conn = sqlite3.connect("school.db")
# Attach the library database
conn.execute("ATTACH DATABASE 'library.db' AS lib")
# Query across both
rows = conn.execute("""
SELECT s.name, b.title
FROM main.students s
JOIN lib.books b ON s.book_id = b.id
""").fetchall()
for row in rows:
print(row)
conn.execute("DETACH DATABASE lib")
conn.close()
Key Takeaways
ATTACH DATABASE 'file.db' AS aliasadds a second database to the current session- Reference attached tables as
alias.table_name; the main database usesmain.table_name - JOIN, INSERT, and SELECT all work across attached databases in one SQL statement
- A single BEGIN transaction covers all attached databases simultaneously
DETACH DATABASE aliasdisconnects the database; the session must re-attach it next time