The Query API¶

All queries are built through the QuerySet API. Use select() to start a query and chain methods to refine it; nothing is sent to the database until you call a terminal method.

Starting a query¶

qs = db.select(User)           # SELECT * FROM user

# Class-level shortcut — locates the database automatically
qs = User.select()             # equivalent; no explicit ``db`` reference needed

All chain methods return a new QuerySet — the original is never modified.

Fetch one by primary key or field values¶

The most common lookups have class-level shortcuts that avoid writing a full query:

# Primary key subscript — raises KeyError when not found
user = User[1]

# Composite PK — list values in declaration order
line = OrderLine[order_id, product_id]

# get() — returns None when no match; raises if multiple rows match
user = User.get(email="alice@example.com")
user = User.get(name="alice", age=30)   # multiple kwargs are ANDed

# exists() — True/False
if User.exists(email="alice@example.com"):
    ...

Filtering¶

Column expressions¶

The most direct way to filter is to compare entity class attributes:

db.select(User).filter(User.name == "alice").fetch_all()
db.select(User).filter(User.age >= 18, User.active == True).fetch_all()

Multiple filter() arguments are combined with AND. Chain several filter() calls for the same effect:

db.select(Product).filter(Product.price > 10).filter(Product.stock > 0)

Lambda predicates¶

The where() method accepts a lambda to express conditions without repeating the class name:

db.select(Product).where(lambda p: p.price > 10).fetch_all()
db.select(User).where(lambda u: u.name == "alice").fetch_all()

Generator syntax¶

Import select() for a PonyORM-style generator expression:

from nextorm import select

results = select(u for u in User if u.age >= 18 and u.active == True)

Note

Generator queries work by decompiling the lambda’s bytecode. Complex Python expressions (function calls, multi-level attribute chains) are not supported; use filter() instead.

Ordering¶

db.select(User).order_by(User.name.asc())
db.select(User).order_by(User.age.desc(), User.name.asc())

Limit and offset¶

db.select(Product).limit(10)
db.select(Product).offset(20).limit(10)   # rows 21-30

Slicing and indexing¶

QuerySet supports Python slice and index syntax:

first  = db.select(Product)[0]             # single row
page   = db.select(Product)[10:20]         # list of rows

Pagination¶

For human-readable 1-based pagination:

page2 = db.select(Product).order_by(Product.name).page(2, pagesize=20)

Terminal methods¶

Method	Description
`fetch_all()`	Return all matching rows as a list.
`fetch_one()`	Return the first matching row or `None`.
`first()`	Alias for `fetch_one()`.
`get()`	Return one row or `None`; raises if multiple rows match.
`get_or_raise()`	Like `get()` but raises `ObjectNotFound` when no row matches.
`count()`	Return `COUNT(*)`.
`exists()`	Return `True` / `False`.
`delete()`	Issue `DELETE … WHERE …`; returns affected row count.
`update(**values)`	Issue `UPDATE … SET … WHERE …`; returns affected row count.

Aggregation¶

Use the aggregate terminal methods or the module-level functions from nextorm.generators:

from nextorm import count, sum, avg, min, max

total = db.select(Product).count()
total_value = db.select(Product).sum("price")
avg_price   = db.select(Product).avg("price")
cheapest    = db.select(Product).min("price")
priciest    = db.select(Product).max("price")

# Generator-expression form
n = count(u for u in User if u.active == True)

# Concatenate all names as a string
names = db.select(User).group_concat("name", sep=", ")

Random rows¶

sample = db.select(Product).random(5)

Joins¶

db.select(Post).join(
    Comment, Comment.post_id == Post.id, join_type="LEFT"
).fetch_all()

Eager loading (prefetch)¶

Avoid N+1 queries by declaring which relations to eager-load:

posts = db.select(Post).prefetch(Post.comments, Post.author).fetch_all()
for post in posts:
    # post.comments already loaded — no extra query
    print(post.author.name, len(post.comments))

Raw SQL¶

For advanced queries that NextORM cannot express:

# Returns entity instances mapped from raw SQL
users = db.select(User).raw(
    "SELECT * FROM user WHERE name ILIKE %s",
    params=["%alice%"],
)

# Or for one-row:
user = db.select(User).raw_one("SELECT * FROM user WHERE id = ?", [1])

Debug helpers¶

Inspect the generated SQL without executing the query:

qs = db.select(User).filter(User.age >= 18).order_by(User.name)
print(qs.get_sql())
# → SELECT id, name, age FROM "user" WHERE age >= ? ORDER BY name ASC

qs.show()           # formatted tabular output to stdout

Distinct¶

db.select(User).distinct().fetch_all()

FOR UPDATE¶

Lock rows for update (PostgreSQL / MariaDB only):

user = db.select(User).filter(User.id == 1).for_update().fetch_one()
user = db.select(User).filter(User.id == 1).for_update(skip_locked=True).fetch_one()

Async queries¶

Use aselect() for the async variant. The AsyncQuerySet API is identical except every terminal method is a coroutine:

results = await db.aselect(User).filter(User.age >= 18).fetch_all()
count   = await db.aselect(User).count()