Pure SQL string builders.
Every builder returns {sql, params} where params is the ordered list of
values bound to $1..$n. Nothing in this module touches the database, which
keeps the SQL surface exhaustively unit-testable.
Field descriptors
Write builders take field descriptors — maps of the shape:
%{column: "age", value: "42", cast: "integer"}:value is the raw parameter sent to Postgrex (a string, or nil for SQL
NULL). :cast is an optional Postgres type expression; when present the
placeholder becomes $n::text::<cast> so Postgrex sends the string verbatim
and Postgres parses it into the column's type. When :cast is nil the
placeholder is bare (textual columns infer a text parameter naturally).
Summary
Types
A column spec for DDL builders. :name and :type are required; :nullable
defaults to true and :primary_key to false.
Functions
Builds an ALTER TABLE ... ADD COLUMN.
Builds an ALTER TABLE ... ALTER COLUMN ... TYPE ....
Builds a SELECT COUNT(*) honoring the same optional WHERE fragment.
Builds a CREATE INDEX statement.
Builds a CREATE TABLE.
Builds a DELETE for one or more rows, each identified by its PK fields.
Builds an ALTER TABLE ... DROP COLUMN.
Builds an ALTER TABLE ... DROP CONSTRAINT statement.
Builds a DROP INDEX statement.
Builds a DROP TABLE. No CASCADE — a table with dependents errors loudly.
Builds an INSERT ... RETURNING *.
Quotes a Postgres identifier, escaping embedded double-quotes.
Quotes a schema-qualified table identifier.
Builds an ALTER TABLE ... RENAME COLUMN.
Builds an ALTER TABLE ... RENAME TO.
Builds a paginated SELECT *.
Builds an ALTER TABLE ... ALTER COLUMN ... SET/DROP NOT NULL.
Builds an UPDATE ... WHERE <pk match> RETURNING *.
Validates a column type against the allowlist, returning the normalized
(trimmed, downcased, single-spaced) form or :error.
Types
Functions
@spec add_column(String.t(), column()) :: {:ok, {String.t(), []}} | {:error, :missing_name | {:invalid_type, String.t()}}
Builds an ALTER TABLE ... ADD COLUMN.
column is a single column spec (see column/0). Returns the same
validation errors as create_table/2.
@spec alter_column_type(String.t(), String.t(), String.t()) :: {:ok, {String.t(), []}} | {:error, {:invalid_type, String.t()}}
Builds an ALTER TABLE ... ALTER COLUMN ... TYPE ....
Builds a SELECT COUNT(*) honoring the same optional WHERE fragment.
Builds a CREATE INDEX statement.
@spec create_table(String.t(), [column()]) :: {:ok, {String.t(), []}} | {:error, :no_columns | :missing_name | {:invalid_type, String.t()}}
Builds a CREATE TABLE.
columns is a list of column specs (see column/0). Columns flagged
primary_key: true are collected into a single PRIMARY KEY (...) table
constraint, so composite keys work. Identifiers are quoted and every type is
validated against the allowlist.
Returns {:error, :no_columns} when columns is empty,
{:error, :missing_name} when a spec lacks a usable name, or
{:error, {:invalid_type, type}} for a disallowed type.
@spec delete(String.t(), [[field()]]) :: {:ok, {String.t(), [term()]}} | {:error, :no_rows | :no_key}
Builds a DELETE for one or more rows, each identified by its PK fields.
rows is a list of pk-field lists. Rows are matched with OR'd, parenthesized
AND groups. Returns {:error, :no_rows} or {:error, :no_key}.
Builds an ALTER TABLE ... DROP COLUMN.
Builds an ALTER TABLE ... DROP CONSTRAINT statement.
Builds a DROP INDEX statement.
Builds a DROP TABLE. No CASCADE — a table with dependents errors loudly.
Builds an INSERT ... RETURNING *.
An empty field list emits INSERT INTO t DEFAULT VALUES RETURNING *, so a
blank submission against a table whose columns all have defaults still works.
Quotes a Postgres identifier, escaping embedded double-quotes.
Quotes a schema-qualified table identifier.
Builds an ALTER TABLE ... RENAME COLUMN.
Builds an ALTER TABLE ... RENAME TO.
@spec select( String.t(), String.t() | nil, String.t() | nil, :asc | :desc, non_neg_integer(), non_neg_integer() ) :: {String.t(), []}
Builds a paginated SELECT *.
where_clause is a raw fragment (without WHERE) or nil. sort_col is a
validated column name or nil. sort_dir is :asc or :desc.
@spec select( String.t(), String.t(), String.t() | nil, String.t() | nil, :asc | :desc, non_neg_integer(), non_neg_integer() ) :: {String.t(), []}
Builds an ALTER TABLE ... ALTER COLUMN ... SET/DROP NOT NULL.
@spec update(String.t(), [field()], [field()]) :: {:ok, {String.t(), [term()]}} | {:error, :no_fields | :no_key}
Builds an UPDATE ... WHERE <pk match> RETURNING *.
fields are the columns to set; pk_fields identify the single row. Both are
field descriptors. Returns {:error, :no_fields} or {:error, :no_key}.
Validates a column type against the allowlist, returning the normalized
(trimmed, downcased, single-spaced) form or :error.
Accepts bare types ("bigint", "text") and parameterized forms
("varchar(255)", "numeric(10,2)") whose base type is allowlisted.