Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL ATTACH DATABASE statement (SQLite)

Reference for SQLite ATTACH DATABASE: open several databases in one connection, qualify tables with schema names, copy data between them, and DETACH.

ATTACH DATABASE is an SQLite statement that adds another database to the current connection. Once attached, its tables are reachable by prefixing them with the name you gave it, so one query can read from, or write into, several database files at the same time.

The database you opened first is called main. A temporary database for TEMP tables is called temp. Every attached database adds one more schema name to the list. Other systems reach the same goal in different ways (schemas, cross-database names, linked servers), which are compared below.

SQL
ATTACH DATABASE ':memory:' AS archive;
SELECT * FROM archive.some_table;
DETACH DATABASE archive;

Try it

SQL Attach an in-memory database and copy rows into it ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Query across two schemas and detach ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
ATTACH [DATABASE] expression AS schema_name;
DETACH [DATABASE] schema_name;
Part Description
expression A string with the path of the database file. ':memory:' creates a private in-memory database, and an empty string '' creates a temporary on-disk one. A file that does not exist is created on first write.
schema_name The name you will use to qualify objects, as in schema_name.table_name. It must not already be in use. main and temp are taken.

Notes: DETACH closes the attachment; the file itself stays. An unqualified table name is looked up in temp, then main, then the attached databases in the order they were attached.

Dialect differences

Dialect How you reach another database
SQLite ATTACH DATABASE 'file.db' AS name. The default limit is 10 attached databases (configurable up to 125).
PostgreSQL No ATTACH. Use schemas inside one database (schema.table), the postgres_fdw extension, or dblink for other databases.
MySQL A "database" and a "schema" are the same thing, and db_name.table_name works across databases on one server with no setup.
SQL Server Three-part names database.schema.table across databases on one instance, and linked servers for other instances. ATTACH exists only for restoring files (CREATE DATABASE ... FOR ATTACH).
Oracle Schemas are users (schema.table), and database links (table@link_name) reach other databases.
SQL
-- MySQL
SELECT * FROM shop.orders o JOIN archive.old_orders a ON a.id = o.id;

-- SQL Server
SELECT * FROM shop.dbo.orders AS o JOIN archive.dbo.old_orders AS a ON a.id = o.id;

Usage notes

  • Qualify names when they could clash. If main and an attached database both have an orders table, write archive.orders and main.orders to be explicit.
  • Attachments belong to the connection. They disappear when it closes, and each new connection must attach again.
  • Transactions span all attached databases. A single COMMIT is atomic across them in normal (rollback-journal) mode, but not in WAL mode: there each database is atomic on its own.
  • Not inside a transaction. ATTACH and DETACH fail while a transaction is open, and you cannot detach a database that is in use.
  • Indexes and triggers live in the schema of their table, and CREATE INDEX archive.idx ON ... names the schema in front of the index name, not the table.
  • Foreign keys cannot cross databases. A reference from one file to a table in another is not enforced.
  • Security: never build the file path from user input. Attaching an arbitrary path lets an attacker create files or read other databases.
  • Listing: PRAGMA database_list (or SELECT * FROM pragma_database_list) shows the schema names and files.