sql.crud

import sql.crud

sql lifts part of this module out to its own top level; each name below is shown with the path that reaches it. Anything still spelled sql.crud.* needs import sql.crud.

Building the four statements that are the same everywhere.

Inserting a row, updating rows that match, deleting rows that match and selecting rows that match are written the same way against every engine, apart from how identifiers are quoted and how placeholders are spelled. Both of those come from the driver, so these builders produce correct SQL for whichever adapter is active.

This is not a query builder and is not trying to become one. There is no join here, no subquery, no expression tree. Anything past a flat list of equality where is written as SQL, which is a better language for it than any chain of method calls.

Every value becomes a bound parameter. Nothing built here interpolates a value into the statement text, which is what makes these safe to hand user input.

Functions

raw()

sql.raw(sql: string) -> Raw

Builds a Raw.

Parameters

• sql (string)

Returns Raw

quote_table()

sql.crud.quote_table(driver, table: string) -> string

Renders a table name, which may carry a schema.

'public.users' becomes "public"."users", and a name with a dot that is genuinely part of it can be quoted by passing it already quoted.

Parameters

• driver (Driver)
• table (string)

Returns string

where()

sql.crud.where(driver, filter, start: number) -> dict

Builds the WHERE clause for a dictionary of conditions.

A value of nil becomes IS NULL rather than = NULL, which no row ever satisfies. A list becomes IN (...), and an empty list becomes a condition no row satisfies, which is what “in nothing” means. A Raw is used as written.

Parameters

• driver (Driver)
• filter (dict|nil)
• start (number) — The binding position the first parameter takes.

Returns dict — { clause, values }, the clause empty when there are no conditions.

insert()

sql.crud.insert(driver, table: string, values: dict, returning) -> dict

Builds an INSERT.

Parameters

• driver (Driver)
• table (string)
• values (dict)
• returning (string|nil) — A column to return, for engines that report an inserted id that way rather than through a last insert id. Ignored where the driver does not support RETURNING.

Returns dict — { sql, values }

Raises QueryError if values is empty.

insert_many()

sql.crud.insert_many(driver, table: string, rows: list) -> dict

Builds an INSERT carrying several rows in one statement.

Every row has to name the same columns, in any order; a row that names a different set raises rather than being padded with nulls, because a missing column and a null column mean different things.

Parameters

• driver (Driver)
• table (string)
• rows (list) — Dictionaries of column to value.

Returns dict — { sql, values }

Raises QueryError if rows is empty, a row has no columns, or the rows disagree about which columns they have.

update()

sql.crud.update(driver, table: string, values: dict, filter) -> dict

Builds an UPDATE.

An update with no where changes every row, which is occasionally what someone means and usually not, so it has to be asked for by passing nil rather than happening by default when a condition dictionary turns out empty.

Parameters

• driver (Driver)
• table (string)
• values (dict)
• filter (dict|nil)

Returns dict — { sql, values }

Raises QueryError if values is empty.

delete()

sql.crud.delete(driver, table: string, filter) -> dict

Builds a DELETE.

Parameters

• driver (Driver)
• table (string)
• filter (dict|nil)

Returns dict — { sql, values }

select()

sql.crud.select(driver, table: string, filter, options) -> dict

Builds a SELECT.

Parameters

• driver (Driver)
• table (string)
• filter (dict|nil)
• options (dict|nil) — columns (a list of names, all of them by default), order (a list of names, each optionally followed by desc), limit and offset.

Returns dict — { sql, values }

Classes

Raw

class sql.Raw

Marks a fragment of SQL to be used as written rather than bound.

The escape hatch for the cases where a value is not a value:

db.update('posts', { views: sql.raw('views + 1') }, { id: 7 })

It is exactly as dangerous as it sounds. A Raw built from anything a user supplied is a SQL injection, so build them from literals and keep values in the parameters where they belong.

• printable — has a @to_string(), so echo and print() show something useful

Fields

Constructor

sql.Raw(sql)

Parameters

• sql (string)

Raw.to_string()

sql.Raw.to_string()

2026, Richard Ore and Zuri contributors