ATTACH DATABASE in SQLite: Cross-Database Queries and Limits

SQLite keeps each database in a single file, and a connection opens one main file. That sounds like a hard wall between datasets, but it is not. ATTACH DATABASE adds more database files to the same connection so you can query and join across all of them in one statement. This guide covers the syntax, the name-resolution rules that trip people up, transaction behavior, and the limits.

The basic syntax

ATTACH DATABASE takes a filename and an internal schema name:

ATTACH DATABASE 'customers.db' AS customers;
ATTACH DATABASE 'products.db'  AS products;

After attaching, refer to a table in an attached database with a schema-name.table-name prefix:

SELECT * FROM customers.accounts WHERE region = 'EU';

The filename follows the same rules as opening a database normally. Two special values are worth knowing: ':memory:' creates a private in-memory database, and an empty string '' creates a temporary on-disk database that is deleted when detached.

ATTACH DATABASE ':memory:' AS scratch;   -- transient working space

Joining across files

Once two databases share a connection, a join across them looks like any ordinary join. There is no special syntax beyond the schema prefix.

ATTACH DATABASE 'orders.db'    AS o;
ATTACH DATABASE 'customers.db' AS c;
 
SELECT c.accounts.name, SUM(o.line_items.amount) AS total
FROM o.line_items
JOIN o.orders   ON o.orders.id = o.line_items.order_id
JOIN c.accounts ON c.accounts.id = o.orders.customer_id
GROUP BY c.accounts.name;

This is the simplest way to combine data that lives in separate files, for example a per-tenant database joined against a shared reference database, without an export/import step.

How names resolve

The schema prefix is optional when a table name is unique across every attached database plus main and temp. If you write an unqualified table name, SQLite searches in a defined order. Two rules matter:

1. The names main and temp always refer to the main database and the temporary-table database. You cannot attach or detach those two.
2. If the same table name exists in more than one database and you omit the prefix, SQLite picks the table in the database that was least recently attached (the main database counts as attached first).

That second rule is a quiet source of bugs. If both main and an attached file have a settings table, an unqualified SELECT * FROM settings resolves to main. Attach order changes which table wins among the attached files. When two databases can share table names, always qualify with the schema prefix. It costs nothing and removes the ambiguity.

Detaching

Remove an attached database with DETACH:

DETACH DATABASE customers;

You cannot detach a database that is inside an active transaction, and you cannot detach main or temp. Detaching a ':memory:' or empty-string database destroys its contents.

Transactions across attached databases

A single transaction can write to multiple attached databases, and SQLite tries to keep the whole thing atomic, but with two important conditions stated in the official docs:

• Cross-file transactions are atomic only if the main database is a real file (not ':memory:') and the journal mode is not WAL.
• If the main database is ':memory:' or any database is in WAL mode, each file commits atomically on its own, but a host crash mid-commit can leave some files updated and others not.

So if you depend on an all-or-nothing write spanning several files, use rollback-journal mode (the default) on a real main database file. If you need WAL's concurrency, accept that cross-file atomicity is per-file rather than global.

The attached-database limit

The number of databases you can attach at once is capped by the compile-time setting SQLITE_MAX_ATTACHED, which defaults to 10 and has a hard maximum of 125. The main database does not count against this limit, but temp effectively does.

There is no SQL command or PRAGMA to read or change this limit. It is controlled from C with sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, N), and that call can only lower the ceiling, never raise it above the compile-time SQLITE_MAX_ATTACHED. To allow more than 10 you have to recompile SQLite with a larger SQLITE_MAX_ATTACHED (up to the hard max of 125), which most prebuilt distributions and language bindings do not do. If you find yourself needing dozens of attached files, that is usually a signal to rethink the data layout: consolidate into one file with a tenant/source column, or query the files in batches.

When to use ATTACH (and when not to)

ATTACH is the right tool for ad-hoc cross-database joins, copying data between files (INSERT INTO main.t SELECT * FROM other.t), and combining a small, fixed set of databases. It is the wrong tool when you have many files, need many concurrent writers across them, or are reaching for it to fake horizontal sharding. For the sharding case see our partitioning alternatives guide.

Common mistakes

• Forgetting the prefix with duplicate table names. Resolution falls back to least-recently-attached, which is rarely what you meant. Qualify the name.
• Expecting WAL to preserve cross-file atomicity. It does not. Atomicity becomes per-file under WAL.
• Detaching inside a transaction. DETACH fails if the database is part of an open transaction. Commit or roll back first.
• Hitting the limit silently. The 11th attach against a default build returns an error, not a warning. Plan the layout before you scale the file count.

Mako connects to SQLite with AI-powered autocomplete, which is handy for writing the schema-qualified joins that cross-database queries require. Try it free at mako.ai.