Using DuckDB's ATTACH to Query Multiple Database Files

Why ATTACH matters

DuckDB can treat each additional DuckDB file as a schema inside the current session. By attaching a file you get cross‑database queries without moving data, which is useful for combining historical archives, sharded logs, or separate ETL outputs.

Worked example: attaching a second DuckDB file

1. Start the DuckDB CLI (no special permissions needed beyond file system access).

   $ duckdb
   

2. Create a primary database in memory (the default) and a secondary file.

   -- In the CLI
   CREATE TABLE main.inventory (product_id INTEGER, qty INTEGER);
   INSERT INTO main.inventory VALUES (1, 100), (2, 250);
   
   -- Save the secondary file
   ATTACH 'sales.duckdb' AS sales;
   CREATE TABLE sales.orders (order_id INTEGER, product_id INTEGER, amount DOUBLE);
   INSERT INTO sales.orders VALUES (101, 1, 15.5), (102, 2, 22.0);
   DETACH sales;
   

3. Re‑open DuckDB and attach the sales file to query across both.

   $ duckdb
   ATTACH 'sales.duckdb' AS sales;
   SELECT i.product_id, i.qty, o.amount
   FROM main.inventory i
   JOIN sales.orders o USING (product_id);
   

   The result shows each product with its inventory quantity and the sales amount from the attached file.
4. Persist writes to the attached file.

   INSERT INTO sales.orders VALUES (103, 1, 12.0);
   COMMIT;   -- required before detach to make the insert durable
   DETACH sales;
   

Limits and requirements

• File format: Only DuckDB‑format files (.duckdb) can be attached. Attempting to attach SQLite, PostgreSQL dumps, or raw CSV returns an error like "Unsupported file format".
• Write isolation: Each attached database maintains its own transaction scope. Changes are not visible in other attached files until you issue COMMIT on that specific database.
• Memory usage: DuckDB loads metadata for every attached file. Attaching many large files can increase RAM consumption noticeably; monitor usage in memory‑constrained environments.
• Cross‑database transactions: There is no atomic transaction that spans multiple attached files. A failure in one file does not roll back changes in another, so application‑level compensation may be needed.

Common pitfalls

1. Missing alias prefix: After ATTACH 'file.duckdb' AS alias; you must reference tables as alias.table_name. Using just table_name yields "table not found" errors.
2. Assuming DETACH commits: DETACH only removes the reference; uncommitted changes in the attached database are lost. Always COMMIT before detaching if you want to persist writes.
3. Attaching non‑DuckDB files: Trying to attach a CSV or Parquet file directly with ATTACH fails. Use READ_CSV or READ_PARQUET instead, or first import the data into a DuckDB file.
4. Overlooking isolation: Assuming an INSERT into an attached table is immediately visible in another attached file without a commit leads to confusing results.

Practical verification

To confirm that an attached file is usable:

1. Launch DuckDB, create a test table in a fresh file: ATTACH 'test.duckdb' AS t; CREATE TABLE t.test (id INTEGER);
2. Run SELECT * FROM t.test; – you should see an empty result.
3. Insert a row, commit, detach, start a new session, re‑attach, and query again. The row should persist, proving write durability.
4. Try attaching a CSV: ATTACH 'data.csv' AS bad; – DuckDB will return an error confirming the format restriction.