SQLite ATTACH DATABASE: Querying Across Multiple Files

One Connection, Many Files

A SQLite connection isn't locked to a single file. With ATTACH DATABASE, you can open additional .db files alongside the one you started with, and query them all as if they were schemas inside one database. It's the closest thing SQLite has to "multiple databases on one server."

The basic form:

ATTACH DATABASE 'archive.db' AS archive;

CREATE TABLE archive.old_orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    total REAL
);

INSERT INTO archive.old_orders VALUES (1, 'Rosa', 49.99);

SELECT * FROM archive.old_orders;

The file archive.db is created if it doesn't exist, just like the main database. From now on in this session, anything prefixed with archive. lives in that second file. Anything prefixed with main. (or unprefixed) lives in the original.

Your connection always has two implicit schemas: main (the file you opened first) and temp (a scratch space for temporary tables). Attaching adds more.

The Syntax and What the Alias Does

ATTACH DATABASE 'path/to/file.db' AS alias_name;

The alias is the schema name you'll use to qualify tables. It's local to the current connection — another connection attaching the same file can pick a different alias. Pick something short and descriptive (archive, analytics, cache) because you'll type it a lot.

A few things worth knowing:

• The path is relative to the process's working directory unless absolute.
• The string ':memory:' attaches a fresh in-memory database under that alias.
• The alias can't collide with main or temp, and can't repeat across attachments.

Joining Across Databases

This is the feature most people attach for. Once two files are in the same connection, you can join their tables in a single query:

ATTACH DATABASE ':memory:' AS archive;

CREATE TABLE main.customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE archive.orders (id INTEGER, customer_id INTEGER, total REAL);

INSERT INTO main.customers VALUES (1, 'Rosa'), (2, 'Boris');
INSERT INTO archive.orders VALUES (101, 1, 49.99), (102, 2, 12.50);

SELECT c.name, o.total
FROM main.customers AS c
JOIN archive.orders AS o ON o.customer_id = c.id;

The query planner treats both schemas the same way it treats tables in main. Indexes on attached tables are used. EXPLAIN QUERY PLAN works across them. There's no network round-trip — both files are open in the same process.

This is genuinely useful for splitting hot data from cold archives, separating per-tenant files, or pulling reference data out of a read-only lookup database.

Read-Only and In-Memory Attachments

If the second database is something you want to read but never modify — a shipped reference dataset, say — attach it read-only with a URI:

ATTACH DATABASE 'file:reference.db?mode=ro' AS ref;

The URI form requires the SQLite library to have SQLITE_OPEN_URI enabled (it is in the CLI and most language bindings). Any INSERT, UPDATE, or DELETE against ref.* will then raise an error before touching the file.

In-memory attachments are equally handy for staging data:

ATTACH DATABASE ':memory:' AS scratch;

CREATE TABLE scratch.tmp_results AS
SELECT customer_id, SUM(total) AS spend
FROM main.orders
GROUP BY customer_id;

SELECT * FROM scratch.tmp_results ORDER BY spend DESC LIMIT 5;

scratch vanishes when the connection closes. It's like temp but you control the lifetime.

Transactions Span Every Attached Database

A single BEGIN/COMMIT covers writes to main and every attached schema. Either everything commits or everything rolls back — atomicity is preserved across files:

ATTACH DATABASE ':memory:' AS archive;

CREATE TABLE main.orders (id INTEGER PRIMARY KEY, total REAL);
CREATE TABLE archive.orders (id INTEGER PRIMARY KEY, total REAL);

BEGIN;
    INSERT INTO archive.orders SELECT * FROM main.orders WHERE id < 100;
    DELETE FROM main.orders WHERE id < 100;
COMMIT;

Moving rows from a live table to an archive file is exactly the kind of operation where you want this guarantee. Without atomicity across files, a crash in the middle would leave you with duplicates or, worse, lost rows.

One caveat: when more than one attached database is being written to in a transaction, SQLite uses a more cautious commit protocol that requires a temporary journal. It's slower than single-file commits, but still safe.

Detaching

When you're done with an attached database, drop it:

DETACH DATABASE archive;

The file stays on disk untouched — DETACH just closes the handle in the current connection. Two restrictions to remember:

• You can't detach main or temp.
• You can't detach a database that's currently inside a transaction or has open statements against it.

If you forget to detach, it's not the end of the world: closing the connection cleans everything up.

Limits and Common Errors

A few practical limits worth knowing:

• Default cap is 10 attached databases per connection (plus main and temp). The compile-time maximum is 125. Hit the limit and you'll see too many attached databases - max 10.
• Each attached file uses a page cache. Attaching a dozen large databases isn't free — RAM goes up.
• ATTACH itself can't run inside a transaction. Run it before BEGIN, or after COMMIT.

A few errors you'll likely meet:

-- File doesn't exist and the directory isn't writable:
Error: unable to open database: 'missing/path.db'

-- You tried to write to a read-only attachment:
Error: attempt to write a readonly database

-- You used the same alias twice:
Error: database archive is already in use

Most of these are obvious once you read them. The "already in use" one trips people up — ATTACH doesn't replace an existing alias; you have to DETACH first.

A Realistic Pattern: Hot/Cold Split

Putting it together — a small archive workflow that moves orders older than a year out of the main database:

ATTACH DATABASE ':memory:' AS archive;

CREATE TABLE main.orders (
    id INTEGER PRIMARY KEY,
    placed_at TEXT,
    total REAL
);
CREATE TABLE archive.orders AS SELECT * FROM main.orders WHERE 0;

INSERT INTO main.orders VALUES
    (1, '2022-06-01', 19.99),
    (2, '2024-11-15', 49.50),
    (3, '2025-08-22', 12.00);

BEGIN;
    INSERT INTO archive.orders
        SELECT * FROM main.orders WHERE placed_at < date('now', '-1 year');
    DELETE FROM main.orders WHERE placed_at < date('now', '-1 year');
COMMIT;

SELECT 'main' AS db, COUNT(*) FROM main.orders
UNION ALL
SELECT 'archive', COUNT(*) FROM archive.orders;

Old rows move to archive.orders, recent ones stay in main. Reports that need history can join across both; day-to-day queries against main.orders stay fast because the table is smaller. Same connection, two files, one transaction.

Next: Prepared Statements

ATTACH is about giving one connection access to more data. The next set of topics is about how applications talk to SQLite safely and efficiently — starting with prepared statements, the foundation of parameter binding and injection-proof queries.