howdy_database - v1.0.0

howdy_database

Package-owned migrations and portable transactions over Gloo for Howdy apps, on PostgreSQL and SQLite. howdy_auth is built on it, and applications can use it for their own tables, with or without auth.

gleam add howdy_database@1 gloo@1

The application opens, configures and closes the Gloo Repo; everything else in this package only receives it. Operations return howdy/service results, so a failure maps straight to an HTTP response.

Opening the database

For SQLite, open the Repo with Gloo and call database.sqlite_defaults(db) once. It turns on foreign keys, waits up to five seconds for another connection’s write lock, and switches the file to write-ahead logging.

For PostgreSQL, howdy/database/postgres opens a Repo with production defaults:

import howdy/database/postgres

let assert Ok(db) =
  postgres.from_env()
  |> result.try(postgres.start)

Behind a pooler such as PgBouncer that rejects unknown startup parameters, set the timeouts on the role with ALTER ROLE ... SET and pass zero here. The driver reports the Erlang node name as application_name.

Migrations

A package is a name and a list of ordinary gloo/migration.Migration values. It owns every table, index and trigger whose name begins with <name>_.

import gloo/migration as gloo_migration
import howdy/migration

pub fn schema() -> migration.Package {
  migration.Package("notes", [
    gloo_migration.new(
      1,
      "create_notes",
      "CREATE TABLE notes_notes (id TEXT PRIMARY KEY, slug TEXT NOT NULL UNIQUE)"
        <> migration.per_database(
        postgres: "; ALTER TABLE notes_notes ADD COLUMN created TIMESTAMPTZ",
        sqlite: "; ALTER TABLE notes_notes ADD COLUMN created BIGINT",
      ),
    ),
  ])
}

Run every package in one transaction from a deployment command, before starting the new version, and check at startup:

// src/migrate.gleam
let assert Ok(_) =
  migration.run(db, [auth.schema(), authorization.schema(), notes.schema()])

// at startup
let assert Ok(_) = migration.check(db, notes.schema())

Migration SQL is trusted code. It is split on semicolons, as in Gloo’s runner, so it cannot contain semicolons inside literals or procedural bodies, nor transaction control. Do not apply the same migrations through gloo/runner.

Transactions and queries

import gloo/sql
import howdy/database
import howdy/service

pub fn rename(db, id: String, slug: String) -> service.Result(Nil) {
  use conn <- database.write_transaction(db, touching: "notes_notes")
  use _ <- result.try(database.one(
    conn,
    "SELECT id FROM notes_notes n WHERE id = $1"
      <> database.for_update(conn, "n"),
    [sql.string(id)],
    decode.field(0, decode.string, decode.success),
    or: service.NotFound("note"),
  ))
  database.execute_or(
    conn,
    "UPDATE notes_notes SET slug = $1 WHERE id = $2",
    [sql.string(slug), sql.string(id)],
    on_constraint: fn(_) { service.Conflict("slug already taken") },
  )
}

Gloo’s SQLite adapter binds $n placeholders by position: never reuse one placeholder in a query, pass the value again.

Call query.where after query.update or query.delete. In Gloo 1.x, query.update and query.delete start a fresh condition list, so a where placed before them is silently dropped and the statement touches every row:

// UPDATE "notes_notes" SET slug = $1 WHERE id = $2
query.from(note.table())
|> query.update([#("slug", sql.string(slug))])
|> query.where(query.Eq("id", sql.string(id)))

// UPDATE "notes_notes" SET slug = $1, which renames every note
query.from(note.table())
|> query.where(query.Eq("id", sql.string(id)))
|> query.update([#("slug", sql.string(slug))])

Sharing a SQLite Repo. Gloo 1.x gives a SQLite Repo one connection. Everything that goes through this module serializes on that Repo, first come first served, so auth and application code can share it. Calls made directly through gloo/repo are not serialized; give those their own Repo on the same file. PostgreSQL’s pool reserves a connection per transaction, so none of this applies there.

Testing

gleam test
HOWDY_DATABASE_TEST_POSTGRES_URL=postgres://postgres@localhost/postgres gleam test

The PostgreSQL tests run only when the variable is set; its user must be able to create databases.

✨ Search Document