* bjopg — PostgreSQL for Bjolang The [[https://github.com/bjoli/bjosql][bjosql]] query language, running on PostgreSQL through [[https://www.npgsql.org][Npgsql]]. Every operation is a bjoroutine: a query suspends its fiber while it waits for the server, and does not hold a thread. #+BEGIN_SRC scheme (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))) #+END_SRC ** Using it #+BEGIN_SRC scheme (depends (package (name (bjopg)) (version (version-at-least "0.1.0")) (source (git (url "https://rikspucko.koketteriet.se/bjoli/bjopg.git"))))) #+END_SRC ~(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. #+BEGIN_SRC scheme (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))))))) #+END_SRC 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: #+BEGIN_SRC sh 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 #+END_SRC ~--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 #+BEGIN_SRC sh BJOPG_TEST="Host=localhost;Database=bjosql_test" bjo run tests/pg.bjo #+END_SRC 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.