gizlang/sql
one SQL database by its location, with the calls that sql gives on Node and Bun. A location is a PostgreSQL server (postgres://, which reaches a GizDB server too), a SQLite file, or on giz an ODBC data source (odbc:, and mysql:// through the MariaDB driver). One SQL for all: parameters $1.., each value text and none for NULL; what differs (DDL types, locks) is the caller's, by dialect.
import * as Sql from "gizlang/sql";Types
Database
type Database = | { kind: "sqlite"; db: Sqlite.Database; } | { kind: "postgres"; pool: Pg.Pool; } | { kind: "session"; connection: Pg.Connection; } | { kind: "odbc"; connection: i64; speaks: string; inside: bool; locked: Ref<bool>; }A database: a SQLite file, a PostgreSQL pool, inside a transaction the one PostgreSQL connection it runs on, or an ODBC connection that speaks the dialect SPEAKS (INSIDE in a transaction).
sqlitepostgressessionodbc
Rows
type Rows = { columns: Vec<string>; rows: Vec<Vec<Option<string>>>; affected: i64; }A statement's column names, its rows (each column as text, none for NULL), and the rows an INSERT, UPDATE or DELETE without RETURNING changed.
columns:Vec<string>rows:Vec<Vec<_gizlang_sdk_core.Option<string>>>affected:i64
Reader
type Reader = { columns: Vec<string>; step: () => Option<Vec<Option<string>>> throws SqlError; dispose: () => bool throws SqlError; }A read stepped one row at a time: the columns the database named, the next row (none at the end) and the close. SQLite gives one. The drivers whose answer comes as one read give none: refused, never approximated.
columns:Vec<string>step:() => _gizlang_sdk_core.Option<Vec<_gizlang_sdk_core.Option<string>>> throws gizlang_sql.SqlErrordispose:() => bool throws gizlang_sql.SqlError
SqlError
type SqlError = { sqlstate: Option<string>; message: string; detail: string; position: Option<i64>; code: i64; }A failure. PostgreSQL fills sqlstate, detail and position from the server's error; SQLite has none of them.
code is the engine's own number (the SQLite extended result code, a transport errno), else 0.
sqlstate:_gizlang_sdk_core.Option<string>message:stringdetail:stringposition:_gizlang_sdk_core.Option<i64>code:i64
Functions
flag
function flag(text: string): boolA boolean column of Rows: SQLite spells true "1", PostgreSQL "t".
sqlError
function sqlError(message: string): SqlErrorAn error with MESSAGE only.
conflictError
function conflictError(message: string): SqlErrorA serialization failure (SQLSTATE 40001) with MESSAGE: a write that lost to another.
integer
function integer(text: Option<string>): i64 throws SqlErrorAn integer column of Rows (decimal in both engines). NULL, or text that is not an i64, is an error, never a
silent 0.
Effects: fail, alloc.
poolOf
function poolOf(location: string): i64The pool size a postgres:// LOCATION asks for with its pool=N parameter (a role whose connection limit is low, such
as a read-only migration role), else POOL.
Effects: fail, alloc.
isSqlite
function isSqlite(location: string): boolWhether LOCATION names a SQLite file: sqlite:PATH, a path that ends in .db, or :memory:.
Effects: fail.
quota
function quota(): i64STORE_QUOTA in bytes, 0 when it is not set: past it a write to a SQLite file fails and reads go on.
Effects: fail, io, alloc.
open
function open(location: string): Database throws SqlErrorThe database at LOCATION: postgres://USER:PASSWORD@HOST:PORT/DATABASE (no percent escapes; as libpq, a trailing ?host=/FOLDER names the folder of the server's unix-domain socket instead of HOST, which may then be empty; &pool=N keeps fewer than POOL connections), or a SQLite file (isSqlite), made with its folder when missing. A SQLite file is opened as each worker process (GIZLANG_WORKERS) needs it: WAL, so readers never wait for the writer; a writer that finds another worker's write in progress waits up to 5 s for it instead of failing; commits sync at checkpoints (synchronous NORMAL: a power loss may drop the last commits, never corrupt). SYS is the process's own unless a .giz caller passes one: a .ts app never names System, as on Node and Bun.
Effects: fail, read, write, console, io, alloc.
Methods
Database.reader
function Database.reader(self: Database, sql: string, params: Vec<Option<string>> = []): Reader throws SqlErrorThe reader of QUERY, its parameters as text bound, on a database the driver steps rows of. PostgreSQL and an ODBC source answer as one read here: they refuse, and a caller narrows the query or keeps the one read.
Effects: fail, read, write, console, io, alloc.
SqlError.toError
function SqlError.toError(self: SqlError): ErrorThe error as TypeScript sees it: an Error named SqlError, with the SQLSTATE (or the engine's code) as its cause.
Effects: fail, alloc.
SqlError.conflicting
function SqlError.conflicting(self: SqlError): boolTrue when the write lost to another or broke a key or constraint: PostgreSQL class 23 (integrity) or 40 (rollback), SQLite's constraint code.
Effects: fail.
SqlError.text
function SqlError.text(self: SqlError): stringThe failure as one line: its message, then its detail when it has one.
Effects: fail, alloc.
Database.dialect
function Database.dialect(self: Database): stringThe SQL dialect a Database speaks: "sqlite", "postgres", or an ODBC source's own (mysql, mssql, oracle, ...).
Database.remote
function Database.remote(self: Database): boolWhether a query crosses the network (PostgreSQL, a GizDB server, an ODBC source): a page then starts its reads together (read).
Database.read
async function Database.read(self: Database, sql: string, params: Vec<Option<string>> = []): Promise<Rows> throws SqlErrorThe rows of a query that reads, on a task of its own, so reads started together overlap on the pool.
Effects: fail, read, write, console, io, alloc.
Database.query
function Database.query(self: Database, sql: string, params: Vec<Option<string>> = []): Rows throws SqlErrorOne statement with parameters $1.. (none for NULL).
Effects: fail, read, write, console, io, alloc.
Database.kept
function Database.kept(self: Database, sql: string, params: Vec<Option<string>> = []): Rows throws SqlErrorA read whose answer depends only on the data it reads (no clock, no random draw, no function with effects). SQLite keeps its answer until the data changes (gizlang/sql/sqlite Database.kept); another database runs it as a query.
Effects: fail, read, write, console, io, alloc.
Database.version
function Database.version(self: Database): string throws SqlErrorThe version of the data this connection reads now: two reads in one version see the same data. SQLite counts its commits (gizlang/sql/sqlite Database.version); another database has none here, "".
Effects: fail, read, write, console, io, alloc.
Database.exec
function Database.exec(self: Database, sql: string): bool throws SqlErrorA script of statements without parameters or results.
Effects: fail, read, write, console, io, alloc.
Database.transaction
function Database.transaction<T>(self: Database, work: (tx: Database) => T throws SqlError): T throws SqlErrorRuns WORK once in a transaction. Commit conflicts are returned with their SQLSTATE; an application effect is never implicitly replayed.
Database.lock
function Database.lock(self: Database, name: string): bool throws SqlErrorInside a transaction: the lock named NAME until it ends, so transactions taking the same name run one after another while readers go on reading (the name need not be a table yet). SQLite's writers already run one after another; PostgreSQL takes a transaction advisory lock on the name; MySQL a session lock that the transaction's end frees.
Effects: fail, read, write, console, io, alloc.
Database.columns
function Database.columns(self: Database, table: string): Vec<string> throws SqlErrorThe names of a table's columns; none when it does not exist. A table of a schema (auth.users) is looked up in that schema; on SQLite that is the database attached as it.
Effects: fail, read, write, console, io, alloc.
Database.tables
function Database.tables(self: Database): Vec<string> throws SqlErrorThe names of the tables in the current schema, in order.
Effects: fail, read, write, console, io, alloc.
Database.dispose
function Database.dispose(self: Database): voidClose the database: a pool waits for its lent connections to come back.
Effects: fail, read, write, console, io, alloc.
Constants
POOL
const POOL: i64 = 8Connections each worker process keeps to PostgreSQL, unless the location's pool=N names fewer.