Query Builder: selection and results
DB.table("products") creates a fresh RawQueryBuilder. Model.query() creates a ModelQueryBuilder sharing its clauses; hydration, serialization and model policies differ. Clauses are synchronous and return the same builder: await only asynchronous terminals.
Execution examples require a booted application and existing tables; toSql() only compiles the query and does not require the table to exist. Use the Product model columns for products; joins also require categories with id and name. The testing guide shows how to prepare an isolated database with its own sample models. Blocks are service/controller functions or queries you can run inside an async action. Write examples modify those sample tables.
Do not share a mutable builder across requests or tasks. The DB gateway itself is shared: create a builder for every query and use clone() to branch.
Building and executing a query
Section titled “Building and executing a query”The chain follows the Laravel Query Builder style: table, selection, filters, ordering and a final operation. Orionis uses Python, dots between methods and await for execution:
from orionis.support.facades import DB
async def available_products(): products = await ( DB.table("products") .select("id", "name", "price") .where("active", True) .where("stock", ">", 0) .orderBy("price", "desc") .orderBy("id") .limit(20) .get() ) return products.all()where("active", True) expresses equality; the three-argument form where("stock", ">", 0) specifies the operator. Both filters are combined with AND. Parentheses allow the chain to span multiple lines. get() executes the query; all() converts the resulting collection into a list of dictionaries.
| Need | Expression inside an async function |
|---|---|
First row or None | await DB.table("products").where("id", product_id).first() |
One value or None | await DB.table("products").where("id", product_id).value("name") |
| Collection of values | await DB.table("products").orderBy("id").pluck("name") |
| Count matches | await DB.table("products").where("active", True).count() |
| Page with metadata | await DB.table("products").orderBy("id").paginate(page=1, per_page=20) |
product_id represents an argument to your function. Write pagination argument names explicitly: the first argument in Orionis is page. Select columns with select("id", "name").get(); get() does not accept a column list. Keep API camelCase names such as orderBy and whereIn, and Python values True, False and None.
Querying a table inside a schema
Section titled “Querying a table inside a schema”Pass the qualified name as one string: DB.table("sales.products") queries the products table in the sales schema. The schema and table must exist and be accessible to the connection. This additional example assumes an engine with that schema, such as PostgreSQL; the unqualified tables in the testing scenario do not create it.
from orionis.support.facades import DB
async def schema_products(): return await ( DB.table("sales.products") .select("id", "name") .where("active", True) .orderBy("id") .get() )
async def reporting_products(): return await ( DB.connection("reporting") .table("sales.products", alias="p") .select("p.id", "p.name") .where("p.active", True) .orderBy("p.id") .get() )reporting is the connection name registered in config/database.py; sales is the schema within that database; p is this query’s alias. You can also write DB.table("sales.products", connection="reporting", alias="p"). Choosing a schema neither selects another connection nor creates the schema.
Reference columns as id, products.id without an alias, or p.id when declaring alias="p". The compiler resolves columns by table name or alias, so do not use sales.products.id. Declare aliases through the alias argument, not inside the string "sales.products as p". With a connection prefix of app_, the physical table is sales.app_products; keep passing the logical name sales.products.
The qualifier’s meaning depends on the engine: a schema in PostgreSQL, a database in MySQL, and a database such as main or an attached database available on the physical connection in SQLite. To combine tables, see joins across schemas; for models, configure a TableDefinition with schema.
Table and model results
Section titled “Table and model results”get() returns Collection, not a Python list. Raw tables contain dictionaries; model queries contain models. first() returns a row/model or None. value() may return None for either a missing row or SQL NULL. pluck() returns a value collection. See collections and pagination for their structures.
Continue with conditions, joins and writes.
Operation reference
Section titled “Operation reference”toSql()
Section titled “toSql()”toSql(*, literal_binds: bool = False) -> strAvailable since 1.2.0. Compiles the current SELECT into a string for debugging. This method is synchronous: call it without await on a DB.table() or model builder. It does not execute SQL, create an engine or open a database connection. Builders require a booted application or a configured ConnectionResolver to resolve their connection settings; the table does not need to exist.
from orionis.support.facades import DB
query = ( DB.table("products") .select("id", "name", "price") .where("active", True) .whereIn("id", [1, 2]) .orderBy("id") .limit(10))
sql = query.toSql()debug_sql = query.toSql(literal_binds=True)| Option | Output |
|---|---|
literal_binds=False (default) | SQL with the selected dialect’s placeholders; expanding parameters such as IN lists are rendered. |
literal_binds=True | SQL with supported values rendered and escaped by the dialect for inspection. |
Both forms respect the selected connection’s dialect and table prefix, including schemas and aliases. Placeholder spelling, quoting and pagination syntax can therefore differ between engines. The result is a str, not a SQL-and-bindings pair.
Compilation uses a clone, preserving the original builder and its plan. Model queries also apply their global scopes and soft-delete visibility to that clone. DB.table() has no model scopes or soft-delete policy to apply.
The string describes the current SELECT. It does not anticipate a later terminal’s changes, such as the limit added by first() or the projection used by count(), include additional eager-loading or pagination queries, or compile inserts, updates or deletes.
Literal output can contain sensitive values. Keep it in controlled debugging output and continue using parameterized queries for execution. Some types, including JSON or custom values without a dialect literal renderer, cannot be rendered with literal_binds=True.
Errors: InvalidQueryException when no table is selected; QueryException for SQL or literal compilation failures; MissingDatabaseDependencyException when a required dialect is unavailable. Connection resolution can also raise OrmConfigurationException before initialization or ConnectionNotFoundException for an unknown connection name.
toPlan()
Section titled “toPlan()”toPlan() -> SelectPlanReturns the live SelectPlan without compiling or executing SQL. Inspect filters, projection and limits.
Result: SelectPlan.
Mutating the returned object changes the query; it is not a snapshot.
from orionis.support.facades import DB
query = DB.table("products").where("active", True).toPlan()clone()
Section titled “clone()”clone() -> SelfCopies plan lists to branch a query. Existing clauses, the table and nested plans retain shared references; this is not a deep copy.
Result: a new builder (Self) with copied plan lists; does not execute SQL.
from orionis.support.facades import DB
query = DB.table("products").where("active", True).clone().where("stock", ">", 0)adoptPlan()
Section titled “adoptPlan()”adoptPlan(plan: SelectPlan) -> SelfReplaces the complete builder plan and returns the same instance. Does not execute or validate table existence.
| Parameter | Use |
|---|---|
plan | SelectPlan becoming the builder’s live plan; clone first when independence is needed. |
Result: the same mutable builder (Self); does not execute SQL.
Shares that plan: use plan.clone() to detach it.
from orionis.support.facades import DB
query = DB.table("products").adoptPlan(DB.table("products").where("stock", ">", 0).toPlan())adoptConnection()
Section titled “adoptConnection()”adoptConnection(name: str | None) -> SelfSelects this builder’s connection. None returns to the default; does not change global configuration.
| Parameter | Use |
|---|---|
name | Name of a connection registered in config/database.py. |
Result: the same mutable builder (Self); does not execute SQL.
from orionis.support.facades import DB
query = DB.table("products").adoptConnection("sqlite")select()
Section titled “select()”select(*columns: str) -> SelfReplaces the projection with the given columns. select() selects all; missing columns fail during compilation or execution.
| Parameter | Use |
|---|---|
columns | Column names as separate arguments, optionally qualified by table or alias. |
Result: the same mutable builder (Self); does not execute SQL.
from orionis.support.facades import DB
query = DB.table("products").select("id", "name")addSelect()
Section titled “addSelect()”addSelect(*columns: str) -> SelfAppends columns to the current projection without removing earlier entries. Does not deduplicate names.
| Parameter | Use |
|---|---|
columns | Column names as separate arguments, optionally qualified by table or alias. |
Result: the same mutable builder (Self); does not execute SQL.
from orionis.support.facades import DB
query = DB.table("products").select("id").addSelect("name")selectRaw()
Section titled “selectRaw()”selectRaw(sql: str, bindings: dict[str, Any] | None=None, alias: str | None=None) -> SelfAppends a trusted SQL expression and its bindings. alias makes the result addressable in a derived table.
| Parameter | Use |
|---|---|
sql | Developer-authored SQL fragment; variable values go through bindings. |
bindings | Dictionary of named :name parameters. None adds no bindings. |
alias | SQL alias for the projection or derived table, used to address its result. |
Result: the same mutable builder (Self); does not execute SQL.
from orionis.support.facades import DB
query = DB.table("products").select("id").selectRaw("price * :factor", {"factor": 2}, alias="double_price")distinct()
Section titled “distinct()”distinct() -> SelfRequests SELECT DISTINCT over the complete projection. Accepts no column names and does not implement DISTINCT ON.
Result: the same mutable builder (Self); does not execute SQL.
from orionis.support.facades import DB
query = DB.table("products").select("category_id").distinct()orderBy()
Section titled “orderBy()”orderBy(column: str, direction: str='asc') -> SelfAppends ascending or descending ordering. Retains earlier ordering rules; an invalid direction raises InvalidQueryException.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
direction | asc or desc; whitespace is trimmed and case normalized. |
Result: the same mutable builder (Self); does not execute SQL.
from orionis.support.facades import DB
query = DB.table("products").orderBy("price", "desc").orderBy("id")latest()
Section titled “latest()”latest(column: str | None=None) -> SelfOrders newest first. Without a column uses created_at for raw tables and the configured creation timestamp for models.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: the same mutable builder (Self); does not execute SQL.
Does not create the column; specify an appropriate column when it is absent.
from orionis.support.facades import DB
query = DB.table("products").latest("id")oldest()
Section titled “oldest()”oldest(column: str | None=None) -> SelfOrders oldest first. Without a column uses created_at for raw tables and the configured creation timestamp for models.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: the same mutable builder (Self); does not execute SQL.
Does not create the column; specify an appropriate column when it is absent.
from orionis.support.facades import DB
query = DB.table("products").oldest("id")groupBy()
Section titled “groupBy()”groupBy(*columns: str) -> SelfAppends grouping columns. Projections and aggregates must satisfy GROUP BY rules of the selected engine.
| Parameter | Use |
|---|---|
columns | Column names as separate arguments, optionally qualified by table or alias. |
Result: the same mutable builder (Self); does not execute SQL.
from orionis.support.facades import DB
query = DB.table("products").select("category_id").selectRaw("COUNT(*)", alias="total").groupBy("category_id")limit()
Section titled “limit()”limit(value: int) -> SelfSets maximum number of rows; accepts zero. Replaces the previous value of this option.
| Parameter | Use |
|---|---|
value | Option value or literal filter text. Limits/offsets must be nonnegative integers and cannot be bool. |
Result: the same mutable builder (Self); does not execute SQL.
Rejects negative values, bool and non-int types with InvalidQueryException.
from orionis.support.facades import DB
query = DB.table("products").orderBy("id").limit(10)take()
Section titled “take()”take(value: int) -> SelfSets maximum number of rows (limit alias); accepts zero. Replaces the previous value of this option.
| Parameter | Use |
|---|---|
value | Option value or literal filter text. Limits/offsets must be nonnegative integers and cannot be bool. |
Result: the same mutable builder (Self); does not execute SQL.
Rejects negative values, bool and non-int types with InvalidQueryException.
from orionis.support.facades import DB
query = DB.table("products").orderBy("id").take(10)offset()
Section titled “offset()”offset(value: int) -> SelfSets rows to skip; accepts zero. Replaces the previous value of this option.
| Parameter | Use |
|---|---|
value | Option value or literal filter text. Limits/offsets must be nonnegative integers and cannot be bool. |
Result: the same mutable builder (Self); does not execute SQL.
Rejects negative values, bool and non-int types with InvalidQueryException.
from orionis.support.facades import DB
query = DB.table("products").orderBy("id").offset(10)skip()
Section titled “skip()”skip(value: int) -> SelfSets rows to skip (offset alias); accepts zero. Replaces the previous value of this option.
| Parameter | Use |
|---|---|
value | Option value or literal filter text. Limits/offsets must be nonnegative integers and cannot be bool. |
Result: the same mutable builder (Self); does not execute SQL.
Rejects negative values, bool and non-int types with InvalidQueryException.
from orionis.support.facades import DB
query = DB.table("products").orderBy("id").skip(10)forPage()
Section titled “forPage()”forPage(page: int, per_page: int=15) -> SelfSets limit=per_page and offset=(page-1)*per_page without executing a query.
| Parameter | Use |
|---|---|
page | Page number, integer >=1, excluding bool. |
per_page | Page size, integer >=1; default 15. |
Result: the same mutable builder (Self); does not execute SQL.
Page and size must be positive integers; does not return a Paginator.
from orionis.support.facades import DB
query = DB.table("products").orderBy("id").forPage(2, 20)count()
Section titled “count()”async count(column: str='*') -> intCounts matching rows; column=”*” counts all and a column counts non-NULL values.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: int.
Aggregates clone the plan and remove ordering, limit and offset; retain filters, grouping, HAVING and unions. With GROUP BY, scalar terminals other than count("") return the first calculated value, rather than one result per group. count("") counts derived-result rows when groups, DISTINCT or UNION are present. For per-group aggregates use selectRaw(…).groupBy(…).get(). exists() clears offset, sets limit=1 and does not load the complete collection.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").where("active", True).count()Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
exists()
Section titled “exists()”async exists() -> boolChecks existence with a SELECT limited to one row.
Result: bool.
Aggregates clone the plan and remove ordering, limit and offset; retain filters, grouping, HAVING and unions. With GROUP BY, scalar terminals other than count("") return the first calculated value, rather than one result per group. count("") counts derived-result rows when groups, DISTINCT or UNION are present. For per-group aggregates use selectRaw(…).groupBy(…).get(). exists() clears offset, sets limit=1 and does not load the complete collection.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").where("active", True).exists()Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
doesntExist()
Section titled “doesntExist()”async doesntExist() -> boolNegates exists().
Result: bool.
Aggregates clone the plan and remove ordering, limit and offset; retain filters, grouping, HAVING and unions. With GROUP BY, scalar terminals other than count("") return the first calculated value, rather than one result per group. count("") counts derived-result rows when groups, DISTINCT or UNION are present. For per-group aggregates use selectRaw(…).groupBy(…).get(). exists() clears offset, sets limit=1 and does not load the complete collection.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").where("active", True).doesntExist()Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
async max(column: str) -> AnyReturns the maximum; returns None without values.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: Any.
Aggregates clone the plan and remove ordering, limit and offset; retain filters, grouping, HAVING and unions. With GROUP BY, scalar terminals other than count("") return the first calculated value, rather than one result per group. count("") counts derived-result rows when groups, DISTINCT or UNION are present. For per-group aggregates use selectRaw(…).groupBy(…).get(). exists() clears offset, sets limit=1 and does not load the complete collection.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").where("active", True).max("price")Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
async min(column: str) -> AnyReturns the minimum; returns None without values.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: Any.
Aggregates clone the plan and remove ordering, limit and offset; retain filters, grouping, HAVING and unions. With GROUP BY, scalar terminals other than count("") return the first calculated value, rather than one result per group. count("") counts derived-result rows when groups, DISTINCT or UNION are present. For per-group aggregates use selectRaw(…).groupBy(…).get(). exists() clears offset, sets limit=1 and does not load the complete collection.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").where("active", True).min("price")Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
async avg(column: str) -> float | NoneReturns the average converted to float, or None without values.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: float | None.
Aggregates clone the plan and remove ordering, limit and offset; retain filters, grouping, HAVING and unions. With GROUP BY, scalar terminals other than count("") return the first calculated value, rather than one result per group. count("") counts derived-result rows when groups, DISTINCT or UNION are present. For per-group aggregates use selectRaw(…).groupBy(…).get(). exists() clears offset, sets limit=1 and does not load the complete collection.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").where("active", True).avg("price")Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
async sum(column: str) -> AnyReturns the sum; returns 0 without values.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: Any.
Aggregates clone the plan and remove ordering, limit and offset; retain filters, grouping, HAVING and unions. With GROUP BY, scalar terminals other than count("") return the first calculated value, rather than one result per group. count("") counts derived-result rows when groups, DISTINCT or UNION are present. For per-group aggregates use selectRaw(…).groupBy(…).get(). exists() clears offset, sets limit=1 and does not load the complete collection.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").where("active", True).sum("price")Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
connection()
Section titled “connection()”connection(name: str) -> SelfSelects a registered connection for this builder without altering the shared DB gateway.
| Parameter | Use |
|---|---|
name | Name of a connection registered in config/database.py. |
Result: the same mutable builder (Self); does not execute SQL.
Terminals require a selected table and a booted application. Query errors propagate; paginate needs positive integers and uses two queries, not an atomic snapshot.
from orionis.support.facades import DB
query = DB.table("products").connection("sqlite")table()
Section titled “table()”table(name: str, *, alias: str | None=None) -> SelfReplaces the table and alias while retaining existing clauses.
| Parameter | Use |
|---|---|
name | Logical table name, such as products or sales.products; this is not a connection name. |
alias | SQL alias for the projection or derived table, used to address its result. |
Result: the same mutable builder (Self); does not execute SQL.
Terminals require a selected table and a booted application. Query errors propagate; paginate needs positive integers and uses two queries, not an atomic snapshot.
from orionis.support.facades import DB
query = DB.table("products").where("active", True).table("products", alias="p")async get() -> CollectionExecutes SELECT and returns a Collection of dictionaries. Does not hydrate models or apply their events, scopes or casts.
Result: Collection.
Terminals require a selected table and a booted application. Query errors propagate; paginate needs positive integers and uses two queries, not an atomic snapshot.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").orderBy("id").get()Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
first()
Section titled “first()”async first() -> dict[str, Any] | NoneQueries a clone with limit=1 and returns a dictionary or None, leaving the original builder limit unchanged.
Result: dict[str, Any] | None.
Terminals require a selected table and a booted application. Query errors propagate; paginate needs positive integers and uses two queries, not an atomic snapshot.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").orderBy("id").first()Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
value()
Section titled “value()”async value(column: str) -> AnySelects one column on a clone and returns its first value or None. None may also represent SQL NULL.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: Any.
Terminals require a selected table and a booted application. Query errors propagate; paginate needs positive integers and uses two queries, not an atomic snapshot.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").where("id", 1).value("name")Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
pluck()
Section titled “pluck()”async pluck(column: str) -> CollectionSelects one column and returns a Collection of its values in query order.
| Parameter | Use |
|---|---|
column | Column name, optionally qualified by table or alias. |
Result: Collection.
Terminals require a selected table and a booted application. Query errors propagate; paginate needs positive integers and uses two queries, not an atomic snapshot.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").orderBy("id").pluck("name")Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.
paginate()
Section titled “paginate()”async paginate(page: int=1, per_page: int=15) -> PaginatorConfigures the page and executes count followed by get to build a dictionary Paginator.
| Parameter | Use |
|---|---|
page | Page number, integer >=1, excluding bool. |
per_page | Page size, integer >=1; default 15. |
Result: Paginator.
Terminals require a selected table and a booted application. Query errors propagate; paginate needs positive integers and uses two queries, not an atomic snapshot.
from orionis.support.facades import DB
async def run_example(): return await DB.table("products").orderBy("id").paginate(page=1, per_page=15)Errors: QueryException on compilation/execution failure; the connection layer sanitizes messages and suppresses chained driver causes.