README- UAsSource
- UColumn
- UColumnDiff
- UColumnType
- UDelete
- UDialect
- UField
- UInsert
- UIsolationLevel
- ULockClause
- ULockMode
- ULostUpdate
- UManyToMany
- UMigration
- UMigrator
- UModel
- UNulls
- UOrder
- UPredicate
- URelation
- URowStream
- USavepoint
- USelect
- USession
- USource
- UTable
- UTxOptions
- UUpdate
- UValue
- Vbelongs_to
- Vcase_
- Vcast
- Vcolumn
- Vdelete
- Vdiff_schema
- Vfield
- Vfunc
- Vhas_many
- Vindex_ddl
- Vinsert
- Vmany_to_many
- Vreflect_columns
- Vreflect_columns_mysql
- Vreflect_columns_pg
- Vreflect_table
- Vreflect_table_for
- Vselect
- Vupdate
moonbitstack/moonorm/query does not have a README file
Value
The bound-value type is moondb's dialect-neutral [@moondb.Value], re-exported
here so the builder's public surface reads as Value (and its constructors —
Int, Text, Null, …) stay unqualified) while the actual type is the one every
moondb driver speaks. Values are always carried out-of-band as parameters, never
interpolated into the SQL string — that is what makes the builder injection-safe.
AsSource
Assign
type AssignWhat one SET assigns: a bound value (col = ?), or an expression rendered
verbatim with its own placeholders, which is the only way to write a column in
terms of itself (n = n + ?).
Conflict
type ConflictThe ON CONFLICT / ON DUPLICATE KEY clause of an upsert: the conflict-target
columns (the unique key that may collide — used by SQLite/PostgreSQL, implicit on
MySQL), the DO UPDATE assignments, and whether the intent is DO NOTHING.
Cte
type CteA common table expression: name AS (subquery). CTEs render in a single leading
WITH clause and their bound parameters come first in the final params list,
matching their leading position in the SQL text. When recursive, the whole
clause is a WITH RECURSIVE (the keyword modifies the clause, not the entry, so
one recursive CTE turns the leading WITH recursive for all of them).
Delete
A DELETE statement builder. Generative like Select, and it shares the same
WHERE clause.
Delete::build
Render (sql, params) for the SQLite/PostgreSQL dialect.
Delete::build_for
Render (sql, params) for dialect. Identical everywhere but RETURNING,
which MySQL does not have and so does not get.
Delete::returning
Return col from each deleted row (SQLite 3.35+, PostgreSQL) — the row's
contents survive the delete only if you ask for them here. Repeatable;
build_for(Mysql) drops it.
Delete::returning_all
Return every column of each deleted row (RETURNING *). See returning.
Delete::where_
Narrow the delete with col op ?. Conditions are ANDed together.
Delete::where_in
Narrow the delete with col IN (subquery) — "delete the rows some other query
picks out". The subquery's bound values splice in at the predicate's position.
Delete::where_not_in
Narrow the delete with col NOT IN (subquery). See where_in.
Dialect
The SQL dialect a statement renders for. Most of the builder is dialect-neutral
— it emits ? placeholders and standard clauses that every backend accepts —
but a few constructs genuinely differ: upsert is ON CONFLICT ... DO UPDATE on
SQLite and PostgreSQL versus ON DUPLICATE KEY UPDATE on MySQL, and RETURNING
exists on SQLite and PostgreSQL but not MySQL. build_for takes a Dialect so
those render correctly; the no-argument build renders the SQLite/PostgreSQL
form, which is also what the ?-placeholder default targets.
Insert
An INSERT statement builder, with optional upsert (ON CONFLICT) and
RETURNING support. Generative like Select.
Insert::build
Render to (sql, params) for the SQLite/PostgreSQL dialect. Equivalent to
build_for(Sqlite); kept as the no-argument default because plain inserts and
the ?-placeholder convention target this form.
Insert::build_for
Render to (sql, params) for dialect, with ? placeholders for every bound
value. Bound values appear in params in SQL-text order: the inserted VALUES
first, then any DO UPDATE SET col = ? values. Upsert renders as ON CONFLICT
on SQLite/PostgreSQL and ON DUPLICATE KEY UPDATE on MySQL; RETURNING is
emitted on SQLite/PostgreSQL and omitted on MySQL.
Insert::do_nothing
On a conflict, keep the existing row and change nothing (ON CONFLICT DONOTHING). MySQL has no such form, so build_for(Mysql) renders a no-op
self-assignment instead.
Insert::do_update
On a conflict, set col to a bound value (col = ?). The value binds as a
parameter after the inserted values, so an upsert stays injection-safe.
Insert::do_update_excluded
On a conflict, set col to the value the failed insert tried to write
(excluded.col on SQLite/PostgreSQL, VALUES(col) on MySQL). This is the
"overwrite with the incoming row" upsert.
Insert::on_conflict
Turn this INSERT into an upsert keyed on targets — the columns of the unique
index that may collide (SQLAlchemy's index_elements). Chain do_update /
do_update_excluded to say what to change on a collision, or leave it and call
do_nothing. SQLite and PostgreSQL name the conflict target explicitly;
build_for(Mysql) ignores it (MySQL infers the key), so pass it regardless and
the right dialect uses it.
Insert::returning
Return col from each inserted (or upserted) row. On SQLite and PostgreSQL this
appends RETURNING, so a caller reads the server-assigned id or a
default/trigger-computed value back in the same round trip instead of a second
SELECT. Repeatable. build_for(Mysql) drops it — MySQL has no RETURNING.
Insert::returning_all
Return every column of each inserted row (RETURNING *). See returning.
Insert::set
Set a column to a value.
Insert::values
Append another row to a multi-row INSERT, in the same column order the first row
established via set (SQLAlchemy's multi-values / executemany). Every value
binds as its own placeholder, so a bulk insert stays injection-safe; each row
must supply one value per column.
JoinClause
type JoinClauseA single JOIN clause: its keyword (JOIN / LEFT JOIN / …), the joined source,
and either a raw ON predicate (an identifier-level expression, never a bound
value) or a USING column list. A CROSS JOIN carries neither.
LockClause
A row-locking clause on a SELECT (SQLAlchemy with_for_update): the lock
mode, the tables it is restricted to (OF …, empty = all), and whether it
fails rather than waits (NOWAIT) or skips locked rows (SKIP LOCKED).
LockMode
Nulls
Where NULLs sort in an ORDER BY term (SQLAlchemy's nulls_first() /
nulls_last()). Auto emits nothing and leaves the placement to the server,
which differs: PostgreSQL sorts NULLs last ascending, SQLite first. First /
Last say it explicitly; MySQL has no such syntax and rejects it.
Order
Predicate
A boolean predicate tree — the parameter-safe way to express arbitrary
AND/OR/NOT groupings (SQLAlchemy's and_() / or_() / not_()), which the
flat where_/eq API (always joined by AND) cannot. Every comparison binds its
value as a placeholder, so a (a = ? OR b = ?) filter stays fully parameterised.
Attach one to a query with Select::where_pred.
Select
pub struct Select {
from_ : Source
ctes : Array[Cte]
cols : Array[String]
joins : Array[JoinClause]
filter : Where
groups : Array[String]
havings : Array[Cond]
orders : Array[OrderTerm]
set_ops : Array[(String, Select)]
distinct_on_ : Array[String]
distinct_ : Bool
limit_ : Int?
offset_ : Int?
lock_ : LockClause?
}A SELECT statement builder. Every method is generative — it returns a new
statement and leaves the receiver alone — so a half-built query is a safe base to
branch from, which is what SQLAlchemy's statement API guarantees.
Select::build
Render to (sql, params), with ? placeholders for every bound value. Bound
values appear in the params list in the same left-to-right order they occur in
the SQL text: CTE subqueries first (the leading WITH), then any derived table
in FROM and the joins, then the WHERE predicates, then HAVING, then the
set-operation operands.
Select::column
Add a projected column (no columns selects *).
Select::count
Project COUNT(*) — the most common aggregate. Sugar for raw("COUNT(*)").
Select::cross_join
Add a CROSS JOIN src — the cartesian product, which takes no ON. Paired with
a Lateral source this is how a row-generating function is joined per row.
Select::distinct
Deduplicate the result set: SELECT DISTINCT. Applies to this query's own
projection (each operand of a set operation carries its own DISTINCT).
Select::distinct_on
Keep the first row of each distinct cols group: SELECT DISTINCT ON (a, b),
PostgreSQL's extension. Which row is "first" is whatever ORDER BY says, so pair
it with an order_by that leads with the same columns. Takes precedence over
plain distinct when both are set; no other dialect accepts it.
Select::eq
Shorthand for where_(col, "=", val).
Select::except_
EXCEPT — rows in this query but not in other (SQL's set difference).
Select::for_update
Take a row lock on the selected rows (SQLAlchemy Select.with_for_update): FOR UPDATE by
default, or FOR SHARE when read; restrict it to certain tables with of; make it fail
immediately on a locked row with nowait, or skip locked rows with skip_locked. The clause is
emitted after ORDER BY / LIMIT / OFFSET, the standard SQL position (SQLite, which has no
row locks, ignores it).
Select::full_join
Add a FULL JOIN src ON <on> — every row of both sides. See join. MySQL has
no FULL JOIN; SQLite gained it in 3.39.
Select::group_by
Add a GROUP BY column (repeatable; columns are emitted in call order).
Select::having
Add a col op ? predicate to the HAVING clause (ANDed with the rest). The
value is bound as a ? placeholder exactly like where_, so aggregate
filters stay injection-safe.
Select::intersect
INTERSECT — rows present in both queries.
Select::join
Add an inner JOIN src ON <on>. src is a table name or a Source (a derived
table brings its bound values with it). The on predicate is rendered verbatim
(it references columns, not bound values), so pass only trusted identifiers.
Select::join_using
Add a JOIN src USING (a, b) — the equi-join on columns of the same name in
both tables, which also collapses each pair into one output column. cols are
identifiers, rendered verbatim.
Select::label
Project an expression under a name: expr AS name (SQLAlchemy's .label()).
The name is what the result row is keyed by, so it is what from_row reads.
Select::left_join
Add a LEFT JOIN src ON <on> — every left row, matched or not. See join.
Select::offset
Skip the first n rows. Paired with limit this is a page.
Select::order_by
Order the result by a column. Repeated calls order by each in turn. nulls
places NULLs explicitly (NULLS FIRST / NULLS LAST); the default leaves it to
the server, which is the only portable choice — MySQL rejects the syntax.
Select::raw
Add a raw column expression (e.g. an aggregate like "SUM(price)" or a
qualified "users.id"). Identical machinery to column; named for intent so
call sites read as "this is an expression, not a plain column name".
Select::right_join
Add a RIGHT JOIN src ON <on> — every right row, matched or not. See join.
SQLite gained it in 3.39; older versions need the join written the other way.
Select::union
UNION this query with other — the combined rows with duplicates removed. A
trailing ORDER BY / LIMIT on this query binds to the whole compound (standard
SQL); one on other makes it a derived table so it keeps binding to other.
Select::union_all
UNION ALL — the combined rows keeping duplicates.
Select::where_
Add a col op ? predicate (ANDed with the rest).
Select::where_exists
Add an EXISTS (subquery) predicate (ANDed with the rest) — true when the
subquery returns any row (SQLAlchemy's exists()). The subquery's bound values
splice in at its position, so it stays injection-safe.
Select::where_in
Add a col IN (subquery) predicate (ANDed with the rest). The subquery's bound
values splice in at the predicate's position, so a correlated or filtering
subquery stays injection-safe exactly like a scalar where_.
Select::where_in_values
Add a col IN (?, ?, …) predicate over an explicit list of values (ANDed with
the rest). Every value binds as its own placeholder, never spliced — this is the
set-membership filter a batch load issues to fetch the related rows of many
parents in one round trip (WHERE fk IN (key1, key2, …)) instead of one query
per parent. An empty vals renders the always-false 1 = 0 — "in the empty set"
matches nothing — so a batch load with no keys returns no rows rather than erroring.
Select::where_not_exists
Add a NOT EXISTS (subquery) predicate (ANDed with the rest). See where_exists.
Select::where_not_in
Add a col NOT IN (subquery) predicate (ANDed with the rest). See where_in.
Select::where_not_in_values
Add a col NOT IN (?, ?, …) predicate over an explicit list of values (ANDed
with the rest). See where_in_values; an empty vals renders the always-true
1 = 1 — "not in the empty set" matches every row.
Select::where_pred
Add a boolean Predicate tree to the WHERE — the parameter-safe AND/OR/NOT
grouping the flat where_/eq cannot express, e.g.
where_pred(Or([Cmp(col="a", op="=", val=Int(1)), Cmp(col="b", op="=", val=Int(2))])).
Predicates are ANDed with the other clauses, so mixing eq(...) and where_pred(...)
composes as one conjunction.
Select::window
Project a window function: <expr> OVER (PARTITION BY … ORDER BY …), optionally
aliased. Window functions compute over a frame of rows without collapsing them
(ROW_NUMBER(), RANK(), a running SUM(x)), so this adds one more projected
column rather than grouping. expr is the function call ("ROW_NUMBER()",
"SUM(amount)"), partition_by splits the rows into independent windows, and
order_by ranks within each. All three are identifier-level SQL (they name
columns and functions, never bound values), so they render verbatim like raw
and carry no parameters. An empty partition_by and order_by yields the whole
result as one unordered window, <expr> OVER ().
Select::with_cte
Attach a common table expression: WITH name AS (sub). Repeatable — several
CTEs render comma-separated in one leading WITH. The subquery is parameterised
like any other statement; its bound values lead the final params list because
the WITH clause leads the SQL text. name is a trusted identifier declared in
code, never a bound value.
Select::with_recursive
Attach a recursive common table expression (SQLAlchemy's select(...).cte(recursive=True)):
WITH RECURSIVE name AS (anchor UNION [ALL] step). anchor is the base term and step
the term that refers back to name in its own FROM — the CTE name is a trusted identifier
referenceable in step and in this query's own FROM, so the usual shape is
select(name).with_recursive(name, anchor, step). all picks UNION ALL (keep duplicates,
the common tree/graph walk) over the default UNION. The two terms compose through the existing
union / union_all builders, so their bound values thread through in text order like any CTE.
Source
What a FROM or a JOIN names. A bare table is Tbl; Sub is a derived table
(a whole Select, whose bound values thread into the enclosing statement's
params at the position the subquery renders); Alias names either of those, which
PostgreSQL and MySQL require of a derived table; Lateral lets a derived table
reference the columns of the sources to its left (PostgreSQL and MySQL 8.0.14+;
SQLite has no LATERAL).
Table
A lightweight table descriptor: a table name plus its known columns. It is
the hand-written stand-in for SQLAlchemy's Table(...) metadata object —
MoonBit has no reflection, so the schema is declared explicitly rather than
introspected. select() turns it into a Select with every column projected.
Update
An UPDATE statement builder. Generative like Select, and it shares the same
WHERE clause, so where_pred / where_in narrow an update exactly as they
narrow a query.
Update::build
Render (sql, params) for the SQLite/PostgreSQL dialect. The SET values come
first and the WHERE values after, in the order the placeholders appear, which is
the order every driver binds in.
Update::build_for
Render (sql, params) for dialect. Identical everywhere but RETURNING,
which MySQL does not have and so does not get.
Update::returning
Return col from each updated row (SQLite 3.35+, PostgreSQL). Repeatable;
build_for(Mysql) drops it.
Update::returning_all
Return every column of each updated row (RETURNING *). See returning.
Update::set
Assign one column a bound value. Repeated calls set each, in the order given.
Update::set_expr
Assign one column an expression instead of a value — set_expr("n", "n + ?",vals=[Int(1)]) renders SET n = n + ?. The expression is rendered verbatim, so
it can read the row's current columns, which a bound value cannot; anything that
varies belongs in vals, one per ?, and binds in the order the placeholders
appear.
Update::where_
Narrow the update with col op ?. Conditions are ANDed together.
Update::where_in
Narrow the update with col IN (subquery) — "update the rows some other query
picks out". The subquery's bound values splice in at the predicate's position.
Update::where_not_in
Narrow the update with col NOT IN (subquery). See where_in.
Where
type WhereThe WHERE clause of any statement — SELECT, UPDATE and DELETE share it, so the
three render identical SQL and bind their values in one order rather than three.
case_
A searched CASE expression: each (condition, result) pair becomes one
WHEN … THEN …, and a non-empty else_ the trailing ELSE. Identifier-level
like func. With no branches there is nothing to test, so it folds to else_
(or NULL), which is what an all-branchless CASE would evaluate to anyway.
cast
fn cast(expr : String, ty : String) -> StringA type conversion: cast("price", "NUMERIC(10,2)") renders
CAST(price AS NUMERIC(10,2)). Identifier-level like func.
func
A function call as a column expression: func("coalesce", ["a", "b"]) renders
coalesce(a, b). This is SQLAlchemy's func.* without the attribute magic — the
arguments are identifier-level SQL, rendered verbatim like Select::raw, so bind
a value by placing a ? and passing it through the surrounding clause.
insert
Start an INSERT into table. Values are bound, never spliced, so a column
holding a quote or a semicolon is a value and not syntax.
is_ident
fn is_ident(s : String) -> BoolWhether s is a bare SQL identifier: a letter or _, then letters, digits or _.
Identifiers cannot be bound as parameters, so a name that is interpolated into SQL
(a table, a savepoint) is checked with this first; anything else is refused rather
than quoted, which keeps the rule the same on every dialect.
select
Start a SELECT over src — a table name, or a Source for a derived table,
an alias or a LATERAL.
Install
Download zipmoonorm — an ORM / SQL toolkit for MoonBit (← SQLAlchemy / SQLModel): a parameterized, injection-safe query builder (select / insert / update / delete).
sqlalchemyormsqlquery-builderdatabasemoonbit
Apache-2.0
Updated 2 days ago
94 downloads