PostgreSQL in Gleam with pog, squirrel, and cigogne

A basic yet opinionated PostgreSQL setup in the Gleam programming language.

---

TL;DR - The setup will work like this:

import cigogne.{type CigogneError}
import cigogne/config
import envoy
import gleam/erlang/process.{type Name}
import gleam/int
import gleam/option.{Some}
import gleam/otp/static_supervisor.{RestForOne}
import gleam/otp/supervision.{type ChildSpecification}
import gleam/result
import pog.{type Connection, type Message}
import sql

fn start_application_supervisor(pool_name: Name(Message)) {
  let pool_child = create_pog_pool_child(pool_name)

  static_supervisor.new(RestForOne)
  |> static_supervisor.add(pool_child)
  |> static_supervisor.start
}

fn create_pog_pool_child(
  pool_name: Name(Message),
) -> ChildSpecification(Connection) {
  let assert Ok(pg_database) = envoy.get("PGDATABASE")
  let assert Ok(pg_user) = envoy.get("PGUSER")
  let assert Ok(pg_password) = envoy.get("PGPASSWORD")
  let assert Ok(pg_host) = envoy.get("PGHOST")
  let assert Ok(pg_port_string) = envoy.get("PGPORT")
  let assert Ok(pg_port) = int.parse(pg_port_string)

  pog.default_config(pool_name)
  |> pog.database(pg_database)
  |> pog.user(pg_user)
  |> pog.password(Some(pg_password))
  |> pog.host(pg_host) // default is 127.0.0.1
  |> pog.port(pg_port) // default is 5432
  |> pog.pool_size(10) // default is 10
  |> pog.supervised
}

fn migrate_db() -> Result(Nil, CigogneError) {
  use config <- result.try(
    config.get("your_app_name") |> result.map_error(cigogne.ConfigError),
  )
  use engine <- result.try(cigogne.create_engine(config))
  cigogne.apply_all(engine)
}

pub fn main() -> Nil {
  let db_pool = process.new_name("pog_pool")

  let assert Ok(_) = start_application_supervisor(db_pool)
  let assert Ok(_) = migrate_db()

  let conn = pog.named_connection(db_pool)
  let assert Ok(_) = sql.create_starfish(conn, "Lucy")

  // ONLY so you can take all the time in the world
  // to marvel at Lucy.
  // In reality, here is the business logic INSTEAD.
  process.sleep_forever()
}

                
            

Too Short; Must Read More

Important: This is an opinionated approach of a Gleam newbie. Alternatives exist and might prove preferable.

We'll use this tech stack:

• PostgreSQL as database
• pog as its canonical database driver for Gleam
• squirrel to generate type-safe functions based on pog
• cigogne to facilitate migrations
• envoy to fetch environment variables

The plan - summoning Lucy

Lucy is Gleam's mascot. Let's summon her as a database row via these steps:

0. Add the dependencies
1. Set up the database driver
2. Create the migration to create a database table - we'll call it starfish
3. Apply the migration
4. Create the .sql file to add a starfish row
5. Generate a type-safe function to call that statement in Gleam
6. Use the generated function in Gleam

0. Add the dependencies

gleam add cigogne@5
gleam add envoy@1
gleam add gleam_erlang@1
gleam add gleam_otp@1
gleam add pog@4
gleam add --dev squirrel@4

                
            

1. Set up the database driver

We need a database connection pool. We'll also want to make this part of the application's supervision tree.

First, set Postgres' environment variables, for example:

PGDATABASE=starfish_db
PGUSER=postgres
PGPASSWORD=postgres
PGHOST=localhost
PGPORT=5432

                
            

Then, set pog up like this:

import envoy
import gleam/erlang/process.{type Name}
import gleam/option.{Some}
import gleam/otp/static_supervisor.{RestForOne}
import gleam/otp/supervision.{type ChildSpecification}
import pog.{type Connection, type Message}

fn create_pog_pool_child(
  pool_name: Name(Message),
) -> ChildSpecification(Connection) {
  let assert Ok(pg_database) = envoy.get("PGDATABASE")
  let assert Ok(pg_user) = envoy.get("PGUSER")
  let assert Ok(pg_password) = envoy.get("PGPASSWORD")
  let assert Ok(pg_host) = envoy.get("PGHOST")
  let assert Ok(pg_port_string) = envoy.get("PGPORT")
  let assert Ok(pg_port) = int.parse(pg_port_string)


  pog.default_config(pool_name)
  |> pog.database(pg_database)
  |> pog.user(pg_user)
  |> pog.password(Some(pg_password))
  |> pog.host(pg_host) // default is 127.0.0.1
  |> pog.port(pg_port) // default is 5432
  |> pog.pool_size(10) // default is 10
  |> pog.supervised
}

fn start_application_supervisor(pool_name: Name(Message)) {
  let pool_child = create_pog_pool_child(pool_name)

  static_supervisor.new(RestForOne)
  |> static_supervisor.add(pool_child)
  |> static_supervisor.start
}

pub fn main() {
  let db_pool = process.new_name("pog_pool")
  let assert Ok(_) = start_application_supervisor(db_pool)
}

                
            

2. Create the migration

While not strictly necessary, we'll use cigogne and its helpful CLI. Run:

gleam run -m cigogne new --name create-starfish-table

                
            

This creates an empty migration at priv/migrations/ in the form of <MigrationTimestamp>-<MigrationName>.sql. Let's fill it out like so:

--- migration:up
CREATE TABLE IF NOT EXISTS starfish (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--- migration:down
DROP TABLE IF EXISTS starfish;

--- migration:end

                
            

3. Apply the migration

We'll go the easy route and let cigogne apply migrations for us on app start. By default, it expects a DATABASE_URL environment variable. Following the example, let's add it like this.

DATABASE_URL=postgresql://postgres:postgres@${PGHOST}:${PGPORT}/${PGDATABASE}

                
            

Then, create priv/cigogne.toml via:

gleam run -m cigogne config init

                
            

And extend the code by:

import cigogne.{type CigogneError}
import cigogne/config
import gleam/result

// ...

fn migrate_db() -> Result(Nil, CigogneError) {
  use config <- result.try(
    config.get("your_app_name") |> result.map_error(cigogne.ConfigError),
  )
  use engine <- result.try(cigogne.create_engine(config))
  cigogne.apply_all(engine)
}

pub fn main() {
  // ...
  let assert Ok(_) = migrate_db()
}

                
            

Note: Replace "your_app_name" with the name from your gleam.toml. This tells cigogne to look for migrations in your application's priv/migrations folder.

This will now apply all migrations that follow cigogne's expected structure when run.

4. Create the .sql file

squirrel also isn't strictly necessary but provides a nice wrapper around pog. Its CLI works differently, though -- we must first create a .sql file and then run the command to generate type-safe functions.

Note: squirrel expects at most one SQL statement per file!

So let's create src/sql/create_starfish.sql.

INSERT INTO starfish (name) VALUES ($1);

                
            

5. Generate a type-safe function

Now, let's gleam run this (or otherwise make sure the database is up and running with the newest migrations). Start a separate session and execute:

gleam run -m squirrel

                
            

This will now create src/sql.gleam with a create_starfish function and some helpful decoding.

6. Use the generated function to summon Lucy

Finally, extend the code by:

import sql

// ...

pub fn main() {
  // ...
  let conn = pog.named_connection(db_pool)
  let assert Ok(_) = sql.create_starfish(conn, "Lucy")

  // ONLY so you can take all the time in the world
  // to marvel at Lucy.
  // In reality, here is the business logic INSTEAD.
  process.sleep_forever()
}

                
            

Lucy is summoned. Beware her demand splitting this up into modules.