based

Database-agnostic types and a composable SQL query builder for Gleam.

based provides a unified interface for interacting with SQL databases. Adapter packages for specific database backends (e.g. PostgreSQL, MariaDB) conform to the types and functions defined here.

gleam add based

Query Builder

Build type-safe SQL queries using based/sql:

import database as db
import based/sql

let query =
  sql.from(sql.table("users"))
  |> sql.select([sql.column("id"), sql.column("name")])
  |> sql.where([sql.column("id") |> sql.eq(db.int(1), of: sql.val)])
  |> sql.to_query(adapter)

// query.sql == "SELECT id, name FROM users WHERE id = ?"
// query.values == [db.Int(1)]

Insert

import based/sql

let inserter =
  sql.rows([#("John", "john@example.com")])
  |> sql.value("name", fn(user) { db.text(user.0) })
  |> sql.value("email", fn(user) { db.text(user.1) })

let query =
  sql.insert(into: sql.table("users"))
  |> sql.values(inserter)
  |> sql.to_query(adapter)

// query.sql == "INSERT INTO users (name, email) VALUES (?, ?)"

Update

import based/sql

let query =
  sql.table("users")
  |> sql.update([sql.set("name", db.text("Jane"), of: sql.val)])
  |> sql.where([sql.column("id") |> sql.eq(db.int(1), of: sql.val)])
  |> sql.to_query(adapter)

// query.sql == "UPDATE users SET name = ? WHERE id = ?"

Delete

import based/sql

let query =
  sql.from(sql.table("users"))
  |> sql.delete()
  |> sql.where([sql.column("id") |> sql.eq(db.int(1), of: sql.val)])
  |> sql.to_query(adapter)

// query.sql == "DELETE FROM users WHERE id = ?"

Running Queries

Use based.query, based.all, or based.one with a configured Db from an adapter package:

import based
import gleam/dynamic/decode

let user_decoder = {
  use id <- decode.field("id", decode.int)
  use name <- decode.field("name", decode.string)

  decode.success(User(id:, name:))
}

// `database` is provided by an adapter package
let assert Ok(users) = based.all(query, database, user_decoder)

Custom Adapters

Adapter packages configure an sql.Adapter to control placeholder formatting, identifier escaping, and value type mapping:

import based/sql
import gleam/int

let mysql_adapter =
  sql.adapter()
  |> sql.on_placeholder(with: fn(_) { "?" })
  |> sql.on_identifier(with: fn(name) { "`" <> name <> "`" })
  |> sql.on_value(with: my_value_to_string)
  |> sql.on_null(with: fn() { MyNull })
  |> sql.on_int(with: fn(i) { MyInt(i) })
  |> sql.on_text(with: fn(s) { MyText(s) })

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

Development

gleam test  # Run the tests