SQLite: ATTACH DATABASE Command

This SQLite post explains how to use the SQLite ATTACH DATABASE command with syntax and examples.

Description

The SQLite ATTACH DATABASE command is used to attach another SQLite database file to your current database connection. If the database file does not exist, it will be created when the command is run.

Syntax

The syntax for the ATTACH DATABASE command in SQLite is:

ATTACH [DATABASE] 'filename' AS database_name;

Parameters or Arguments

DATABASE

Optional. The command can be run as either ATTACH DATABASE or ATTACH.

filename

The name of the database file to attach to the current database connection. If the database you are attaching is in the same directory as your current database file, you do not need to specify the full path.

database_name

The logical name for the database file to use within the context of the current database connection.

Note

• After a database file has been attached, use the logical database name (not the filename) to refer to the objects within the attached database.
• The database names called main and temp are reserved names within your database connection and can not be used for attached databases. The database name called main is reserved for the primary database and the database name called temp is reserved for the database that holds temporary tables.
• Attached databases must use the same text encoding as the main database.
• If you use :memory: as the filename in the ATTACH DATABASE command, a new n-memory database will be created.
• When the database connection is closed, the attached database will be automatically be detached.

Example

Let's look at an example of how to attach a database in SQLite.

First, we'll use the .database command to show all the databases in our current connection:

sqlite> .database
seq  name             file                                                      
---  ---------------  ----------------------------

0    main             /AODBA/test.sqlite

As you can see, we have our primary database called main that can be found in the location /AODBA/test.sqlite. Next, let's attach a database called example.sqlite with the following command:

sqlite> ATTACH DATABASE '/AODBA/example.sqlite' AS example;

If the database file called example.sqlite did not already exist, it will be created when the above command is run.

Now that we have attached the example.sqlite database, let's re-run the .database command to show all of the databases in our current connection:

sqlite> .database
seq  name             file                                                      
---  ---------------  ----------------------------

0    main             /AODBA/test.sqlite
2    example          /AODBA/example.sqlite

Now you can see that the example.sqlite database has been attached and all of its objects will be available within this database connection. When you want to access one of the objects in the attached database, you can prefix the object with example as the database name.

To remove an attached database from your connection, use the DETACH DATABASE command or close the database connection.