In-memory databases and Shared Cache

[ncruces]

It's common to use shared cache to share in-memory databases between connections. This is specially useful for tests.

But shared cache is not supported by this driver:

go-sqlite3/sqlite3/sqlite_opt.h

Line 12 in f7ac770

#define SQLITE_OMIT_SHARED_CACHE

What's the alternative?

[ncruces]

Shared cache use is discouraged, SQLITE_OMIT_SHARED_CACHE is recommended.

The increasingly recommended alternative is to use the memdb VFS, which we do support.

So instead of:

db, err := sql.Open("sqlite3", "file:data.db?mode=memory&cache=shared")

Do:

import _ "github.com/ncruces/go-sqlite3/vfs/memdb"
…
db, err := sql.Open("sqlite3", "file:/data.db?vfs=memdb")

The slash / in the name is important, as that makes it a shared database!

This is especially useful for tests. Using shared cache with any concurrency requires using unlock notifications to handle SQLITE_LOCKED, whereas "normal" connections require a busy handler to handle SQLITE_BUSY. If you use shared cache in-memory databases, you'll be testing something very different from production.

For tests, what you (typically) want is a database that's:

• created for that test,
• shared between all connections opened by the test,
• isolated from connections opened by other tests, and
• deleted after the test ends.

For this use:

uri := memdb.TestDB(t)
db, err := sql.Open("sqlite3", uri)