Current section
Files
Jump to
Current section
Files
README.md
# glsql
[](https://hex.pm/packages/glsql)
[](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 <https://hexdocs.pm/glsql>.
## Contributing
Found a bug or have a feature request? Open an issue at
<https://github.com/andbitty/glsql/issues>.