# Use GizDB

:::status{of="gizdb"}
:::

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](../start.md) and the
  [command-line guide](command-line.md).
- 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](../operations/gizdb-server.md).
- 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`:

```gizlang title="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:

| Name | Module | What it does |
| --- | --- | --- |
| `open(dir)` | `gizdb` | Opens the database in a folder. An empty folder becomes a new database. |
| `connection(dialect)` | `gizdb` | Makes a SQL session. `"postgres"` selects PostgreSQL rules. |
| `Conn.run(e, sql, params, now, seed)` | `gizdb` | Runs the statements in `sql`. It returns the rows of the last statement, or a `DbError`. `now` is the clock in microseconds for `now()`, and `seed` is the seed for `random()`. |
| `Value.int`, `Value.text` | `gizdb` | Parameter values for `$1`, `$2` and the next. |
| `render(value, "", dialect)` | `gizdb` | The text form of a value, as psql shows it. `Option.none()` is NULL. |
| `sqlstate(error)` | `gizdb` | The PostgreSQL SQLSTATE of an error, for example `23505`. |
| `Engine.close()` | `gizdb` | Closes the database. |

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

### Step 2: run the program

```sh
giz notes.giz
```

Output:

```text 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`:

```gizlang title="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()!;
}
```

```sh
giz count.giz
```

```text output
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

:::status{of="gizdb-server"}
:::

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.

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

2. In a different terminal, connect with psql:

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

   `first.sql`:

   ```sql
   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:

   ```text
   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](../operations/gizdb-server.md).

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

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

`bind.sql`:

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

```text
 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.

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

```sh
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"
```

```text
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:

| Option | Meaning |
| --- | --- |
| `--port N` | TCP port. The default is 5432. |
| `--bind ADDRESS` | Listen address. The default is 127.0.0.1. |
| `--dir DIR` | Database folder. The default is `data`. |
| `--password SECRET` | Require this password. `GIZDB_PASSWORD` is the same. |
| `--trust-all yes` | Accept every client without a password on a `--bind` address other than this host. Without a password and without this option, such a server does not start. |
| `--files DIR` | The folder whose files a superuser can query. Refer to [file queries](#query-files). |
| `--kv-port N`, `--kv-tenant NAME` | Also listen for the Redis protocol. Refer to [Use the Redis protocol](#use-the-redis-protocol). |

[Operate the GizDB server](../operations/gizdb-server.md) 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.

| Level | What a transaction sees | How you select it |
| --- | --- | --- |
| READ COMMITTED (the default of the `postgres` dialect) | A new snapshot for each statement | `BEGIN` |
| REPEATABLE READ | One snapshot for the full transaction. A write that conflicts fails with SQLSTATE 40001. | `BEGIN ISOLATION LEVEL REPEATABLE READ` |
| SERIALIZABLE (the default of the `sqlite` dialect) | One snapshot. The commit validates what the transaction read. | `BEGIN ISOLATION LEVEL SERIALIZABLE` |

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

`SET TRANSACTION` and `SET SESSION CHARACTERISTICS` also select the level:

```sh
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"
```

```text
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`:

```gizlang title="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()!;
}
```

```sh
giz isolation.giz
```

```text output
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

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

`tx.sql`:

```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;
```

```text
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

:::status{of="dal"}
:::

`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`:

```gizlang title="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:

```sh
giz typed.giz
```

```text output
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`](#two-sessions-in-one-program). Make `index.giz`:

```gizlang title="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()!;
}
```

```sh
giz index.giz
```

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

```text output
-: 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`:

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

```sh
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
```

```text
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

:::status{of="gizdb-backup"}
:::

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

```sh
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
```

```text
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:

```text
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.

```sh
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](../operations/gizdb-server.md#back-up-and-restore-to-a-point-in-time).

### Use the Redis protocol

:::status{of="gizdb-kv"}
:::

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

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

```sh
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
```

```text
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](../benchmarks.md) page.


### Live queries

:::status{of="gizdb-live"}
:::

`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.

```sql
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

:::status{of="gizdb-files"}
:::

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

```sql
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

:::status{of="gizdb-kafka"}
:::

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.

```sh
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

:::status{of="gizdb-ingest"}
:::

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

| Module | Source | Tables | Call |
| --- | --- | --- | --- |
| `gizdb:ingest/otlp` | OpenTelemetry exporters (OTLP/HTTP, JSON or protobuf, no compression) | `otel_logs`, `otel_spans`, `otel_metrics` | `handle(connection, request)` answers `POST /v1/logs`, `/v1/traces` and `/v1/metrics` |
| `gizdb:ingest/focus` | A cloud bill as a FOCUS CSV export | `focus_ledger`, `focus_imports` | `load(connection, stream, source, period, keys)` replaces one source and billing period in one write |
| `gizdb:ingest/stripe` | The Stripe API, read-only, with a restricted key | `stripe_customers`, `stripe_invoices` and four more, `stripe_cursors` | `reader(sys, base, key, account)`, then `reader.sync(connection, kind, through)`; `webhook(...)` checks the signature and applies one event |


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.

```gizlang
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:

```sh
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

:::status{of="gizdb-search"}
:::

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.

```sql
-- 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](vector.md).

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):

```sql
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:

| Setting | Default | Effect |
| --- | --- | --- |
| `search.rrf_k` | 60 | A larger k gives the top ranks less weight. |
| `search.text_weight` | 1 | The weight of the full-text rank. 0 ignores the full-text side. |
| `search.vector_weight` | 1 | The weight of the vector rank. |

`$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](sql-ml.md)). 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:

```sql
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;
```

```text
 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:

```sql
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`.

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

```text
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

:::status{of="gizdb-server"}
:::

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:

   ```sh
   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.

   ```python
   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;`):

   ```sh
   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"
   ```

   ```text
   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.

```sh
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"
```

```text
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.

```sh
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"
```

```text
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).

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

```text
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](https://gizstack.com/feedback) 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

| Area | SQL |
| --- | --- |
| Tables | `CREATE TABLE` with `PRIMARY KEY`, `NOT NULL`, `DEFAULT`, `serial`; `CREATE TABLE IF NOT EXISTS`; `DROP TABLE IF EXISTS`; `CREATE VIEW` |
| Writes | `INSERT` with many rows and with `RETURNING`; `INSERT ... ON CONFLICT (key) DO UPDATE SET ... = excluded....`; `UPDATE`; `DELETE` |
| Queries | `WHERE`, `ORDER BY`, `LIMIT`, `GROUP BY`, `count`, `sum`, `max`; inner and `LEFT JOIN`; `IN (SELECT ...)`; `WITH` (CTE); window functions |
| Functions | `upper`, `length`, `coalesce`, `now()`, `version()`, `jsonb` with `->>`; all of them are in [SQL functions](../reference/sql.md) |
| Numbers | `0.1 + 0.2` is `0.3`; `numeric(p,s)` is exact |
| Transactions | `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `ROLLBACK TO SAVEPOINT`, `BEGIN ISOLATION LEVEL ...`, `SHOW transaction_isolation`; SQLSTATE 40001 on a write conflict in REPEATABLE READ |
| Indexes | `CREATE INDEX`, `CREATE UNIQUE INDEX` and `UNIQUE` columns (23505 on a duplicate), `DROP INDEX`, `EXPLAIN` |
| Import and export | `COPY ... FROM STDIN` and `TO STDOUT` in text, CSV, binary and Parquet |
| Live and files | `changes(...)`, `read_csv(...)`, `read_parquet(...)`, file globs as tables |
| Search | `match`, `near`, `score()`, `rrf()`, `rrf(rank, rank, k [, weight, weight])`, `pgml.tune_search`, `<->`, `<=>`, `<#>`; `recall_at_k`, `precision_at_k`, `ndcg_at_k`, `reciprocal_rank`, `average_precision` |
| Protocol | Simple and extended query protocol (`\bind`); SCRAM-SHA-256 passwords; SQLSTATE in errors |

### Modules

`giz doc NAME` prints the reference of any declaration below.

| Module | Contents |
| --- | --- |
| `gizdb` | `open`, `Engine` (`close`, `sync`, `checkpoint`), `connection`, `Conn` (`run`, `query`), `Rows`, `Value`, `Type`, `Schema`, `render`, `sqlstate`, `DbError` |
| `gizdb:typed` | `Slot`, `Contract`, the codecs |
| `gizdb:query` | `sql`, `Prepared` (a typed query) and the query builders |
| `gizdb:ingest/otlp`, `focus`, `stripe` | Copies from OpenTelemetry, FOCUS bills and Stripe (see above) |

### The gizdb command

| Command | Does |
| --- | --- |
| `gizdb serve` | Runs the server: PostgreSQL protocol, and with options the Redis and Kafka protocols and an OTLP receiver |
| `gizdb restore` | Restores a base backup and its archived WAL to a point in time |
| `gizdb branch` | Makes a branch from a base backup that shares its sealed files |
| `gizdb verify` | Checks a stopped database without writing to it: log replay, stray files, keys, NOT NULL, CHECK and foreign keys |
| `gizdb salvage` | Copies a database with a damaged log aside, then cuts the log at the first bad frame |
| `gizdb cluster` | Serves many databases on one port, each in its own process |
| `gizdb migrate` | Copies, verifies and compares a database from another system (`copy`, `verify`, `compare`, `status`) |
| `gizdb ingest` | Loads a FOCUS bill (`focus`) or a Stripe account (`stripe`) into a stopped database |
| `gizdb --help` | Lists every command and option |