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. glsql itself is only needed at build time, so it goes under dev-dependencies:

gleam add pog
gleam add --dev glsql

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 gleam/dynamic/decode
import gleam/option.{type Option}
import glsql/schema.{type Column, Column}
import pog

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

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, decode.string)
  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.text(row.created_at),
  ]
}

Use the generated module with pog to query the table:

import db/users
import gleam/list
import gleam/option
import pog

pub fn main() {
  let config =
    pog.default_config("main")
    |> pog.host("localhost")
    |> pog.database("my_app")
  let assert Ok(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.pool)

  // 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: "…",
    )
  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.pool)

  rows
}

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. Built-in Postgres types already cover
# text, varchar, int2/4/8, serial, bool, numeric, float4/8, date,
# timestamp, timestamptz, json, jsonb, and bytea.
[types.timestamptz]
gleam_type = "gleam/time/timestamp.Timestamp"
decoder = "ts.decoder()"
encoder = "pog.text(ts.to_rfc3339($))"

# Rename a column whose name collides with a Gleam reserved word.
[rename]
"users.type" = "kind"

# 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.

Development

gleam run   # Run the project
gleam test  # Run the tests

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.