type User { id: Int, name: Str }
query findUser(name: Str): User[dyn] {
SELECT id, name FROM users WHERE name = {name}
}
proc main(): void {
pooled := hive.sql.pool(hive.sql.DatabaseDriver.SQLite(), "file::memory:?cache=shared", 1, 1)
if pooled 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')")
found := hive.sql.run(db, findUser("ada"))
if found is Result.Ok(users) {
echo users
}
hive.sql.close(db)
} else if pooled is Result.Error(err) {
echo "could not open the database: {err.message}"
}
}query insertUser(id: Int, name: Str): void {
INSERT INTO users (id, name) VALUES ({id}, {name})
}
query userNames(): Str[dyn] {
SELECT name FROM users ORDER BY name
}
query deleteUser(id: Int): void {
DELETE FROM users WHERE id = {id}
}
proc main(): void {
opened := hive.sql.pool(hive.sql.DatabaseDriver.SQLite(), "file::memory:?cache=shared", 1, 1)
if opened is Result.Ok(db) {
hive.sql.raw(db, "CREATE TABLE users (id INTEGER, name TEXT)")
hive.sql.run(db, insertUser(1, "ada"))
hive.sql.run(db, insertUser(2, "grace"))
if hive.sql.run(db, userNames()) is Result.Ok(names) {
echo names // a Str[dyn]: the column itself
}
if hive.sql.run(db, deleteUser(1)) is Result.Ok(rows) {
echo "deleted {rows} row" // an Int: what the statement touched
}
hive.sql.close(db)
}
}query everything(): Table {
SELECT * FROM users ORDER BY id
}
proc main(): void {
opened := hive.sql.pool(hive.sql.DatabaseDriver.SQLite(), "file::memory:?cache=shared", 1, 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 VALUES (1, 'ada'), (2, 'grace')")
echo hive.sql.run(db, everything()) // Ok([["id", "name"], ["1", "ada"], ["2", "grace"]])
hive.sql.close(db)
}
}A query is a func whose body is SQL and whose return type describes its rows. Calling one runs nothing: it answers with a hive.sql.Fragment<rows> — the text, and the values bound beside it — which can be bound, passed and held like any value, and hive.sql.run(connection, fragment) is what runs it. Values are always bound as parameters, never spliced into the text, so nothing a caller supplies can change what a statement means: an interpolated {name} in the body becomes a placeholder, ?, rewritten to $1, $2, … for PostgreSQL.
The return type decides the shape of the result:
Row[dyn], comes back as Result<Row[dyn], hive.sql.SqlError>, its columns matched to fields by name — a column spelt differently needs an alias, SELECT u.name AS author, and a row type holds scalars only;Str[dyn] or Int[dyn], comes back as that column;Table comes back as a header row of column names, then every row as text — the one result SELECT * fills, since SELECT * against a row type is a compile error: it says neither how many columns come back nor what they are called;void marks a statement, and comes back as the number of rows it touched.hive.sql.connect(driver, connString) and hive.sql.pool(driver, connString, maxOpen, maxIdle) both open a pool of connections — pool just sets its limits — answering with a Result<SqlConnection, SqlError>; hold one for the life of the program, and close it at the end. A driver is hive.sql.DatabaseDriver.SQLite(), .PostgreSQL() or .Other(name); the first two are built in, and their drivers are fetched once, on the first build that uses the module. SQL assembled at run time goes through hive.sql.raw(connection, text), which answers with a Table too. A SqlError carries a reason — "Connection", "Query", "Shape" for a different number of columns, "Convert" for a cell that does not fit its field — and a message.