GizStack
View .mdOpen in ChatGPTOpen in Claude

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.

gizlang
import * as Sql from "gizlang/sql";

Types

Database

gizlang
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).

  • sqlite
  • postgres
  • session
  • odbc

Rows

gizlang
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

gizlang
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.SqlError
  • dispose: () => bool throws gizlang_sql.SqlError

SqlError

gizlang
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: string
  • detail: string
  • position: _gizlang_sdk_core.Option<i64>
  • code: i64

Functions

flag

gizlang
function flag(text: string): bool

A boolean column of Rows: SQLite spells true "1", PostgreSQL "t".

sqlError

gizlang
function sqlError(message: string): SqlError

An error with MESSAGE only.

conflictError

gizlang
function conflictError(message: string): SqlError

A serialization failure (SQLSTATE 40001) with MESSAGE: a write that lost to another.

integer

gizlang
function integer(text: Option<string>): i64 throws SqlError

An 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

gizlang
function poolOf(location: string): i64

The 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

gizlang
function isSqlite(location: string): bool

Whether LOCATION names a SQLite file: sqlite:PATH, a path that ends in .db, or :memory:.

Effects: fail.

quota

gizlang
function quota(): i64

STORE_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

gizlang
function open(location: string): Database throws SqlError

The 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

gizlang
function Database.reader(self: Database, sql: string, params: Vec<Option<string>> = []): Reader throws SqlError

The 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

gizlang
function SqlError.toError(self: SqlError): Error

The 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

gizlang
function SqlError.conflicting(self: SqlError): bool

True 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

gizlang
function SqlError.text(self: SqlError): string

The failure as one line: its message, then its detail when it has one.

Effects: fail, alloc.

Database.dialect

gizlang
function Database.dialect(self: Database): string

The SQL dialect a Database speaks: "sqlite", "postgres", or an ODBC source's own (mysql, mssql, oracle, ...).

Database.remote

gizlang
function Database.remote(self: Database): bool

Whether a query crosses the network (PostgreSQL, a GizDB server, an ODBC source): a page then starts its reads together (read).

Database.read

gizlang
async function Database.read(self: Database, sql: string, params: Vec<Option<string>> = []): Promise<Rows> throws SqlError

The 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

gizlang
function Database.query(self: Database, sql: string, params: Vec<Option<string>> = []): Rows throws SqlError

One statement with parameters $1.. (none for NULL).

Effects: fail, read, write, console, io, alloc.

Database.kept

gizlang
function Database.kept(self: Database, sql: string, params: Vec<Option<string>> = []): Rows throws SqlError

A 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

gizlang
function Database.version(self: Database): string throws SqlError

The 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

gizlang
function Database.exec(self: Database, sql: string): bool throws SqlError

A script of statements without parameters or results.

Effects: fail, read, write, console, io, alloc.

Database.transaction

gizlang
function Database.transaction<T>(self: Database, work: (tx: Database) => T throws SqlError): T throws SqlError

Runs WORK once in a transaction. Commit conflicts are returned with their SQLSTATE; an application effect is never implicitly replayed.

Database.lock

gizlang
function Database.lock(self: Database, name: string): bool throws SqlError

Inside 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

gizlang
function Database.columns(self: Database, table: string): Vec<string> throws SqlError

The 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

gizlang
function Database.tables(self: Database): Vec<string> throws SqlError

The names of the tables in the current schema, in order.

Effects: fail, read, write, console, io, alloc.

Database.dispose

gizlang
function Database.dispose(self: Database): void

Close the database: a pool waits for its lent connections to come back.

Effects: fail, read, write, console, io, alloc.

Constants

POOL

gizlang
const POOL: i64 = 8

Connections each worker process keeps to PostgreSQL, unless the location's pool=N names fewer.