glsql

glsql reads a plain .sql schema file and generates a typed Gleam module for every table: a record type, a decoder, a column list, and param encoders for your driver. It never connects to a database, so generation works offline and in CI.

Install

glsql generates code that imports pog, so add both. Nothing glsql provides is imported by what it writes, so it is only needed at build time and goes under dev-dependencies:

gleam add pog
gleam add --dev glsql

Date and time columns generate code that imports gleam/time/timestamp and gleam/time/calendar, so add gleam_time too if your schema has any. Gleam warns when a module comes from a package you do not depend on directly:

gleam add gleam_time

Starting a pog pool also means naming a process and unwrapping what the actor returns, so the example below needs these two as well:

gleam add gleam_erlang gleam_otp

Quick start

Write a schema:

-- priv/schema.sql
create table users (
  id uuid primary key default gen_random_uuid(),
  email text not null unique,
  display_name text,
  is_admin boolean not null default false,
  created_at timestamptz not null default now()
);

Configure glsql:

# glsql.toml
schema = "priv/schema.sql"
out_dir = "src/db"
driver = "pog"

Generate:

gleam run -m glsql

This writes src/db/users.gleam:

// users.gleam
// Generated by glsql from priv/schema.sql. Do not edit.

import db/glsql_schema.{type Column, Column}
import gleam/dynamic/decode
import gleam/option.{type Option}
import gleam/time/timestamp
import pog

pub type Users {
  Users(
    id: String,
    email: String,
    display_name: Option(String),
    is_admin: Bool,
    created_at: timestamp.Timestamp,
  )
}

pub const table: String = "users"

pub const columns: String = "id, email, display_name, is_admin, created_at"

pub fn decoder() -> decode.Decoder(Users) {
  use id <- decode.field(0, decode.string)
  use email <- decode.field(1, decode.string)
  use display_name <- decode.field(2, decode.optional(decode.string))
  use is_admin <- decode.field(3, decode.bool)
  use created_at <- decode.field(4, pog.timestamp_decoder())
  decode.success(Users(id:, email:, display_name:, is_admin:, created_at:))
}

pub fn to_params(row: Users) -> List(pog.Value) {
  [
    pog.text(row.id),
    pog.text(row.email),
    pog.nullable(fn(v) { pog.text(v) }, row.display_name),
    pog.bool(row.is_admin),
    pog.timestamp(row.created_at),
  ]
}

A col_*() function is generated for each column too, which the Partial selects section below uses.

Alongside the table modules glsql writes one glsql_schema.gleam, holding the Column type those accessors return. It lives in out_dir with the rest of the generated code, so a build needs nothing from glsql itself.

Use the generated module with pog to query the table:

import db/users
import gleam/erlang/process
import gleam/list
import gleam/option
import gleam/otp/actor
import gleam/time/timestamp
import pog

pub fn main() {
  let config =
    pog.default_config(process.new_name("pog"))
    |> pog.host("localhost")
    |> pog.database("my_app")
  let assert Ok(actor.Started(data: db, ..)) = pog.start(config)

  // Read: the columns constant keeps the SELECT list and the
  // decoder's field order in sync, so this stays correct as the
  // schema evolves.
  let query =
    pog.query(
      "select " <> users.columns <> " from " <> users.table
      <> " where email = $1",
    )
    |> pog.parameter(pog.text("ada@example.com"))
    |> pog.returning(users.decoder())
  let assert Ok(pog.Returned(_, rows)) = pog.execute(query, db)

  // Write: to_params gives the values in column order, so they
  // line up with the same placeholders.
  let new_user =
    users.Users(
      id: "…",
      email: "grace@example.com",
      display_name: option.Some("Grace"),
      is_admin: False,
      created_at: timestamp.system_time(),
    )
  let insert =
    pog.query(
      "insert into " <> users.table <> " (" <> users.columns <> ")"
      <> " values ($1, $2, $3, $4, $5)",
    )
    |> list.fold(users.to_params(new_user), _, fn(q, p) { pog.parameter(q, p) })
  let assert Ok(_) = pog.execute(insert, db)

  rows
}

Partial selects

The query above fetches every column, because users.decoder() expects a full Users record. When a query only needs a few columns, fetching the rest wastes bandwidth. Each generated col_*() function returns a Column carrying its own decoder, so you can write a smaller record and compose just the pieces you need instead of always selecting everything:

import db/users
import gleam/dynamic/decode
import pog

pub type UserPreview {
  UserPreview(id: String, email: String)
}

fn user_preview_decoder() -> decode.Decoder(UserPreview) {
  use id <- decode.field(0, users.col_id().decoder)
  use email <- decode.field(1, users.col_email().decoder)
  decode.success(UserPreview(id:, email:))
}

pub fn find_preview(db: pog.Connection, email: String) {
  let query =
    pog.query("select id, email from " <> users.table <> " where email = $1")
    |> pog.parameter(pog.text(email))
    |> pog.returning(user_preview_decoder())
  pog.execute(query, db)
}

The position passed to decode.field must match the order of columns in your select list, not their order in the schema.

Configuration

glsql.toml at the project root:

schema = "priv/schema.sql"   # file, defaults to "priv/schema.sql"
out_dir = "src/db"           # defaults to "src/db"
driver = "pog"               # defaults to "pog"

# Override or add a type mapping. These already have one: text, varchar,
# character varying, char, citext, uuid, json, jsonb, bytea, tsvector,
# inet, cidr, macaddr, the integer types (int2, smallint, int4, int,
# integer, int8, bigint, serial, bigserial, smallserial), bool, boolean,
# float4, real, float8, double precision, numeric, decimal, date, time,
# timestamp, timestamptz, and the spellings that write out with time zone
# or without time zone.
#
# `gleam_type` and `decoder` go into the generated file as written, so any
# module they name has to be listed in `imports`.
[types.timestamptz]
gleam_type = "timestamp.Timestamp"
decoder = "pog.timestamp_decoder()"
encoder = "pog.timestamp($)"
imports = ["gleam/time/timestamp"]

# Rename a column whose name collides with a Gleam reserved word. A key with
# no dot applies to that column in every table, which is usually what you
# want for a reserved word. A "table.column" key overrides it for one table.
[rename]
type = "type_"
"badges.type" = "badge_type"

# Override the generated module or type name for a table.
[tables.users]
module = "user"
type = "User"

Unknown keys are rejected with a did-you-mean suggestion, so a typo like out_dr fails generation instead of silently writing to the wrong place.

Types that need a mapping

Some Postgres types have no built-in mapping, because which Gleam type suits them depends on how you want to represent the value. Generation stops with an Unsupported type error naming the column, and the fix is a [types] entry:

• range and multirange types, such as daterange and int8range
• interval
• time with time zone, also written timetz
• money, xml, bit, bit varying
• geometric types, such as point and polygon
• hstore, ltree, tsquery, macaddr8

Postgres renders a range as text, so reading one as a String works:

[types.daterange]
gleam_type = "String"
decoder = "decode.string"
encoder = "pog.text($)"

Pick whatever Gleam type matches what the driver returns for the column. The same applies to a domain or enum you define yourself: map it by its plain name, without the schema, so public.mpaa_rating is configured as [types.mpaa_rating].

A mapping to a type of your own needs the module in imports, or the generated file will not compile:

[types.citext]
gleam_type = "email.Email"
decoder = "email.decoder()"
encoder = "pog.text(email.to_string($))"
imports = ["my_app/email"]

Arrays and nullability

An array column becomes a List of whatever the element type maps to, and a column without not null becomes an Option. A nullable array is both, so labels text[] generates labels: Option(List(String)). A primary key counts as not null even when the column does not say so.

Known limits

Tables up to 142 columns work. Wider than that, the generated module does not compile.

decoder() reads one column per use expression, so a wide table gives a deeply nested chain. Past 142 the build stops with a stack overflow. A chain written out by hand nests just as deeply and stops at the same width, so writing the decoder yourself does not get around it. ulimit and RUST_MIN_STACK do not move where the limit falls.

Splitting the table is the way around it for now.

Upgrading from 2.x

Generated modules used to import glsql/schema, so glsql had to be a normal dependency and went into production builds along with the TOML and file reading it needs to generate. The Column type is now written into out_dir instead, as glsql_schema.gleam.

In gleam.toml, move the glsql line out of [dependencies] into [dev-dependencies] and ask for the new version:

[dev-dependencies]
glsql = ">= 3.0.0 and < 4.0.0"

Then regenerate, which is what points the import at out_dir:

gleam run -m glsql
gleam build

Regenerating is not optional. Modules written by 2.x import glsql/schema, so leaving them as they are and only editing gleam.toml gives a build that fails on an application module importing a dev dependency.

Development

gleam test  # Run the tests
gleam run   # Regenerate the modules committed under test/generated

The tests compare what codegen produces against those committed modules, and the build type-checks them. So after changing codegen, regenerate and read the diff.

Further documentation can be found at https://hexdocs.pm/glsql.

Contributing

Found a bug or have a feature request? Open an issue at https://github.com/andbitty/glsql/issues.