Most tutorials treat SQLite databases as isolated, single-file islands — you open one file, you query it, you close it. But SQLite has a genuinely useful feature that a lot of people never discover: the ability to attach multiple database files to a single connection and query across them as if they were one unified database. That’s what ATTACH DATABASE does, and once you understand it, you’ll start finding uses for it more often than you’d expect. In this guide, I’ll walk through exactly how it works, why you’d want to use it, and where its limitations lie.
The Basic Syntax
ATTACH DATABASE 'path/to/file.db' AS alias_name;
Let’s make this concrete. Suppose you have two separate SQLite database files: sales.db and inventory.db. From within a SQLite session connected to sales.db, you can attach inventory.db like this:
ATTACH DATABASE 'inventory.db' AS inventory;
From this point forward, in the current session, you can reference tables in inventory.db by prefixing them with the alias you chose:
SELECT * FROM inventory.products;
Meanwhile, tables in the main database — the one you originally opened — don’t need any prefix at all, though you can still reference them explicitly as main.table_name if you want to be unambiguous.
Setting Up a Practical Example
Let’s build this out properly so you can follow along. First, create and populate a sales database:
-- Working in sales.db
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
order_date TEXT
);
INSERT INTO orders (product_id, quantity, order_date) VALUES
(101, 5, '2024-03-01'),
(102, 2, '2024-03-02'),
(101, 3, '2024-03-03');
Now, in a separate file, create an inventory database:
-- Working in inventory.db
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
unit_price REAL
);
INSERT INTO products (id, name, unit_price) VALUES
(101, 'Wireless Mouse', 19.99),
(102, 'Mechanical Keyboard', 89.99);
Now open a session against sales.db and attach inventory.db:
sqlite3 sales.db
ATTACH DATABASE 'inventory.db' AS inventory;
With both databases attached to the same session, you can now write a query that joins across them, something that would otherwise be impossible since they’re two completely separate files:
SELECT o.id, i.name, o.quantity, i.unit_price, (o.quantity * i.unit_price) AS total
FROM orders o
JOIN inventory.products i ON o.product_id = i.id;
This runs a join between a table in the main database and a table in an attached one, exactly as if they’d always lived in the same file.
Why This Is Useful
There are a handful of situations where ATTACH DATABASE genuinely earns its keep:
Separating concerns across files. Some applications intentionally split data into separate database files — perhaps one for user data, one for logs, one for configuration — for reasons of organization, backup strategy, or access control. ATTACH lets you query across those boundaries when needed without merging everything into a single monolithic file.
Merging or migrating data. If you’re consolidating data from an old database into a new one, you can attach the old file and use INSERT INTO … SELECT to copy records across, without writing a separate export/import script.
ATTACH DATABASE 'old_system.db' AS legacy;
INSERT INTO orders (product_id, quantity, order_date)
SELECT product_id, quantity, order_date FROM legacy.orders;
Cross-database reporting. If different parts of a system write to different SQLite files, you can attach them all temporarily to build a report that spans the whole picture, without permanently restructuring your storage.
Testing and comparison. You can attach a backup or snapshot database alongside your live one and directly compare data between them using SQL, rather than exporting both and diffing manually.
ATTACH DATABASE 'backup_2024_01.db' AS backup;
SELECT o.id FROM orders o
LEFT JOIN backup.orders b ON o.id = b.id
WHERE b.id IS NULL;
That last query finds every order that exists in the current database but not in the backup — useful for spotting exactly what changed since the backup was taken.
Detaching a Database
Once you’re done working across databases, you can detach one to remove it from the current session:
DETACH DATABASE inventory;
After this, inventory.products is no longer accessible in this session — you’d need to re-attach it if you wanted to query it again. This doesn’t delete or modify the file in any way; it simply disconnects it from the current session.
Attaching an In-Memory Database
You’re not limited to attaching files on disk. You can also attach a temporary, in-memory database, which is handy for scratch work you don’t want persisted anywhere:
ATTACH DATABASE ':memory:' AS scratch;
CREATE TABLE scratch.temp_calc (
id INTEGER PRIMARY KEY,
result REAL
);
Everything in this attached in-memory database vanishes the moment the session ends, which makes it a genuinely useful workspace for intermediate calculations during a complex multi-step query process.
The Limit on Attached Databases
SQLite does impose a limit on how many databases you can attach to a single connection at once. Historically this default limit has been 10, though it’s a compile-time setting that can be adjusted in some builds, and you can check or influence it with:
PRAGMA compile_options;
For the vast majority of use cases, this default limit is more than enough. If you find yourself needing to attach dozens of database files simultaneously, it’s usually a sign that a different architecture — like consolidating into fewer files, or moving to a client-server database — might serve you better.
Important Limitations to Know
Attached databases are per-connection, not permanent. Every time you open a new connection to sales.db, you’ll need to re-run the ATTACH statement if you want inventory.db available again. There’s no way to make an attachment “stick” permanently as part of the database file itself.
You can’t create cross-database foreign keys. Foreign key constraints only work within a single database file. If orders.product_id is meant to reference inventory.products.id, SQLite won’t enforce that relationship across the attachment boundary, even with foreign keys turned on. You’d need to handle that consistency check yourself, in application logic.
Transactions spanning multiple attached databases behave carefully, but not identically to a single-file transaction. SQLite does support transactions that span attached databases, and it uses a two-phase commit-like process internally to keep them consistent, but it’s worth testing this behavior thoroughly if your application depends heavily on cross-database transactional integrity, since it’s more complex than a same-file transaction.
Attached database aliases must be unique within a session. You can’t attach two databases under the same alias, and you can’t use main or temp as an alias since those names are reserved for the primary database and the temporary database respectively.
A Realistic Migration Example
Let’s walk through a slightly larger example — migrating specific records from an old database into a new one, filtering along the way:
ATTACH DATABASE 'legacy_customers.db' AS legacy;
INSERT INTO customers (name, email, signup_date)
SELECT name, email, signup_date
FROM legacy.customers
WHERE signup_date >= '2023-01-01';
DETACH DATABASE legacy;
This pulls only customers who signed up in 2023 or later from the legacy database into the current one, then cleanly detaches once the migration is done. Wrapping this in a transaction is a good idea too, so a failure partway through doesn’t leave you with a half-migrated dataset:
BEGIN TRANSACTION;
ATTACH DATABASE 'legacy_customers.db' AS legacy;
INSERT INTO customers (name, email, signup_date)
SELECT name, email, signup_date
FROM legacy.customers
WHERE signup_date >= '2023-01-01';
COMMIT;
DETACH DATABASE legacy;
Common Mistakes to Avoid
Forgetting the alias must be used as a prefix for every reference to tables in that database. Writing SELECT * FROM products when products only exists in the attached database, not the main one, will fail unless you prefix it: SELECT * FROM inventory.products.
Assuming foreign keys work across attachments. As mentioned, they don’t. Don’t rely on the database to enforce referential integrity between attached databases — you’ll need to check that yourself.
Not detaching databases you no longer need. Leaving unnecessary databases attached doesn’t usually cause serious problems, but it clutters your session and can lead to confusing bugs if two attached databases happen to have tables with the same name.
Trying to attach more databases than the configured limit allows. If you hit this limit, you’ll get a clear error, but it can catch you off guard if you’re dynamically attaching many files in a loop without realizing there’s a ceiling.
Best Practices Worth Adopting
Use clear, descriptive aliases when attaching — inventory, legacy, backup — rather than vague names like db2, so your queries stay readable.
Detach databases as soon as you’re done working across them, especially in long-running application sessions, to avoid confusion and keep your session’s table namespace clean.
Wrap cross-database operations like migrations inside transactions, so a failure partway through doesn’t leave data in an inconsistent state.
Don’t rely on ATTACH DATABASE as a long-term architectural pattern for applications that genuinely need a single unified schema — it’s a fantastic tool for one-off tasks, migrations, and reporting, but if you find yourself attaching the same set of databases every single time your application starts, it might be worth asking whether those files should just be one database to begin with.
Wrapping Up
ATTACH DATABASE is one of those SQLite features that quietly solves a whole category of problems most people don’t realize SQLite can handle — cross-database queries, migrations, comparisons, and reporting, all without leaving the comfort of plain SQL. It’s not something you’ll use every day, but when the right situation comes up, knowing this command exists can save you from writing a much more complicated script in Python or another language to accomplish the same thing. Keep its limitations in mind, particularly around foreign keys and per-connection scope, and it’ll serve you well.