SQLite: How to Attach an In-Memory Database in SQLite

This SQLite tutorial explains how attach an in-memory database in SQLite with syntax and examples.

Description

An in-memory SQLite database can be created and attached to your database connection using the ATTACH DATABASE command. An in-memory database will be saved in memory instead of being file-based.

Syntax

The syntax to attach an in-memory database in SQLite is:

ATTACH [DATABASE] ':memory:' AS database_name;

Parameters or Arguments

DATABASE

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

database_name

The logical name for the in-memory database to use within the context of the current database connection.

Note

• After the in-memory database has been attached, use the logical database name to refer to the objects within the memory-based 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.
• When the database connection is closed, the in-memory database will be automatically be detached.

Example

Let's look at an example of how to attach an in-memory 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             /techonthenet/test.sqlite

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

sqlite> ATTACH DATABASE ':memory:' AS mem_example;

This command will create and attach a new in-memory database called mem_example. It will be stored in memory and not saved to a file.

Now that we have attached the in-memory 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             /techonthenet/test.sqlite
2    mem_example

Now you can see that the in-memory database called mem_example has been attached. It is stored in memory and not stored as a file so the file value is blank. When you want to access one of the objects in the in-memory database, you can prefix the object with mem_example as the database name.

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

More Examples

Here are more examples that show how to use the ATTACH DATABASE command in SQLite: