The in-memory trap

A row type

type User { id: Int, name: Str }

query findUser(name: Str): User[dyn] {
	SELECT id, name FROM users WHERE name = {name}
}

proc lookUp(db: hive.sql.SqlConnection, name: Str): Str {
	if using db run findUser(name) is Result.Ok(rows) {
		if rows bounds 0 {
			return rows[0].name
		}
	}
	return "?"
}

proc main(): void {
	// A plain ":memory:" would give each of these eight connections a
	// private, empty database of its own. The shared cache is what makes
	// them all the same one.
	opened := hive.sql.pool(hive.sql.DatabaseDriver.SQLite(), "file::memory:?cache=shared", 8, 1)
	if opened is Result.Ok(db) {
		using db run raw "CREATE TABLE users (id INTEGER, name TEXT)"
		using db run raw "INSERT INTO users (id, name) VALUES (1, 'ada'), (2, 'grace')"

		echo await [lookUp(db, "ada"), lookUp(db, "grace")]
		hive.sql.close(db)
	}
}

A plain :memory: SQLite database belongs to the connection, not to the process. Each connection gets a private, empty database that vanishes when it closes — and combined with a pool, that has a sharp edge. One query at a time works fine. Eight at once does not: the pool opens further connections, each one lands on a database of its own, and the queries that went to the new ones come back "no such table". A program that passes every test single-threaded starts failing the moment requests overlap, which is exactly what happens behind an HTTP server, since it runs each request on a thread of its own.There are two ways out, and only one of them is a real answer. pool(…, 1, 1) holds the pool to a single connection, which works but serialises every query in the program through it. "file::memory:?cache=shared" is the one to use: the connections share a single in-memory database, so the pool can be as wide as the work is. None of this is Hive's rule — it is SQLite's — but it is the thing about an in-memory database worth knowing before production teaches it to you.

← Prev Next →