Use GizDB

Coming In development. This is the design it ships to. Roadmap

GizDB is the database of GizStack. It is written in Gizlang. You can use it in two ways:

• Embedded. Your Gizlang program opens a database folder and runs SQL in its own process.
• Server. The GizDB server speaks the PostgreSQL wire protocol. psql and other PostgreSQL clients connect to it.

This guide is for developers who know PostgreSQL or SQLite.

• Part 1 is a tutorial. In approximately 15 minutes, you make a table, insert rows, query them and use a transaction, from a Gizlang program.
• Part 2 has how-to guides, one task each.
• Part 3 is the reference: the SQL that this guide ran, and the modules.

Each output on this page is the real output of its example, run on the GizDB development build.

Before you start

You need:

• The Gizlang toolchain. Refer to the installation guide and the command-line guide.
• Nothing else. GizDB comes with the toolchain as the compiled module gizdb, so a program imports it and starts in under a second. The gizdb server command comes with it too.
• On a database host without the toolchain, install the server alone: curl -fsSL https://gizstack.com/install | sh -s -- --only gizdb. Refer to Operate the GizDB server.
• For Part 2: psql (this guide used psql 16.14) and, for the Redis protocol, redis-cli.

Tutorial: store and query data with GizDB

In this tutorial, you write one program. It opens a database, makes a table, inserts rows, queries them and uses transactions.

Step 1: write the program

Make the file notes.giz:

import {System} from "gizlang:system";
import {Result, Option} from "gizlang:core";
import {Engine, Conn, Value, open, connection, render, sqlstate} from "gizdb";

/** Runs SQL with parameters, and prints each result row, or the SQLSTATE of an error. */
function run(e: mut Engine, c: mut Conn, sql: string, params: Vec<Value>): void {
    match (c.run(e, sql, params, 0, 0u)) {
        Result.ok(r) => {
            for (const row of r.rows) {
                let line = "";
                for (const i of range(0, row.length)) {
                    if (i > 0) { line += " | "; }
                    match (render(row[i], "", c.dialect)) {
                        Option.some(t) => { line += t; }
                        Option.none() => { line += "NULL"; }
                    }
                }
                print(line);
            }
        }
        Result.err(x) => { print("ERROR " + sqlstate(x) + ": " + x.message); }
    }
}

export function main(sys: System): void {
    // 1. Open (or create) the database in the folder "notes-data".
    sys.files()!.makeDirectories("notes-data")!;
    let e = open(sys.directory("notes-data")!)!;
    let c = connection("postgres");

    // 2. Create a table.
    run(e, c, "DROP TABLE IF EXISTS notes", []);
    run(e, c, "CREATE TABLE notes (id int PRIMARY KEY, title text NOT NULL, stars int DEFAULT 0)", []);

    // 3. Insert rows: literals, then parameters ($1, $2, ...).
    run(e, c, "INSERT INTO notes VALUES (1, 'Buy milk', 2), (2, 'Write guide', 5)", []);
    run(e, c, "INSERT INTO notes (id, title, stars) VALUES ($1, $2, $3)",
        [Value.int(3), Value.text("Call Ana"), Value.int(4)]);
    run(e, c, "INSERT INTO notes (id, title) VALUES (4, 'Read later') RETURNING id, stars", []);

    // 4. Query.
    print("-- stars >= 3");
    run(e, c, "SELECT id, title, stars FROM notes WHERE stars >= $1 ORDER BY stars DESC", [Value.int(3)]);
    print("-- totals");
    run(e, c, "SELECT count(*), sum(stars), max(title) FROM notes", []);

    // 5. A transaction that rolls back changes nothing.
    run(e, c, "BEGIN", []);
    run(e, c, "UPDATE notes SET stars = 0", []);
    run(e, c, "ROLLBACK", []);
    // A transaction that commits applies all of its statements together.
    run(e, c, "BEGIN", []);
    run(e, c, "UPDATE notes SET stars = stars + 1 WHERE id = 1", []);
    run(e, c, "DELETE FROM notes WHERE id = 4", []);
    run(e, c, "COMMIT", []);
    print("-- after the transactions");
    run(e, c, "SELECT id, stars FROM notes ORDER BY id", []);

    // 6. Errors carry a SQLSTATE.
    run(e, c, "INSERT INTO notes VALUES (1, 'Duplicate', 0)", []);

    // 7. Close the database. The data stays in "notes-data".
    e.close()!;
}

What the program uses:

A statement without BEGIN commits alone (autocommit), as in psql.

Step 2: run the program

giz notes.giz

Output:

4 | 0
-- stars >= 3
2 | Write guide | 5
3 | Call Ana | 4
-- totals
4 | 11 | Write guide
-- after the transactions
1 | 3
2 | 5
3 | 4
ERROR 23505: duplicate key value violates unique constraint "notes_pkey"

• The first line is the RETURNING row. The default gave stars = 0.
• The rolled-back UPDATE did not change the totals.
• The committed transaction changed row 1 and deleted row 4 together.

Step 3: open the data again

The data is in the folder notes-data. A second program opens it again. Make count.giz:

import {System} from "gizlang:system";
import {Option} from "gizlang:core";
import {open, connection, render} from "gizdb";

export function main(sys: System): void {
    let e = open(sys.directory("notes-data")!)!;
    let c = connection("postgres");
    const r = c.run(e, "SELECT count(*) FROM notes", [], 0, 0u)!;
    print("notes: " + (render(r.rows[0][0], "", c.dialect) ?? "NULL"));
    e.close()!;
}

giz count.giz

notes: 3

You have used GizDB embedded. Next, run the same SQL through the GizDB server.

How-to guides

Run the GizDB server and connect with psql

Coming In development. This is the design it ships to. Roadmap

The server is the gizdb command of the toolchain.

1. Start it. --dir is the database folder, and --port is the TCP port. The server listens on 127.0.0.1 by default.

   shCopy

   mkdir -p pgdata
   gizdb serve --port 61201 --dir pgdata

2. In a different terminal, connect with psql:

   shCopy

   pg_isready -h 127.0.0.1 -p 61201
   psql -h 127.0.0.1 -p 61201 -U postgres -f first.sql

   first.sql:

   sqlCopy

   CREATE TABLE notes (id int PRIMARY KEY, title text NOT NULL, stars int DEFAULT 0);
   INSERT INTO notes VALUES (1, 'Buy milk', 2), (2, 'Write guide', 5), (3, 'Call Ana', 4);
   SELECT id, title, stars FROM notes WHERE stars >= 3 ORDER BY stars DESC;
   SELECT version();

   Output:

   textCopy

   127.0.0.1:61201 - accepting connections
   CREATE TABLE
   INSERT 0 3
    id |    title    | stars
   ----+-------------+-------
     2 | Write guide |     5
     3 | Call Ana    |     4
   (2 rows)
   
            version
   -------------------------
    PostgreSQL 16.0 (GizDB)
   (1 row)

3. To stop the server, press Ctrl+C, or send it SIGTERM.

To run the server as a service, with users, TLS, backups and upgrades, refer to Operate the GizDB server.

Parameters with the extended query protocol also work. With psql 16, use \bind:

psql -h 127.0.0.1 -p 61201 -U postgres -f bind.sql

bind.sql:

SELECT id, title FROM notes WHERE stars >= $1 ORDER BY id \bind 4 \g

 id |    title
----+-------------
  2 | Write guide
  3 | Call Ana
(2 rows)

Require a password

Without a password, the server accepts every client. To require a password, give --password, or set GIZDB_PASSWORD so that the password is not in the process list. Passwords authenticate by SCRAM-SHA-256.

GIZDB_PASSWORD=change-me gizdb serve --port 61204 --dir authdata

psql -h 127.0.0.1 -p 61204 -U postgres -w -c "SELECT 1"
PGPASSWORD=wrong psql -h 127.0.0.1 -p 61204 -U postgres -c "SELECT 1"
PGPASSWORD=change-me psql -h 127.0.0.1 -p 61204 -U postgres -At -c "SELECT 1"

psql: error: connection to server at "127.0.0.1", port 61204 failed: fe_sendauth: no password supplied
psql: error: connection to server at "127.0.0.1", port 61204 failed: FATAL:  password authentication failed for user "postgres"
1

Server options

gizdb serve --help lists every option. These are the common ones:

Operate the GizDB server gives --archive, --tls-cert, --tls-key, --max-connections, --statement-timeout, --threads, --quota and --socket.

Use transactions and isolation levels

GizDB transactions use snapshots. Readers never wait for writers.

When a commit fails with 40001, run the full transaction again.

SET TRANSACTION and SET SESSION CHARACTERISTICS also select the level:

psql -h 127.0.0.1 -p 61201 -U postgres -At -c "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ" -c "BEGIN" -c "SHOW transaction_isolation" -c "COMMIT"
psql -h 127.0.0.1 -p 61201 -U postgres -At -c "BEGIN" -c "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" -c "SHOW transaction_isolation" -c "COMMIT"
psql -h 127.0.0.1 -p 61201 -U postgres -At -c "SET sql_dialect = 'sqlite'" -c "SHOW transaction_isolation"

SET
BEGIN
repeatable read
COMMIT
BEGIN
SET
serializable
COMMIT
SET
serializable

Two sessions in one program

Each connection(...) is a separate session on the same engine. This program shows what session A sees while session B writes. Make isolation.giz:

import {System} from "gizlang:system";
import {Result, Option} from "gizlang:core";
import {Engine, Conn, open, connection, render, sqlstate} from "gizdb";

/** Runs SQL as session `who`. Prints the rows, "waits" for a row lock, or the SQLSTATE. */
export function run(e: mut Engine, c: mut Conn, who: string, sql: string): void {
    match (c.run(e, sql, [], 0, 0u)) {
        Result.ok(r) => {
            if (c.blocked !== "") { print(who + ": " + sql + " -> waits"); return; }
            let out = "";
            for (const row of r.rows) {
                for (const v of row) { out += " " + (render(v, "", c.dialect) ?? "NULL"); }
            }
            print(who + ": " + sql + " ->" + out);
        }
        Result.err(x) => { print(who + ": " + sql + " -> ERROR " + sqlstate(x)); }
    }
}

export function main(sys: System): void {
    sys.files()!.makeDirectories("iso-data")!;
    let e = open(sys.directory("iso-data")!)!;
    let a = connection("postgres");
    let b = connection("postgres");
    run(e, a, "A", "DROP TABLE IF EXISTS acct");
    run(e, a, "A", "CREATE TABLE acct (id int PRIMARY KEY, bal int)");
    run(e, a, "A", "INSERT INTO acct VALUES (1, 100)");
    run(e, a, "A", "SHOW transaction_isolation");

    // READ COMMITTED: each statement sees what committed before it.
    run(e, a, "A", "BEGIN");
    run(e, a, "A", "SELECT bal FROM acct WHERE id = 1");
    run(e, b, "B", "UPDATE acct SET bal = 150 WHERE id = 1");
    run(e, a, "A", "SELECT bal FROM acct WHERE id = 1");
    run(e, a, "A", "COMMIT");

    // REPEATABLE READ: one snapshot for the transaction; a write conflict fails with 40001.
    run(e, a, "A", "BEGIN ISOLATION LEVEL REPEATABLE READ");
    run(e, a, "A", "SELECT bal FROM acct WHERE id = 1");
    run(e, b, "B", "UPDATE acct SET bal = 200 WHERE id = 1");
    run(e, a, "A", "SELECT bal FROM acct WHERE id = 1");
    run(e, a, "A", "UPDATE acct SET bal = bal + 1 WHERE id = 1");
    run(e, a, "A", "ROLLBACK");
    e.close()!;
}

giz isolation.giz

A: DROP TABLE IF EXISTS acct ->
A: CREATE TABLE acct (id int PRIMARY KEY, bal int) ->
A: INSERT INTO acct VALUES (1, 100) ->
A: SHOW transaction_isolation -> read committed
A: BEGIN ->
A: SELECT bal FROM acct WHERE id = 1 -> 100
B: UPDATE acct SET bal = 150 WHERE id = 1 ->
A: SELECT bal FROM acct WHERE id = 1 -> 150
A: COMMIT ->
A: BEGIN ISOLATION LEVEL REPEATABLE READ ->
A: SELECT bal FROM acct WHERE id = 1 -> 150
B: UPDATE acct SET bal = 200 WHERE id = 1 ->
A: SELECT bal FROM acct WHERE id = 1 -> 150
A: UPDATE acct SET bal = bal + 1 WHERE id = 1 -> ERROR 40001
A: ROLLBACK ->

When a statement must wait for a row lock that a different session holds, Conn.run returns with c.blocked set and no rows. The PostgreSQL server runs the statement again when the lock is free. An embedded program must do the same.

Savepoints through the server

psql -h 127.0.0.1 -p 61201 -U postgres -f tx.sql

tx.sql:

BEGIN;
UPDATE notes SET stars = 0;
ROLLBACK;
SELECT sum(stars) FROM notes;
BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
UPDATE notes SET stars = stars + 1 WHERE id = 1;
SAVEPOINT s1;
DELETE FROM notes WHERE id = 3;
ROLLBACK TO SAVEPOINT s1;
COMMIT;
SELECT id, stars FROM notes ORDER BY id;

BEGIN
UPDATE 3
ROLLBACK
 sum
-----
  11
(1 row)

BEGIN
 transaction_isolation
-----------------------
 repeatable read
(1 row)

UPDATE 1
SAVEPOINT
DELETE 1
ROLLBACK
COMMIT
 id | stars
----+-------
  1 |     3
  2 |     5
  3 |     4
(3 rows)

Run typed queries from Gizlang

Coming In development. This is the design it ships to. 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; };

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 Result.ok([Value.int(from)]); },
        (row: Vec<Value>): Result<Task, DbError> => {
            return Result.ok({
                id: Typed.integerCodec().decode(row[0])!,
                title: Typed.textCodec().decode(row[1])!,
                done: Typed.booleanCodec().decode(row[2])!,
            });
        })!;

    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.

Make indexes

GizDB has primary keys, unique indexes and btree secondary indexes. A btree index on (chat, seq) serves chat = $1, ranges on seq, and ORDER BY seq ... LIMIT.

This embedded example uses the run function of isolation.giz. Make index.giz:

import {System} from "gizlang:system";
import {open, connection} from "gizdb";
import {run} from "./isolation.giz";

export function main(sys: System): void {
    sys.files()!.makeDirectories("index-data")!;
    let e = open(sys.directory("index-data")!)!;
    let c = connection("postgres");
    run(e, c, "-", "DROP TABLE IF EXISTS events");
    run(e, c, "-", "CREATE TABLE events (id bigint PRIMARY KEY, chat int NOT NULL, seq int NOT NULL, body text)");
    run(e, c, "-", "INSERT INTO events VALUES (1, 7, 1, 'hello'), (2, 7, 2, 'how are you'), (3, 8, 1, 'other chat'), (4, 7, 3, 'bye')");
    run(e, c, "-", "CREATE INDEX events_chat_seq ON events (chat, seq)");
    run(e, c, "-", "CREATE UNIQUE INDEX events_body ON events (body)");
    run(e, c, "-", "EXPLAIN SELECT seq, body FROM events WHERE chat = 7 ORDER BY seq DESC LIMIT 2");
    run(e, c, "-", "SELECT seq, body FROM events WHERE chat = 7 ORDER BY seq DESC LIMIT 2");
    run(e, c, "-", "SELECT body FROM events WHERE chat = 7 AND seq BETWEEN 2 AND 3 ORDER BY seq");
    run(e, c, "-", "INSERT INTO events VALUES (5, 9, 1, 'hello')");
    run(e, c, "-", "SELECT chat, seq, row_number() OVER (PARTITION BY chat ORDER BY seq DESC) FROM events ORDER BY chat, seq");
    e.close()!;
}

giz index.giz

The run function puts all the cells of a result on one line:

-: DROP TABLE IF EXISTS events ->
-: CREATE TABLE events (id bigint PRIMARY KEY, chat int NOT NULL, seq int NOT NULL, body text) ->
-: INSERT INTO events VALUES (1, 7, 1, 'hello'), (2, 7, 2, 'how are you'), (3, 8, 1, 'other chat'), (4, 7, 3, 'bye') ->
-: CREATE INDEX events_chat_seq ON events (chat, seq) ->
-: CREATE UNIQUE INDEX events_body ON events (body) ->
-: EXPLAIN SELECT seq, body FROM events WHERE chat = 7 ORDER BY seq DESC LIMIT 2 -> Sort by 1 keys (top)  (rows 1)   Project 2 columns  (rows 1)     Range events by index 0 descending (first rows)  (rows 1)
-: SELECT seq, body FROM events WHERE chat = 7 ORDER BY seq DESC LIMIT 2 -> 3 bye 2 how are you
-: SELECT body FROM events WHERE chat = 7 AND seq BETWEEN 2 AND 3 ORDER BY seq -> how are you bye
-: INSERT INTO events VALUES (5, 9, 1, 'hello') -> ERROR 23505
-: SELECT chat, seq, row_number() OVER (PARTITION BY chat ORDER BY seq DESC) FROM events ORDER BY chat, seq -> 7 1 3 7 2 2 7 3 1 8 1 1

• EXPLAIN shows Range events by index 0 descending: the query walks the index events_chat_seq backward, and stops after the limit.
• The unique index refuses a second 'hello' with SQLSTATE 23505.
• The last query shows a window function.

Import and export CSV

The server supports COPY ... FROM STDIN and COPY ... TO STDOUT in the text and CSV formats of PostgreSQL. The psql command \copy uses them. A server-side file path in COPY is refused.

sales.csv:

id,city,amount
1,Seoul,12.50
2,Busan,7.25
3,Seoul,3.00

psql -h 127.0.0.1 -p 61201 -U postgres \
  -c "CREATE TABLE sales (id int PRIMARY KEY, city text, amount numeric(10,2))" \
  -c "\copy sales FROM 'sales.csv' WITH (FORMAT csv, HEADER true)" \
  -c "SELECT city, sum(amount) FROM sales GROUP BY city ORDER BY city" \
  -c "\copy (SELECT * FROM sales ORDER BY id) TO 'out.csv' WITH (FORMAT csv, HEADER)"
cat out.csv

CREATE TABLE
COPY 3
 city  |  sum
-------+-------
 Busan |  7.25
 Seoul | 15.50
(2 rows)

COPY 3
id,city,amount
1,Seoul,12.50
2,Busan,7.25
3,Seoul,3.00

• numeric(10,2) is an exact decimal. Thus the sum of 12.50 and 3.00 is exactly 15.50.
• COPY streams: a large file loads in batches of 64k rows, with bounded memory.
• COPY ... TO STDOUT (FORMAT parquet) writes Parquet, and FORMAT binary is the binary format of PostgreSQL.

Make a backup

Coming In development. This is the design it ships to. Roadmap

BACKUP TO 'folder' makes a base backup of a running server. The folder must exist and be empty. Only a superuser can run it.

mkdir -p backups/base1
psql -h 127.0.0.1 -p 61201 -U postgres -c "BACKUP TO '/full/path/to/backups/base1'"
ls backups/base1 | head -4

BACKUP
BACKUP_LABEL
log-0000000000000000
log-000000000001f0d6
MANIFEST

The file BACKUP_LABEL shows that the backup is complete. A second BACKUP TO into the same folder fails:

ERROR:  the backup directory is not empty

Restore or branch

Use a completed base backup and its archived WAL. The destination folder must exist and be empty.

gizdb restore --base backups/base1 --logs archive --to restored --until-lsn LSN
gizdb branch --base backups/base1 --logs archive --to branch --until-lsn LSN

Replace LSN with a decimal complete commit boundary. Use --until TIME for a time target. Omit both target options to select the latest complete commit. Both commands report the ending LSN. Branch shares sealed files from the backup and creates a new history. Mutable indexes and WAL stay private. The parent can keep running while the commands read its completed backup and archive. To restore a backup, also to a point in time, refer to Operate the GizDB server.

Use the Redis protocol

Coming In development. This is the design it ships to. Roadmap

The server can also listen for the Redis protocol (RESP). Give --kv-port and a tenant name with --kv-tenant.

gizdb serve --port 61201 --dir pgdata --kv-port 61203 --kv-tenant demo

redis-cli -p 61203 PING
redis-cli -p 61203 SET greeting hello
redis-cli -p 61203 GET greeting
redis-cli -p 61203 HSET user:1 name Ana
redis-cli -p 61203 HGETALL user:1
redis-cli -p 61203 INCR visits
redis-cli -p 61203 INCR visits
redis-cli -p 61203 EXPIRE greeting 60
redis-cli -p 61203 TTL greeting

PONG
OK
hello
1
name
Ana
1
2
1
60

Keys survive a restart on the same --dir and --kv-tenant, also after SIGTERM or SIGKILL. By default a write reply waits for the WAL sync (appendfsync always). CONFIG SET appendfsync everysec answers after the log write and syncs within a second; no writes the log and does not sync it. A restart reads the last checkpoint and at most 16 MiB of log (about 150 ms for 100k keys).

The keys are rows of SQL tables, in the same transactions as the rest of the database. SQL reads them in the view _giz_kv_keys (tenant, db, key, type, value, count, expires); one SQL transaction can change keys and other rows. RESP logs in as a SQL role: AUTH user password (or HELLO 3 AUTH user password) checks the role's password, and AUTH password checks --password. A superuser reads and writes every key. Another role needs grants on the KV relations, as in SQL: GRANT SELECT ON _giz_kv, _giz_kv_items, _giz_kv_ordered, _giz_kv_ttl, _giz_kv_size TO reader lets reader run read commands; a write command also needs INSERT, UPDATE and DELETE.

--kv-shards N gives the keys N writers, each on a thread of its own, and --threads serves the clients. Without it, the main engine writes them. Measured against Redis on one host: GET reaches Redis from 4 shards; SET is 0.3 to 0.4 of Redis. The numbers are on the benchmarks page.

Live queries

Coming In development. This is the design it ships to. Roadmap

changes('table', after [, wait_ms [, 'column', value]]) returns the committed changes after a position: the LSN, a deleted flag, then the row. With a wait, an empty answer holds the connection until a commit writes the table or the wait ends. So one query is a live feed, with no message broker.

SELECT * FROM changes('orders', 0);              -- every change so far
SELECT * FROM changes('orders', 1520, 30000);    -- wait up to 30 s for the next one
SELECT * FROM changes('orders', 1520, 30000, 'customer', 42);

A position that the log no longer holds gives SQLSTATE GZ001. Read the table again, then follow from its current LSN.

Query files

Coming In development. This is the design it ships to. Roadmap

Start the server with --files DIR. A superuser can then query the CSV and Parquet files under DIR as tables, with no load step:

SELECT city, sum(amount) FROM 'sales/*.csv' GROUP BY city;
SELECT * FROM read_csv('sales/2026-10.csv', header => true);
SELECT count(*) FROM read_parquet('events/*.parquet');

Ingest streams with Kafka clients

Coming In development. This is the design it ships to. Roadmap

Start the server with --kafka-port 9092. Kafka producers write into tables, and consumers read the change feed, with the Kafka protocol. A topic is a table.

gizdb serve --dir data --port 5432 --kafka-port 9092
kcat -b 127.0.0.1:9092 -t events -P <<< '{"user": 7, "kind": "visit"}'
psql -h 127.0.0.1 -U postgres -c "SELECT count(*) FROM events"

Copy telemetry, cost and payment data in

Coming In development. This is the design it ships to. Roadmap

Three modules copy data from a standard format into tables of your program's GizDB, through an Access.Connection. Each has an initialize(connection) that makes its tables (IF NOT EXISTS).

Your program owns authentication, TLS and retention. A retry writes no second copy: an OTLP row is keyed by the digest of its record, a Stripe row by its account and ID, and a FOCUS load replaces its period.

import * as Http from "gizlang:http";
import * as Access from "gizdb:access";
import * as Otlp from "gizdb:ingest/otlp";

/** The HTTP handler of the routes /v1/logs, /v1/traces and /v1/metrics. */
export function receive(connection: Access.Connection, request: Http.Request): Http.Response {
    Otlp.initialize(connection)!;
    return Otlp.handle(connection, request);
}

The gizdb command uses the same modules without a program:

gizdb serve --dir data --otlp-port 4318      # also an OTLP/HTTP receiver on 127.0.0.1:4318
gizdb ingest focus --dir data --file bill.csv --source aws \
  --start 2026-09-01T00:00:00Z --end 2026-10-01T00:00:00Z --customer-tag team:customer
STRIPE_KEY=rk_live_... gizdb ingest stripe --dir data --account acct_123

• The OTLP receiver listens on loopback only, because it has no authentication. Point a local OpenTelemetry Collector at it (otlphttp exporter, compression: none).
• gizdb ingest writes to a stopped database: it holds the folder's LOCK, as gizdb verify does.
• gizdb ingest stripe copies the six resource kinds, then the events (--kinds selects), each up to --through (seconds since 1970; default now). A next run continues from the saved cursors.

Search text and vectors

Coming In development. This is the design it ships to. Roadmap

Search is part of SQL. Text and vector indexes update in the same commit as the row, and the permission rules of the table apply to every result.

-- full text, scored by BM25
SELECT id, title, score() FROM docs WHERE match(body, 'raft consensus') ORDER BY score() DESC LIMIT 10;
-- nearest neighbors under a filter
SELECT id FROM items WHERE tenant = 7 ORDER BY embedding <=> $1 LIMIT 10;

match reads a full-text index of its column: CREATE INDEX docs_body ON docs USING bm25(body).

<-> is the Euclidean distance, <=> the cosine distance and <#> the negative inner product, as in pgvector. Index options, tuning and data larger than memory are in Vector search in GizDB.

To combine both, write one query: match OR near, ordered by rrf(). BM25 scores and distances are not on one scale, so GizDB ranks the rows on each side and fuses the ranks (reciprocal rank fusion):

SELECT id, title, rrf() AS score
FROM docs
WHERE tenant = 7 AND (match(body, 'raft consensus') OR near(embedding <=> $1))
ORDER BY rrf() DESC LIMIT 10;

• Each side ranks at least 50 rows, and at least the rows that LIMIT and OFFSET reach.
• The other conditions of WHERE (here tenant = 7) filter both sides.
• The query needs ORDER BY rrf() DESC and a LIMIT. It cannot have GROUP BY, DISTINCT or a window. To aggregate the hits, put the query in a subquery.
• rrf() in the select list is the fused score.

The planner writes this query as two ranked subqueries and a FULL JOIN on the table's key, with rrf(text_rank, vector_rank, k, text_weight, vector_weight). You can also write that form yourself. Three settings change the fusion:

$1 is the embedding of the query. pgml.embed makes it in the database, and pgml.rank can order the fused rows again with a cross-encoder (refer to Machine learning in GizDB). The Search box of the console's Data area writes this query for a table.

To measure a search, compare its ranking with the ids that are relevant:

SELECT recall_at_k(ARRAY[3, 1, 7], ARRAY[1, 2], 3) AS recall, ndcg_at_k(ARRAY[3, 1, 7], ARRAY[1, 2], 3) AS ndcg;

 recall |        ndcg
--------+---------------------
    0.5 | 0.38685280723454163
(1 row)

precision_at_k, reciprocal_rank and average_precision take the same arguments. avg of reciprocal_rank over the queries is MRR. A program that does not use the database gets the same numbers from gizml:ml.

To tune a search, give pgml.tune_search the search (a query of $1, the query text, with the ids in its first column) and the judgments (rows of query text and relevant ids). It runs the search for each judgment under each setting, and gives the mean nDCG and recall of each setting, best first:

SELECT * FROM pgml.tune_search(
  'SELECT id FROM docs WHERE match(body, $1) OR near(embedding <=> pgml.embed(''intfloat/e5-small-v2'', $1)::vector)
   ORDER BY rrf() DESC LIMIT 10',
  'SELECT query, relevant_ids FROM judgments',
  '{"rrf_k": [10, 60], "text_weight": [0.5, 1, 2], "ef_search": [40, 100], "k": 10}') LIMIT 1;

Then SET the best values, or ALTER ROLE ... SET them. The session's own settings do not change.

Dialects

SET sql_dialect selects the rules of another database for a session: postgres (the default), sqlite, mysql, sqlserver and oracle.

psql -h 127.0.0.1 -p 61201 -U postgres -At -c "SET sql_dialect = 'sqlite'" -c "SHOW sql_dialect"

SET
sqlite

A quoted name keeps its case in the PostgreSQL and Oracle dialects. The engine compares names in lowercase, so the SQL lexer spells a quoted name that its dialect does not fold to the same text in a lowercase-stable form (db/core/names.giz). Catalogs, result labels, error texts and the catalog functions show it as written. Rewrite your own code that builds SQL from names with quoteIdentifier.

Run Oracle applications

Coming In development. This is the design it ships to. Roadmap

GizDB speaks Oracle Net (TNS) on the same port as the PostgreSQL protocol. The server reads the first bytes of a connection and chooses the protocol, and each Oracle statement runs in the oracle dialect. An Oracle driver connects with no change to the application, except for the address.

1. Start the server as in the previous guide, with a password:

   shCopy

   gizdb serve --port 15521 --dir oradata --password secret

2. Connect with an Oracle driver. The service name is not used: every service is this database. These drivers log on and pass the same checks in the GizDB tests: JDBC thin (ojdbc 12.2, 19c and 23), python-oracledb (thin mode), node-oracledb (thin mode) and go-ora. ojdbc 12.1 and older do not log on.

   pythonCopy

   import oracledb
   conn = oracledb.connect(user="app", password="secret", dsn="127.0.0.1:15521/anyname")

   A statement can bind IN, OUT and IN OUT values and use RETURNING ... INTO. A REF CURSOR can be returned. The DBMS_OUTPUT buffer is read with GET_LINE. CLOB and BLOB columns work through LOB locators.

3. To try the dialect without an Oracle driver, use psql with the dialect set at startup. psql sends a statement at each semicolon, so give one statement to each -c (a PL/SQL block ends with its own END;):

   shCopy

   export PGOPTIONS='-c sql_dialect=oracle' PGPASSWORD=secret
   psql -h 127.0.0.1 -p 15521 -U app -d postgres \
     -c "CREATE TABLE emp (id NUMBER PRIMARY KEY, name VARCHAR2(20), sal NUMBER(8,2))" \
     -c "INSERT INTO emp VALUES (1, 'Ada', 5200)" \
     -c "INSERT INTO emp VALUES (2, 'Bo', 4100)" \
     -c "CREATE OR REPLACE FUNCTION raise_pct(s NUMBER, p NUMBER) RETURN NUMBER IS BEGIN RETURN s + s * p / 100; END;" \
     -c "SELECT name, raise_pct(sal, 10) AS new_sal FROM emp ORDER BY id"

   textCopy

   CREATE TABLE
   INSERT 0 1
   INSERT 0 1
   CREATE FUNCTION
    NAME | NEW_SAL
   ------+---------
    Ada  |    5720
    Bo   |    4510
   (2 rows)

Unquoted names are upper case, as in Oracle, and an empty string is NULL. A quoted name keeps its case, so "Orders", "orders" and orders (which is ORDERS) are three names.

Object types

An object type has attributes, a constructor, and member and static routines. A table column can have an object type.

psql -h 127.0.0.1 -p 15521 -U app -d postgres \
  -c "CREATE TYPE point_t AS OBJECT (x NUMBER, y NUMBER, MEMBER FUNCTION dist RETURN NUMBER)" \
  -c "CREATE TYPE BODY point_t AS MEMBER FUNCTION dist RETURN NUMBER IS BEGIN RETURN SQRT(x * x + y * y); END; END;" \
  -c "CREATE TABLE shapes (id NUMBER, p point_t)" \
  -c "INSERT INTO shapes VALUES (1, point_t(3, 4))" \
  -c "SELECT s.p.x AS x, s.p.dist() AS d FROM shapes s"

CREATE OBJECT TYPE
CREATE PACKAGE
CREATE TABLE
INSERT 0 1
 X | D
---+---
 3 | 5
(1 row)

CREATE TYPE BODY is reported as CREATE PACKAGE in the psql tag. USER_TYPES, USER_TYPE_ATTRS and USER_TYPE_METHODS list the types. UNDER makes a subtype and TREAT narrows a value. A member call runs the method of the declared type: there is no dynamic dispatch to a subtype's override. DROP TYPE t FORCE drops a type that other types use.

Pipelined functions and WITH FUNCTION

A PIPELINED function returns its rows one at a time with PIPE ROW, and a query reads them with TABLE(). WITH FUNCTION defines a function that only one query sees.

psql -h 127.0.0.1 -p 15521 -U app -d postgres \
  -c "CREATE TYPE num_tab AS TABLE OF NUMBER" \
  -c "CREATE FUNCTION first_n(n NUMBER) RETURN num_tab PIPELINED IS BEGIN FOR i IN 1 .. n LOOP PIPE ROW (i * i); END LOOP; RETURN; END;" \
  -c "SELECT SUM(column_value) AS total FROM TABLE(first_n(4))" \
  -c "WITH FUNCTION twice(n NUMBER) RETURN NUMBER IS BEGIN RETURN n * 2; END; SELECT twice(21) AS answer FROM dual"

CREATE TYPE
CREATE FUNCTION
 TOTAL
-------
 30
(1 row)

 ANSWER
--------
     42
(1 row)

Errors

Errors carry Oracle numbers and texts. A name that does not exist is ORA-00904 or ORA-00942, a text that is not a number is ORA-01722, and a NOT NULL column that gets NULL is ORA-01400 (ORA-01407 in an update).

psql -h 127.0.0.1 -p 15521 -U app -d postgres -c "SELECT * FROM emp WHERE id = 'x'"

ERROR:  ORA-01722: invalid number

Check what matches Oracle

Compatibility is measured against Oracle Database Free 23, not read from its manual. Each release runs a corpus of statements in the oracle dialect and compares every answer (column labels, rows, row counts and ORA numbers) with the answer that Oracle gave. A new difference fails the test suite, and so does a known one that now matches, so the list of differences only gets shorter. A statement that answers differently from Oracle is a defect: send it with what Oracle answered.

Not supported yet: collections of records in TABLE(), collection OUT parameters, and dynamic dispatch of an overridden member. Another known difference from Oracle: ora_hash values.

Time zones: TIMESTAMP WITH TIME ZONE values carry their offset or region (TZH, TZM, TZR, TZD, AT TIME ZONE, FROM_TZ, TZ_OFFSET, ALTER SESSION SET TIME_ZONE). A stored WITH TIME ZONE column keeps the instant only and reads back at +00:00. The default session zone prints as +00:00, not as a region name.

Reference

SQL on this page

Modules

giz doc NAME prints the reference of any declaration below.

The gizdb command