based/sql
A type-safe SQL query builder for Gleam.
Queries are built using a pipeline API that starts with a table source and chains modifiers to construct SELECT, INSERT, UPDATE, and DELETE statements. Phantom types ensure that only valid modifiers can be applied to each query kind at compile time.
Quick example
import based/sql
let adapter = sql.adapter()
let query =
sql.from(sql.table("users"))
|> sql.select([sql.column("name"), sql.column("email")])
|> sql.where([sql.column("active") |> sql.is_true()])
|> sql.order_by([sql.asc(sql.column("name"))])
|> sql.limit(10)
|> sql.to_query(adapter)
query.sql
// -> "SELECT name, email FROM users WHERE active IS TRUE ORDER BY name ASC LIMIT 10"
query.values
// -> []
Custom adapters
Use adapter() with builder functions to configure how queries are
rendered for a specific database backend. This controls placeholder style,
identifier quoting, and value type mapping.
let mysql_adapter =
sql.adapter()
|> sql.on_null(fn() { mysql.null })
|> sql.on_int(fn(i) { mysql.int(i) })
|> sql.on_text(fn(s) { mysql.text(s) })
|> sql.on_placeholder(fn(_) { "?" })
|> sql.on_value(mysql_value_to_string)
|> sql.on_identifier(fn(name) { "`" <> name <> "`" })
Phantom types
The Builder(kind, v) type uses phantom types to restrict which modifier
functions can be called. For example, join only accepts Builder(Select, v),
and set only accepts Builder(Update, v). This helps callers avoid
building invalid SQL queries. It is possible to call functions that do
not modify the provided Builder. Passing Builder(Insert, v)
to sql.where will not modify the query builder.
Types
Database adapter that controls how queries are serialized.
An adapter defines how placeholders, identifiers, and values are formatted
for a specific database backend. Create one with adapter() and
configure it using the on_* builder functions.
pub opaque type Adapter(v)The main query builder type, parameterized by a phantom kind type
and a value type v.
The phantom type restricts which modifier functions can be applied, preventing some invalid combinations at compile time.
pub opaque type Builder(kind, v)A SQL column reference, optionally qualified by a table name, aliased, or wrapped in an aggregate function.
pub opaque type ColumnA WHERE clause condition.
Conditions are built using constructor functions like eq, gt, like,
is_null, etc. They can be combined with and, or, and not. Raw SQL
conditions are also supported via raw.
pub opaque type Condition(v)The action to take when an INSERT conflict occurs.
DoNothingignores the conflicting row (ON CONFLICT ... DO NOTHING)DoUpdate(sets:)updates specified columns (ON CONFLICT ... DO UPDATE SET ...). Each tuple is#(column_name, raw_sql_expression). Column names are quoted by the adapter; expression strings are emitted verbatim (e.g."excluded.quantity").
pub type ConflictAction {
DoNothing
DoUpdate(sets: List(#(String, String)))
}Constructors
-
DoNothing -
DoUpdate(sets: List(#(String, String)))
A Common Table Expression (CTE) for use with WITH clauses.
Created with cte and optionally refined with cte_columns.
pub opaque type Cte(v)An intermediate builder representing a table source.
Created by from() or from_subquery(), then passed to select() or
delete() to produce a full query builder.
pub opaque type From(a, v)Describes how to convert an input of type a into an internal operand
for query building.
pub opaque type Kind(a, v)Contains a parameterized SQL string and an ordered list of values. Can be
used directly or built from a sql.Builder.
let q = sql.from(sql.table("users"))
|> sql.select([sql.column("name")])
|> sql.to_query(adapter)
q.sql // -> "SELECT name FROM users"
q.values // -> []
pub type Query(v) {
Query(sql: String, values: List(v))
}Constructors
-
Query(sql: String, values: List(v))
A reusable specification for INSERT rows. Defines the column names and how to map inputs to values.
Built using rows and piping through value. The value constructor
functions (e.g. text, int) are provided by your database adapter
package.
let users =
sql.rows([alice, bob])
|> sql.value("name", fn(u) { db.text(u.name) })
|> sql.value("age", fn(u) { db.int(u.age) })
sql.insert(into: sql.table("users"))
|> sql.values(users)
pub opaque type Rows(a, v)Values
pub fn adapter() -> Adapter(v)Creates a new adapter with unconfigured value handlers.
Defaults to "?" placeholders and does not quote identifiers.
The on_value, on_null, on_int, and on_text handlers
will panic if left unconfigured.
Configure the returned adapter using the on_* builder functions
before passing it to to_query or to_string.
pub fn and(
left: Condition(v),
right: Condition(v),
) -> Condition(v)Combines two conditions with AND.
pub fn between(
column: Column,
low: a,
high: a,
of kind: Kind(a, v),
) -> Condition(v)Creates a BETWEEN condition.
pub const col: Kind(Column, v)Kind that treats the input as a column reference for column-to-column
comparisons. Not to be confused with the column() function, which
creates a Column — this constant is a Kind used with the of:
parameter in condition functions like eq, gt, etc.
sql.column("id")
|> sql.eq(sql.column("user_id") |> sql.column_for(orders), of: sql.col)
pub fn column_as(column: Column, alias alias: String) -> ColumnSets an alias on a column. No-ops on star.
pub fn distinct(
builder: Builder(Select, v),
) -> Builder(Select, v)Adds DISTINCT to a SELECT query.
pub fn eq(
column: Column,
input: a,
of kind: Kind(a, v),
) -> Condition(v)Creates an equality condition.
pub fn for_update(
builder: Builder(Select, v),
) -> Builder(Select, v)Adds FOR UPDATE to a SELECT query for row-level locking.
pub fn from(table: Table) -> From(Table, v)Starts building a query from a table. This is the entry point for SELECT and DELETE queries.
Pass into select or delete to choose the query kind.
pub fn from_subquery(
builder: Builder(Select, v),
alias alias: String,
) -> From(Subquery, v)Creates a From that selects from a subquery instead of a table.
pub fn full_join(
builder: Builder(Select, v),
table table: Table,
on on: List(Condition(v)),
) -> Builder(Select, v)Adds a FULL JOIN clause to a SELECT query.
pub fn group_by(
builder: Builder(Select, v),
columns: List(Column),
) -> Builder(Select, v)Adds a GROUP BY clause to a SELECT query.
pub fn gt(
column: Column,
input: a,
of kind: Kind(a, v),
) -> Condition(v)Creates a greater-than condition.
pub fn gt_eq(
column: Column,
input: a,
of kind: Kind(a, v),
) -> Condition(v)Creates a greater-than-or-equal condition.
pub fn having(
builder: Builder(Select, v),
conditions: List(Condition(v)),
) -> Builder(Select, v)Adds a HAVING clause to a SELECT query.
pub fn in(
column: Column,
values: List(a),
of kind: Kind(a, v),
) -> Condition(v)Creates an IN condition.
pub fn inner_join(
builder: Builder(Select, v),
table table: Table,
on on: List(Condition(v)),
) -> Builder(Select, v)Adds an INNER JOIN clause to a SELECT query.
pub fn insert(into tbl: Table) -> Builder(Insert, v)Creates a new INSERT query builder for the given table.
pub fn left_join(
builder: Builder(Select, v),
table table: Table,
on on: List(Condition(v)),
) -> Builder(Select, v)Adds a LEFT JOIN clause to a SELECT query.
pub fn like(
column: Column,
input: a,
of kind: Kind(a, v),
) -> Condition(v)Creates a LIKE condition.
pub fn limit(builder: Builder(a, v), n: Int) -> Builder(a, v)Adds a LIMIT clause. Applies to SELECT and UPDATE queries.
pub fn list(of map: fn(a) -> v) -> Kind(a, v)Creates a kind that maps an arbitrary type to a value using a conversion
function. Useful for in clauses with custom types.
pub fn lt(
column: Column,
input: a,
of kind: Kind(a, v),
) -> Condition(v)Creates a less-than condition.
pub fn lt_eq(
column: Column,
input: a,
of kind: Kind(a, v),
) -> Condition(v)Creates a less-than-or-equal condition.
pub fn not_eq(
column: Column,
input: a,
of kind: Kind(a, v),
) -> Condition(v)Creates an inequality condition.
pub fn not_like(
column: Column,
input: a,
of kind: Kind(a, v),
) -> Condition(v)Creates a NOT LIKE condition.
pub fn offset(builder: Builder(a, v), n: Int) -> Builder(a, v)Adds an OFFSET clause. Applies to SELECT and UPDATE queries.
pub fn on_conflict(
builder: Builder(Insert, v),
target: String,
action: ConflictAction,
wheres: List(Condition(v)),
) -> Builder(Insert, v)Adds an ON CONFLICT clause to an INSERT query (upsert).
pub fn on_identifier(
adapter: Adapter(v),
with handle_identifier: fn(String) -> String,
) -> Adapter(v)Sets the identifier quoting function.
pub fn on_int(
adapter: Adapter(v),
with handle_int: fn(Int) -> v,
) -> Adapter(v)Sets the function that wraps an Int into the value type v.
Adapter packages use this to convert Gleam Int values into
their value type v.
pub fn on_null(
adapter: Adapter(v),
with handle_null: fn() -> v,
) -> Adapter(v)Sets the function that produces the null representation for type v.
pub fn on_placeholder(
adapter: Adapter(v),
with handle_placeholder: fn(Int) -> String,
) -> Adapter(v)Sets the placeholder format function.
pub fn on_text(
adapter: Adapter(v),
with handle_text: fn(String) -> v,
) -> Adapter(v)Sets the function that wraps a String into the value type v.
Adapter packages use this to convert Gleam String values into
their value type v.
pub fn on_value(
adapter: Adapter(v),
with handle_value: fn(v) -> String,
) -> Adapter(v)Sets the function used to render a value as a literal SQL string.
pub fn or(
left: Condition(v),
right: Condition(v),
) -> Condition(v)Combines two conditions with OR.
pub fn order_by(
builder: Builder(a, v),
order_by: List(Order),
) -> Builder(a, v)Sets the ORDER BY clause. Applies to SELECT and UPDATE queries.
pub fn query(sql: String) -> Query(v)Creates a Query from a raw SQL string with no parameters.
pub fn raw(sql: String) -> Condition(v)Creates a raw SQL condition.
Warning: The given SQL string is embedded directly in the query without any escaping or parameterization. Never pass untrusted user input to this function — doing so may expose your application to SQL injection attacks.
pub fn recursive(builder: Builder(a, v)) -> Builder(a, v)Marks the query’s WITH clause as WITH RECURSIVE.
pub fn returning(
builder: Builder(a, v),
columns: List(Column),
) -> Builder(a, v)Adds a RETURNING clause. Applies to INSERT, UPDATE, and DELETE queries.
pub fn right_join(
builder: Builder(Select, v),
table table: Table,
on on: List(Condition(v)),
) -> Builder(Select, v)Adds a RIGHT JOIN clause to a SELECT query.
pub fn rows(values: List(a)) -> Rows(a, v)Creates a new Rows with the given values. Pipe through value to add
columns and extractors.
pub fn select(
from: From(a, v),
columns: List(Column),
) -> Builder(Select, v)Converts a From into a SELECT query with the given columns.
pub fn set(
column: String,
input: a,
of kind: Kind(a, v),
) -> Set(v)Sets a column to a value in an UPDATE query. Can be called multiple times.
pub const subquery: Kind(Builder(Select, v), v)Kind that treats the input as a subquery for scalar comparisons.
pub fn to_query(
builder: Builder(a, v),
adapter: Adapter(v),
) -> Query(v)Serializes a query builder into a Query(v) with parameterized placeholders.
Returns a Query record with .sql containing the SQL string with
placeholders and .values containing the parameter values in order.
pub fn to_string(
builder: Builder(a, v),
adapter: Adapter(v),
) -> StringSerializes a query builder into a plain SQL string with values inlined.
Values are formatted using the adapter’s on_value handler. This is useful
for debugging and logging — use to_query for actual database execution.
pub fn union(
selects: List(Builder(Select, v)),
) -> Builder(Union, v)Combines SELECT queries with UNION.
pub fn union_all(
selects: List(Builder(Select, v)),
) -> Builder(UnionAll, v)Combines SELECT queries with UNION ALL.
pub fn update(
tbl: Table,
sets: List(Set(v)),
) -> Builder(Update, v)Creates a new UPDATE query builder for the given table.
pub fn value(
rows: Rows(a, v),
column: String,
extract: fn(a) -> v,
) -> Rows(a, v)Adds a column to the rows being inserted, with a function to extract the SQL value.
pub fn values(
builder: Builder(Insert, v),
rows: Rows(a, v),
) -> Builder(Insert, v)Sets the rows to be inserted.