ATTACH Database

Code

-- Attach another database file
ATTACH DATABASE 'archive.db' AS archive;

-- List attached databases
PRAGMA database_list;

-- Query across databases
SELECT * FROM main.users
UNION ALL
SELECT * FROM archive.users WHERE archived_at < '2024-01-01';

-- Copy data between databases
INSERT INTO archive.users
SELECT * FROM main.users WHERE last_login < '2023-01-01';
DELETE FROM main.users WHERE last_login < '2023-01-01';

-- Detach when done
DETACH DATABASE archive;

Explanation

ATTACH DATABASE adds another SQLite file to the current connection under an alias, allowing cross-database queries via the alias prefix. The main database is always called 'main', and attached databases are addressed by their alias. This is a common pattern for archiving old data without a full migration.

More SQLite Snippets