SQL

A row type

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) {
		using db run raw "CREATE TABLE users (id INTEGER, name TEXT)"
		using db run raw "INSERT INTO users (id, name) VALUES (1, 'ada')"

		found := using db run 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}"
	}
}

One column, and no rows at all

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) {
		using db run raw "CREATE TABLE users (id INTEGER, name TEXT)"
		using db run insertUser(1, "ada")
		using db run insertUser(2, "grace")

		if using db run userNames() is Result.Ok(names) {
			echo names                      // a Str[dyn]: the column itself
		}
		if using db run deleteUser(1) is Result.Ok(rows) {
			echo "deleted {rows} row"       // an Int: what the statement touched
		}
		hive.sql.close(db)
	}
}

A query declaration is a func whose body is inline SQL and whose return type describes its rows. Columns are matched to fields by name, not position, so reordering a SELECT can never silently remap them, and values are always bound as parameters — never spliced into the text — so nothing a caller supplies can change what the statement means. connect opens a single connection and pool a whole pool of them, both answering with a Result<SqlConnection, SqlError>; close lets one go. SQL assembled at run time, rather than declared as a query, goes through run raw instead — untyped by construction, and easy to grep for.The return type does more than name the row: it decides the shape of the whole result, so you can read that off the declaration without looking at the SQL at all. A declared row type comes back as Result<Row[dyn], SqlError>. A single scalar vector — Str[dyn], Int[dyn] — needs no row type at all and comes back as that column. And void marks a statement rather than a query, coming back as the number of rows it touched.Because columns are matched by name, name them — and the compiler holds you to it. A column whose name differs from its field needs an alias (SELECT u.name AS author), a row type holds scalars only, and SELECT * against a declared row type is a compile error. It says neither how many columns come back nor what they are called, so nothing can line them up against the fields; and what it stands for changes the day somebody adds a column to the table, which would turn a query that compiled and worked into rows that no longer fit, in a program nobody edited. The rule is about the result and only the result, so count(*), a * 2, a star inside a subquery and the select list of a void statement all still compile — and so does SELECT * into a Table, which is rows of cells and has no field names to disagree with.

← Prev Next →