• bjopg — PostgreSQL for Bjolang
  • Using it
  • Operations
  • The pool and transactions
  • Fibers and cancellation
  • Errors
  • Types
  • A server for development
  • Tests

bjopg — PostgreSQL for Bjolang

The bjosql query language, running on PostgreSQL through Npgsql. Every operation is a bjoroutine: a query suspends its fiber while it waits for the server, and does not hold a thread.

(import (std datetime))
(import (std uuid))
(import (bjosql core))
(import (bjopg core))

(def/table aches Ache
  (id Uuid #:primary-key)
  (what string)
  (felt Instant)
  (severity int))

(defbjo (main args)
  (match (call-with-db "Host=localhost;Database=aches"
           (bjoroutine (db)
             (def (Ok _) (create-table db aches) :propagate)
             (def (Ok _) (insert db aches (Ache (id (uuid-v7)) (what "knee") (felt (now)) (severity 2))) :propagate)
             (fetch db aches '(select (where (severity (>= 2))) (order-by (desc felt))))))
    ((Ok rows) (loop (:for a rows) (:do (println (record-ref a what)))) 0)
    ((Err e) (println (sql-error-message e)) 1)))

Using it

(depends
  (package (name (bjopg))
           (version (version-at-least "0.1.0"))
           (source (git (url "https://rikspucko.koketteriet.se/bjoli/bjopg.git")))))

(bjosql) comes along with it. The package declares Npgsql, and bjo restores it for any build that uses the package, so a program declares no NuGet packages of its own.

Operations

Each operation is a bjoroutine, and returns a (Result SqlError ...).

(open-db connection-string)	a pool of connections; see below
(close-db db)	closes the pool and its connections
(call-with-db connection-string f)	open, call f, and close however f returns
(create-table db t)	CREATE TABLE IF NOT EXISTS
(insert db t row)	the row as stored, with the key the server chose for an #:auto column
(insert-all db t rows)	all of the rows or none of them, in one transaction
(fetch db t '(select ...))	the rows, read into the table's record
(fetch-one db t '(select ...))	the first row, as an Option
(fetch-rows db t '(select ...))	the rows as Row values, for a projection
(execute db t '(update ...))	the number of rows changed; also for delete
(query-sql db s) (execute-sql db s)	SQL written by hand, from sql, with ? for each value
(with-transaction db f)	commits when f returns Ok; rolls back on Err or on a throw

The connection string is Npgsql's: "Host=localhost;Port=5432;Database=app;Username=ada;Password=...". open-db makes one connection and gives it back, so a server that cannot be reached is an Err from open-db and not from the first query. A connection string that cannot be read is the program's mistake, and throws.

The pool and transactions

A Db is a pool. Each operation takes a connection, runs, and gives it back, so any number of fibers can use one Db at the same time. Open one Db when the program starts, and use it everywhere.

with-transaction holds one connection for its whole function, and gives the function a Db that runs everything on that connection. Use that Db inside the function, not the pool: a statement given to the pool runs on another connection, outside the transaction.

(with-transaction db
  (bjoroutine (tx)
    (def (Ok _) (execute tx accounts '(update (set (balance ,(- a 100))) (where (id (= 1))))) :propagate)
    (execute tx accounts '(update (set (balance ,(+ b 100))) (where (id (= 2)))))))

A transaction inside a transaction is a savepoint. When it fails, it is rolled back, and the outer transaction goes on. PostgreSQL refuses every statement in a transaction after an error until it is rolled back; the savepoint is what makes the outer transaction usable again.

The Db of a transaction is one connection, so use it from one fiber at a time.

Fibers and cancellation

A call suspends the fiber while it waits for the server. The ambient cancellation token is passed to Npgsql, so a cancelled scope cancels its query on the server. With bjoweb's #:cancel-on-disconnect #t, a client that goes away cancels the query it was waiting for.

There is no blocking version of the operations yet. From code that is not a bjoroutine, use (sync/blocking (promise-join (bjo ...))).

Errors

(DbError code message) is PostgreSQL's SQLSTATE and message:

"23505"	a unique violation
"23503"	a foreign key violation
"42601"	SQL the server cannot parse
"3D000"	a database that does not exist
"08000"	a server that cannot be reached, or a connection that was lost

"08000" is connection_exception, the class the SQL standard has for this. A lost connection is a DbError and not an exception, because a server on the network can go away while the program is correct. (DecodeError message) is a row that does not fit its record, and (BadQuery message) is a statement that cannot be written. Anything else is an exception.

Types

Bjolang	PostgreSQL	Sent as (.NET)
int	integer	long
long	bigint	long
double	double precision	double
string	text	string
bool	boolean	bool
(Array byte)	bytea	byte[]
Date	date	DateOnly
Time	time	TimeOnly
DateTime	timestamp	DateTime, kind Unspecified
Instant	timestamptz	DateTime, kind Utc
decimal	numeric	decimal
Uuid	uuid	Guid
JsonDoc	jsonb	string, as jsonb

Every value is a typed parameter, so no cast is written in the SQL. A value is read back by the PostgreSQL type of its column: a date, a timestamp and a timestamptz all arrive as a .NET DateTime, and only the type name tells them apart.

A decimal is numeric, so ORDER BY, < and SUM on it are exact and numeric, as they are not in SQLite. jsonb stores JSON in a form of its own: the value is the same after a read, but the key order and the spaces can differ.

A type bjosql does not have, such as inet, is a column with #:sql-type "INET" and a type whose ToSql answers (SqlOther "inet" text). It is sent as text with a cast, $1::inet, and the server checks it. A column of a type with no case of its own is read as (SqlOther type-name text).

A server for development

PostgreSQL does not need systemd. As your own user, without root:

initdb -D ~/.local/share/postgres/dev -U "$USER" --auth=trust --encoding=UTF8 --locale=C.UTF-8
echo "listen_addresses = 'localhost'"  >> ~/.local/share/postgres/dev/postgresql.conf
echo "unix_socket_directories = '/tmp'" >> ~/.local/share/postgres/dev/postgresql.conf
pg_ctl -D ~/.local/share/postgres/dev -l ~/.local/share/postgres/dev.log start
createdb -h localhost bjosql_test

--auth=trust lets every local connection in without a password, so the server only listens on localhost. pg_ctl ... stop stops it. A container that restarts does not start it again: run the start line.

Tests

BJOPG_TEST="Host=localhost;Database=bjosql_test" bjo run tests/pg.bjo

Every line should start with "ok:". Without BJOPG_TEST the test says so and runs nothing. It makes tables named bjopg_..., and drops them first and last.

It checks that each type is stored as PostgreSQL's own type and reads back equal; that ORDER BY and tests on an instant, a uuid and a decimal are those of the values; nulls in typed columns; the counts of updates and deletes; the SQLSTATE of a unique violation, of bad SQL, of a database that does not exist and of a server that is not there; transactions that commit, roll back on Err and on a throw, and a savepoint that fails inside a transaction that then commits; a type of one's own sent as SqlOther with a cast; and 20 fibers querying one pool at once, which overlap rather than wait for each other.