ATTACH and DETACH
ATTACH and DETACH let a database session connect to (or disconnect from) additional database files at runtime, enabling cross-database queries without a separate connection.
Overview
In embedded/single-file databases like DuckDB and SQLite, ATTACH lets an active session bring in another database file under an alias, so you can query or join across multiple database files as if they were schemas in one database. DETACH removes an attached database from the session.
Copy code
ATTACH 'warehouse.db' AS warehouse;
ATTACH 'archive.db' AS archive (READ_ONLY);
SELECT * FROM warehouse.main.orders
UNION ALL
SELECT * FROM archive.main.orders;
DETACH archive;
Once attached, tables are referenced as alias.schema.table (or just alias.table, since main is the default schema), and DDL/DML can target the attached database directly.
DuckDB specifics
DuckDB's ATTACH 'path' AS alias (OPTIONS...) supports a READ_ONLY option for safe concurrent access to a file another process might also be reading, a TYPE option to attach non-DuckDB databases (SQLite and PostgreSQL, via their respective extensions), and options like ENCRYPTION_KEY for encrypted databases. USE alias switches the session's default database context, so subsequent unqualified table names resolve against that database:
Copy code
ATTACH 'sales.db' AS sales;
USE sales;
SELECT * FROM orders; -- resolves to sales.main.orders
A DuckDB in-memory session can attach any number of on-disk databases at once, which is the basis for DuckDB's common use as a cross-database query engine — joining data that physically lives in separate DuckDB files (or even separate database systems, like an attached PostgreSQL database) in a single query. DETACH alias releases the attachment; you cannot detach the database currently selected as the default via USE.
Related terms
SQLite is a lightweight, serverless database engine that runs as an embedded part of an application rather than as a separate server process.
in-memory database →An in-memory database is a type of database management system that primarily relies on a computer's main memory (RAM) for data storage and processing, as…
Temporary table →A temporary table is a table scoped to a single session (or transaction) that is automatically dropped when the session ends, used for intermediate results that don't need to persist.
DuckLake →DuckLake is an open table format, created by the DuckDB team, that stores lakehouse metadata in a transactional SQL database instead of files — while keeping the actual data as Parquet files in object storage.
schema →A schema is a logical container or blueprint that defines how data is organized within a database.
analytical database →An analytical database, also known as an Online Analytical Processing (OLAP) database, is designed to efficiently handle complex queries and data analysis…
FAQS
Yes. ATTACH both files under aliases (e.g., ATTACH 'a.db' AS a; ATTACH 'b.db' AS b;), then reference tables as a.table_name and b.table_name in the same query, including joins and unions across them.
It attaches the database file without acquiring a write lock, allowing multiple processes or sessions to safely read the same database file concurrently, at the cost of not being able to modify it through that connection.
