SQLite ATTACH and DETACH DATABASE: Query Several Files at Once

Ninad Pathak

October 7, 2026

ATTACH DATABASE opens a second SQLite file on the same connection, so one query can use tables from both files. In this article I’ll walk you through attaching a file and querying across both, then detaching it again.

TLDR

ATTACH DATABASE 'archive.db' AS archive; adds a second file to the current connection.
Refer to its tables as archive.table_name. The original database is always main.

DETACH DATABASE archive; removes it. There’s no form that detaches everything at once.
Attachments last only for the connection that made them, and the default limit is 10 attached databases.
Attaching a file that doesn’t exist creates it.

What is ATTACH DATABASE in SQLite?

“The ATTACH DATABASE statement adds another database file to the current database connection” (SQLite ATTACH). Each attached file gets a schema name, and you put that name in front of a table to use it, like archive.orders. DETACH DATABASE takes the file off the connection again (SQLite DETACH).

The schema names main and temp are reserved. main is the file you opened, and temp holds temporary tables. Neither one can be attached or detached.

SQLite ATTACH and DETACH syntax

ATTACH [DATABASE] 'path/to/file.db' AS schema_name;
SELECT * FROM schema_name.table_name;
DETACH [DATABASE] schema_name;

Part	Notes
`'path/to/file.db'`	Same rules as opening a file. `':memory:'` attaches an in-memory database, and `''` a temporary one
`schema_name`	Any name except `main` and `temp`
`DATABASE`	Optional keyword

How to attach a database

To show you how this works, I will use two files: shop.db with a customers table, and archive.db with an orders table. Both have a small settings table too. This script runs the whole flow in one sqlite3 session:

.headers on
.mode column
ATTACH DATABASE 'archive.db' AS archive;
.databases
SELECT c.name, count(o.id) AS orders, sum(o.total) AS spent
FROM customers AS c JOIN archive.orders AS o ON o.customer_id = c.id
GROUP BY c.id;
SELECT value FROM settings;
SELECT value FROM archive.settings;
DETACH DATABASE archive;
SELECT * FROM archive.orders;

$ sqlite3 shop.db < 'session.sql'
main: /private/tmp/sqldocs-f3-sqlite-attach-database/shop.db r/w
archive: /private/tmp/sqldocs-f3-sqlite-attach-database/archive.db r/w
name   orders  spent
-----  ------  -----
Ada    2       40.5
Grace  1       40.0
value
-------
shop.db
value
----------
archive.db
Parse error near line 11: no such table: archive.orders

Step 1: Attach the second file

ATTACH DATABASE 'archive.db' AS archive; opens archive.db on the same connection. The shell’s .databases command lists every attached file with its schema name.

Step 2: Join tables across both files

Prefix tables with their schema name. The join above combines customers from main with archive.orders.

Both files have a settings table. Unqualified, settings means the one in main. Use archive.settings for the other.

Step 4: Detach it

DETACH DATABASE archive; removes the file from the connection. After that, archive.orders no longer exists, which is the last line of the output.

Examples

Each example runs in a new sqlite3 process, so it starts with nothing attached.

Attachments last for one connection only

The second command opens a new connection, which doesn’t see the first one’s attachment:

sqlite> ATTACH 'archive.db' AS archive; SELECT count(*) AS orders FROM archive.orders;
orders
------
3
sqlite> SELECT count(*) FROM archive.orders;
Error: in prepare, no such table: archive.orders

Use a table name without its prefix

If only one database has a table with that name, the prefix is optional. Here orders exists only in archive:

sqlite> ATTACH 'archive.db' AS archive; SELECT id, total FROM orders;
id  total
--  -----
1   25.0
2   40.0
3   15.5

When the name is in more than one database, SQLite’s documentation says it uses “the one in the database that was least recently attached”, which is why settings meant main.settings in the session above.

Copy a table from another database file

CREATE TABLE … AS SELECT copies an attached table into main:

sqlite> ATTACH 'archive.db' AS archive; CREATE TABLE orders_copy AS SELECT * FROM archive.orders; SELECT count(*) AS copied FROM orders_copy;
copied
------
3

Write to an attached database

Writes work on attached tables too:

sqlite> ATTACH 'archive.db' AS archive; INSERT INTO archive.orders (customer_id, total) VALUES (2, 9.99); SELECT count(*) AS archive_orders FROM archive.orders;
archive_orders
--------------
4

Create a new database file with ATTACH

Attaching a path that doesn’t exist creates the file once something is written to it:

$ ls new.db
ls: new.db: No such file or directory
sqlite> ATTACH 'new.db' AS fresh; CREATE TABLE fresh.notes (body TEXT);
$ ls new.db
new.db

Attach an in-memory scratch database

sqlite> ATTACH ':memory:' AS scratch; CREATE TABLE scratch.t AS SELECT 1 AS x; SELECT * FROM scratch.t;
x
-
1

Edge cases

These are the errors you’ll meet when attaching and detaching.

main and temp can’t be attached or detached

sqlite> ATTACH 'archive.db' AS main;
Error: stepping, database main is already in use
sqlite> DETACH DATABASE main;
Error: stepping, cannot detach database main

DETACH needs a name

Some tutorials show DETACH DATABASE; with no name to detach everything. It’s a syntax error:

sqlite> ATTACH 'archive.db' AS archive; DETACH DATABASE;
Error: in prepare, near ";": syntax error
  DETACH DATABASE;
                 ^--- error here

Detaching a name that isn’t attached fails too:

sqlite> DETACH DATABASE archive;
Error: stepping, no such database: archive

You can’t detach inside an open write transaction

Commit or roll back first:

sqlite> ATTACH 'archive.db' AS archive; BEGIN; INSERT INTO archive.orders (customer_id, total) VALUES (1, 5.0); DETACH DATABASE archive;
Error: stepping, database archive is locked

Up to 10 attached databases by default

The limit is SQLITE_MAX_ATTACHED, which is “set to 10 by default”. It can’t go above 125 (Limits in SQLite):

$ sqlite3 shop.db "$(for i in 1 2 3 4 5 6 7 8 9 10 11; do printf "ATTACH ':memory:' AS m%s; " $i; done)"
Error: stepping, too many attached databases - max 10

Transactions across files in WAL mode

A transaction that writes to several attached files is atomic across them, unless the main database is :memory: or the journal mode is WAL. In those cases, the documentation says that after a crash “some of those files might get the changes where others might not”.

Conclusion

Use ATTACH DATABASE whenever you need two SQLite files in one query, such as a report across files or a copy from one to the other. Prefix tables with the schema name and detach when you’re done. Each connection has its own attachments.