Access application data

Preview Runs today. The interface can still change. Roadmap

This guide owns data access, not GizDB SQL or server administration. Start here to connect an application to GizDB, PostgreSQL, SQLite or ODBC, query typed records and carry committed changes to GizWeb. Model your data defines records, validation and policies. Use GizDB covers the native engine, SQL, indexes, search and protocols.

The database is the source of truth. A Source reports which operations it can execute; not every adapter supports snapshots, CDC, schema changes or server-specific SQL.

Choose the right data layer

The web guide tutorial uses level 1. Each level below it gives more control and needs more code. Use the highest level that does the job, and go down for one query, not for the whole app.

Level 1 keeps its records in the GizDB database that the app's own process serves: a folder, data/db for a Pages.app, with no separate server to start. With DATABASE_URL set to a gizdb or PostgreSQL server, the same records live there: the app reads and writes them by SQL, its pages follow their changes through a Relay, and it serves on one thread. A SQLite file cannot hold an app (it has no schema for the app's people), so Pages.app stops with a message that says so. Levels 2 to 5 reach the other engines.

At level 1, the records of a Shape are read with a filter whose values are bound.

import * as Model from "gizdb:model";
import * as Data from "gizdb:data";

/** A saved note. */
@Model.Table("notes")
export type Note = { @Model.Key id: string; title: string; body: string; };

/** The notes with the title TITLE. */
export function titled(db: Data.Db, title: string): Vec<Note> {
    const shape = Model.shape<Note>();
    return Data.find(db, shape, "title = $1", [title], Model.rowReader<Note>(shape))!;
}

At level 5 you map each column yourself. SQLite gives every column as optional text, and you bind the values ($1), never put them into the SQL text:

import {Option} from "gizlang:core";
import * as Sqlite from "gizlang:sqlite";

function main(): void {
    const db = Sqlite.open("notes.db")!;
    db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL)")!;
    const rows = db.query("SELECT id, title FROM notes WHERE title = $1", [Option.some("Milk")])!.rows;
    print(`${rows.length} notes`);
}

Open a database and query it with types

Access.local opens an embedded database in a folder: the data actor runs in your process. One engine opens a folder at a time: a second opener, in this or another process, fails until the first closes. The threads of one process (Task.thread) share the engine: each thread calls Access.connect(local.endpoint, local.identity, local.secret) for its own Connection. Other processes reach the data through a gizdb server, with Source.postgres.

This program makes a table, queries it with a typed query, updates a row with an expected revision and rolls a transaction back.

import {System} from "gizlang:system";
import {Result} from "gizlang:core";
import * as Access from "gizdb:access";
import * as Typed from "gizdb:typed";
import * as Query from "gizdb:query";
import {Value, Type, DbError, parse} from "gizdb";

type Task = { id: i64; title: string; revision: i64; };

/** The parameters of the query, and the record of a result row. */
function params(done: bool): Vec<Value> throws DbError { return [Value.boolean(done)]; }
function task(row: Vec<Value>): Task throws DbError {
    return Task(try Typed.integerCodec().decode(row[0]), try Typed.textCodec().decode(row[1]), try Typed.integerCodec().decode(row[2]));
}

function main(sys: System): void {
    const dir = sys.temporaryDir()!;
    const db = Access.local(sys, dir, "guide", 67108864)!;
    const c = db.connection;
    c.write(parse("CREATE TABLE tasks(id BIGINT PRIMARY KEY, title TEXT NOT NULL, done BOOLEAN NOT NULL, revision BIGINT NOT NULL)")!, [])!;
    const rows = [[Value.int(1), Value.text("Write the guide"), Value.boolean(false), Value.int(0)],
                  [Value.int(2), Value.text("Test the guide"), Value.boolean(false), Value.int(0)]];
    c.write([Query.insert("tasks", ["id", "title", "done", "revision"], rows)!], [])!;

    // A typed query: columns with codecs, one parameter, decoded records.
    const id = Query.field("tasks", "id", Typed.integerCodec());
    const title = Query.field("tasks", "title", Typed.textCodec());
    const revision = Query.field("tasks", "revision", Typed.integerCodec());
    const done = Query.field("tasks", "done", Typed.booleanCodec());
    let tree = Query.where(Query.table("tasks"), Query.equalTo(done, Query.parameter(0, Typed.booleanCodec())));
    tree = Query.projection(tree, "id", id); tree = Query.projection(tree, "title", title); tree = Query.projection(tree, "revision", revision);
    tree = Query.orderBy(tree, id, false, false);
    const outputs = [Typed.Slot("id", Type.int(), false), Typed.Slot("title", Type.text(), false), Typed.Slot("revision", Type.int(), false)];
    const open = Query.Prepared(tree, [Typed.Slot("done", Type.boolean(), false)], outputs,
        (input: bool): Result<Vec<Value>, DbError> => { return params(input); },
        (row: Vec<Value>): Result<Task, DbError> => { return task(row); }, "");
    const source = Access.native(c, "guide");
    for (const task of open.fetch(source, false)!) { print(string(task.id) + " " + task.title + " r" + string(task.revision)); }

    // An update that expects the revision it read: exactly one row, or a conflict.
    const finish = Query.require(parse("UPDATE tasks SET done = true, revision = revision + 1 WHERE id = $1 AND revision = $2")![0], 1)!;
    c.write([finish], [Value.int(1), Value.int(0)])!;
    try {
        void try c.write([finish], [Value.int(1), Value.int(0)]);
        print("stale write accepted");
    } catch (error) {
        print("stale write: " + error.message);
    }

    // A transaction has one owner. Its writes are visible to others only after commit.
    const tx = c.begin()!;
    tx.write([finish], [Value.int(2), Value.int(0)])!;
    tx.rollback()!;
    print("open after rollback: " + string(open.fetch(source, false)!.length));

    db.close()!;
    dir.close();
}

1 Write the guide r0
2 Test the guide r0
stale write: required 1 affected rows, got 0
open after rollback: 1

Rules:

• Write with an expected revision. Read the revision, then write with Query.require(statement, 1). A concurrent write makes the count 0, and the whole write rolls back with a conflict. Read again and retry.
• Use Connection.begin for more than one write that must succeed together. commit, rollback, savepoint, rollbackTo and release act on that one transaction. An idle transaction expires after its lease.
• Page with keys, not offsets: Query.page(source, query, input, keyFields, after, size).
• Load relations in one query for each relation: Query.include(source, parents, relation).
• Raw SQL with typed results is the escape hatch: Query.sql(text, parameters, outputs, encode, decode). Query.statements(text) parses a script of several statements (DDL, admin writes).
• Write a Shape's records with the SQL text of Model.insertRowsSql(shape, n) (or Store.insert(db, shape, record) for one record; db/store.giz also has migrate, records, record and one) and read them with Model.select(shape, filter, parameters, encode, of) (key order). The same statement and query run on each Source: Access.native(connection, id) (GizDB), sqlite(database, id) (db/ingest/sqlitesource.giz, so a program without SQLite does not link it), Source.postgres(connection, id) and Source.odbc(connection, id, semantics) (SQLite and MySQL drivers are tested). Source.semantics(dialect) gives the collation, time zone and NULL typing of postgres, sqlite and mysql.
• A watch needs a change log. GizDB gives one (source.watch). A SQLite or PostgreSQL client source refuses the watch as a missing capability. A watched query has no ORDER BY.

Open an application Store

The low-level gizdb tutorial above embeds an engine directly. gizdb:embedded opens a folder and serves it from the calling process when no server owns it. gizdb:store is a client: it cannot start an unserved folder. It also accepts .db (SQLite), :memory: and postgres://USER:PASSWORD@HOST:PORT/DATABASE locations, sharing one SQL-oriented API.

import {System} from "gizlang:system";
import {Option} from "gizlang:core";
import * as Embedded from "gizdb:embedded";

export function main(sys: System): void {
    let db = Embedded.open(sys, "app-data")!;
    db.exec("CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY, body TEXT)")!;
    db.query("INSERT INTO notes (id, body) VALUES ($1, $2) ON CONFLICT (id) DO NOTHING",
        [Option.some("1"), Option.some("hello")])!;
    const result = db.query("SELECT body FROM notes WHERE id = $1", [Option.some("1")])!;
    for (const row of result.rows) { print(row[0] ?? "NULL"); }
    db.close();
}

Run with giz app-data.giz; it prints hello, including on later runs. query binds the $1-style parameters and returns text-or-NULL rows plus an affected count for mutations; exec runs SQL without parameters or returned rows. select also returns column names; transaction, tables, columns and close are in db/store.giz.

For a worker, import gizdb:store and call Store.open(sys, "app-data") after an owning server or embedded process is running. For an external PostgreSQL-protocol server, use Store.open(sys, "postgres://..."); the URL optionally takes &pool=N. The Gizlang PostgreSQL client supports certificate-checked TLS via sslmode=require, verify-ca or verify-full. See PostgreSQL TLS and server configuration.

Use typed queries with gizdb:typed codecs and gizdb:model for record contracts and schema migrations. follow<T> does not make a primary key automatically; define the key and constraints needed by the application.

Run typed queries from Gizlang

Preview Runs today. The interface can still change. Roadmap

gizdb:query connects Gizlang record types to tables. gizdb:typed holds the value contracts (Slot, the codecs). It does not import the SQL engine, so a web model or an application that only checks and converts values does not link the database:

• follow<T>(engine, conn, table) (gizdb:sql/session, next to connection) makes or changes the table so that its columns agree with the record type T. It returns the statements that it ran.
• Query.sql<P, R>(text, parameters, outputs, encode, decode) makes a typed query. P is the parameter type, and R is the type of one result row.
• The codecs of gizdb:typed (integerCodec, textCodec, booleanCodec, floatCodec, decimalCodec, optionalCodec and others) convert between Gizlang values and database values.

Make typed.giz:

import {System} from "gizlang:system";
import {Result} from "gizlang:core";
import {Value, Type} from "gizdb:value";
import {DbError} from "gizdb:error";
import {open} from "gizdb:core/engine";
import {connection, follow} from "gizdb:sql/session";
import * as Typed from "gizdb:typed";
import * as Query from "gizdb:query";

/** The record type is the table definition. */
type Task = { id: i64; title: string; done: bool; };

/** The parameters of the query, and the record of a result row. */
function params(from: i64): Vec<Value> throws DbError { return [Value.int(from)]; }
function task(row: Vec<Value>): Task throws DbError {
    return {
        id: Typed.integerCodec().decode(row[0])!,
        title: Typed.textCodec().decode(row[1])!,
        done: Typed.booleanCodec().decode(row[2])!,
    };
}

export function main(sys: System): void {
    sys.files()!.makeDirectories("typed-data")!;
    let e = open(sys.directory("typed-data")!)!;
    let c = connection("postgres");

    // follow<T> creates or changes the table so that it agrees with the record type.
    for (const sql of follow<Task>(e, c, "task")!) { print(sql); }
    c.run(e, "INSERT INTO task VALUES (1, 'Plan', true), (2, 'Build', false), (3, 'Ship', false)", [], 0, 0u)!;

    // A typed query: an i64 parameter in, Task records out.
    const open = Query.sql<i64, Task>(
        "SELECT id, title, done FROM task WHERE done = false AND id >= $1 ORDER BY id",
        [Typed.Slot("from", Type.int(), false)],
        [Typed.Slot("id", Type.int(), false), Typed.Slot("title", Type.text(), false), Typed.Slot("done", Type.boolean(), false)],
        (from: i64): Result<Vec<Value>, DbError> => { return params(from); },
        (row: Vec<Value>): Result<Task, DbError> => { return task(row); })!;

    const params = open.values(1)!;
    const rows = c.query(e, open.tree, params)!;
    for (const row of rows.rows) {
        const task = open.decode(row)!;
        print(string(task.id) + " " + task.title + " done=" + string(task.done));
    }
    e.close()!;
}

Run it one time in a new folder:

giz typed.giz

CREATE TABLE "task" ("id" BIGINT, "title" TEXT, "done" BOOLEAN)
2 Build done=false
3 Ship done=false

• Query.sql accepts exactly one SELECT. It parses the text one time. open.values(input) checks the parameters against their slots before the query runs.
• c.query(e, tree, params) runs the parsed query and returns the rows.
• To rename a field later, write /** @was title */ above the new field name. follow then renames the column, and keeps its data.
• The table that follow makes has no primary key. Thus a second run of this program inserts the rows again.

Connect external databases

Choose gizdb:store to give app code one database API. Choose gizdb:source when a typed query must run on a different provider, with an explicit capability contract. A Source does not turn every external database into the DATABASE_URL store of GizWeb.

Source.postgres uses the PostgreSQL wire protocol for GizDB or a PostgreSQL server; it is not the same as GizDB's Access.native watch-enabled source. ODBC support means a generic driver boundary, not certified SQL Server or Oracle behavior.

PostgreSQL, managed PostgreSQL and TLS

Set the URL outside your program. Store.open uses gizlang:postgres and supports connection pooling. The URL parser expects an explicit port and does not accept general URL percent-escaping; do not assume it supports every libpq URI extension.

export DATABASE_URL='postgres://app:secret@db.example.com:5432/app?sslmode=verify-full&sslrootcert=/etc/my-ca.pem'

import {System} from "gizlang:system";
import {Option} from "gizlang:core";
import * as Store from "gizdb:store";

export function main(sys: System): void {
    let db = Store.open(sys, sys.environment("DATABASE_URL")!)!;
    const result = db.query("SELECT name FROM users WHERE id = $1", [Option.some("1")])!;
    for (const row of result.rows) { print(row[0] ?? "NULL"); }
    db.close();
}

The Store URL also accepts &pool=N after its first query parameter. Never embed passwords in source files or logs. gizlang:postgres does support TLS in the current SDK: sslmode=require, verify-ca and verify-full negotiate TLS and check the server certificate and host name. Supply sslrootcert=FILE when your certificate chain needs an explicit authority. This is stricter than libpq's usual sslmode=require behavior. sslmode=disable, allow and prefer do not request TLS in this implementation. The parsing and option round trips are tested in tests/sdk/postgres/url.test.giz. Test network trust and authentication against the actual target before deployment.

For a typed Source instead of a Store.Database, use this inside a function with a System sys parameter:

import * as Pg from "gizlang:postgres";
import * as Source from "gizdb:source";
import {System} from "gizlang:system";

function customers(sys: System): void {
    const connection = Pg.connect(sys, Pg.optionsOf(sys.environment("DATABASE_URL")!));
    const source = Source.postgres(connection, "customers");
    // Run a Query.Prepared on source. Close connection when the work is done.
    connection.close();
}

See Open a database and query it with types for the complete typed-query execution path.

SQLite, including an in-memory database

For a standalone SQLite file, use Sqlite.open("app.db") or Store.open(sys, "app.db"); Store.open(sys, ":memory:") is also supported. Use Sqlite.Database.transaction or Store.Database.transaction to keep related writes atomic. Store.query binds $1 parameters and returns optional text cells. SQLite uses SQLite's own types and concurrency semantics, not GizDB's.

For Source queries, open gizlang:sqlite, then adapt it with sqlite(database, "table") from gizdb:ingest/sqlitesource. The separate adapter keeps applications without SQLite from linking its native library.

GizWeb Pages.app rejects a .db-file DATABASE_URL because its people and access records require schemas unsupported by that app path. Direct DAL or standalone Store usage is a different case and can use SQLite.

MySQL, MariaDB and other ODBC drivers

gizlang:odbc uses the system ODBC driver manager (libodbc.so.2 on Linux) plus an installed database-specific driver. A MySQL/MariaDB example:

import {System} from "gizlang:system";
import * as Odbc from "gizlang:odbc";
import * as Source from "gizdb:source";

export function main(sys: System): void {
    const dsn = sys.environment("ODBC_DSN")!;
    const connection = Odbc.open(sys, dsn)!;
    const source = Source.odbc(connection, "items", Source.semantics("mysql")!)!;
    print(source.identity);
    connection.close();
}

Set ODBC_DSN to an ODBC connection string using the installed driver, server, credentials and database. Do not assume a generic ODBC driver preserves every PostgreSQL or GizDB type. tests/db/data-access/engines/run.giz includes SQLite ODBC and MariaDB ODBC integration paths when their drivers and test server are available. Other engines, such as SQL Server, require their own driver and compatibility testing. db/ora/client.giz provides a separate Oracle TNS client, not a general DATABASE_URL=oracle:// adapter.

Documents, files and HTTP data

Source.document(identity, slots, json) exposes a JSON array as typed rows. Source.httpJson(sys, host, port, request, identity, slots, bodyBytes) reads a JSON response and applies a bounded body limit (at most 16 MiB); it does not follow redirects. Source.files reads supported file formats through a directory, pattern, format and resource budget. These adapters do not provide SQL transactions or native change logs. File and document readers mainly push down projections; they do not claim full SQL join, filter or aggregate pushdown. The executor or caller must handle those operations.

Use typed queries and transactions

Choose Data.find, Data.one, Data.insert and Data.write for record-oriented CRUD. Model.shape<T>() is the single declaration of columns, form checks and row decoding. Data.install creates required tables. For many related writes, use Data.within: its callback runs under one transaction, and a rejection rolls the transaction back.

For custom SQL use Query.sql<P, R> with declared parameter and result slots; Query.Prepared.fetch(source, input) runs the same typed query on compatible Sources. Use Query.page for keyset pagination, Query.include for relationship loading, and Query.require(statement, 1) with a revision predicate for optimistic concurrency. A zero-row expected-revision write is a conflict, not a successful overwrite. One transaction belongs to one owner/source. Closing a reader, a transaction and a connection releases its resources; an idle Access transaction can expire.

For direct Store operations, query(sql, params) binds values and returns rows; exec(sql) runs SQL without bound parameters or a row result, and select includes column names. Store.Database.transaction keeps writes on one connection. Never concatenate untrusted values or identifiers into SQL text.

Scope data access to the caller

Data.Db without a scope is a trusted application connection. At an untrusted page, API or agent boundary, use Data.as(db, space, principal, now) to obtain a Data.Scoped context. Data.rows, Data.create, Data.update, Data.patch and Data.remove enforce the gizdb:gate and gizdb:policy rules. Model annotations for row and field access are in Model your data. A direct Store.query or Source SQL call does not automatically enforce that scope. Database roles and RLS are a separate defense when the provider supports them.

Live changes from database to browser

Live is supported, but it depends on how writes reach the database. A live view is not created by DATABASE_URL alone. A page must follow records and serve its subscription route; see GizWeb live updates.

1. Register Data.follow(sys, db, shape, filter, keys) before reading the rows. The returned position covers changes committed between registration and the first render.
2. Put Web.live(out, topic, position) attributes on the HTML element being followed.
3. Register Web.subscriptions and answer with Web.subscribed. Recheck the caller's authorization before computing patches.
4. Render changed keys with a keyed Html.Patch. A lost watch or a whole invalidation requires re-reading the page or result set, not replaying an old patch.
5. Call Data.unfollow, or Watch.close, when the subscriber leaves.

The browser kernel maintains a page subscription channel at /_s (WebSocket when used); server-side Web.subscribed also exposes a long-poll subscription route. Keyed patches update only changed DOM elements and keep unrelated inputs and focus. See the working comments application: main.giz installs the subscription route, store.giz opens follows, and view.ghtml writes keyed patches.

GizDB native watches

Access.observe/Data.follow track committed GizDB changes. Data.watch exposes a Watch.wait and Watch.close wrapper; a lost subscription is followed again and the caller must re-read the selection. A watched select does not include ORDER BY. The internal web contract caps active watches per database and expires idle watch leases; the documented defaults are 64 watches and 60 seconds without a poll. A filtered native follow delivers keys when rows enter or leave its selection, as tested by tests/web/data/records/run.giz. Re-read changed keys to establish current membership and visibility; a lost watch requires a complete refresh.

The GizDB SQL function changes(...) is a separate WAL-based committed change feed: read GizDB live SQL for offsets and GZ001 resync semantics.

Live from external PostgreSQL or SQLite

Data.connect(store, feed, name) uses a Relay when its Store does not expose the native engine. Construct one with Relay.relay(sys, store, keep), as web/pages.giz does. Relay creates feed and feed_history tables and writes change notices in the same transaction as the DAL data mutation. This works for writes performed through the DAL and for code which publishes the corresponding notice. An independent SQL client writing directly to PostgreSQL or SQLite does not automatically populate that feed. For ordered changes from an external CDC consumer, use Relay.ingest(source, position, notices) after the source's commit. The source name must identify a single ordered change stream (include its partition when applicable); position is a positive monotonically increasing i64 offset. notices is a Vec<Relay.Notice>; one source commit may change many rows, so publish all of its notices at the same offset. An empty vector advances the CDC checkpoint for an irrelevant source commit without emitting a false row-change event. The Relay stores only the highest accepted offset per source in feed_external and appends all notices atomically in the Relay store. A replay, duplicate or older position returns false; a new event returns true.

import * as Relay from "gizdb:relay";

// Call after the external transaction commits.
function publish(feed: Relay.Relay): bool {
    return feed.ingest("orders/public/partition-0", 101,
        [Relay.Notice("orders", "42", false)]);
}

A consumer must translate its provider's native LSN or binlog coordinate to an ordered positive offset. Use one offset per committed batch, not one per row. A table-wide event with key="" invalidates the whole selection, not a fake empty-key row. Data.changed waits across Relay instances; when using nonblocking waitMs=0, a different process may see the feed head up to 25 ms later. Use a bounded wait or poll again. The offset/notice atomicity does not make the external source commit atomic with the Relay's commit. Configure CDC retention and replay from the consumer's committed source offset, and recover with a full resnapshot when a source log or Relay cursor has expired.

This is the CDC receiving boundary, not a built-in PostgreSQL logical decoder, MySQL binlog reader or ODBC change-capture driver. Those provider-specific consumers must still be supplied; Source.postgres and Source.odbc declare changeLog=false.

Relay cursors can be trimmed when retention is exceeded; on loss, resnapshot and resubscribe. Do not treat a notification as a current authorized row image: query the record again in the caller's scope. The live delivery contract is not an exactly-once external event-processing system.

Source contracts and multi-database limits

Source reports its own Capabilities: schema, projection, filter, aggregation, join, window, ordering, limit, snapshots, change log, mutation, transaction, cancel and compute. Source.admit rejects requests a provider cannot execute; Source.describe returns a capability/schema version contract. Source.pinSchema checks a discovered schema against the expected typed slots.

Bounded typed reads across independent databases

Query.Prepared.fetchAcross(sources, params, maxRows) runs one already typed query on each compatible Source and concatenates its decoded result rows in the supplied source order. Source.federated(sources, false, false) checks source identities and SQL semantics before the first query is issued. Every result must fit the explicit combined maxRows budget (1 to 1,000,000); exceeding the budget is an error, not silent truncation. The underlying fetchBounded path closes the cursor even when an oversized result or a decoding error aborts the read.

import * as Query from "gizdb:query";
import {Source} from "gizdb:source";

function across<P, R>(query: Query.Prepared<P, R>, primary: Source, archive: Source, parameters: P): Vec<R> {
    return query.fetchAcross([primary, archive], parameters, 10000);
}

This is read-only fan-out with common output contracts, not distributed JOIN, global ORDER BY, a federated aggregate, globally consistent snapshots, atomic writes or a distributed commit coordinator. Filtering and ordering apply only within each source; results from different sources are simply concatenated. Use explicit provenance and a caller-owned merge algorithm where cross-source identity, deduplication or ordering matters. The source adapters still enforce their own authentication and row policies; DAL scope is not automatically copied.

A PostgreSQL or ODBC Source currently advertises snapshot=false and changeLog=false in the generic text adapter even if the underlying server supports snapshots or native CDC. Source.federated checks each source's identity, collation, timezone, dialect and NULL semantics. More than one source with atomic=true is rejected without a distributed coordinator; live=true requires change logs from every source. This contract does not by itself implement a global distributed SQL planner, cross-database ACID or automatic cross-engine CDC.

Exact feature boundary

Do not rely on a best-effort dual-write or a sequential SQL fan-out as if either provided cross-database ACID. For global transactions, select a single database as transactional owner or build and validate a distributed coordinator separately.

Migrations and operational boundaries

The model migration guide explains Schema.changes, Store.migrate and Migrate.apply with bounded backfill and explicit contract steps. Do not assume every external provider accepts the same migration DDL. The gizdb migrate CLI has its own supported adapters; server migration is not a guarantee of a generic reverse ETL connector for every Source. For ML training and inference entirely in code, with no database, see Machine learning. For backups, recovery, roles, TLS and quotas of a GizDB server, use the server operations guide. Provider-specific backups of external PostgreSQL, SQLite and MySQL remain the owner's responsibility.

Troubleshooting

Reference

For complete application code see GizWeb, and for concrete integration checks see tests/db/data-access/engines/run.giz, tests/db/relay/run.giz and tests/web/data/records/run.giz.

Data-access levels and portability example

This section records the verified examples and measurements for this topic. The source and test paths in this document remain authoritative.

1. Architectural Overview of the 5 DAL Levels

Gizlang's Data Access Layer provides five progressive levels of control. Developers write at the highest level that fulfills their requirements and drop down to lower levels for specific queries without rewriting the application architecture:

┌────────────────────────────────────────────────────────┐
│ Level 1: Model (Data)                                  │
│ High-level CRUD, auto-derived table, live web bindings │
└───────────────────────────┬────────────────────────────┘
                            │
┌───────────────────────────▼────────────────────────────┐
│ Level 2: Typed Queries (DAL)                           │
│ Keyset pagination, prepared joins, transaction control │
└───────────────────────────┬────────────────────────────┘
                            │
┌───────────────────────────▼────────────────────────────┐
│ Level 3: Connector (Source)                            │
│ Unified query interface across GizDB, PG, SQLite, ODBC │
└───────────────────────────┬────────────────────────────┘
                            │
┌───────────────────────────▼────────────────────────────┐
│ Level 4: Parametric SQL Statements                     │
│ Raw SQL execution with strict parameter binding ($1)   │
└───────────────────────────┬────────────────────────────┘
                            │
┌───────────────────────────▼────────────────────────────┐
│ Level 5: Direct Drivers                                │
│ Low-level embedded SQLite C-API file handles           │
└────────────────────────────────────────────────────────┘

2. Level 1: Model Operations (gizdb:data)

Used for primary application data operations where the record type defines the table structure:

import * as Model from "gizdb:model";
import * as Data from "gizdb:data";

@Model.Table("notes")
export type Note = {
    @Model.Key id: string;
    title: string;
    body: string;
};

export function findNotesByTitle(db: Data.Db, title: string): Vec<Note> {
    const shape = Model.shape<Note>();
    return Data.find(db, shape, "title = $1", [title], Model.rowReader<Note>(shape))!;
}

3. Level 2: Typed Queries & Keyset Pagination

When queries require joins across multiple tables, aggregation, or high-throughput keyset pagination:

Query.page(source, query, input, keyFields, after, size) runs a Prepared query one page at a time. It orders by keyFields and continues after the key values after, so it never scans skipped rows the way OFFSET does. The Page it returns holds at most size rows and the key of its last row as next (none on the last page). The keys may be columns the result does not show. See typed queries for a complete program.

4. Level 3: Multi-Engine Connectors (Source)

The same typed queries execute interchangeably across different storage backends:

• Access.native: Embedded or networked GizDB engine.
• Source.postgres: Remote PostgreSQL wire protocol database.
• Source.sqlite: Local SQLite file database.
• Source.odbc: Legacy enterprise databases via ODBC bridge.

5. Levels 4 & 5: Raw SQL & Embedded Drivers

For specialized analytical queries, administrative DDL, or embedded single-file tooling:

• Level 4 (SQL Text): Safe parametric queries using $1, $2 parameter substitution (Data.statements(sql, params)).
• Level 5 (Direct Driver): Zero-abstraction C-level SQLite handle bindings (Sqlite.open(path)).