glsql

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

```sh
gleam add pog
gleam add --dev glsql
```

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

```gleam
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
}
```

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

```sh
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>.