ATTACH DATABASE, DETACH, and Cross-Database Queries

Learning outcomes

SQLite usually feels like “one connection, one file,” but a connection can address several database schemas at once. The main file is named main, connection-local temporary objects live in temp, and ATTACH DATABASE adds other files under names you choose.

One connection can have multiple named database schemas

PRAGMA database_list;-- Typical starting result includes main; temp appears when needed.ATTACH DATABASE 'fieldnotes_archive.db' AS archive;PRAGMA database_list;

archive is a schema name for this connection, not a server or cluster node. The attached database is still a normal SQLite database file accessed through the same SQLite connection/VFS environment.

Create and address objects with schema-qualified names

-- In main:CREATE TABLE IF NOT EXISTS main.device(  device_id INTEGER PRIMARY KEY,  device_code TEXT NOT NULL UNIQUE,  device_name TEXT NOT NULL,  status TEXT NOT NULL);-- In the attached archive file:CREATE TABLE IF NOT EXISTS archive.retired_device(  device_code TEXT PRIMARY KEY,  device_name TEXT NOT NULL,  retired_at TEXT NOT NULL);INSERT INTO archive.retired_device(device_code,device_name,retired_at)VALUES('PUMP-002','Legacy Cooling Pump 2','2025-12-31');SELECT * FROM archive.retired_device;

Schema qualification becomes especially important when two schemas contain objects with the same name. Do not rely on SQLite's name-resolution order for ambiguous cross-database code; write main.table and archive.table deliberately.

Cross-database joins are local SQL, not distributed SQL

SELECT d.device_code AS current_code,       a.device_code AS archived_codeFROM main.device AS dLEFT JOIN archive.retired_device AS a  ON a.device_code=d.device_codeORDER BY d.device_code;

SQLite can execute this because both schemas are visible to one connection. There is no network coordinator, sharding protocol, distributed transaction manager, replication system, or remote query engine implied by ATTACH.

Copy and transform between files

BEGIN;INSERT INTO archive.retired_device(device_code,device_name,retired_at)SELECT device_code,device_name,'2026-08-12'FROM main.deviceWHERE device_code='OLD-001' AND status='retired';DELETE FROM main.deviceWHERE device_code='OLD-001' AND status='retired';COMMIT;

This is a compelling ATTACH use case because the transformation is expressed in SQL and both files are visible to the same transaction. But the durability guarantee across both files depends on journal configuration, which is the subtle part.

Cross-file crash atomicity has a documented boundary

Current SQLite documentation says a transaction that updates multiple attached database files is atomic across the set if the main database is not :memory: and the journal mode is not WAL. If the main database is in-memory or WAL mode is involved, each individual database file remains atomic, but a host crash in the middle of a multi-file COMMIT can leave some files committed and others not.

This distinction is about crash-time durability across files—not whether an ordinary SQL error inside an open transaction can be rolled back.

Attached limits and connection-specific state

The maximum number of attached databases is controlled by SQLite's SQLITE_LIMIT_ATTACHED runtime limit and compile-time ceiling. Do not design around a guessed number. Applications using the C API can inspect/set limits within allowed bounds; high-level drivers expose varying subsets.

PRAGMA database_list;DETACH DATABASE archive;PRAGMA database_list;

DETACH removes the schema from this connection; it does not delete the database file.

Use cases that fit ATTACH

Lab: two-file integration with explicit verification

-- Start from a disposable main database.DROP TABLE IF EXISTS device;CREATE TABLE device(  device_id INTEGER PRIMARY KEY,  device_code TEXT UNIQUE NOT NULL,  device_name TEXT NOT NULL,  status TEXT NOT NULL);INSERT INTO device(device_code,device_name,status) VALUES('PUMP-007','Cooling Water Pump 7','active'),('FAN-014','Exhaust Fan 14','inspection_due'),('OLD-001','Legacy Pump','retired');ATTACH DATABASE 'chapter12_archive.db' AS archive;DROP TABLE IF EXISTS archive.retired_device;CREATE TABLE archive.retired_device(  device_code TEXT PRIMARY KEY,  device_name TEXT NOT NULL,  retired_at TEXT NOT NULL);BEGIN;INSERT INTO archive.retired_deviceSELECT device_code,device_name,'2026-08-12'FROM main.device WHERE status='retired';DELETE FROM main.device WHERE status='retired';COMMIT;SELECT COUNT(*) AS current_count FROM main.device;SELECT COUNT(*) AS archived_count FROM archive.retired_device;PRAGMA database_list;DETACH archive;

Expected logical state: two current devices remain in main and one retired device is in the archive file.

Verification checkpoint

Production judgment and bridge

ATTACH teaches that “filename” is part of SQLite's connection contract. Lesson 4 looks directly at that contract through URI filenames: read-only opens, create policies, private/shared cache selection, immutable files, and VFS choice.

Authoritative references

• SQLite — ATTACH DATABASE.
• SQLite — Limits.
• SQLite — WAL.