SQLite - DETACH Database

SQLite - DETACH DATABASE

The DETACH DATABASE command is used to disconnect a previously attached database from the current database connection. Each attached database is identified by an alias, and the DETACH command uses this alias to specify which database to disconnect.

You cannot detach the default main or temp databases. If a database file was attached using multiple aliases, detaching one alias does not affect the others. If the detached database was an in-memory database, it is destroyed and its data is lost permanently.

Syntax

The syntax requires the alias name that was used in the ATTACH DATABASE statement.

DETACH DATABASE 'Alias-Name';

Complete Example

Let’s walk through a full attach-and-detach cycle. First, we’ll create two separate database files.

-- In your terminal:
$ sqlite3 main.db "CREATE TABLE t1 (id INT); INSERT INTO t1 VALUES (1);"
$ sqlite3 reporting.db "CREATE TABLE reports (report_id INT); INSERT INTO reports VALUES (101);"

Now, let’s start a session with main.db and see the process.

$ sqlite3 main.db

-- Step 1: Check initial database connections
sqlite> .databases
seq  name             file
---  ---------------  --------------------------
0    main             /path/to/main.db

-- Step 2: Attach the reporting database with the alias 'reports_db'
sqlite> ATTACH DATABASE 'reporting.db' AS reports_db;

-- Step 3: Verify the database is now attached
sqlite> .databases
seq  name             file
---  ---------------  --------------------------
0    main             /path/to/main.db
2    reports_db       /path/to/reporting.db

-- You can now query across databases
sqlite> SELECT * FROM main.t1, reports_db.reports;
id  report_id
--  ---------
1   101

-- Step 4: Detach the reporting database
sqlite> DETACH DATABASE 'reports_db';

-- Step 5: Check the final state. The connection is gone.
sqlite> .databases
seq  name             file
---  ---------------  --------------------------
0    main             /path/to/main.db

-- Attempting to query the detached database will now result in an error
sqlite> SELECT * FROM reports_db.reports;
Error: no such table: reports_db.reports