Howdy

Howdy Database

howdy_database is an optional package for apps that keep data in PostgreSQL or SQLite through Gloo. It gives you:

  • a way to open PostgreSQL with safe production defaults, and the settings SQLite needs
  • transactions and queries that behave the same on both databases, and return service.Result so a failure maps straight to an HTTP response
  • migrations owned by a package, checksummed and checked at startup

Howdy Auth is built on it, so an app with auth already has it. You can use it for your own tables with or without auth.

Your app opens, configures and closes the database. Everything in this package only receives the Gloo Repo.

Install

Add the package to gleam.toml, next to howdy and gloo:

howdy_database = { git = "https://github.com/mikeyjones/howdy.git", ref = "v2.0.0", path = "database" }
gloo = ">= 1.0.2 and < 2.0.0"

Opening SQLite

Open the Repo with Gloo, then call database.sqlite_defaults once:

import gloo/adapter/sqlite
import howdy/database

pub fn connect() {
  let assert Ok(db) = sqlite.start(sqlite.file("app.db"))
  let assert Ok(_) = database.sqlite_defaults(db)
  db
}

It turns on foreign keys, which SQLite leaves off, waits up to five seconds for another connection's write lock instead of failing at once, and switches the file to write-ahead logging, so a backup or another reader never blocks the writer. On PostgreSQL it does nothing.

Opening PostgreSQL

howdy/database/postgres opens a Repo with production defaults:

import gleam/result
import howdy/database/postgres

pub fn connect() {
  let assert Ok(db) =
    postgres.from_env()
    |> result.try(postgres.start)
  db
}

from_env reads DATABASE_URL. from_url takes the same postgres://user:password@host:port/database?sslmode=… form directly. The URL is never repeated in an error, so the password stays out of your logs.

start waits up to 10 seconds for the server to answer, and refuses a server older than PostgreSQL 14. A wrong host, password or database fails as the app starts, not at the first request.

TLS

Without an sslmode:

  • TLS is off for loopback addresses, and for host names without a dot, such as a Compose service called db.
  • Everything else gets TLS, verified against the system's certificate authorities.

sslmode=require encrypts without verifying, and sslmode=disable turns TLS off. allow and prefer are refused, because they fall back to plain text without telling you.

Timeouts

Every connection starts with:

  • timezone UTC
  • a 30 second statement_timeout
  • a 5 second lock_timeout
  • a 60 second idle_in_transaction_session_timeout

Change them before start, in milliseconds. Zero turns one off:

postgres.from_env()
|> result.map(postgres.statement_timeout(_, 10_000))
|> result.map(postgres.pool_size(_, 20))
|> result.try(postgres.start)

postgres.parameter(config, name, value) sets any other server parameter. Migrations lift the statement and lock timeouts for their own transaction, so a long migration is not cut short.

Behind a pooler such as PgBouncer that rejects unknown startup parameters, set the timeouts on the database role with ALTER ROLE … SET, and pass zero here.

Querying with pog or Squirrel

Code that talks to pog directly, such as the functions Squirrel generates, can share the same pool. start_pool starts it like start, but keeps the pog.Connection as well as the Repo:

let assert Ok(pool) =
  postgres.from_env()
  |> result.try(postgres.start_pool)

let db = postgres.repo(pool)          // for auth and other Howdy modules
let conn = postgres.connection(pool)  // for pog and Squirrel

postgres.transaction opens one transaction and gives you both handles to it. Everything commits or rolls back together, and a Howdy module's own transaction becomes a savepoint inside it:

use conn, db <- postgres.transaction(pool)
use note <- result.try(
  sql.insert_note(conn, owner, body) |> result.map_error(postgres.error),
)
audit.record(db, owner, note.id)

Open the transaction this way, not with database.transaction: a Repo does not give up its pog connection, so Squirrel queries cannot join a transaction the Repo opened. postgres.error turns a pog error into a service.Error the way howdy/database does: a constraint violation becomes a Conflict, anything else an Internal, without the driver's message.

If your app starts pog itself, postgres.from_pog(started) wraps the pool in the same way, without waiting for the server. Queries sent straight to pog skip the query spans that the Repo records.

Sharing a SQLite Repo

Gloo gives a SQLite Repo exactly one connection. Everything that goes through howdy/database takes turns on it, first come first served, so auth and your own code can share one Repo safely.

Calls made directly through gloo/repo do not take turns. If your app makes them while requests are being served, give that code a second Repo of its own, opened on the same file.

PostgreSQL's pool hands each transaction its own connection, so none of this applies there.