# glsql [![Package Version](https://img.shields.io/hexpm/v/glsql)](https://hex.pm/packages/glsql) [![Hex Docs](https://img.shields.io/badge/hex-docs-ffaff3)](https://hexdocs.pm/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`](https://hex.pm/packages/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`: ```sh 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: ```sh 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: ```sh gleam add gleam_erlang gleam_otp ``` ## Quick start Write a schema: ```sql -- 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: ```toml # glsql.toml schema = "priv/schema.sql" out_dir = "src/db" driver = "pog" ``` Generate: ```sh gleam run -m glsql ``` This writes `src/db/users.gleam`: ```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](#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: ```gleam 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: ```gleam 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: ```toml 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: ```toml [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: ```toml [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: ```toml [dev-dependencies] glsql = ">= 3.0.0 and < 4.0.0" ``` Then regenerate, which is what points the import at `out_dir`: ```sh 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 ```sh 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 . ## Contributing Found a bug or have a feature request? Open an issue at .