pg — PostgreSQL for slate

A PostgreSQL client written in slate, speaking the wire protocol itself. No libpq, no C binding, no blocking call: it is slate:net and slate:crypto and about a thousand lines of slate.

slate install github.com/slate-language/pg

It needs slate 0.0.35 or later. 0.0.4 carried slate:crypto — a package cannot have a native of its own, so SHA-256, HMAC, PBKDF2 and a nonce from the kernel all had to arrive in the language before a client could log in to a modern PostgreSQL at all; 0.0.5 carries the two things TLS needs, startTls, which upgrades an open socket, and a connect that takes a name rather than only an address; 0.0.6 carries md5, which an older server's login asks for and which this package used to write out in slate; 0.0.34 is where an operator method is named for its operator, so Decimal answers for +, <=> and unary_- rather than for plus, compare and negated; and 0.0.35 is where the global len(x) and the .len() method alias are gone in favor of .length.

The floor moves to slate 0.1.0, which is where / between two integers answers a real — whole-number division is \ — and where an integer grows instead of wrapping. Both are written into decimal.sl: the division it means is spelled \, and the 18-digit ceiling is a magnitude the type enforces rather than a wrap it detects.

0.3.0 needs 0.0.9. 0.0.8's rest parameters are what let query take its parameters as arguments and its operator hooks are what let a Decimal answer for +; 0.0.9 is what the exported types need — a type declaration could not name one imported from another file before it.

import { pg, Answer, Result } from pg
import { decimal, Decimal } from pg/decimal

// What a query answers, and what a program's own vocabulary is built on.
reported(a: Answer) -> string = if a.ok then string(a.value.rows.length) + " rows" else a.error

Answer has every field but ok optional, which is the honest shape: a refusal has no value at all, and reading one off it would be undefined — which slate refuses to carry anywhere.

import { pg } from pg

async main()
    val db = (await pg("postgres://ada@127.0.0.1/notes")).value
    val r = await db.query("select id, title from notes where author = $1", "ada")

    for row in r.value.rows
        print(row.id, row.title)

    db.close()

main()

The client is on the loop, and that is the point

A database call in a server must not stop the server. Every query here is a promise on the same event loop that is answering HTTP, so a handler waiting for PostgreSQL is the only thing waiting. Binding libpq would have made that impossible rather than merely difficult — PQexec blocks, and a blocking call on this loop stops the whole program.

Connecting

pg takes a URL, an object, or nothing at all.

val db = (await pg("postgres://ada:secret@db.internal:5432/notes")).value
val db = (await pg({ host: "127.0.0.1", user: "ada", database: "notes", password: "secret" })).value
val db = (await pg({ })).value

With nothing given it reads what psql reads — PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE — and falls back to 127.0.0.1, 5432, $USER, and a database named after the user. So a program that takes none of them still connects wherever psql does.

It answers a result rather than throwing, which is slate's rule for anything that reaches the network: a database that is not up, a password that is wrong and a role that does not exist are all things a program handles.

val made = await pg(env("DATABASE_URL"))

if !made.ok then print("no database:", made.error)

Logging in

SCRAM-SHA-256, MD5 and cleartext, and the first is the one that matters: PostgreSQL 14 and later configure it by default, so a client without it cannot log in to a current server at all.

The exchange is RFC 7677's, checked in both directions — this client verifies the server's signature as well as sending its own proof, so a server that cannot prove it knew the password is refused rather than trusted.

TLS

PostgreSQL negotiates TLS rather than assuming it, and that is why a secure connect would not have done: a connection begins in the clear, sends eight bytes asking, and reads a single byte back — S for yes, N for no. Only then does the handshake start. sslmode is libpq's, spelled the same way, in an option or in the URL a provider hands out:

val db = await pg({ host: "db.example.com", sslmode: "require", trust: authority })
val db = await pg("postgres://user:secret@db.example.com/app?sslmode=require")

mode	what happens
disable	nothing is sent; the connection is in the clear
allow, prefer	asks; a server that says no is spoken to in the clear
require, verify-ca, verify-full	asks; a server that says no is refused

prefer is the default, which is libpq's. Every connection asks, and a server that will not speak TLS is spoken to exactly as it was before.

require verifies fully, which libpq's require does not. libpq encrypts without checking who it is talking to unless told verify-full; slate's TLS offers no way to skip verification, so the three strict modes are one behaviour here. The host is what the certificate is checked against, which is why it is a name and not an address — an address works too and is checked against the certificate's addresses, but a hosted database is reached by name. A private authority is named with trust, as PEM text, which is added to the machine's own store rather than put in place of it.

A handshake that fails is not retried in the clear. libpq drops to an unencrypted connection under prefer when TLS fails after the server said S; a certificate that does not check out is a reason to stop rather than a reason to continue quietly.

Querying

val r = await db.query("select id, title from notes")
val r = await db.query("select * from notes where id = $1", 7)
val r = await db.query("select * from notes where id = any($1)", ids)

The parameters are the arguments after the SQL, and a list a program worked out is spread: db.query(sql, ...values). An array parameter is then an ordinary one — $1::text[] is given ["a", "b"] and nothing has to be wrapped twice.

Parameters decide which protocol is spoken. No parameters is a simple Query, which may hold several statements; any parameters is Parse/Bind/Execute, which may hold one. That is not an optimisation — a simple query cannot carry a parameter at all.

A parameter is never interpolated into the SQL. The server parses the statement before it is given a single value, so nothing a parameter contains can become part of the query. $1 is the whole of the defence against injection and there is no escaping function here because there is nothing to escape.

A query answers a result:

{ ok: true, value: { rows, values, fields, command, count, results } }
{ ok: false, error: "...", code: "23505", info: { ... } }

rows	an array of objects, keyed by column name
values	the same rows as arrays, for a query with two columns of one name
fields	{ name, oid } per column
command	"SELECT", "INSERT", "UPDATE" …
count	how many rows it touched, or null
results	one entry per statement, for a simple query holding several

A refusal is an answer and not a fault, because a unique violation, a syntax error and a table that is not there are all things a server wants to turn into a status code:

val put = await db.query("insert into notes (title) values ($1)", title)

if !put.ok && put.code == "23505" then return { status: 409, body: "that title is taken" }

code is the SQLSTATE and info carries the whole ErrorResponse with its fields named — severity, detail, hint, table, column, constraint, position and the rest.

Calling query on a connection that is closed throws, because that is the program's own mistake rather than the database's answer. slate's two channels, used the way slate uses them.

Pipelining is what happens when you do not await

Answers come back in the order the queries were written, which is the protocol's own guarantee — so issuing several and awaiting them afterwards costs one round trip rather than several.

val a = db.query("select count(*) from notes")
val b = db.query("select count(*) from authors")

print((await a).value.rows[0].count, (await b).value.rows[0].count)

Types

Everything crosses in the protocol's text format, in both directions. What comes back:

PostgreSQL	slate
bool	true / false
int2, int4, int8	an integer
float4, float8	a real — NaN and Infinity included
numeric	text, because a double would round it
text, varchar, char, and anything unrecognised	text
json, jsonb	the parsed value
bytea	an array of bytes
date, time, timestamp, timestamptz	a slate temporal value
any array of the above	an array
null	null, which is never the empty string

A type this does not know comes back as the text the server sent, which is never wrong — it is what the server chose to send. PostgreSQL has hundreds of types and a program may have defined its own, so refusing what it did not recognise would be a wall between a program and its own database.

numeric staying text is the decision here worth defending. It is an arbitrary-precision decimal — it is what a money column is — and slate's real is a double. A program that wants arithmetic asks for ::float8 and says so.

Going out: text as itself, numbers as numbers, true as t, null as absence (which is not the empty string), an object as JSON, and an array as a PostgreSQL array literal. For bytea, say so with bytea(bs) — slate has one array type and a database has both int[] and bytea, so that is the one place the two cannot both be guessed at.

import { pg, bytea } from pg

await db.query("insert into files (name, body) values ($1, $2)", "a.png", bytea(bs))

numeric, and the exact decimal beside it

A numeric column comes back as TEXT by default, and that loses nothing: it is an arbitrary-precision decimal — which is what a money column is — and slate's real is a double, so reading one as a number would quietly round values the database went to some trouble to keep exact. node's pg answers a string for the same reason.

What text cannot do is arithmetic, so there is a Decimal:

import { decimal, Decimal } from pg/decimal

val total = decimal("19.99") + decimal("1.60")     // 21.59, exactly

print(total.toFixed(2))

It answers for +, -, *, /, %, unary - and the four orderings. A value is scaled integer units, so every one of those is exact — except /, which keeps six places by default and divided(b, n) where a program wants to say. A whole number mixes (total + 1); a real is refused, because 0.1 is not 0.1 in a double and accepting one would put back the rounding this type exists to keep out.

A connection may be told to hand numeric columns over as Decimals, which is the one option that changes what a column is:

val db = (await pg({ ..., decimals: true })).value
val r = await db.query("select price, tax from sales")

print((r.value.rows[0].price + r.value.rows[0].tax).toFixed(2))

It is off by default because of what it costs: toJSON(row) renders an object where it used to render a string, and string(d) is the object too — slate has no way for a class to say how it prints. A program that turns it on writes d.toFixed(2) where it wrote nothing before.

Eighteen significant digits is the ceiling, and going past it is a fault rather than a wrap: a slate integer is 64 bits and wraps, which is the one thing a money type may not do quietly. A numeric a Decimal cannot hold — NaN, or two hundred digits — keeps its text, which is what every value this driver cannot read already does.

The rest of the connection

db.close()                      // and every query still waiting is answered
db.status()                     // "I" idle, "T" in a transaction, "E" in a failed one
db.parameters()                 // server_version, TimeZone, client_encoding, …
db.onNotice((n) -> ...)         // `raise notice`, and anything else the server says
db.onNotify((n) -> ...)         // `listen` / `notify`: { channel, payload, pid }

A transaction is SQL, because it already is — begin, commit, rollback and a savepoint are statements, and status() is how a program asks where it stands.

When a connection goes, every query waiting on it is settled with a result rather than left. A program awaiting an answer that will never come would otherwise wait for the rest of the run, which in a server is a request that never finishes.

Pooling

import { pool } from pg

val p = (await pool("postgres://ada@127.0.0.1/notes", { max: 8 })).value

val r = await p.query("select id, title from notes where author = $1", "ada")

await p.close()

pool takes exactly what pg takes, URL and all, since a pool is the same database reached more than once. The four numbers beside it may travel in the same object where the options are one:

val p = (await pool({ host: "db", database: "notes", max: 8 })).value

min	1	opened before the first query, and never reaped
max	10	connections at once; a borrow past this waits
idle	30000	ms a connection above min is kept before it is closed
timeout	10000	ms a borrow waits once max are out

These are node-postgres's four under shorter names — its min, max, idleTimeoutMillis and connectionTimeoutMillis — because they are the numbers a person arrives already knowing.

min is 1 rather than 0 so that a pool which cannot reach the database says so when it is made. A pool of nothing always succeeds and reports the connection error on the first query, which moves a deployment mistake out of start-up and into a request.

One statement, or one connection

p.query borrows a connection, runs one statement and gives it straight back. It answers exactly what db.query answers, plus the two things that can go wrong before there is a connection at all — an open that failed, and a wait that ran out — both as { ok: false, error }.

p.with holds one connection for as long as the closure runs, which is the transaction case and the only reason a program needs to see a connection at all: begin and commit are two statements and they must be the same connection or they mean nothing.

val done = await p.with(async (db) ->
    await db.query("begin")
    await db.query("insert into notes (title) values ($1)", title)
    await db.query("commit")

    true)

What the closure answers comes back under value. What it throws is thrown on, since that is the program's own failure and not the pool's — the connection is given back first.

What is dropped rather than returned

A connection that died is not put back, and neither is one left inside a transaction: a closure that threw between begin and commit leaves the server holding it open, and the next borrower would find itself inside somebody else's work. Both are closed, and the next borrow opens a fresh one.

A query that FAILED is not a connection that failed. A constraint violation, a syntax error and a table that is not there all answer { ok: false } on a connection that is perfectly well, and a pool that dropped one on every such answer would reconnect for every typo.

Waiting, and closing

A borrow past max queues rather than opening a connection anyway, because max is usually the server's max_connections divided among the machines that talk to it. A waiter is given the next connection that comes back, oldest first, and is failed after timeout — so a program that leaks a borrow is a request that answers slowly rather than a server that stops.

p.close() drains. It fails every waiter at once, closes everything idle, and waits for what is out to come back rather than closing it underneath a query. A borrow after that throws, which is where calling query on a closed connection already goes.

p.size()                        // connections open or opening
p.free()                        // of those, the ones nobody is using
p.waiting()                     // borrows queued at `max`

Migrations

A directory of numbered SQL files, applied in order and once each, over a connection or a pool:

migrations/
    0001_people.up.sql
    0001_people.down.sql
    0002_pets.up.sql

import { pg, migrate, rollback, migrationStatus } from pg

val db = (await pg(env("PG_URL"))).value

val r = await migrate(db, "migrations")              // { ok, value: { applied } }
val s = await migrationStatus(db, "migrations")      // { ok, value: { applied, pending, changed, missing } }
val b = await rollback(db, "migrations", { steps: 1 })   // { ok, value: { reverted } }

The number is the order and the identity. It is compared as a number, so 10_toys runs after 2_pets whatever their names sort as; the .down.sql is optional. Files that do not end in .sql are ignored. A .sql file not named NNNN_name.up.sql or NNNN_name.down.sql, two files with one number, and a .down.sql with no .up.sql are refused with a sentence naming the file.

What ran is recorded in schema_migrations(version, name, checksum, applied_at), made where it is absent. The checksum is the SHA-256 of the .up.sql.

• Each migration is its own transaction, with its row. One that fails leaves nothing behind, the ones after it stay pending, and the answer is { ok: false, error, code, info, applied }, where applied lists what this run applied before the failure.
• An applied migration whose file has changed is refused, by name, before anything runs. It is never re-run and never skipped: put back what was applied, or write the change as a new migration. migrationStatus lists it under changed, and rollback will not undo it either.
• pg_advisory_lock is held for the whole run, so two processes started at once, such as two replicas of one deployment, apply each migration once between them. The second one waits and then finds nothing to do. { lock: n } uses a key of your own, for a database with two independent sets of migrations.
• rollback undoes the newest first, steps of them (1 by default), each in its own transaction with the deletion of its row. If any of them has no .down.sql, the rollback is refused before any of them runs.
• A pool is asked for one connection for the whole run, because an advisory lock belongs to a session.

Everything read from the directory or the database comes back as an answer. A steps that is not a positive count, or a connection left inside a transaction, is a fault. A migration cannot contain anything PostgreSQL refuses inside a transaction (create index concurrently, for example).

A package cannot ship a command, because the scripts that slate run reads belong to the project, so examples/migrate.sl is the file a project copies:

PG_URL=postgres://ada@127.0.0.1/notes slate migrate.sl up
PG_URL=postgres://ada@127.0.0.1/notes slate migrate.sl down 2
PG_URL=postgres://ada@127.0.0.1/notes slate migrate.sl status

It reads MIGRATIONS for the directory (migrations by default) and PG_URL or DATABASE_URL for the database. It exits 0 when the database is where it was asked to be, 1 when it is not, and 2 for a command it does not understand.

What is not here

• Seeders and factories.
• COPY, which is refused with a sentence rather than left to desynchronise the connection.
• Cursors, LISTEN beyond delivering the notification, and named prepared statements.

Tests

slate test tests

Every test talks to a PostgreSQL server written in slate — the fake under tests/fake.sl — whose replies are written out longhand from the message formats. So what passes is this client agreeing with the protocol rather than with whichever database happens to be installed, and the suite needs no server. The SCRAM test goes further and does the server's half of the exchange, checking the client's proof the way PostgreSQL would.

The fake counts its connections, which is what makes the pool testable at all: min, max, reuse, the waiter timeout and dropping are every one of them a statement about how many times a client logged in.

check/live.sl is the other half of that bargain: the same ground against a real PostgreSQL 16 over SCRAM-SHA-256, run by hand, because a fixture cannot prove the two halves fit together. The pool is checked there against pg_stat_activity, which is the server's own count of what this client opened. check/migrate.sl does the same for migrations: it shows that a failure keeps nothing and that two runners started together apply each migration once.

Licence

ISC. See LICENSE.