Reading Data with PormG

This section covers the read side of PormG — querying, filtering, joining, aggregating, and transforming data from your database. Every query starts from Model.objects and uses a Django-inspired chainable API.

Section Map

Query Execution and Outputs

PormG provides several terminal methods to execute a query and return data in different formats:

Choosing an Output Format

query = M.Result.objects
query.filter("driverid__nationality" => "Brazilian", "positionorder" => 1)
query.values("driverid__surname", "raceid__name")

# As model-aware rows — best for ORM-style iteration
results = query.list()
for row in results
    println(row[:driverid__surname], " won at ", row[:raceid__name])
end

# As plain dictionaries — useful when another framework requires Dict values
dicts = query.list(:dict)

# As a DataFrame — best for analysis
df = query.values("driverid__surname", "raceid__year") |> DataFrame

# As JSON — best for API responses
json_str = query.list(:json)

# Just the count
n = query.count()      # => 42

# Just a boolean check
has_any = query.exists()  # => true

Rows returned by .list(), .first(), .last(), .earliest(), .latest(), .get(), .create(), .get_or_create(), and .update_or_create() are PormGRow values. They support property access, indexed access, many-to-many relationship accessors, and dirty tracking for row.save() and row.delete():

driver = M.Driver.objects.get("driverref" => "hamilton")

println(driver.forename, " ", driver.surname)

driver.nationality = "British"
driver.save()

For framework integrations that require plain dictionaries, use .list(:dict). For tabular analysis, pipe the query to DataFrame.

A query is a Tables.jl table

A query implements the Tables.jl interface, so DataFrame is one sink among many: anything that reads a Tables.jl table reads a query, with no PormG method involved. Each read executes the query once, and the columns hold the same typed values as .list():

using DataFrames, CSV   # each sink is your own dependency: `Pkg.add(["DataFrames", "CSV", "Tables"])`
import Tables

query = M.Result.objects.filter("raceid__year" => 2009, "position" => 1).
    values("raceid__date", "driverid__surname", "points").
    order_by("raceid__date")

df   = query |> DataFrame           # DataFrames' own constructor
rows = Tables.rowtable(query)       # a Vector of NamedTuples
CSV.write("winners_2009.csv", query)

raceid__date is a Date in the DataFrame and in the row table, on every engine, as it is in .list(); CSV.write writes it as ISO text (2009-03-29).

The type of a computed number

A number the database computes reads back as the Julia type the call means, and that type is the same on every engine. It is the type Django gives the call (its output_field):

The engines do not compute these values in the same type, so PormG converts what the driver delivers:

• PostgreSQL computes Avg over integers, and every function PormG renders over (x)::numeric (Round, Mod, Sqrt, Abs of a float, …), as a numeric. The driver delivers that as a Decimal, and PormG reads it as a Float64 when the call means a float.
• SQLite computes the decimal family as a double. PormG reads it as a Decimal built from that double to 15 significant digits, as Django's SQLite backend does. A sum of 0.1 and 0.2 reads as 0.3, not 0.30000000000000004.
• MySQL computes Sum over integers and Avg as a DECIMAL. PormG turns the integer sum back with DIV 1 and reads the mean as the Float64 or Decimal it means (each connection sets div_precision_increment = 30, so a mean is not cut to 4 decimals). MySQL computes Sqrt, Power, Exp and Ln over a decimal as a double, which PormG reads as it does SQLite's.

q = M.Result.objects
q.filter("raceid" => 18)
q.values("avg_grid" => Avg("grid"))
q.list(:dict)
# => [Dict(:avg_grid => 11.5)]     a Float64 on every engine

q = M.Constructor_results.objects
q.filter("raceid" => 18)
q.values("total" => Sum("points"), "avg" => Avg("points"))
q.list(:dict)
# PostgreSQL => total 39, avg 3.5454545454545455   both Decimals
# SQLite     => total 39, avg 3.54545454545455     both Decimals
# MySQL      => total 39, avg 3.545454545454545454545454545455   both Decimals

The type is the same on every engine. The last digits can differ, as the Avg above shows: PostgreSQL computes in exact numeric, MySQL in a DECIMAL and SQLite in a double. The same holds for a float, so Sqrt(10) is 3.162277660168379 on PostgreSQL and 3.1622776601683795 on SQLite and MySQL. That is how each engine spells the number, not a different meaning, and PormG leaves it alone. Rounding collects where a value is rounded and where PormG refuses instead.

This applies to the value a projection, a CTE(...) column or a Subquery(...) reads back, and to a value that is one of several operands when one of them is a computed number: Coalesce(Avg("grid"), 0) reads as a Float64 on every engine. A Coalesce, Greatest, Least, NullIf or Case over columns and literals only is not a computed number: each row's value is one of its operands, and it reads back as the driver delivers it. The exception is an output_field naming a decimal type: the call has declared what it means, so it reads as a Decimal, as a Cast to one does (#1212).

An integer's width follows the engine, for a column and a computed value alike: PostgreSQL and MySQL keep the column's own type (an IntegerField reads as an Int32, a BigIntegerField or a Count as an Int64), and SQLite has one 64-bit integer, so every integer reads as an Int64 there. That is the same value in different widths, not a different meaning, so PormG leaves it alone. Compare integers by value (==), or convert with Int(x) where one Julia type is needed — or declare the type once with a typed read, which converts integer widths for you.

Typed rows: list(T)

.list() and .list(:dict) hold their values as Any, so every field access in the code a query feeds is a run-time dispatch. A typed read builds each row into a type you declare — a struct or a NamedTuple type — and returns Vector{T}, so everything after the query is concrete and type-stable (and inferred: list(T) is Vector{T} to the compiler, first(T) is Union{Nothing, T}):

struct DriverRow
    driverid::Int64
    surname::String
    number::Union{Nothing, Int64}
    dob::Date
end

rows = M.Driver.objects.filter("nationality" => "Brazilian").order_by("surname").list(DriverRow)
rows[1].surname                        # a String, known to the compiler

M.Driver.objects.filter("nationality" => "Brazilian").first(DriverRow)   # DriverRow or nothing
M.Driver.objects.get(DriverRow, "driverref" => "senna")                  # DriverRow, or DoesNotExist

With no values(...), the query projects exactly fieldnames(T), so it reads only the columns T needs. A field can name a joined column (driverid__surname is a valid field name). With values(...), every projected name, aliases included, must be a field of T, and every field of T must be projected. That is how joined and computed columns reach a typed row:

struct ResultRow
    surname::String
    race::String
    points::Float64
end

M.Result.objects.
    values("surname" => "driverid__surname", "race" => "raceid__name", "points").
    filter("raceid__year" => 2008, "driverid__driverref" => "hamilton").
    order_by("raceid__round").
    list(ResultRow)

struct TotalsRow
    surname::String
    races::Int64
    avg_points::Float64
end

M.Result.objects.
    values("surname" => "driverid__surname", "races" => Count("resultid"), "avg_points" => Avg("points")).
    filter("driverid__driverref__@in" => ["hamilton", "alonso"]).
    order_by("surname").
    list(TotalsRow)

A field with no column and a column with no field both raise QueryBuildError before any SQL runs: a typed read refuses rather than drops a column, so a misspelled alias cannot pass silently. values("*") is refused too, because list(T) already projects T's fields.

NULL reads as the field's declared absent value:

Every field must hold a concrete type once Nothing/Missing is set aside: Int64, String, Union{Nothing, Date}. An abstract field (::Integer, ::Real, ::Any) is refused before SQL runs, because it would take values the rules below refuse (a Bool into ::Integer, a Decimal into ::Real) and its rows would not be concretely typed.

Conversion is decided by the two types: whether a column's type can go into a field's type never depends on the value, so a query cannot pass in a test and fail on one row in production by type. The one check that looks at a value is range — an integer that does not fit the field's integer type, or an integer a float cannot hold exactly. Each value is first read as every other terminal reads it (a DateTimeField as a ZonedDateTime, a DecimalField as a Decimal, …), then:

The integer rule is what lets one struct read the same on every engine: an IntegerField reads as an Int32 on PostgreSQL and MySQL and as an Int64 on SQLite, and an Int64 field takes both. A refusal names the field, the field's type and the column's type, never the value.

A typed row is a read-only projection, like jOOQ's fetchInto or sqlx's query_as!: it has no lazy relations, no many-to-many accessors and no save(). Use .list() for those.

Typed reads in a trimmed build

An application compiled ahead of time with juliac --trim keeps only the code the compiler can follow from its entry point. Of the read terminals, such a build supports the typed read only: list(T), first(T), last(T), get(T, …), earliest(T, …) and latest(T, …). They decode the driver's result straight into T, and every call on the way is typed. The terminals that hold their values as Any are JIT-only: .list(), .first() and .get() without a type (their PormGRow rows), list(:dict), list(:json), a raw fetch or execute, and reading a query as a Tables.jl table (DataFrame(query)). Under the JIT nothing changes, and every terminal works as this page describes. A trimmed build does not yet run a query end to end; #1599 tracks the rest of the work.

Writing T: show_query = :struct

You do not have to work out each field's type by hand. Every read terminal takes show_query = :struct, which renders the query without running it and returns, as a String, the struct list(T) accepts for that query: one field per projected column, in projection order, named by the column's output name:

M.Result.objects.filter("driverid__nationality" => "Brazilian", "raceid__year" => 2008).
    values("surname" => "driverid__surname", "team" => "constructorid__name", "points" => Sum("points")).
    order_by("surname").
    list(show_query = :struct) |> print

# show_query = :struct, rendered for SQLite: the row type list(T) accepts. Review it before pasting.
struct ResultRow
    surname::String                  # driverid__surname: CharField on Driver
    team::String                     # constructorid__name: CharField on Constructor
    points::Union{Nothing, Float64}  # SUM over points: NULL when no row matches
end

Paste it into your code, review it, rename what you like, and call list(ResultRow) on the same query. PormG returns text and never evaluates it: no type is created in your module for you, and once pasted, the struct is yours like any other code. The workflow is the same for a person and for a coding agent: render, paste, review the comments, run.

What the text says:

• The using lines its types need, and only those: Dates (Date, Time, DateTime), Decimals (Decimal) and TimeZones (ZonedDateTime). PormG does not re-export these types, so Decimals and TimeZones must be in your project (Pkg.add) for the pasted struct to compile.
• A header naming the engine it was rendered for. An integer column is Int64 on every engine, since a typed read widens PostgreSQL's and MySQL's Int32. A type that does differ between engines is marked in its comment (a DateTimeField(type = "TIMESTAMP") reads as a ZonedDateTime on SQLite and a DateTime on PostgreSQL), so a struct written against SQLite does not surprise anyone on PostgreSQL.
• A comment per field naming its source: the field type, the joined path, or the function.
• Union{Nothing, T} wherever the column can be NULL: a null = true column, any column on the outer side of a LEFT join (a path through a ForeignKey(null = true), or an explicit on(path, join_type = "LEFT")), an aggregate over no rows, and every computed value PormG cannot prove non-NULL. The comment says why. The choice is conservative on purpose: a Union{Nothing, T} field takes every non-NULL value too, so an unneeded one costs you an edit, while a missing one would fail the read on the first NULL. If you read rows into a DataFrame and prefer missing, edit the field to Union{Missing, T}: the typed read accepts both.
• A # TODO line, never Any, for a column whose type PormG cannot name on that engine: a JSON key's value ("profile__titles", whose type is the data's; Cast it to the type you mean), or a PostgreSQL ArrayField, which reads as Vector{T}, or Vector{Union{Missing, T}} when an element is NULL. list(T) refuses an abstract field, so an Any would only fail later; until you declare the field, the read refuses that column by name.
• var"…" for a projection name that is not a valid Julia identifier on its own (a keyword such as values("end" => "position")).

With no values(...), the struct is the model's fields, which is what list(T) would project. With a typed terminal, list(T, show_query = :struct) returns the struct the query needs for fieldnames(T), so it shows where your declarations differ from what the read accepts:

struct DriverRow
    driverid::Int32
    surname::String
    dob::String
end

M.Driver.objects.list(DriverRow, show_query = :struct) |> print
# using Dates
#
# # show_query = :struct, rendered for SQLite: the row type list(T) accepts. Review it before pasting.
# struct DriverRow
#     driverid::Int64  # IDField
#     surname::String  # CharField
#     dob::Date        # DateField
# end

count() (its column form included), aggregate(), exists() and the writes return no rows to declare, so they raise QueryBuildError under show_query = :struct. So does values("*"), alone or beside other columns: a typed read cannot project it, so drop it for the model's fields, or name every column in values(...).

Serializing rows to JSON

JSON.json on a PormGRow — or on the Vector{PormGRow} that .list() returns — emits the row's columns and nothing else. For rows as returned by a query, that is exactly the JSON .list(:json) produces:

using JSON

query = M.Driver.objects.filter("nationality" => "Brazilian").
    values("driverref", "surname").
    order_by("driverref")

JSON.json(query.list()) == query.list(:json)   # => true

(Both read the row's stored columns, so an edited row — driver.nationality = "British" before .save() — serializes what you set, while a fresh .list(:json) re-queries the database.)

The difference is where the result can go. .list(:json) hands back a String, so nesting it inside a larger response object double-encodes it; serializing the rows directly does not:

JSON.json((count = query.count(), drivers = query.list()))
# => {"count":32,"drivers":[{"driverref":"barrichello","surname":"Barrichello"}, ...]}

Durations go through the same formatter either way, so a DurationField reads as "00:01:49.088" rather than as a struct dump, on every engine.

A DecimalField goes through the same formatter too, and comes out as a JSON number carrying the digits the value actually has — not as a string, and not re-rounded through a Float64 on the way out:

query = M.Constructor_results.objects
query.values("points")
query.order_by("constructorresultsid")
query.limit(5)

query.list(:json)
# => [{"points":14},{"points":8},{"points":9},{"points":5},{"points":2}]

A whole value reads as 14, not 14.0, and a fractional one keeps its scale — {"points":0.5}.

On PostgreSQL that is exact at any declared width. A DecimalField(max_digits = 24, decimal_places = 2) holding 12345678901234567.89 serializes with all nineteen digits, where routing it through a Float64 would have rendered 1.2345678901234568e16. LibPQ delivers a NUMERIC as a Decimals.Decimal, so nothing on the path narrows it.

On MySQL it is exact at any declared width too. A DecimalField is a DECIMAL(p, s) column, up to 65 digits, and MySQL.jl delivers it as a Decimals.Decimal.

On SQLite it is exact up to fifteen digits, and PormG refuses anything wider. SQLite has no exact decimal type. A DECIMAL(p, s) column gets SQLite's NUMERIC affinity, which converts the value as it is stored into an Int64 or a Float64 — preferring an integer conversion, and silently dropping what will not fit. Measured on a column declared wider than that:

declared DECIMAL(24, 2), inserted   SQLite stores   read back
  14                                integer         14                      (Int64)
  0.5                               real            0.5                     (Float64)
  1.000000000000000000001           integer         1                       (Int64)
  123456789012345.67                real            1.2345678901234567e14   (Float64)
  12345678901234567.89              integer         12345678901234568       (Int64)

Note the third row: the fractional part is not rounded, it is discarded, and the value becomes a plain 1. Nothing on the read path can recover that, which is why the fix is on the declaration: a Float64 keeps fifteen significant digits exactly, so makemigrations raises BackendCapabilityError for a DecimalField with max_digits above 15 on SQLite rather than create a column like the one above. Every decimal column PormG creates on SQLite therefore holds the values it accepts exactly — and because it does, PormG reads a DecimalField declared with max_digits of 15 or fewer back as the Decimals.Decimal that was written, the same type PostgreSQL and MySQL return, rather than the Int64/Float64 SQLite stored it as. The declaration decides it, not who created the column: a field declared wider than 15 digits — a table created outside PormG, or before this refusal — still behaves as the table shows and reads back raw, because SQLite may already have rounded what it holds and a Decimal rebuilt from that would only look exact. Narrowing such a field to 15 does not recover digits SQLite already dropped: the third row above would then read back as an exact-looking 1. Use PostgreSQL or MySQL where more than fifteen digits is the point.

This applies to a column's own value. A decimal nested inside a container — a PostgreSQL numeric[], which LibPQ delivers as a Vector{Decimal} — is not reached, and still serializes through a Float64. That is the same limit the duration formatter above has, and it is unchanged by this.

# ✗ raises: driverid was not projected, and PormG won't lazily load it
row = M.Result.objects.values("resultid", "points").first()
driver_name = row.driverid.forename

# ✓ project the related column, then read it
row = M.Result.objects.values("resultid", "driverid__forename").first()
driver_name = row[:driverid__forename]

# ✓ or project the raw foreign-key value
row = M.Result.objects.values("resultid", "driverid").first()
driver_id = row[:driverid]

Serializing a model, a query or a field

Rows are the thing worth serializing; a model is not. Handing one to JSON.json gives you a short marker naming it, never its schema:

JSON.json(M.Driver)                      # => {"pormg_model":"driver"}
JSON.json(M.Driver.fields["surname"])    # => {"pormg_field":"CharField"}
JSON.json(M.Driver.objects)              # => {"pormg_query":"driver"}

That is a deliberate floor, not an export format. The model graph is cyclic and densely cross-referenced, so serializing it by reflection re-walks every path through it — on the F1 schema these docs use, one model produced 2,175,304 characters, and a larger schema exhausted memory. The marker makes that unrepresentable: nothing PormG hands a serializer can contain another PormG value.

Nothing you would want in a response is affected, because a model never belonged in one. If you need schema information as data, read it off the model directly rather than serializing the model: M.Driver.field_names for the columns, or keys(M.Driver.fields) for every declared field — field_names covers only fields that own a real column, so it omits a ManyToManyField.

Query Styles

PormG supports both a fluent interface (recommended) and a legacy pipe style.

Fluent Interface (Recommended)

Chain methods directly and finish with a terminal call:

# Full chain with terminal call
drivers = M.Driver.objects.
    filter("nationality" => "Brazilian").
    order_by("surname").
    limit(10).
    list()

# Route a query to another configured database pool
results = M.Result.objects.
    db("client_42").
    filter("points__@gt" => 10).
    list()

Pipe Style (Legacy)

The pipe style is still supported but the fluent form is preferred in docs and user-facing code:

query = M.Driver.objects |> filter("nationality" => "Brazilian")
df = query |> DataFrame

Handler Mutation Model

Every query handler follows four rules. They are where PormG deliberately differs from Django's clone-per-call querysets, so they are worth internalizing once:

1. Model.objects returns a fresh handler on every access. Two mentions of M.Driver.objects are two independent queries — state never leaks between them.
2. Chain methods mutate the handler in place and return that same handler (not a copy). Assigning a chain to a second variable aliases the same query.
3. .copy() is the branching escape hatch — deep-copy a base query, then extend each copy independently (example below).
4. Terminal methods never mutate the handler. count, exists, list, first, get, and the show_query/inspect_query inspection paths all execute on an internal copy of the handler. A handler stays reusable after any read terminal — q.first() does not leave a limit(1) behind, inline filters passed to q.get("field" => v) do not persist, and q.update(...) after q.first() is valid.

Re-call semantics: which methods accumulate

Calling the same chain method twice is not always the same operation. The semantics follow Django: filter accumulates, while values/order_by replace their previous call (Django documents this as "each order_by() call will clear any previous ordering").

q = M.Result.objects
q.filter("raceid__year" => 2019)
q.filter("positionorder" => 1)           # accumulates: year = 2019 AND positionorder = 1

q.values("driverid__surname")
q.values("driverid__surname", "points")  # replaces: only surname + points are selected

q.order_by("points")
q.order_by("-points")                    # replaces: ORDER BY points DESC only

Branching with .copy()

base_query = M.Result.objects.filter("positionorder" => 1)

# Reuse for different projections — each copy evolves independently
winners_by_driver = base_query.copy().values("driverid__surname", "wins" => Count("resultid"))
winners_by_team   = base_query.copy().values("constructorid__name", "wins" => Count("resultid"))

Chainable Methods Reference

These methods modify the query builder and return the handler for further chaining. The On re-call column states what a second call of the same method does (see the handler mutation model above):

Basic Retrieval Examples

Simple Filter and List

# Return model-aware rows
data = M.Status.objects.filter("status" => "Engine").list()

# Return a DataFrame
df = M.Status.objects.filter("status" => "Engine") |> DataFrame

Count and Existence Checks

count  = M.Status.objects.filter("status" => "Engine").count()
exists = M.Status.objects.filter("status" => "Engine").exists()

Pagination

# Page 1: first 20 results
page1 = M.Driver.objects.order_by("surname").limit(20).list()

# Page 2: skip 20, take 20
page2 = M.Driver.objects.order_by("surname").limit(20).offset(20).list()

# Same thing in one call — .page(limit, offset)
page2_alt = M.Driver.objects.order_by("surname").page(20, 20).list()

# .page(n) is limit-only: it sets LIMIT and leaves any offset already on the handler alone
top20 = M.Driver.objects.order_by("surname").page(20).list()

A page size of 0 is a real limit: .limit(0) returns no rows, the same as LIMIT 0 in SQL and qs[:0] in Django. To remove a limit already on a handler, use .limit(nothing). A negative value or a Bool (true is an Integer in Julia) raises QueryBuildError for .limit(), .offset() and .page() alike, on every engine: a row count is zero or more, and a Bool is not one. (Left to the engines, one rejects a negative LIMIT and another reads it as "no limit".)

n = 0                                             # e.g. a page size computed from a selection
M.Driver.objects.order_by("surname").limit(n).list()   # → no rows

q = M.Driver.objects.order_by("surname").limit(20)
q.limit(nothing)                                  # removes the limit again: every row

Read terminals on a sliced query

A terminal called on a query with .limit() or .offset() set works inside the slice, the way Django's terminals do on qs[:n]. A terminal that cannot do that raises QueryBuildError; it never ignores the slice.

q = M.Driver.objects.filter("nationality" => "British").order_by("surname").limit(5)

q.count()     # → 5, not every British driver
q.first()     # → the first of those five

q.limit(0)
q.exists()    # → false: a zero-row page holds no row

# Count before slicing to get the total for a paginated response
base  = M.Driver.objects.filter("nationality" => "British")
total = base.count()
rows  = base.order_by("surname").page(20, 40).list()

Distinct Results

nationalities = M.Driver.objects.values("nationality").distinct().list()

.count() and .exists() answer for the same rows: values("nationality").distinct().count() is the number of nationalities, not of drivers. The same holds for a grouped projection — values("nationality", "n" => Count("driverid")).count() counts the groups, and a filter on "n" applies before they are counted. A filter on any other values() alias applies too, as it does for .list(): values("next_grid" => F("grid") + 1).filter("next_grid__@gt" => 20).count() counts the results that started 20th or further back, and a filter on a window alias raises the same QueryBuildError it raises from .list().

An aggregate with nothing to group by is one row whatever the filters matched: values("n" => Count("resultid")) returns a single row because COUNT over no rows is 0, not zero rows. Its count would be a constant (1, or 0 under a slice), so .count() and .exists() raise QueryBuildError instead of answering it. The message names three ways out:

brazilian = M.Result.objects.filter("driverid__nationality" => "Brazilian")

brazilian.count()                                                          # the matched results
brazilian.values("n" => Count("resultid")).filter("n__@gt" => 3).count()   # 1 or 0: a HAVING on the alias
brazilian.aggregate("n" => Count("resultid")).n                            # the aggregate's value

A filter on the alias is a HAVING, which can remove that one row, so the second line is answered as written. A shape that only the build shows is ungrouped is refused the same way — an aggregate beside a Value(...) literal that a filter reads, say.

Exists(...) is different, and follows Django: it ignores the subquery's SELECT list and keeps its grouping and HAVING, so Exists(standings) over standings.values("t" => Count("driverstandingsid")) asks whether any standing matched. That is what lets one correlated handle serve both Subquery(...) and Exists(...) (see Subqueries and CTEs). Exists(...) can only clear a projection it recognizes before building the query, so the ungrouped shapes that only the build shows — the literal one above, or an aggregate beside a window with an empty WindowOver() — raise QueryBuildError from it as well, rather than answer a constant.

# ✗ raises: surname is not in the SELECT DISTINCT projection
M.Driver.objects.values("nationality").distinct().order_by("surname").list()

# ✓ include the sort key in values() (distinct over both columns) …
M.Driver.objects.values("nationality", "surname").distinct().order_by("surname").list()

# ✓ … or drop distinct() if you meant "one row per nationality, ordered by an aggregate"

At the REPL

Models, queries and rows print as readable summaries, so you can build a query one step at a time and look at it as you go.

Typing a model shows its columns as the database holds them — the SQL type, the relations, and the schema flags:

julia> M.Driver
PormG model · driver · db "db_sl"
  driverid     IDField       BIGINT  pk  unique  index
  driverref    CharField     VARCHAR(250)
  number       IntegerField  INTEGER  null
  code         CharField     VARCHAR(250)
  forename     CharField     VARCHAR(250)
  surname      CharField     VARCHAR(250)
  dob          DateField     DATE
  nationality  CharField     VARCHAR(250)
  url          CharField     VARCHAR(250)
  reverse: driver_standings, lap_times, pit_stops, qualifying, result, sprint_results
  query: driver.objects.filter(…)

The reverse: line lists the accessors other models installed on this one through their ForeignKeys — those are the names you traverse backwards with, as described in Values and Joins.

A query handler shows the clauses you have set so far:

julia> q = M.Result.objects.filter("driverid__surname" => "Senna", "positionorder__@lte" => 3).
           values("year" => "raceid__year", "race" => "raceid__name", "pts" => "points").
           order_by("-points").limit(20)
PormG query · result
  filter    driverid__surname => "Senna", positionorder <= 3
  values    year, race, pts
  order_by  -points
  page      limit 20
  not executed — .list() · .count() · DataFrame(q) · SQL: show_query(q)

The values line names the output columns — the aliases you gave — not the field paths behind them, so it reads as the columns the result will actually carry.

Rows print as themselves, with the primary key first:

julia> row = M.Driver.objects.filter("surname" => "Senna").values("driverid", "forename", "surname", "nationality").first()
PormG row · driver · 4 columns
  driverid     102
  forename     "Ayrton"
  nationality  "Brazilian"
  surname      "Senna"

And list() uses standard Julia vector display, one row per line — so a long result set elides in the middle the way any Julia vector does:

julia> M.Driver.objects.filter("surname" => "Fittipaldi").values("driverid", "forename", "surname").list()
4-element Vector{PormGRow}:
 Row(driver: driverid=104, forename="Christian", surname="Fittipaldi")
 Row(driver: driverid=224, forename="Emerson", surname="Fittipaldi")
 Row(driver: driverid=290, forename="Wilson", surname="Fittipaldi")
 Row(driver: driverid=850, forename="Pietro", surname="Fittipaldi")

For a tabular view of a result set, pipe to DataFrame instead — see Choosing an Output Format above.

Query Inspection

You can inspect the generated SQL without executing the query:

query = M.Result.objects.
    filter("driverid__nationality" => "Brazilian").
    values("driverid__surname", "points").
    order_by("-points")

# Get just the SQL string
sql = query.list(show_query=:sql)

# Get full metadata (SQL, parameters, dialect, operation)
meta = query.list(show_query=:dict)

# Benchmark the builder with zero overhead
@time query.list(show_query=:none)

# Dedicated inspection API with heuristic intent detection
inspection = query.inspect()
println(inspection[:sql_text])
println(inspection[:operation])  # => :select

show_query is supported on terminal methods such as list(), first(), get(), count(), exists(), create(), delete(), update(), bulk_insert(), and bulk_update().

:pretty is the readable form of :sql — for a log line, or to read a long join at the REPL:

query = M.Result.objects.
    filter("driverid__surname" => "Senna", "raceid__year__@gte" => 1990).
    values("raceid__name", "points").
    order_by("-points")

println(query.list(show_query = :pretty))

SELECT
  "Tb_1"."name" as "raceid__name",
  "Tb"."points" as "points"
FROM "result" as "Tb"
INNER JOIN "race" AS "Tb_1" ON "Tb"."raceid" = "Tb_1"."raceid"
INNER JOIN "driver" AS "Tb_2" ON "Tb"."driverid" = "Tb_2"."driverid"
WHERE "Tb_2"."surname" = $1
  AND "Tb_1"."year" >= $2
ORDER BY "points" DESC NULLS FIRST

The query plan: explain()

show_query shows what PormG sends; .explain() asks the database how it would run it — which indexes it uses and which tables it reads in full. Like Django's QuerySet.explain(), it explains the SELECT and never a write.

plan = M.Result.objects.
    filter("driverid__surname" => "Senna").
    values("raceid", "points").
    explain()

plan[:indexes_used]   # ["result_driverid_lunjedbf_idx"] — Senna's results, found by driver
plan[:seq_scans]      # ["driver"] — the surname filter reads the driver table in full

On PostgreSQL, explain(analyze = true) executes the query and adds measured timing (:execution_time_ms, :planning_time_ms); buffers = true and verbose = true add PostgreSQL's detail to :plan. SQLite has only EXPLAIN QUERY PLAN — it reports the indexes and scans, names tables by the query's alias (Tb_1 rather than driver), has no cost or timing, and raises BackendCapabilityError for any of the three options. MySQL runs EXPLAIN FORMAT=JSON: it reports the indexes ("PRIMARY" for the primary key), the full scans by alias, a cost, and a row estimate where the plan states one, with no timing, and raises BackendCapabilityError for the three options too.

PormG reports what the plan states and does not guess at missing indexes: a full scan of a small table is the plan a database should choose. The API reference lists every key.

Database Routing

If you use multiple configured pools, select the target database per query:

# Route to a staging database
q = M.Driver.objects.db("staging").filter("code" => "SEN")

# Route to a tenant database (with lazy resolution)
results = M.Result.objects.db("client_42").filter("positionorder" => 1).list()

See Configuration: Dynamic Multi-Tenancy for setting up connection resolvers.

Reading Roadmap

If you are learning the API from scratch, the recommended order is:

1. Values and Joins — Start with column selection and __ join traversal.
2. Filters and Aggregates — Add lookup operators, grouping, and HAVING.
3. Functions and Dates — Use SQL functions, date extraction, and Case/When.
4. Subqueries and CTEs — Decompose complex queries with IN subqueries and WITH.
5. Field Expressions — Reach for F() when you need column-to-column logic or arithmetic.
6. Q Objects — Use Q()/Qor() only when plain filter pairs stop being expressive enough.