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 hive.sql.run(db, 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) {
hive.sql.raw(db, "CREATE TABLE users (id INTEGER, name TEXT)")
hive.sql.raw(db, "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. connect and pool both open a pool of connections, so this has a sharp edge. One query at a time works; several at once do not — the pool opens further connections, each 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 one query at a time starts failing the moment requests overlap, which is exactly what happens behind an HTTP server.
There are two ways out, and one of them is the real answer. pool(…, 1, 1) holds the pool to a single connection, which works but runs every query in the program through it one by one. "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. None of this is Hive's rule — it is SQLite's — but it is worth knowing before production teaches it to you.