database/sql: Memory databases intermittently lose data

[ncruces]

This is a restatement of this Stack Overflow question, and answer, with a look at specifics for the "ncruces" Go driver.

In database/sql when you open a :memory: database and create a table like this:

db, err := sql.Open("sqlite3", ":memory:")
…
err = db.Exec(`CREATE TABLE users (id INT, name VARCHAR(10))`)

You may intermittently get errors when querying/updating this table. Is this a bug? Why not?

[ncruces]

No, this is not a bug in the driver, or SQLite.

When you open a "connection" with database/sql you're actually opening a pool of connections. But SQLite :memory: are private to a single SQLite connection. So each query, or statement, may be run against the same or a different :memory: database, and you have no control over this. If you, by accident, get a new empty database, your table (or whatever data) will be missing.

This is a known issue that affects all Go SQLite drivers, this driver is not special in that regard. However, other drivers may offer you the option to share a :memory: database across connections by using shared cache. This is a poor solution that would not work with this driver. With this driver, connections are isolated from each other and, for the most part, cannot share memory.

The alternative is to use our memdb VFS, which is compatible with SQLite's memdb VFS.

Please also see: #97