SQLite In-Memory Database: Fast, Disposable Databases with :memory:

A Database That Lives in RAM

SQLite has a special filename: :memory:. Open a database with that name and SQLite skips the disk entirely — the whole database lives in RAM. Tables, indexes, transactions, foreign keys, every feature works exactly the same. The only difference is that when the connection closes, the database is gone.

From the command line:

sqlite3 :memory:

You're now at the SQLite prompt with a fresh, empty database that exists only in memory. Create a table, insert some rows, query them — all normal:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('first note'), ('second note');
SELECT * FROM notes;

Quit the session and that data evaporates. No file is left behind, because no file was ever created.

Why You'd Want One

A database that can't survive a restart sounds like a bug, not a feature. It's actually useful in three situations.

Tests. Every test gets a clean database in milliseconds. No tempfile cleanup, no leftover state from the previous run, no shared fixture file getting locked. Most Python, Node, and Go test suites that use SQLite open :memory: for exactly this reason.

Throwaway analysis. Load a CSV, run some queries, throw it away. Faster than spinning up a real database and easier than parsing the file in code each time.

Caching and scratch space. Inside a long-running program, an in-memory SQLite database is a surprisingly good ad-hoc query engine for data you've already loaded.

The common thread: you want SQL, you don't want persistence.

Performance: Faster, But Not Magically So

In-memory databases skip the disk, so writes that would normally hit the filesystem are just memory updates. Workloads that are I/O-bound get noticeably faster. Workloads that are CPU-bound — complex query planning, big sorts — barely change, because SQLite was already caching hot pages in memory anyway.

A quick demonstration of how identical the syntax is:

CREATE TABLE numbers (n INTEGER);

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1000
)
INSERT INTO numbers SELECT n FROM seq;

SELECT COUNT(*), SUM(n), AVG(n) FROM numbers;

That ran against an in-memory database, but it's the same SQL you'd run against a file. The database engine doesn't care.

In-Memory vs File: When to Pick Which

The trade-off is simple, and worth being explicit about:

• File database (mydata.db): Persists across restarts. Multiple processes can open it. Survives crashes (with WAL mode, mostly). Use this for anything that needs to remember things.
• In-memory database (:memory:): Disappears on close. Private to the connection that opened it (by default). Faster for write-heavy throwaway work. Use this for tests, scratch work, and short-lived caches.

If you're not sure, you want a file. In-memory is the special case.

Each Connection Gets Its Own

A subtle thing that catches people: opening :memory: twice gives you two separate databases. They don't share tables, they don't share data, they don't see each other at all.

-- Terminal 1
sqlite3 :memory:
sqlite> CREATE TABLE t (x); INSERT INTO t VALUES (1);

-- Terminal 2
sqlite3 :memory:
sqlite> SELECT * FROM t;
Error: no such table: t

That's not a bug — it's the design. :memory: means "a private database for this connection." The same applies inside a single program: if your code opens two connections to :memory:, they each get their own isolated database.

Sharing an In-Memory Database Across Connections

If you do need multiple connections to see the same in-memory database, SQLite supports it through URI filenames and shared cache. The magic string is file::memory:?cache=shared:

sqlite3 'file::memory:?cache=shared'

Any connection in the same process that opens that exact URI joins the same database. Close all of them and the database is gone.

You can also give a named in-memory database, which is helpful when you want several distinct shared databases:

sqlite3 'file:mydb?mode=memory&cache=shared'

The name mydb here is just a label — there's still no file. Two connections that open file:mydb?mode=memory&cache=shared share one database; a connection that opens file:other?mode=memory&cache=shared gets a different one.

Persisting an In-Memory Database to Disk

Sometimes you do an entire workflow in memory and then decide you want to keep the result. The CLI has a .backup dot-command for this:

sqlite3 :memory:
sqlite> CREATE TABLE results (id INTEGER, score REAL);
sqlite> INSERT INTO results VALUES (1, 0.91), (2, 0.87);
sqlite> .backup snapshot.db
sqlite> .quit

snapshot.db is now a regular file database with the same contents. You can open it later with sqlite3 snapshot.db and pick up where you left off.

The reverse works too — .restore loads a file database into the current connection's memory:

sqlite3 :memory:
sqlite> .restore snapshot.db
sqlite> SELECT * FROM results;

From application code, the SQLite C API exposes the same machinery as sqlite3_backup_init, and most language bindings wrap it. Python's sqlite3 module has Connection.backup(), for example.

A Common Pitfall

People sometimes try to "save" an in-memory database by attaching a file and copying:

ATTACH DATABASE 'output.db' AS disk;
CREATE TABLE disk.notes AS SELECT * FROM main.notes;
DETACH DATABASE disk;

That works for simple table copies, but it doesn't preserve indexes, triggers, views, or foreign keys exactly as they were. For a faithful copy of an entire database, use .backup (or the backup API) — it does a binary-accurate copy at the page level.

What You Take Away

• :memory: is a special SQLite filename that creates a database in RAM with no file backing.
• The SQL is identical to a file database — same tables, same queries, same constraints.
• Each connection to :memory: is private; use shared-cache URIs (file::memory:?cache=shared) when multiple connections need to share one.
• It's the right tool for tests, throwaway analysis, and short-lived caches — not for anything that needs to survive a restart.
• Promote a memory database to disk with .backup when you decide you want to keep it.

Next: Creating Tables

You've seen CREATE TABLE flash by in a few examples already. The next page slows down and walks through it properly — column definitions, types, the constraints you can attach, and the small choices that make a schema pleasant to live with.