SQLite Basics: Query Separate Database Files with ATTACH

Sometimes an import, archive, or lookup catalog lives in another SQLite file. ATTACH makes that database available to the current connection under a schema name, so a query can use both sets of tables. These examples create disposable files; they never open an existing database.

The scripts require SQLite 3.7.11 or later because they use multirow VALUES inserts. This is the examples’ syntax floor, not ATTACH’s introduction date. Only the runtime listed below was executed. Multirow INSERT release notes.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation starts with a fresh in-memory main database and prints pipe-separated rows, showing missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

The SQL below also creates the named database files in this scratch directory. Those files survive when the runner exits; the main in-memory database does not. Run each example once before cleanup, or begin again in a fresh scratch directory. Do not substitute an existing database or backup path.

1. Join orders to a separate stock database

Save as join-attached-stock.sql:

ATTACH DATABASE 'warehouse.sqlite' AS warehouse;
CREATE TABLE main.orders (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL,
    requested INTEGER NOT NULL
);
CREATE TABLE warehouse.stock (
    sku TEXT PRIMARY KEY,
    available INTEGER NOT NULL
);
INSERT INTO main.orders VALUES (1, 'pen', 4), (2, 'pad', 8);
INSERT INTO warehouse.stock VALUES ('pen', 12), ('pad', 5);
SELECT o.id, o.sku, o.requested, s.available,
       s.available >= o.requested AS can_fill
FROM main.orders AS o
JOIN warehouse.stock AS s ON s.sku = o.sku
ORDER BY o.id;
DETACH DATABASE warehouse;

Run:

python3 run-sql.py join-attached-stock.sql

Output:

1|pen|4|12|1
2|pad|8|5|0

Both tables participate in one SELECT even though only stock is stored in the warehouse file. A 1 in the last column means the available quantity covers the request; a 0 means it does not. This report reads stock but does not reserve it, so it is not an inventory-allocation transaction.

The explicit prefixes separate main.orders from warehouse.stock. Here main is the runner’s in-memory database; warehouse is the alias we chose. Relative filenames are resolved from the scratch working directory. ATTACH creates a missing database file, so a misspelled production path can accidentally create an empty database instead of opening the intended one.

2. Copy completed jobs without removing the originals

Save as copy-jobs-to-archive.sql:

ATTACH DATABASE 'archive.sqlite' AS archive;
CREATE TABLE main.jobs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    done INTEGER NOT NULL
);
CREATE TABLE archive.jobs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);
INSERT INTO main.jobs VALUES
    (1, 'Import labels', 1),
    (2, 'Check totals', 0),
    (3, 'Export summary', 1);
BEGIN;
INSERT INTO archive.jobs(id, title)
SELECT id, title FROM main.jobs WHERE done = 1;
COMMIT;
SELECT id, title FROM archive.jobs ORDER BY id;
SELECT 'source rows', COUNT(*) FROM main.jobs;
DETACH DATABASE archive;

Run:

python3 run-sql.py copy-jobs-to-archive.sql

Output:

1|Import labels
3|Export summary
source rows|3

Only the two completed jobs are copied. The final count confirms all three source rows remain, which makes this a copy rehearsal rather than a destructive move. Listing the destination columns explicitly avoids depending on a future table’s column order.

The transaction groups the archive insert; it does not make this an idempotent import. Running the setup again against the same archive file would fail because its table already exists. For a real archive process, define duplicate handling and verify the copy before considering any source deletion. Do not add a DELETE to this tutorial and assume it is a complete migration.

3. Disambiguate equal table names and reopen the file

Save as reopen-attached-settings.sql:

ATTACH DATABASE 'labels.sqlite' AS labels;
CREATE TABLE main.settings (key TEXT PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE labels.settings (key TEXT PRIMARY KEY, value TEXT NOT NULL);
INSERT INTO main.settings VALUES ('theme', 'light');
INSERT INTO labels.settings VALUES ('theme', 'dark');
SELECT 'main', value FROM main.settings WHERE key = 'theme';
SELECT 'labels', value FROM labels.settings WHERE key = 'theme';
DETACH DATABASE labels;
ATTACH DATABASE 'labels.sqlite' AS saved;
SELECT 'reopened', value FROM saved.settings WHERE key = 'theme';
DETACH DATABASE saved;

Run:

python3 run-sql.py reopen-attached-settings.sql

Output:

main|light
labels|dark
reopened|dark

The same table name resolves to different data when qualified by its schema. Reattaching the file with a new alias still finds dark: the alias belongs to the connection, while the table contents belong to the file. DETACH stops using the file; it does not delete it.

Avoid relying on unqualified-name resolution when multiple databases have similar schemas. A connection’s attachment state should not silently change which settings, customers, or jobs a query reads. Close active statements and end transactions before detaching a database used by them.

The ATTACH reference describes schema names, paths, and transaction limits. In particular, crash-atomic commits across multiple database files require an on-disk main database and a non-WAL journal mode. This runner uses an in-memory main database, so it must not be used to demonstrate cross-file crash atomicity. The DETACH reference explains removal from the current connection.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py join-attached-stock.sql copy-jobs-to-archive.sql reopen-attached-settings.sql
rm -- warehouse.sqlite archive.sqlite labels.sqlite
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.