Databases
sql.model.query
import sql.model.query
Zuri
sqllifts part of this module out to its own top level; each name below is shown with the path that reaches it. Anything still spelledsql.model.query.*needsimport sql.model.query.
Reads and writes a model's rows as records, by conditions.
db.model(Post) starts a query on a connection or a transaction. Every
method that narrows the query returns a new query, and leaves the one it
was called on unchanged, so you can keep a query and build on it in more
than one direction:
var published = db.model(Post).where({ published: true })
var recent = published.order('created_at desc').limit(10).all()
var count = published.count()
ZuriConditions
where() with a dictionary works like the conditions of the CRUD
helpers: a value matches itself, nil matches null, a list matches any
of its values, and an empty list matches nothing. Anything else is a
fragment of SQL, with a ? for each value:
db.model(Post).where('views > ? or featured = ?', [100, true])
ZuriEvery condition must be true. A fragment is put in parentheses when it's
added, so an or inside it stays inside it.
Associations
include() loads associations together with the records, with one query
per association instead of one per record. A dotted name goes further:
include('comments.author') loads the comments of every post and the
author of every comment in three queries, however many there are.
Classes
Query
class sql.model.Query
ZuriA query over one model's rows.
- printable — has a
@to_string(), soechoandprint()show something useful - iterable — can be walked with
foranditer
Query.definition()
sql.model.Query.definition() -> Definition
ZuriThe definition of the model queried.
Returns Definition
Query.where()
sql.model.Query.where(filter, values) -> Query
ZuriNarrows the query to rows meeting conditions.
Parameters
filter(dict|string) — Conditions as a dictionary of column to value, or a fragment of SQL.values(list|nil) — For a fragment, the values its?stand for.
Returns Query
Query.where_not()
sql.model.Query.where_not(filter: dict) -> Query
ZuriNarrows the query to the rows that don't meet the conditions. A row
whose column is null isn't equal to any value, so a value excludes it
too. nil excludes null itself.
Parameters
filter(dict) — Column names mapped to values, aswhere()reads them.
Returns Query
Query.order()
sql.model.Query.order(terms) -> Query
ZuriOrders the rows, after any order already given.
Parameters
terms(string|list) — Each a column, optionally followed byascordesc, or asql.Raw.
Returns Query
Query.limit()
sql.model.Query.limit(count: number) -> Query
ZuriReturns at most count rows.
Parameters
count(number)
Returns Query
Query.offset()
sql.model.Query.offset(count: number) -> Query
ZuriSkips the first count rows.
Parameters
count(number)
Returns Query
Query.scope()
sql.model.Query.scope(name: string, argument) -> Query
ZuriApplies a scope the model declared.
Parameters
name(string)argument(any) — Handed to the scope.
Returns Query
Raises ModelError if the model declares no such scope.
Query.include()
sql.model.Query.include(names) -> Query
ZuriLoads associations along with the records.
Parameters
names(string|list[string]) — Association names, dotted to reach the associations of what they load.
Returns Query
Query.all()
sql.model.Query.all() -> list[Model]
ZuriEvery matching record.
Returns list[Model]
Query.first()
sql.model.Query.first() -> Model|nil
ZuriThe first matching record, or nil. Ordered by the primary key when no order was given, so it is the same one every time.
Returns Model|nil
Query.find()
sql.model.Query.find(key) -> Model
ZuriThe record with primary key key.
Parameters
key(any)
Returns Model
Raises RecordNotFound if there is none.
Query.find_by()
sql.model.Query.find_by(filter: dict) -> Model|nil
ZuriThe first record meeting conditions, or nil.
Parameters
filter(dict)
Returns Model|nil
Query.exists()
sql.model.Query.exists() -> bool
ZuriWhether any row matches.
Returns bool
Query.count()
sql.model.Query.count() -> number
ZuriHow many rows match. Order, limit and offset do not change it.
Returns number
Query.pluck()
sql.model.Query.pluck(column: string) -> list
ZuriOne column of every matching row, cast as the model casts it.
Parameters
column(string)
Returns list
Query.sum()
sql.model.Query.sum(column: string) -> number|Decimal|nil
ZuriThe total of a column over the matching rows, as the engine reports it:
a number, or a sql.Decimal for an exact column on PostgreSQL and
MySQL. Nil when no row matches.
Parameters
column(string)
Returns number|Decimal|nil
Query.min()
sql.model.Query.min(column: string) -> any
ZuriThe smallest value of a column over the matching rows, or nil.
Parameters
column(string)
Returns any
Query.max()
sql.model.Query.max(column: string) -> any
ZuriThe largest value of a column over the matching rows, or nil.
Parameters
column(string)
Returns any
Query.average()
sql.model.Query.average(column: string) -> number|Decimal|nil
ZuriThe mean of a column over the matching rows, as the engine reports it, or nil.
Parameters
column(string)
Returns number|Decimal|nil
Query.page()
sql.model.Query.page(number: number, size: number) -> dict
ZuriOne page of the matching records, and where it sits among them.
var page = db.model(Post).order('created_at desc').page(2, 20)
for post in page.records {
echo post.title
}
echo 'page ${page.page} of ${page.pages}'
ZuriParameters
number(number) — Which page, counting from 1.size(number) — How many records to a page.
Returns dict — { records, total, page, size, pages }, total
the matching records in all and pages how many pages they fill, at
least 1.
Query.stream()
sql.model.Query.stream() -> RecordCursor
ZuriThe matching records read one at a time, for more than fit in memory at once. Associations are not loaded this way.
for post in db.model(Post).stream() {
index(post)
}
ZuriReturns RecordCursor
Raises ModelError if the query includes associations.
Query.create()
sql.model.Query.create(attributes: dict) -> Model
ZuriCreates a record from attributes and saves it.
Parameters
attributes(dict) — Assigned asfill()assigns them.
Returns Model
Raises RecordInvalid if it fails validation.
Query.update()
sql.model.Query.update(values: dict) -> number
ZuriUpdates every matching row in one statement, without building the
records: no validation runs and no hook is called. The model's updated
timestamp is set too, unless values sets it.
Parameters
values(dict) — Column to value, asql.Rawwritten as given.
Returns number — How many rows changed.
Query.delete()
sql.model.Query.delete() -> number
ZuriDeletes every matching row in one statement, without building the records: no hook is called. With no conditions, that is every row.
Returns number — How many rows went.
Query.from_sql()
sql.model.Query.from_sql(sql: string, params) -> list[Model]
ZuriRecords built from a statement of one's own, for what conditions cannot say: a join, a union, a window. The statement selects the model's columns by their names.
db.model(Post).from_sql('
select posts.* from posts
join authors on authors.id = posts.author_id
where authors.name = ?
', ['Ada'])
ZuriParameters
sql(string)params(list|dict|nil)
Returns list[Model]
Query.load()
sql.model.Query.load(records: list, names) -> list[Model]
ZuriLoads associations onto records already in hand, one query per association for all of them.
Parameters
records(list[Model]) — Records of this model.names(string|list[string])
Returns list[Model] — The same records.
Query.to_sql()
sql.model.Query.to_sql() -> dict
ZuriThe statement all() runs, and the values it binds.
Returns dict — { sql, values }
Query.to_string()
sql.model.Query.to_string()
ZuriRecordCursor
class sql.model.RecordCursor
ZuriRecords read one at a time from a cursor.
- printable — has a
@to_string(), soechoandprint()show something useful - iterable — can be walked with
foranditer
Constructor
sql.model.RecordCursor(cursor, definition)
ZuriParameters
cursor(Cursor)definition(Definition)
RecordCursor.next()
sql.model.RecordCursor.next() -> Model|nil
ZuriThe next record, or nil once there are none.
Returns Model|nil
RecordCursor.close()
sql.model.RecordCursor.close()
ZuriStops reading, releasing the cursor. Reading to the end does the same.
RecordCursor.to_string()
sql.model.RecordCursor.to_string()
Zuri2026, Richard Ore and Zuri contributors