DuckDB is a high-performance, in-process SQL database management system that supports various modes of operation, including the ability to create an in-memory database. An in-memory database stores all data in RAM, ensuring fast access and excellent performance.

This article explores how to create an in-memory database in DuckDB.

The Options for Creating an In-Memory Database

DuckDB supports two main approaches to creating an in-memory database:

1. Leaving the database name blank (empty string)
2. Using the special name :memory:

Both approaches result in a database that exists only in memory and is discarded when the connection is closed. this means all data is lost when the connection is closed (unless you’ve taken steps to persist it somewhere).

Example 1. Leaving the Database Name Blank

Here’s an example of using a blank database name when launching the DuckDB CLI:

duckdb

That will launch DuckDB with an in-memory database.

Here’s the output in my CLI:

Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

Example 2. Using the Special Name :memory:

We also have the option of explicitly specifying an in-memory database by using :memory: as the name:

duckdb ':memory:'

Here’s an example that uses the ATTACH statement:

ATTACH ':memory:' AS TempDB;

Here I specified :memory: as the database file and TempDB as its alias.

Showing the Database

When using the DuckDB command line interface (CLI) we can use the .database command to list the names and files of attached databases:

.databases

Output:

memory: 
TempDB: 

Both of these databases are in-memory databases. If they were persistent, the file name would be included.

Why Use an In-Memory Database?

An in-memory database can be useful for scenarios where:

• Speed is critical, and the overhead of disk I/O must be avoided.
• Temporary computations or transformations are required without persisting data.
• Testing and prototyping are performed without affecting a persistent database.

DuckDB’s in-memory capabilities make it ideal for lightweight, fast, and temporary SQL operations.

Advantages and Considerations

In-memory databases have some advantages over persistent databases, but they also have their drawbacks and limitations. Here are some things to consider.

Advantages

• Speed: Data is stored in memory, eliminating disk I/O delays.
• Isolation: Temporary data doesn’t affect persistent storage.
• Flexibility: Ideal for testing, prototyping, or temporary computations.

Considerations

• Memory Usage: Ensure the system has sufficient RAM for the dataset size.
• Persistence: Data is lost when the connection is closed. Use a persistent database for critical data.

Conclusion

Creating an in-memory database in DuckDB is straightforward and offers excellent performance for temporary tasks. Whether you use :memory: for explicit intent or leave the name blank for convenience, you can create an in-memory database just as quickly, if not more quickly, than a persistent one.