API Reference

import_models(path::AbstractString; into::Module = Main) -> Module

The function form of @import_models (#1232), for a script or a REPL session — one that compares two databases, say — where the macro's string literal and module context get in the way. Includes the models file at path (relative to pwd(), or absolute) into into, registers its models exactly as the macro does, and returns the models module:

PormG.Configuration.load("db"; env = "dev")
PormG.Configuration.load("db_archive"; env = "dev")
M = PormG.import_models("db/models.jl"; into = Module(:Live))         # both files define
A = PormG.import_models("db_archive/models.jl"; into = Module(:Archive))   # `module models`
M.Driver.objects.filter("surname" => "Senna").count(), A.Driver.objects.count()

The file must define one module; its folder must be loaded first, as with the macro. In a package, keep the macro: it resolves the path against the calling file and precompiles with the package.

install_ai_skills(target_dir::String = pwd(); io::IO = stdout)
    -> (; installed_dir, written, stale, referenced_in, stub)

Copy PormG's pormg-usage skill bundle into <target_dir>/.github/skills/pormg-usage/, the file set an AI coding assistant (GitHub Copilot, Claude, Cursor, …) reads to learn the PormG API. Re-running it is the update mechanism: every shipped file is overwritten with the copy from the PormG version your project resolves, so run it again after each PormG bump.

The bundle is flat — SKILL.md, a router, plus the topic files it links to — and the whole directory is copied so none of its relative links dangle after install.

Claude Code registers a skill only from .claude/skills/<name>/SKILL.md, so it also writes <target_dir>/.claude/skills/pormg-usage/SKILL.md: a discovery stub holding the bundle's front-matter and a pointer to the bundle's SKILL.md, rewritten on every run. When any step of that path is a link (.claude, .claude/skills, the skill directory or the file), it writes no stub.

It writes only those two places, and reports what it found rather than acting on it:

• written — the files it copied into the bundle directory, by name.
• stub — the path of the Claude Code stub, or nothing when it was not written (a link is on its path, or the write failed; both are logged as a warning).
• stale — files already in the target directory that this PormG version does not ship. Usually a topic file an older PormG installed and a later one renamed; possibly a file you added yourself. They are left in place — delete them if they came from PormG, because an assistant browsing the directory would still read their outdated content.
• referenced_in — the project's agent-instruction files (AGENTS.md, CLAUDE.md, .github/copilot-instructions.md, .github/instructions/*.md) that mention pormg-usage. Copying the files does not make an assistant read them: when this is empty the function prints a one-line pointer to add to one of those files. It never edits them itself — many projects keep a hand-curated skills table there.

Returns nothing when the bundle could not be found or copied (the failure is logged).

setup(path::String = DB_PATH; root = nothing)

Interactively setup the connection.yml file for PormG. This will prompt for database adapter, name, and connection details. For PostgreSQL it also asks which driver opens the connections, LibPQ (the default) or Postgres. A typed answer is written as postgres_driver: into each environment block; an empty answer writes no key, and the driver is then chosen at load time (PORMG_POSTGRES_DRIVER, then LibPQ) (#1676).

root means what it means for Configuration.load: the project root path is relative to. With it, the folder is joinpath(root, path) and the AI skills (if you accept them) go into root, from any working directory. Without it, a relative path is resolved against pwd(), and the skills go into pwd() even when path is absolute; the prompt names the folder before writing to it. An absolute path with root is refused, since joinpath would discard the root (#1294).

const APP_ROOT = normpath(joinpath(@__DIR__, ".."))
PormG.setup("db"; root = APP_ROOT)   # APP_ROOT/db/connection.yml, APP_ROOT/.github/skills/…

upgrade_guide([io::IO = stdout]; from, to = <current code>, structured = false)

Print the change-log entries a consuming app must work through to move from PormG version from up to to. The default to covers the current code — every released entry plus the uncut entries still marked Unreleased that the install is running (release-train model), so a consumer dev'ing PormG at HEAD sees work that has not been stamped with a release number yet. Pass to = pkgversion(PormG) to scope to the installed release only. Reads the upgrading/ log shipped with the resolved PormG install — one file per entry — so the scope is accurate against the version your app actually depends on, not a latest-on-GitHub copy that may not match.

Entries print newest-first; each keeps its "How to find the calls to migrate" grep and its before → after, with the PormG-internal per-app rollout table trimmed off.

from is required — pass the PormG version your app currently depends on. Both from and to accept a VersionNumber or a version string (v"0.2" or "0.2").

Pass structured = true to get the entries back as data instead of printing — a Vector of (; version, title, body) named tuples, newest-first — for programmatic consumers.

Throws

• ArgumentError when from is omitted — a mistake in the call itself.
• InvalidConfigurationError (<: ConfigurationError <: PormGError) when the bundled upgrading/ log is missing or holds no entries — a broken install, which would otherwise print "nothing to port" and read as being up to date.

Examples

julia> using PormG

julia> PormG.upgrade_guide(from = v"0.1")            # everything up to the installed version

julia> PormG.upgrade_guide(from = "0.2", to = "0.3")

julia> entries = PormG.upgrade_guide(from = v"0.1", structured = true);

@pormg_debug
@pormg_debug condition

Contributor-facing breakpoint hook, scattered through PormG's source. It expands to nothing, so it costs a package user exactly nothing at runtime.

To make the call sites live while debugging PormG itself, ]dev PormG, load Revise and Infiltrator before PormG, then redefine the macro to expand to a real breakpoint and edit the target file so Revise re-parses it — macros expand at parse time, so redefining the macro without touching the call site has no effect. The step-by-step recipe is in Contributing & Debugging.

@pormg_debug false                  # inert; flip to `true` (or a real condition) to fire
@pormg_debug model.name == "result" # fires only for that model, once wired up

PormG.Kernel

Layer 1 — the shared vocabulary. Everything in here is a noun: abstract types, constants, the error taxonomy root, and the two dependency-free helpers (_emsg, config) that the rest of the package needs before it can define anything of its own.

The invariant

Kernel imports nothing from PormG. Every other submodule may import from Kernel, and Kernel is included first, so "may I use this name?" stops depending on where a file happens to sit in PormG.jl's include chain.

That ordering used to be implicit, and it bit: the #231 error taxonomy was defined in src/querybuilder/exceptions.jl (included at step 11; that file is now error_funnels.jl and holds only message-composing funnels), so Models, Configuration, Dialect and ConnectionPool — all included earlier — could not name a single one of its types. Each submodule resolves import PormG: … at include time, so "defined later in the module body" means "does not exist yet".

What does NOT belong here

Behavior. In particular Backend.jl stays in PormG, even though it looks like core: the weakdep extensions define their methods as PormG.backend_execute(…) = …, and Julia only accepts a qualified method definition on the module that owns the binding. Moving those generics here would break every extension method with function Kernel.backend_execute must be explicitly imported to be extended — and it would fail at using LibPQ / using SQLite, not at using PormG, so precompiling the package would not catch it. The generics dispatch on the PormGPostgres / PormGSQLite markers defined below, which works fine from where they are.

Rule of thumb: Kernel holds the nouns, PormG keeps the verbs.

AmbiguousFieldError(msg) <: FieldAccessError <: UsageError <: PormGError

A name on this query means two things at once, so it has no single meaning and PormG refuses to guess. Two cases raise it:

• a __ path's first segment names both a declared CTE and a model field, reverse accessor, many-to-many field, JSONField or cjoin/on() join path (#492). The message prints the CTE("<name>", "<path>") spelling that selects the CTE side;
• a filter(...) key names both a model field and a projection alias of the same name that projects something other than that column, e.g. values("points" => Sum("points")) followed by filter("points" => …) (#703). The message prints the rename that resolves it.

Deliberately its own type rather than an UnknownFieldError: the name is known twice, not unknown, and the remedy differs. A consuming app's typo handler should not also fire when a schema change makes an existing name collide.

BackendCapabilityError(msg) <: PormGError

The active backend cannot do this — a PostgreSQL-only lookup on SQLite (JSONB containment, iunaccent_*, the regex family), full-text search on SQLite (the @search lookup and the Search* functions, #31), an explicit window frame= on SQLite, bulk_copy on SQLite, with_advisory_lock(...; on_missing_lock = :error) on SQLite, a DecimalField wider than the 15 digits SQLite stores exactly (raised by makemigrations, #648), or a SQLite library older than a feature requires. The query is well-formed and the configuration is fine; the remedy is to change the request or the backend — each message names the specific way out. Split out of UnsupportedConnectionError in the pre-publish naming pass — capability limits are a user-facing contract, not an internal error.

ConfigurationError <: PormGError  (abstract)

Umbrella for configuration failures — the connection setup, and the install PormG runs from — catch it to get every case below. Like FieldAccessError, this is an abstract mid-node rather than a throwable type, so the pre-existing MissingConfigurationError can live inside the bucket instead of beside it. catch ConfigurationError must not have holes; that class of surprise is the reason this taxonomy exists.

Subtypes: InvalidConfigurationError, WritesDisabledError (the change_data: false write switch — its remedy is a config edit), and Configuration.MissingConfigurationError (a missing folder/connection.yml, or a selected environment with no matching block).

DatabaseError <: PormGError  (abstract)

Umbrella for failures raised by the database itself, once a statement has reached it — as opposed to UsageError, which reports PormG refusing the call. catch DatabaseError covers every case below without naming a driver package.

Subtypes: IntegrityError (a constraint said no), OperationalError (transient — the connection dropped, a deadlock, a lock timeout), and StatementError (the statement itself was rejected, plus anything the backend could not classify).

All three are built from structured fields rather than a msg::String, so read them with error_message. The reason the database gave is carried as data (#987), each field nothing when the driver does not report it:

The rendered text never carries a value — error_message, showerror and string alike — so it is safe to return to a client. The database's DETAIL, HINT and LINE n: excerpt quote the row, so they live only in the driver's own exception, kept in .cause for a caller that logs it to a trusted sink.

try
    M.Driver.objects.create("code" => "SEN")
catch e
    e isa IntegrityError && e.sqlstate == "23505" && return conflict(error_message(e))
    # Not `retry()`: after a dropped connection the row may already exist (#1042).
    e isa OperationalError && return service_unavailable(error_message(e))
    rethrow()
end

Connect-time failure is not here: it never reached a statement, and has been ConnectionPool.PoolConnectError under PoolError since #261.

DefinitionError <: PormGError  (abstract)

Umbrella for model-definition-time failures — catch DefinitionError covers both a bad field constructor argument (FieldValidationError) and a bad model/schema shape (ModelDefinitionError). They almost always surface together: one include("models.jl") can raise either, and a handler that names only one silently misses the other — which is exactly what the upgrade log's own #239 migration recipe did.

DoesNotExist <: PormGError

get() matched zero rows. Carries model_name and the rendered filters (structured — no msg field; read it with error_message). Often normal control flow: catch it to implement get-or-create-style logic.

FieldAccessError <: UsageError <: PormGError  (abstract)

Umbrella for field/accessor lookup failures — catch it to get UnknownFieldError ("no such field"), AmbiguousFieldError ("that name has two meanings here") and LazyTraversalError ("unsupported lazy traversal").

FieldValidationError(msg) <: DefinitionError <: PormGError

A field constructor was given an invalid argument — a kwarg of the wrong type, a max_length outside its permitted range, a default that does not satisfy the field's own contract, a choices shape that does not parse, or a field type that cannot serve as a primary key.

Raised while defining a model. Contrast InvalidValueError, which is raised while coercing a value on the insert/update path.

FilterError(msg) <: UsageError <: PormGError

An invalid filter shape — an unknown lookup, a list where one value belongs, an argument that shapes the SQL (an isnull flag) — or an operator misused on a JSON/subquery column. A bound value the filter refuses is an InvalidValueError instead (#988).

IntegrityError(adapter, cause; sqlstate, constraint, table, column, message) <: DatabaseError <: PormGError

A constraint rejected the statement — UNIQUE, FOREIGN KEY, NOT NULL, CHECK, or an exclusion constraint. This is the one database failure applications routinely handle rather than propagate, which is why it is its own type.

adapter is "PostgreSQL" or "SQLite"; cause is the driver's own exception, with the full text the server sent. On PostgreSQL this is derived from SQLSTATE class 23, so it is exact; on SQLite it comes from SQLite's own literal constraint messages. The other fields are the reason as data — see DatabaseError.

InvalidConfigurationError(msg) <: ConfigurationError <: PormGError

The configuration — or the install it runs from — is unusable or inconsistent: an unsupported adapter, an unknown connection key, a malformed extensions setting, an unsupported PostgreSQL extension, a model not bound to a connection (or bound to an entry whose pool was never built), a missing driver package (using LibPQ / using SQLite forgotten), an attempt to overwrite a static connection, or a missing or empty upgrading/ log bundled with the install (upgrade_guide, #639).

InvalidMigrationError(msg) <: MigrationError <: PormGError

The migration engine refused or could not complete an operation — a duplicate index name in a plan, an invalid answer to an interactive makemigrations prompt, or a migration-engine step that cannot proceed (no pending plan, an unparseable introspected DDL statement, a missing model file). The importer-pointed-at-the-wrong-backend case is BackendCapabilityError.

InvalidValueError(msg) <: UsageError <: PormGError
InvalidValueError(reason, kind; op, model, field, field_type, row) <: UsageError <: PormGError

A value was refused: it failed coercion/type validation on a filter, insert or update, an identifier failed the fail-closed safety check, or an interval/duration literal could not be parsed. Raised by the Models.format_*_sql coercion helpers, which every write and filter path calls (#239), and by a lookup's own checks on the value it binds — a @year bound, @family, a numeric JSON comparison (#988).

A refusal never contains the value it refused (#971): a bound value can be a password or any other secret, and an app may hand e.msg to an HTTP client. The message names where it happened — the operation, the field, the row of a bulk write — and why, and the value stays out of it.

Fields, beyond the rendered msg:

• kind::Symbol — what was wrong, as data: :type (a value of the wrong type), :format (text that does not parse), :range (parses, but out of bounds), :nul (a NUL character, #951), :json_nul (a NUL inside a JSON document, #954), :other for everything else.
• reason::String — the formatter's explanation, without any field or row.
• op, model, field, field_type, row — where it happened, nothing until a funnel knows. A formatter raises with the reason alone; the funnel that knows the field and row attaches them once (with_location).

LazyTraversalError(msg) <: FieldAccessError <: UsageError <: PormGError

An unprojected ForeignKey was read off a fetched row. PormG has no lazy FK traversal; the message steers the caller to up-front values(...) projection (#204).

MigrationError <: PormGError  (abstract)

Umbrella for migration-engine failures — catch it to get every case below, including a refused destructive plan.

Subtypes: InvalidMigrationError, and Migrations.DestructiveMigrationError (a destructive plan applied non-interactively without destructive=true).

ModelDefinitionError(msg) <: DefinitionError <: PormGError

A model or schema definition is invalid — more than one primary key, a duplicate related_name, a reverse accessor that shadows a field or contains __ / @, an illegal field name, a UniqueConstraint that names an unknown or many-to-many field, an unresolvable ForeignKey / ManyToManyField target, a Model(...) call given something that is not a PormGField, or a generated models file whose name cannot be its module's name (f1-models.jl, 1975.jl, Models.jl: the name without .jl must be a Julia identifier and not one the file imports, #1486).

MultipleObjectsReturned <: PormGError

get() matched more than one row — usually a data-integrity surprise rather than control flow. Carries model_name, the offending count, and the rendered filters (structured — no msg field; read it with error_message).

OperationalError(adapter, cause; sqlstate, constraint, table, column, message) <: DatabaseError <: PormGError

The database could not complete the statement for a reason outside the statement itself, and retrying may succeed — the connection dropped mid-query, a deadlock was detected, a serialization failure occurred, or a lock could not be acquired in time.

catch OperationalError is the retry signal, for work that is safe to repeat. A connection that dropped with no error code may have dropped after the server ran the statement, so an INSERT, UPDATE or DELETE may already have committed: repeat a read freely, but repeat a write only when a second run cannot change the outcome (#1042). fetch retries a dropped connection itself only when the statement provably never ran. PormG raises this error for with_advisory_lock acquisition timeouts too: contention is a runtime condition, not misuse. So does get_or_create on PostgreSQL when its insert returns no row and the row cannot be read back (#1299): usually the row the insert conflicted with was deleted in between, and the call retried can succeed (a trigger or rule that skips the insert fails every retry). The fields are those of DatabaseError.

PoolError <: PormGError

Abstract umbrella for connection-pool failures — catch PoolError to handle both saturation and connect failure without naming each. Subtypes: ConnectionPool.PoolTimeoutError (no connection became available in time) and ConnectionPool.PoolConnectError (the backend refused or dropped the connection).

Both concrete types keep their structured fields (adapter, pool_size, attempts, …) and their own showerror, so they do not use the uniform msg::String shape — read them with error_message.

The umbrella lives here rather than in ConnectionPool on purpose (#261). The taxonomy's rule is that a concrete subtype either lives in Kernel or has a dedicated Kernel-owned abstract umbrella above it — the root PormGError does not count, or the rule would be vacuous. The pool errors were the only pair satisfying neither, which is the same mid-include-chain trap that made #239 need the Kernel extraction (#255). Configuration is included before ConnectionPool and already reasons about pool failure, so it could not have named those types.

PormGArrayLiteral(literal::String)

An ArrayField value on its way to PostgreSQL — the wrapper the field's formatter returns (#28). literal is the array in PostgreSQL's own text form ({1,2}, {"a b",NULL}), already validated element by element and printed the way PostgreSQL's array_out prints it.

PormG binds this text as ONE parameter and lets the column type it, rather than handing a Julia vector to the driver: LibPQ and Postgres.jl each render a vector differently (LibPQ writes nothing and Inf as the words), and a bare vector already means "a list of values" to the parameter collectors, which expand it for @in. The wrapper is not an AbstractArray, so no collector can mistake it for one — the PormGBytes reasoning.

Layer 1 for the same reason as PormGBytes: Models produces it and QueryBuilder consumes it.

PormGBackend <: PormGAbstractType

The backend/dialect marker — the dispatch key for SQL rendering and driver selection.

Its subtypes, PormGPostgres, PormGSQLite and PormGMySQL, are what every dialect method dispatches on, so a function that renders SQL differently per backend is written as one method per engine rather than as a runtime branch. The values you actually hold are the concrete connection pools: PostgresConnectionPool <: PormGPostgres, SQLiteConnectionPool <: PormGSQLite and MySQLConnectionPool <: PormGMySQL.

PormGMySQL is MySQL 8.4 or newer (#1130): reads, writes, migrations and inspectdb. What it lacks is refused before any SQL is sent — see the MySQL Guide.

Deliberately not <: PormGSettings: that is the configuration/Settings type, and a pool carries none of its fields (#186).

Adding a backend means defining PormG.backend_* methods in a package extension — see Extending PormG.

PormGBytes(bytes::Vector{UInt8})

A binary payload on its way to the database — the wrapper BinaryField's formatter puts around a byte vector so the parameter collectors can recognize it (#296).

It exists because a bare Vector{UInt8} is indistinguishable from "a list of values", and both backends get that wrong in opposite ways:

• PostgreSQL — add_parameter!(::PormGPostgresParam, ::AbstractArray) pushes the vector through to LibPQ, which binds every parameter in text format and renders any vector as a PostgreSQL array literal. UInt8[0x00, 0xFF] reaches the server as the five characters {0,255}, and bytea's escape-format input parser accepts that string literally — so the column silently stores the ASCII of {0,255} instead of the two bytes. No error is raised.
• SQLite — add_parameter!(::PormGSQLiteParam, ::AbstractArray) expands an array into one ? per element, so an n-byte payload becomes n placeholders and the statement fails on a column-count mismatch.

Dispatching on the wrapper instead of on Vector{UInt8} keeps filter("x__in" => UInt8[1, 2]) expanding into an IN list as it always has — only values that came through a binary field are treated as one opaque blob.

Layer 1 on purpose: Models produces it and QueryBuilder consumes it, so neither can own it.

PormGError <: Exception

Root of PormG's semantic error taxonomy (#231). Every error PormG raises for a domain failure — an unknown field, an invalid value, a refused write, a pool timeout, a database rejection — is a subtype, so one catch clause covers the whole surface:

try
    M.Driver.objects.get("driverref" => "nobody")
catch e
    e isa PormGError || rethrow()
    @error "query failed" msg=error_message(e)
end

Use error_message to read a caught error: the structured subtypes carry typed fields rather than a .msg string.

Catch a narrower type when you want to act on one failure — DoesNotExist, IntegrityError, PoolTimeoutError — and the umbrellas (UsageError, FieldAccessError, PoolError, DatabaseError, ConfigurationError, MigrationError, DefinitionError) to group a family. UsageError is the one to catch for "PormG refused what I passed in": it is narrower than this root, and it keeps matching when a refusal moves between its members.

These are deliberately not <: ArgumentError: a clean break so callers match a type instead of string-matching a message. Plain Julia-level misuse (a missing kwarg, a missing path) still raises the stock Julia exception, because it is not a PormG domain error.

The type lives in PormG.Kernel — layer 1 — so every submodule can name it and its subtypes regardless of include order (#239). The full list is in the API reference.

PormGField <: PormGAbstractType

Base type for field definitions — CharField, IntegerField, ForeignKey, and the rest.

A field is a component of a model, not a kind of model: PormGField is a sibling of PormGModel, not a subtype, so it deliberately does not satisfy ::PormGModel signatures, which all read model-only attributes (#186).

Fields own their validation — a value is checked here, before any SQL is generated — and carry the formatter that coerces a Julia value on its way into the database (inserts, bulk writes, bound filter parameters). The way back is not the field's job: values read back are decoded by the representation table (value_parser), per canonical kind and backend.

See also PormG Field Types Reference.

PormGModel <: PormGAbstractType

Base type for model definitions — one table, its fields, and its relations.

Instances are produced by Models.Model("table_name", field = FieldType(...), ...) and collected by @import_models / set_models. Dispatch on it when you are writing code that takes "any model", such as a framework extension that registers its own tables.

See also PormGField, Defining Models in PormG, Extending PormG.

ProtectedError(msg) <: PormGError

A delete() was refused because other rows reference the target through a ForeignKey declared with on_delete = PROTECT (or RESTRICT). Nothing about the call is malformed — the data forbids it, and the remedy is to delete or reassign the referencing rows first. Mirrors Django's ProtectedError/RestrictedError; previously filed under the long-tail QueryBuildError, which made this case indistinguishable from a malformed delete.

QueryBuildError(msg) <: UsageError <: PormGError

Structural/API misuse while building a query — joins, CTEs, projection, ordering, window and bulk configuration, and the like. The default bucket for query-builder misuse that isn't one of the sharper categories below.

SQLObject <: PormGAbstractType

Base type for the query state itself — the accumulated filters, projections, joins and ordering that a query is built from.

The concrete type you hold is SQLObjectQuery. You rarely name SQLObject in application code; it appears when a helper accepts "the underlying query object", e.g. the mq.object handed to a subquery or CTE constructor.

See also SQLObjectHandler, Architecture & Request Flow.

SQLObjectHandler <: SQLObject

Base type for the chainable wrapper around a query — the thing Model.objects returns and .filter(...), .values(...), .list() hang off.

The concrete type is ObjectHandler; its fluent methods are synthesized by getproperty, so they have no bindings of their own and ?query.filter cannot work — the full method reference lives on object instead (?object).

A query is a Tables.jl table

A query is a Tables.jl source (#1344), so any Tables.jl sink reads it: DataFrame(query) after using DataFrames, CSV.write(io, query), Tables.rowtable(query). Each read executes the query once.

The columns carry the same values list() returns: a temporal column or expression alias holds ZonedDateTime / Date / Time / CompoundPeriod values on every engine, not the driver's raw text (#582). To keep the engine's own text on purpose, project it as text: ToChar(...) or Cast(F(col), "TEXT").

using DataFrames
query = M.Result.objects.filter("raceid__year" => 2020).
    values("driverid__forename", "constructorid__name", "laps")
df = query |> DataFrame

See also SQLObject (the state it wraps).

StatementError(adapter, cause; sqlstate, constraint, table, column, message) <: DatabaseError <: PormGError

A statement failed to execute — invalid SQL, an unknown table or column, a type the backend would not accept, or insufficient privileges. Also the landing type for any failure on the database path that could not be classified, so catch DatabaseError never has a hole.

Usually a bug to fix rather than a condition to handle. The driver's exception is in .cause and the fields are those of DatabaseError; the SQL text is deliberately not stored, because it can embed user data (the @error … sql=… log sites already surface the statement where that is appropriate).

The wording says could not execute, not the database rejected this, on purpose. Being the unclassified fallback means a PormG-internal fault on the statement path can land here too — the SQLite worker's malformed-payload invariant, for one, and get_or_create on SQLite finding no row after its insert reported success (#1299) — and claiming the server refused something it never saw would send a reader hunting for a SQL bug that does not exist.

TransactionError(msg) <: PormGError

The transaction API was used in a way that cannot work — atomic(durable=true) or without_foreign_keys nested inside an open transaction on the same database, or an operation on a model bound to one connection attempted while a transaction is open on another.

Not a DatabaseError: nothing was sent, and the database is not involved. Every case is caught before any statement is issued. A deadlock or a rollback the server forces is an OperationalError instead.

Introduced in #268 so the two checks stop reporting as unrelated types (QueryBuildError said "query shape" for what is a transaction-nesting mistake; InvalidConfigurationError said "your config is wrong" when the config was fine and the call pattern was not).

UnknownFieldError(msg) <: FieldAccessError <: UsageError <: PormGError

A field, alias, column, or __ lookup path does not exist on the model or the projected row.

UnsafeMutationError(msg) <: UsageError <: PormGError

An UPDATE or DELETE was requested without a filter (or in another unsafe shape) and refused.

UnsupportedConnectionError(msg) <: PormGError

Two cases, each named by its message. A connection object that is none of PormG's engine pools reached an execution path — a PormG internal dispatch bug; the message asks the user to report it. Or MySQL has no faithful SQL for what was asked: a Cast to a type with no MySQL target that holds its meaning (interval, bytea, a type of your own), or a default on a binary column. The catchable replacement for the internal ErrorException from #197.

Narrowed in the pre-publish naming pass: it previously also covered backend capability limits (now BackendCapabilityError) and models not bound to a connection (now InvalidConfigurationError, whose docstring always claimed that case) — three disjoint remedies distinguishable only by message text, which is the failure mode this taxonomy exists to remove.

UsageError <: PormGError  (abstract)

Umbrella for "PormG refused the call for what it asked" — catch it to answer a bad request without naming the concrete refusal. Its members:

• FieldAccessError — no such field, an ambiguous name, an unprojected relation;
• FilterError — an invalid filter shape;
• InvalidValueError — a value PormG refused;
• QueryBuildError — a malformed query;
• UnsafeMutationError — an UPDATE or DELETE in an unsafe shape.

What it promises: PormG refused the call for what it asked, not because the database failed, and the remedy is to change the call — retrying it unchanged is not one. Catching the umbrella keeps working when a refusal moves between its members, which has happened on more than one train (#576, #971, #988).

try
    M.Result.objects.filter("points__@gte" => user_supplied).list()
catch e
    e isa UsageError || rethrow()
    return bad_request(error_message(e))
end

It does not promise that nothing reached the database. Most refusals are raised while the query is built, but a few come after statements of the same call ran:

• a bulk write validates each row as it goes, so a later row is refused after earlier chunks were sent (and, with allocate_pk, after ids were drawn);
• delete() probes the related tables, and may already have run its cascade UPDATEs and DELETEs;
• get_or_create reports a missing UNIQUE constraint on its lookup fields when the database rejects the ON CONFLICT.

Without a transaction of the caller's, the call's own transaction rolls those statements back. Inside run_in_transaction they are the caller's to roll back: a block that catches a bulk's or a delete()'s UsageError and returns normally commits what they ran (after a get_or_create refusal, PostgreSQL has already aborted the transaction), so rethrow it, or catch it outside the block.

Deliberately not members, because a bad request is the wrong answer to them: BackendCapabilityError (the remedy is the backend), ProtectedError (the data forbids the delete), UnsupportedConnectionError (a PormG bug), and the DatabaseError family (the database itself said no).

WritesDisabledError(msg) <: ConfigurationError <: PormGError

The connection is not permitted to insert/update/delete — its settings carry change_data: false. The remedy is a configuration edit (connection.yml), which is why this lives under ConfigurationError. Renamed from PermissionError in the pre-publish naming pass: that name read as OS/file permissions to some audiences and database GRANTs to others, while the actual meaning is PormG's own write switch.

error_message(e::PormGError) -> String

The text of any PormG error, as a String.

Use this instead of e.msg. Seven subtypes are built from structured fields and have no msg field at all — DoesNotExist, MultipleObjectsReturned, ConnectionPool.PoolTimeoutError, ConnectionPool.PoolConnectError, and the three DatabaseError subtypes (IntegrityError, OperationalError, StatementError) — so e.msg throws a FieldError on exactly the errors a caller is least likely to have tested against (#261, #268).

try
    M.Result.objects.values("bad alias!" => "points").list()
catch e
    e isa PormGError || rethrow()
    @error "PormG rejected the query" msg=error_message(e) type=typeof(e)
end

Defined via showerror, which every subtype implements, so it stays correct for subtypes added later without needing a new method. For subtypes that use the generic showerror above, the result is exactly e.msg (it prints that field verbatim, and _emsg has already normalized any ANSI at construction). Subtypes with their own showerror return that richer rendering instead — e.g. Configuration.MissingConfigurationError and Migrations.DestructiveMigrationError both carry a msg yet prefix it with the error name, so error_message is a superset of .msg, never a subset.

register_ignore_tables!(tables) -> Vector{String}

Register table-name patterns that schema introspection (convert_schema_to_models, import_models_from_*, makemigrations) should always skip — e.g. a consumer framework's own infrastructure tables. Additive and idempotent (deduplicated); returns the full list.

Intended to be called once at load time, typically from a package extension's __init__:

# in YourPkgPormGExt.__init__
isdefined(PormG, :register_ignore_tables!) && PormG.register_ignore_tables!(["yourpkg_jobs"])

PormG.Functions

The SQL function library, and the index of it. Because these names are not exported into Main by using PormG, ?Sum answers nothing until you import them — so this docstring is the entry point: it lists every constructor and where each family is documented.

Aggregate — Sum, Avg, Count, Max, Min — see Filters and Aggregates

Conditional — Case, When — see Functions and Dates

Window — WindowOver, WindowSpec, Rank, DenseRank, RowNumber, Lag, Lead, FirstValue, LastValue, NthValue — see Window Functions

String — Concat, Lower, Upper, Length, Replace, Trim, LTrim, RTrim, LPad, RPad, Substr

Math — Abs, Round, Floor, Ceil, Sqrt, Exp, Ln, Power, Mod

Type / value — Cast, Extract, ToChar, Value, Coalesce, Greatest, Least, NullIf

Full-text search (PostgreSQL) — SearchQuery, SearchVector, SearchRank, SearchHeadline — see Full-Text Search

They live here rather than at the top level because the names are generic enough to collide with Base and user code (Sum, Count, Max, Replace, Round, Length…), so the library has exactly one home and you opt in explicitly:

using PormG, PormG.Functions          # brings Sum, Count, … into scope
using PormG.Functions: Sum, Count     # …or just the ones you use
# or qualify without importing:
M.Result.objects.values("n" => PormG.Functions.Count("resultid"))

Q, Qor, F, Exists, OuterRef, Subquery, CTE and Interval are not part of this library — they are query primitives and stay on the top-level using PormG surface.

Interval(period)
Interval(duration_string)

Explicit duration wrapper for F-expression date arithmetic (#25). Holds a Julia Dates.Period / Dates.CompoundPeriod, or parses a portable time-duration string ("HH:MM:SS(.fff)", "M:SS", or bare seconds) into a time-only CompoundPeriod.

Interval(...) is interchangeable with a bare period wherever date arithmetic is used — F("date") + Interval(Month(1)) is identical to F("date") + Month(1). The string form is the escape hatch for time-based intervals: F("logged_at") + Interval("01:30:00").

Note: the name is shared with the Intervals.jl ecosystem — if you also using Intervals, disambiguate as PormG.QueryBuilder.Interval.

ObjectHandler <: SQLObjectHandler

The query handler Model.objects returns — the object every fluent chain is built on.

Its methods are synthesized by getproperty rather than being real fields, which means the Julia REPL cannot help you with them: ?query.filter does not work (it errors, for any Julia value). The complete fluent reference lives on object — type ?object — and on the API reference.

query = M.Driver.objects          # an ObjectHandler
query.filter("nationality" => "Brazilian")
rows = query.values("forename", "surname").list()

Chainable methods mutate the handler and return it; terminal methods execute and return a result. Use .copy() when you need to branch a chain without disturbing the original.

A model-aware row returned by list(), first(), and get().

Wraps a Dict{Symbol, Any} and remembers which model produced it, enabling dot-access to fields and many-to-many relationship accessors.

WindowSpec <: SQLType

The OVER (...) clause of a window function, in structured form.

Fields

• partition_by::Vector — the grouping the window restarts on. Empty means one window over the whole result set.
• order_by::Vector — the ordering inside each window, stored as given: a "-points" entry stays "-points", and the - prefix is resolved to DESC at build time.
• frame::Union{String,Nothing} — an explicit frame clause, or nothing for the SQL default (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). PostgreSQL only. Held in the grammar WindowOver documents: WindowOver stores its own rebuilt spelling, and a frame assigned here directly is parsed when the query is built, raising InvalidValueError if it is outside that grammar.

Build one with WindowOver, which validates and coerces its arguments; the @kwdef constructor is exported for the rare case where you want to assemble or mutate a spec directly. The same spec can be reused across several window functions in one query.

See also Window Functions.

first(objct::SQLObjectHandler; show_query::Symbol = :execute)

Return the first PormGRow matching the current query, or nothing if no records match.

A slice the caller set is honored: first takes the first row of the slice, so q.limit(0).first() is nothing and q.offset(10).first() is the eleventh row (#1053).

Like every read terminal (count, exists, list, get), first executes on an internal copy of the handler — the limit(1) it needs is applied to that copy, never to objct. The handler is reusable afterwards, including for .update():

q = M.Driver.objects
q.filter("nationality" => "British")
driver = q.first()                      # q is unchanged — no limit leaks in
q.update("nationality" => "English")    # still valid on the same handler

first(objct::SQLObjectHandler, T::Type; show_query = :execute) -> Union{Nothing, T}

first as a typed read (#1352): the first row built into T, or nothing. The projection and conversion rules are list(T)'s.

get(objct::SQLObjectHandler, filters...; show_query=:execute) -> PormGRow

Return exactly one row matching the query filters.

Filters can be passed inline or applied with .filter() before calling .get():

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

Raises DoesNotExist when no rows match and MultipleObjectsReturned when more than one row matches.

Like every read terminal, get executes on an internal copy of the handler: inline filters do not persist on objct, so the handler can be reused afterwards with its original filter list intact.

A slice the caller set is honored: get looks for its one row inside the slice, so q.limit(0).get() raises DoesNotExist (#1053). Inline filters join the WHERE clause before the slice applies, the same as a filter() call after limit().

get(objct::SQLObjectHandler, T::Type, filters...; show_query = :execute) -> T

get as a typed read (#1352): exactly one row, built into T. Raises DoesNotExist and MultipleObjectsReturned as get does.

row = M.Driver.objects.get(DriverRow, "driverref" => "senna")

last(objct::SQLObjectHandler; show_query::Symbol = :execute)

Return the last PormGRow matching the current query, or nothing if no records match.

The mirror of first: it inverts the query's ordering and takes one row. When an order_by(...) is set, last() returns the row that first() would return under the reversed ordering. When no ordering is set, it falls back to primary-key descending, so last() is always well-defined (matching Django). Like every read terminal, it runs on an internal copy — the inverted ordering and limit(1) never leak into the caller's handler.

On a query with limit() or offset() set, last raises QueryBuildError, as Django's does: reversing the ordering changes which rows the slice holds (#1053).

last(objct::SQLObjectHandler, T::Type; show_query = :execute) -> Union{Nothing, T}

last as a typed read (#1352).

Abs(column)

Returns the absolute value of a number. Over an integer it is an integer of the same type on both engines; over any other number PostgreSQL computes a numeric (#1147), and the value reads back as the operand's type on both engines: a Float64 over a float, a Decimal over a decimal (#1121).

Avg(x; distinct::Bool = false)

Aggregate AVG(x) — the mean of x across the group.

x is a field path ("points", "driverid__surname"), an F expression, or a nested function object. With distinct = true it renders AVG(DISTINCT x).

Like Count and Sum — and unlike Max/Min — AVG is covered by the to-many fan-out guard (#74): a join that multiplies rows would silently inflate the mean, so PormG raises instead. Passing distinct = true is an explicit opt-in and is exempt.

The mean reads back as a Float64 over integers or floats and as a Decimal over a DecimalField, on both engines (#1121), as Django's Avg types it. PostgreSQL computes avg(integer) as a numeric, which PormG reads as the Float64 it means; SQLite computes a decimal mean as a double, which PormG reads as a Decimal to 15 significant digits.

An Avg over a BooleanField is refused with a QueryBuildError, as Sum is (#953): the share of true rows is Avg(When("is_active" => true, then = 1, otherwise = 0)).

The mean of a duration is a duration. An Avg over a date, a timestamp, a time or a UUID is refused with a QueryBuildError on every engine (#1387): none is a number. Average the number you mean, such as Avg("date__@year").

See also Filters and Aggregates.

CTE(name::AbstractString, path::AbstractString; desc::Bool = false) -> CTEReference

Reference a column of a CTE declared with .with(...) as an object rather than as the ordinary "<name>__<path>" string. Both spellings mean the same column and render identical SQL; the object is the disambiguator (#444, #492).

A CTE name that collides with nothing needs no object — values("ev__sku") is the idiomatic spelling. Write the handle when the CTE's name also names something on the model, because then the shared __ path has two readings and PormG refuses to choose:

driver_totals = M.Result.objects
driver_totals.values("driverid", "n" => Count("resultid"))

q = M.Result.objects
q.with("driverid" => driver_totals)       # "driverid" is ALSO a ForeignKey of Result
q.values("points", "driverid__surname")   # → AmbiguousFieldError: the FK, or the CTE?
q.values("points", "n" => CTE("driverid", "n"))   # the CTE's column, explicitly

It is also required on the right of a filter pair, where a bare string is a value rather than a column: filter("surname" => CTE("d91", "surname")) correlates, while filter("surname" => "d91__surname") compares surname against that literal text — quietly matching nothing on a text column, and raising FilterError on a numeric one.

A handle sits on either side of a comparison operator (==, !=, <, <=, >, >=), as F(...) does: filter(CTE("driverid", "n") > 5) and filter(F("points") == CTE("d91", "points")) (#1529). It takes + - * / on either side as F(...) does too — CTE("best", "best") - F("points"), 2 * CTE("driverid", "n"), CTE("s", "first") + Day(7) — and a date, timestamp or duration column is typed as one, so its arithmetic is date arithmetic on every engine (#1539).

The second argument is a path, not a bare column, and carries the same __ vocabulary the rest of PormG uses — a hop out of the CTE through a projected ForeignKey, a JSON sub-path, or an operator suffix:

q.filter(CTE("ev", "sku") => "ABC")                       # plain column
q.values("s" => CTE("ev", "parent__sku"))                 # hop through a projected FK
q.filter(CTE("ev", "meta__driver") => "senna")            # JSON sub-path
q.filter(CTE("ev", "seen__@yyyy_mm__@lte") => "1991-10")  # operator suffix
q.order_by(CTE("monaco_stats", "total_points"; desc = true))

An unaliased projection is named by joining the two with a double underscore — values(CTE("parent", "sku")) emits the output column parent__sku, byte-identical to what values("parent__sku") produces.

SQL functions, aggregates and window clauses accept a handle wherever they accept a field path — Lower(CTE("ev","sku")), Cast(CTE("ev","qty"), "text"), Sum(CTE("ev","qty")), Rank(over = WindowOver(partition_by = CTE("ev","sku"))).

desc = true is meaningful in order_by(...) and in a window's order_by; anywhere else it raises a QueryBuildError. Both take the string's - prefix too, so it is a convenience rather than the only way to order descending. A CTE column cannot be referenced from on(...), cjoin(...) or cjoin_on(...) — those clauses target model relations — however it is spelled, including as the operand of an F comparison.

See also Subqueries and CTEs.

Case(conditions; default = "NULL", output_field = nothing)

A SQL CASE … END expression: evaluate each When branch in order and return the first match.

Arguments

• conditions: a Vector of When branches, or a single bare When.
• default: the ELSE branch. Defaults to the string "NULL", which is emitted as the SQL literal NULL — it is not a bound parameter, so pass a Julia value (0, "") when you want a real default. missing is emitted as NULL too, and a column expression (F("grid")) renders as that column.
• output_field: the result type. Accepts a PormGField instance (e.g. CharField(), whose .type is used; a DecimalField casts to its numeric(max_digits, decimal_places), #1221) or a raw SQL type string. Renders as a ::type cast on PostgreSQL and a CAST(...) on SQLite — date(...) for a date, and a time type raises BackendCapabilityError there (#822). In a CTE body it is also the column's type; without it the type is inferred from the branches, and branches that do not agree raise QueryBuildError — see How a CTE Column Is Typed. A text, integer or scaled numeric(p, s) output_field refuses, branch by branch, the operands Cast refuses (#1028, #1040, #1217): round a float branch first (then = Round("points", 1) at numeric(10,1)).

Usable anywhere a column expression is — in values(), nested inside Sum, as a filter right-hand side, and in .update().

Without output_field, a projected Case reads back as the type its branch values and default agree on, on every engine, as Coalesce does. Over a DurationField, Case([When("grid" => 1, then = F("fastestlaptime"))]) is a Dates.CompoundPeriod on SQLite as on PostgreSQL, not the stored interval text (#1262). Values of different types read back as the driver delivers them.

using PormG.Functions: Case, When
using PormG.Models: CharField          # field types are not part of PormG.Functions

"podium" => Case([
    When("positionorder" => 1, then = "win"),
    When("positionorder__@lte" => 3, then = "podium"),
], default = "none", output_field = CharField())

See also When, Functions and Dates.

Cast(expression, type)

Casts a column or expression to a SQL type — PostgreSQL (x)::type, SQLite CAST(x AS type). SQLite has no time types: a date target renders date(x) there, and timestamp, timestamptz, time and interval raise BackendCapabilityError (#822).

type is preferably a field object (Cast("grid", BigIntegerField())), which renders in each engine's own spelling. A DecimalField casts to its own precision and scale (#1221): Cast(x, DecimalField(max_digits = 10, decimal_places = 1)) is Cast(x, "numeric(10,1)"), and DecimalField() is numeric(10,2). A CharField given a max_length casts to that length (#1252): Cast(x, CharField(max_length = 3)) is Cast(x, "varchar(3)"), while CharField() declares none and casts to unbounded text. A SlugField or URLField casts to the length its column has (#1438), varchar(50) / varchar(200) by default; an EmailField declares none. An ArrayField casts to its element's type with [] (ArrayField(CharField(max_length = 3)) is "varchar(3)[]"), on PostgreSQL only: SQLite and MySQL raise BackendCapabilityError. A string is accepted when it is a single type name ("integer", "bigint", "text", "timestamptz"), one of the multi-word names "double precision", "character varying", "bit varying", "timestamp with time zone" (and without, and the time forms), optionally followed by a size (n) or (n, m) — "numeric(10,2)", "varchar(20)" — and, on PostgreSQL only, array brackets ("integer[]"). Anything else raises InvalidValueError when the expression is built, on both engines: a type name is a keyword in the SQL, so it cannot be a bind parameter, and PormG only writes a spelling it has parsed. The same rule applies to every output_field= string.

The expression may be a column path, an F(...) expression, another function or a Subquery(...) — Cast(Subquery(s), "date") casts the one value the subquery returns per row.

A cast is checked against what it means on each engine (#1147): the engine that would compute a different value raises QueryBuildError when the query is built, and the others build it.

• to text (CharField(), "text", …): a float, a decimal, a timestamp, an interval or a JSON document builds everywhere, each engine writing its own text ('25' or '25.0'). A boolean means 'true'/'false', and SQLite, which writes '1', refuses it (#1028);
• to an integer, a float, a decimal or a numeric function means the nearest integer, and SQLite, whose cast truncates, refuses it (#1028). Say how to round to build it everywhere: Cast(Round(x), IntegerField()), Floor(x) or Ceil(x). A boolean casts to 1/0 and passes;
• to numeric(p, s) (or numeric(p), scale 0), an operand with more than s digits after the point (a float column, a float literal with more places (#1050), text, a numeric function, a decimal with more places) means the value rounded to the scale, and SQLite, which keeps every digit, refuses it (#1040). Round it first, Cast(Round(x, 2), "numeric(10,2)"), or cast to an unscaled "numeric". A DecimalField target is its numeric(max_digits, decimal_places);
• to a single-precision float ("real", "float4", "float(p)" for p ≤ 24), any value means the value rounded to a 4-byte float, a Float32, and SQLite, which keeps the 8-byte double, refuses it (#1215). Cast to "float8" or FloatField(), a Float64 on every engine;
• to text of a declared length ("varchar(n)", "char(n)", "char", which is char(1), PostgreSQL's "bpchar(n)" and "nchar(n)", CharField(max_length = n), SlugField, URLField), any value means the text cut to n characters, and SQLite, which enforces no length, refuses it (#1252). Cast to "text" or CharField() and cut the fetched value in Julia.

using PormG.Functions: Cast, Round
using PormG.Models: IntegerField

M.Result.objects.values("resultid", "points_int" => Cast(Round("points"), IntegerField()))
M.Result.objects.values("resultid", "points_num" => Cast("points", "numeric"))

See also Functions and Dates and Rounding.

Ceil(column)

Returns the smallest integer greater than or equal to a number. Over an integer it is that integer, of the same type, on both engines; over any other number PostgreSQL computes a numeric (#1147), and the value reads back as the operand's type on both engines: a Float64 over a float, a Decimal over a decimal (#1121).

Coalesce(args...; output_field=nothing)

Returns the first non-null value in the list of arguments. It takes two or more arguments: fewer raise QueryBuildError when the expression is built (#859), since one argument is the argument itself.

A string argument is a column path. A number, a Bool, or a Date/DateTime/ZonedDateTime/Time is a literal that is bound as a parameter (Coalesce("points", 0) means Coalesce("points", Value(0))). Wrap a string literal in Value. Any other value raises QueryBuildError when the expression is built (#705).

The value reads back as the column's own Julia type on both engines when every argument is of one type — Coalesce("date", "fp1_date") is a Date, not SQLite's stored text. When the argument types differ, the value comes back as the engine delivers it (#824): on SQLite, the stored value of the argument that won.

output_field casts the result to the type it names, on both engines (#852): Coalesce("number", 0; output_field = "integer") renders (…)::integer on PostgreSQL and CAST(… AS INTEGER) on SQLite, so the value, a filter on it, and a CTE column typed by it all agree. On SQLite a date renders date(…), and the other temporal types and arrays raise BackendCapabilityError, as for Cast. A text or integer output_field refuses the operands Cast refuses (#1028): over a float, round first (Floor("points")). So does a scaled numeric(p, s) one (#1040).

Concat(expressions; output_field=nothing)

Concatenates multiple strings or columns.

A NULL operand is skipped, read as an empty string, on both engines, so the result is never NULL: Concat(Value("#"), "number", Value(" "), "surname") is "# Senna" for a driver with no number. This follows Django's Concat. PostgreSQL renders CONCAT(…), which skips a NULL itself, and SQLite renders COALESCE(operand, '') || …. To get NULL when a column is NULL, test it explicitly: Case(When("number__@isnull" => false, then = Concat(…))) — a Case with no match is NULL.

Text, integer, date, time and uuid operands read the same on both engines. Any other operand (a boolean, a float, a decimal, a timestamp, an interval or a whole JSON document) raises QueryBuildError on every engine, because the query does not say which text it means (#1027, #1028, #1147): a literal (true, 1.5, a Decimal) when the expression is built, a column or an expression of those types when the query is. Say it: Cast(x, "text"), which writes each engine's own text, a Case for a boolean, ToChar(x, "YYYY-MM-DD HH:MI:SS") for a timestamp, or format the fetched value in Julia.

The result is text on both engines, so output_field, when given, must be a text type (CharField(), TextField(), "text", "varchar(20)"). Any other type raises InvalidValueError when the expression is built. Concat renders no cast, so a number declared there would be a type the SQL never applies. To get a number, cast the result explicitly: Cast(Concat(…), "integer").

Count(x; distinct::Bool = false)

Creates an aggregate COUNT function object for use in query building.

Arguments

• x: The column or expression to count.
• distinct::Bool = false: If true, counts only distinct values of x.

Examples

# Count just when other_model_id is distinct  
query = MyModels.model_test |> object;
query.filter("id__@gte" => 1)
query.values("id", "count" => Count("other_model_id", distinct=true))
df = query |> DataFrame

DenseRank(; over::WindowSpec = WindowOver())
DenseRank(over::WindowSpec)

Window DENSE_RANK() — like Rank, but without gaps after ties: two rows tied for 1st are both 1 and the next row is 2, not 3.

Use it when you want "how many distinct values outrank this one", and Rank when you want a true finishing position.

See also RowNumber, Window Functions.

Exists(query::SQLObjectHandler) -> ExistsObject

Wrap a subquery as a SQL EXISTS predicate. Renders as EXISTS (SELECT 1 … LIMIT 1), so it answers "is there at least one match?" without counting or fetching the child rows.

Correlate the subquery to the current outer row with OuterRef; the result can be used two ways.

As a filter predicate — pass it positionally to filter, alongside ordinary pairs or inside Qor:

# Results whose driver set a lap under 90 s in that same race
fast_laps = M.Lap_times.objects.filter(
    "raceid"             => OuterRef("raceid"),
    "driverid"           => OuterRef("driverid"),
    "milliseconds__@lte" => 90_000,
)

n = M.Result.objects.filter(Exists(fast_laps)).count()

As a projected boolean column — pair it with an alias inside values:

standings = M.Driver_standings.objects.filter("driverid" => OuterRef("driverid"))

query = M.Driver.objects
query.values("surname", "has_standings" => Exists(standings))

A projected Exists reads back as a Bool on both engines; SQLite evaluates it to 0/1, which PormG reads back as a Bool (#965).

A limit() or offset() on the subquery is honored: Exists(sub.limit(0)) is always false, and Exists(sub.offset(2)) asks whether sub has a third row (#1053). Its order_by() is dropped, because an EXISTS answer cannot depend on it.

See also Subquery for the scalar (single-value) form and Subqueries and CTEs.

Exp(column)

Returns the exponential value (e^x) of a number.

It reads back as a Decimal over a DecimalField and as a Float64 over an integer or a float, on both engines (#1121), as Django's Exp types it. PostgreSQL computes it as a numeric.

Extract(column, part)

Extracts a component ("year", "month", "dow", …) from a date/time column.

part is case-insensitive and must be a PostgreSQL EXTRACT field: CENTURY, DAY, DECADE, DOW, DOY, EPOCH, HOUR, ISODOW, ISOYEAR, JULIAN, MICROSECONDS, MILLENNIUM, MILLISECONDS, MINUTE, MONTH, QUARTER, SECOND, TIMEZONE, TIMEZONE_HOUR, TIMEZONE_MINUTE, WEEK, YEAR. Anything else — PostgreSQL's synonyms such as "years" or "hr" included — raises InvalidValueError when the expression is built, on every engine. SQLite runs YEAR MONTH DAY HOUR MINUTE SECOND DOW DOY WEEK ISOYEAR ISODOW, numbered as PostgreSQL numbers them, and raises BackendCapabilityError for the rest. MySQL runs those and QUARTER, EPOCH, MILLISECONDS and MICROSECONDS with PostgreSQL's meaning (#1130), and raises BackendCapabilityError for the rest and for the EPOCH of a time. A date part of a time expression raises QueryBuildError there, since MySQL reads a time's date as today's.

The part must be one the column holds, checked against the field the column is declared as when the query is built, on both engines (#1070). It raises QueryBuildError otherwise:

• the time-of-day parts (HOUR, MINUTE, SECOND, MILLISECONDS, MICROSECONDS) read a DateTimeField or a TimeField;
• the TIMEZONE parts read a DateTimeField with a time zone (the default TIMESTAMPTZ);
• EPOCH reads any of those, a DateField or a DurationField;
• every other part reads a DateField or a DateTimeField.

The "col__@hour" transforms are this function, so they follow the same rule. An operand PormG cannot name a field for — an expression, a subquery, an untyped CTE column — is checked by its type: a duration expression (a timestamp difference, interval arithmetic, a Sum or Avg of a duration, a time minus a time on PostgreSQL) reads only EPOCH, as a DurationField does, and raises QueryBuildError under any other part on every engine (#1388). Anything else passes unchecked.

A filter on the result is held to the part's range: HOUR 0–23, MINUTE and SECOND 0–59, MONTH 1–12, DAY 1–31, QUARTER 1–4, WEEK 1–53, ISODOW 1–7, DOW 0–6, DOY 1–366. A value outside it raises InvalidValueError rather than matching nothing.

EPOCH, MILLISECONDS, MICROSECONDS and JULIAN are decimals on every engine that has them, read back as a Decimal; PostgreSQL renders them EXTRACT(… FROM x)::numeric (#1457, #1552). MICROSECONDS, and JULIAN of a date, are whole; the others carry a fraction, so Concat over one asks for a cast to text (#1027).

To change the result type, wrap it in Cast — e.g. on PostgreSQL, Cast(Extract("date", "epoch"), "bigint").

F(field_name::AbstractString) -> FExpression

Reference a database column rather than a Julia value (the Django F() equivalent). The comparison or arithmetic happens inside SQL, so no data is pulled into Julia and the update stays a single atomic statement.

F expressions support +, -, * and / against constants, other Fs and SQL functions. + and - additionally accept a Dates period or an Interval, for date arithmetic. A period is also an interval value (#1553): * scales one by a number (F("lap") * Second(90), either way round), / divides one written first (Minute(3) / F("lap")), and - takes one beside an interval on either side (F("time") - Second(1), Minute(4) - F("time")), as + does (F("time") + Second(1)). A number divided by a period is refused.

# Field-to-field comparison — grid position worse than finishing position
query = M.Result.objects.filter(F("grid") > F("positionorder"))

# Arithmetic projection, computed by the database
query = M.Result.objects
query.values("resultid", "adjusted" => F("points") * 2)

# Atomic update against the column's current value (no read-modify-write race)
M.Result.objects.filter("resultid" => 1).update("points" => F("points") + 1)

# Field-to-field update
M.Result.objects.filter("resultid" => 1).update("position" => F("positionorder"))

Prefer a plain lookup when the predicate compares against a scalar: write filter("points__@gt" => 20), not filter(F("points") > 20).

Every operator builds a new expression and leaves its operands untouched, so one handle can be bound to a name and reused across as many predicates as you like:

pts = F("points")
M.Result.objects.filter(pts > 10, pts < 25)   # two independent predicates on the same column

See also Field Expressions.

FirstValue(x; over::WindowSpec = WindowOver())

Window FIRST_VALUE(x) — the value of x in the first row of the window frame.

Safe under the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), because the frame always starts at the partition's first row. LastValue is not — see its docstring.

See also NthValue, Window Functions.

Floor(column)

Returns the largest integer less than or equal to a number. Over an integer it is that integer, of the same type, on both engines; over any other number PostgreSQL computes a numeric (#1147), and the value reads back as the operand's type on both engines: a Float64 over a float, a Decimal over a decimal (#1121).

Greatest(args...; output_field=nothing)

Returns the greatest value in the list of arguments. It takes two or more arguments: fewer raise QueryBuildError when the expression is built (#859), since one argument is the argument itself.

A string argument is a column path. A number, a Bool, or a Date/DateTime/ZonedDateTime/Time is a literal that is bound as a parameter (Greatest("points", 0) means Greatest("points", Value(0))). Wrap a string literal in Value. Any other value raises QueryBuildError when the expression is built (#705).

The value reads back as the column's own Julia type on both engines when every argument is of one type — Greatest("date", "fp1_date") is a Date, not SQLite's stored text. When the argument types differ, the value comes back as the engine delivers it (#824): on SQLite, the stored value of the argument that won.

output_field casts the result to the type it names, on both engines (#852): Greatest("grid", 1; output_field = "integer") renders (…)::integer on PostgreSQL and CAST(… AS INTEGER) on SQLite, so the value, a filter on it, and a CTE column typed by it all agree. On SQLite a date renders date(…), and the other temporal types and arrays raise BackendCapabilityError, as for Cast. A text or integer output_field refuses the operands Cast refuses (#1028): over a float, round first (Floor("points")). So does a scaled numeric(p, s) one (#1040).

Over an uncast JSON key (Greatest("metadata__wins", "constructorid")) it raises QueryBuildError on every engine (#1497), as Max does: the key's value has no declared type, so the query does not say whether it compares numbers or text. Cast the key, Cast("metadata__wins", "numeric"); an output_field casts the result after the comparison, so it does not say it.

Joined(alias::AbstractString, path::AbstractString; desc::Bool = false) -> JoinedReference

Reference a column of the joined copy declared by .cjoin_on(...). The joined copy's columns live in their own namespace, so an alias may share a name with a model field or with a CTE and neither shadows the other (#481):

q = M.Result.objects
q.cjoin_on(M.Driver, alias = "d", on = [Joined("d", "driverid") == F("driverid")])
q.values("points", "who" => Joined("d", "surname"))
q.filter(Joined("d", "nationality") => "Brazilian")

Inside a cjoin_on on list the two sides of the join are named by how you write the reference:

A reference may name another cjoin_on's alias, which is how a join correlates against a third table, in either declaration order: the alias it names is emitted first (#982). Operator suffixes work in filter(...), so a comparison against a literal on the joined side is an ordinary pair — filter(Joined("d", "points__@gte") => 3).

desc = true is meaningful in order_by(...) and in a window's order_by; anywhere else it raises a QueryBuildError. An unaliased projection is named by joining the two with a double underscore, so values(Joined("d", "surname")) emits the output column d__surname.

This replaces the F("d.surname") dotted-string spelling, which was removed in the same change: it resolved fail-open (a typo in the alias reported an unknown field named "typo.col"), it could not carry an operator suffix, and the bare string form could not be projected — values("d.surname") raised, because the dotted branch lived on the filter resolver only. (Wrapped in F(...) it did project, since F routes through that resolver.)

See also Custom Joins.

LPad(column, len, fill = " ")

Pads a string on the left with fill until it is len characters long — LPad(Cast("number", "text"), 3, "0") turns 44 into "044". A string longer than len is cut to its first len characters, a fill of several characters repeats and is cut where the length is reached (LPad("code", 6, "xy") over "HAM" is "xyxHAM"), an empty fill pads nothing, and a NULL string is NULL. The same on PostgreSQL (LPAD), on SQLite, which has no LPAD and calls a function PormG registers on every connection it opens (pormg_lpad), and on MySQL (LPAD, an empty literal fill rendered as the cut, since MySQL's pads to ""; a fill column empty on a row gives "" there, #1130).

column is a column path or an expression, and it must be text: PostgreSQL has no lpad over a number, a date, a time, a boolean, a uuid or a JSON document, so such a column raises QueryBuildError when the query is built, on both engines. Convert it to text first — an integer, a date, a time or a uuid with Cast (Cast(x, "text")), a timestamp with ToChar. fill is text too: a string there is a literal, and a number, a date or a boolean (a literal or a column) raises QueryBuildError. A len below 0 or above 268435454 (PostgreSQL's limit in a UTF-8 database) raises InvalidValueError.

Zero-filling an integer column into a text column, in one statement:

M.Driver.objects.
    filter("number__@isnull" => false).
    update("code" => LPad(Cast("number", "text"), 3, "0"))

LTrim(column)

Removes leading whitespace from a string.

Lag(x; offset::Integer = 1, default = nothing, over::WindowSpec = WindowOver())

Window LAG(x, offset) — the value of x from offset rows earlier in the window.

Arguments

• x: the column to read — a column path, an expression, or a Subquery(...) projecting one value per row. Required — passing nothing raises QueryBuildError.
• offset: how many rows back. Must be non-negative; negatives raise QueryBuildError (use Lead to look forward).
• default: value returned at the window edge where no previous row exists. Omit it and those rows come back missing/NULL.
• over: the WindowOver spec. order_by is what makes "earlier" meaningful.

offset and a plain-value default are bound as query parameters, not interpolated. A column expression as default — default = F("grid") — renders as that column instead.

See also Lead, Window Functions.

LastValue(x; over::WindowSpec = WindowOver())

Window LAST_VALUE(x) — the value of x in the last row of the window frame.

WindowOver(
    partition_by = ["constructorid"],
    order_by     = ["positionorder"],
    frame = "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"  # PostgreSQL and MySQL
)

See also FirstValue, Window Functions.

Lead(x; offset::Integer = 1, default = nothing, over::WindowSpec = WindowOver())

Window LEAD(x, offset) — the value of x from offset rows later in the window. The forward-looking mirror of Lag; the arguments, the parameter binding, the QueryBuildError on a negative offset, and the default-at-the-edge behavior are identical.

See also Window Functions.

Least(args...; output_field=nothing)

Returns the least value in the list of arguments. It takes two or more arguments: fewer raise QueryBuildError when the expression is built (#859), since one argument is the argument itself.

A string argument is a column path. A number, a Bool, or a Date/DateTime/ZonedDateTime/Time is a literal that is bound as a parameter (Least("points", 25) means Least("points", Value(25))). Wrap a string literal in Value. Any other value raises QueryBuildError when the expression is built (#705).

The value reads back as the column's own Julia type on both engines when every argument is of one type — Least("date", "fp1_date") is a Date, not SQLite's stored text. When the argument types differ, the value comes back as the engine delivers it (#824): on SQLite, the stored value of the argument that won.

output_field casts the result to the type it names, on both engines (#852): Least("grid", 25; output_field = "integer") renders (…)::integer on PostgreSQL and CAST(… AS INTEGER) on SQLite, so the value, a filter on it, and a CTE column typed by it all agree. On SQLite a date renders date(…), and the other temporal types and arrays raise BackendCapabilityError, as for Cast. A text or integer output_field refuses the operands Cast refuses (#1028): over a float, round first (Floor("points")). So does a scaled numeric(p, s) one (#1040).

Over an uncast JSON key (Least("metadata__wins", "constructorid")) it raises QueryBuildError on every engine (#1497), as Max does: the key's value has no declared type, so the query does not say whether it compares numbers or text. Cast the key, Cast("metadata__wins", "numeric"); an output_field casts the result after the comparison, so it does not say it.

Length(column)

Returns the length of a string.

Ln(column)

Returns the natural logarithm of a number.

It reads back as a Decimal over a DecimalField and as a Float64 over an integer or a float, on both engines (#1121), as Django's Ln types it. PostgreSQL computes it as a numeric.

Lower(column)

Converts a string to lowercase.

Max(x)

Aggregate MAX(x) — the largest value of x in the group.

There is no distinct keyword: MAX(DISTINCT x) and MAX(x) are the same value.

MAX/MIN are deliberately exempt from the to-many fan-out guard (#74) that Count, Sum and Avg trip: duplicating rows across a to-many join cannot change an extremum, so the query is safe where a sum would be wrong.

The value reads back as the same Julia type the column itself does, on both engines: a MAX over a DateField is a Date, over a DurationField a Dates.CompoundPeriod (#800). MAX returns one of the column's own values, so the column's read-side parser applies to it. This does not extend to a computed aggregate: Sum and Avg come back as the engine delivers them.

Over a BooleanField it answers "is any row true?" and reads back as a Bool on both engines. It renders BOOL_OR(x) on PostgreSQL, which has no max(boolean), and MAX(x) over SQLite's stored 0/1 (#953).

Over an uncast JSON key (Max("metadata__wins")) it raises QueryBuildError on every engine (#1491): the key's value has no declared type, so the query does not say whether it orders numbers or text. Write Max(Cast("metadata__wins", "numeric")) or Max(Cast("metadata__wins", "text")).

See also Min, Filters and Aggregates.

Min(x)

Aggregate MIN(x) — the smallest value of x in the group. The mirror of Max in every respect: no distinct keyword, exempt from the fan-out guard (#74), and the result reads back as the column's own Julia type on both engines (#800). Over a BooleanField it answers "are all rows true?": BOOL_AND(x) on PostgreSQL, MIN(x) on SQLite (#953). Over an uncast JSON key it raises QueryBuildError, as Max does (#1491).

See also Filters and Aggregates.

Mod(dividend, divisor)

Returns the remainder (modulo) of a division.

It reads back as a Decimal when an operand is a decimal and as a Float64 otherwise, on both engines (#1121), as Django's Mod types it: Mod("grid", 2) is 1.0 for an odd grid, not 1.

A string argument is a column path. A number, a Bool, or a Date/DateTime/ZonedDateTime/Time is a literal that is bound as a parameter (Mod("points", 2) means Mod("points", Value(2))). Wrap a string literal in Value. Any other value raises QueryBuildError when the expression is built (#705).

NthValue(x, n::Integer; over::WindowSpec = WindowOver())

Window NTH_VALUE(x, n) — the value of x in the n-th row of the window frame, counting from 1. n <= 0 raises QueryBuildError.

n is positional, not a keyword, and is rendered as a literal integer in the SQL rather than a bound parameter — SQL requires a constant there.

The same frame caveat as LastValue applies whenever n reaches past the current row: under the default frame those rows come back NULL.

using PormG.Functions: NthValue, WindowOver

"runner_up" => NthValue("driverid__surname", 2,
    over=WindowOver(partition_by=["raceid"], order_by=["positionorder"]))

See also FirstValue, Window Functions.

NullIf(field1, field2)

Returns NULL if field1 equals field2, otherwise returns field1.

A string argument is a column path. A number, a Bool, or a Date/DateTime/ZonedDateTime/Time is a literal that is bound as a parameter (NullIf("points", 0) means NullIf("points", Value(0))). Wrap a string literal in Value. Any other value raises QueryBuildError when the expression is built (#705).

NullIf("code", "") compares two columns; write NullIf("code", Value("")) for the empty string.

The value is field1's, so it reads back as field1's own Julia type on both engines: NullIf("date", "fp1_date") is a Date, not SQLite's stored text (#824).

Over an uncast JSON key in either operand (NullIf("metadata__wins", 0)) it raises QueryBuildError on every engine (#1510): a key's value has no declared type, so the query does not say whether the comparison is between numbers or text. Cast the key: NullIf(Cast("metadata__wins", "numeric"), 0).

OuterRef(field_name::AbstractString) -> OuterRefObject

Reference a column of the enclosing query from inside a subquery — the correlation that turns an independent child query into a per-outer-row one. Use it inside the query you hand to Exists or Subquery.

# "did this driver set a lap under 90 s in this race?" — both columns come from the outer row
fast_laps = M.Lap_times.objects.filter(
    "raceid"             => OuterRef("raceid"),
    "driverid"           => OuterRef("driverid"),
    "milliseconds__@lte" => 90_000,
)

query = M.Result.objects.filter(Exists(fast_laps))

OuterRef("pk") resolves to the outer model's primary key, so a correlation does not have to name the column: filter("driverid" => OuterRef("pk")) against an outer M.Driver query.

It binds to the immediately enclosing query, as Django's does. A subquery nested inside another subquery — in a filter, a projection, a When or a join condition — correlates to the subquery around it, never further out (#938). Reaching two levels up (Django's OuterRef(OuterRef(…))) is not supported, and raises QueryBuildError. That holds when both enclosing levels have the column, without a warning (#952): to use the outer value further in, correlate the middle query on it, and the inner reference carries it down.

Used outside an Exists/Subquery build there is no outer query to bind to, so an OuterRef there raises QueryBuildError — wrapped in a function or not.

An outer column may be wrapped in a scalar function or a window column inside the correlated query — Lower(OuterRef("surname")), Cast(OuterRef("driverid"), "text"), Lag(OuterRef("driverid"), over = …) — and resolves against the outer row exactly as the bare reference does.

Correlate on a base column of the outer model. A joined path (OuterRef("constructorid__name")) adds a join to the outer query and is outside the validated surface.

Power(base, exponent)

Returns base raised to the power of exponent.

It reads back as a Decimal when an operand is a decimal and as a Float64 otherwise, on both engines (#1121), as Django's Power types it: Power("grid", 2) is 4.0 for a grid of 2.

A string argument is a column path. A number, a Bool, or a Date/DateTime/ZonedDateTime/Time is a literal that is bound as a parameter (Power("points", 2) means Power("points", Value(2))). Wrap a string literal in Value. Any other value raises QueryBuildError when the expression is built (#705).

Q(x...)

Create a QObject with the given filters.

Arguments

• x...: key-value pairs, Qor(x...), or Q(x...) objects.

Example

a = object("tb_user")
a.filter(Q("name" => "John", Qor("age" => 18, "age" => 19)))

Qor(x...)

Create a QorObject from the given arguments. The QorObject represents a disjunction of SQLTypeQ or SQLTypeQor objects.

Arguments

• x...: A variable number of arguments. Each argument can be either a SQLTypeQ or SQLTypeQor object, or a Pair object.

Example

a = object("tb_user")
a.filter(Qor("name" => "John", Q("age__gte" => 18, "age__lte" => 19)))

RPad(column, len, fill = " ")

Pads a string on the right with fill until it is len characters long — RPad("code", 5, ".") turns "HAM" into "HAM..". Like LPad, a longer string is cut to its first len characters, a fill of several characters repeats and is cut, an empty fill pads nothing, and a NULL string is NULL, on PostgreSQL (RPAD) and SQLite (pormg_rpad) alike.

column and fill must be text, as for LPad: anything else raises QueryBuildError when the query is built, and a len below 0 or above 268435454 raises InvalidValueError.

RTrim(column)

Removes trailing whitespace from a string.

Rank(; over::WindowSpec = WindowOver())
Rank(over::WindowSpec)

Window RANK() — position within the window, leaving gaps after ties: two rows tied for 1st are both 1 and the next row is 3.

Takes no column; the ordering comes entirely from over. Omitting over ranks the whole result set as one unordered window, which is rarely what you want — pass a WindowOver with order_by. The positional form Rank(spec) is shorthand for Rank(over=spec).

See also DenseRank (no gaps), RowNumber (always unique), Window Functions.

Replace(column, find, replace)

Replaces all occurrences of find with replace in the string.

column is a column path. find and replace are text: a string there is a literal, and a number raises QueryBuildError naming its string spelling (#705).

Round(column, precision=0)

Rounds a number to precision decimal places, half away from zero.

Each engine rounds with its own ROUND, as Django's Round does: PostgreSQL renders ROUND(x::numeric, d), which rounds the value's exact decimal form (a DecimalField keeps its Decimal), and SQLite ROUND(x, d), which rounds the stored double. The two agree except at a decimal tie whose double sits just below it: Round(2.675, 2) is 2.68 on PostgreSQL and 2.67 on SQLite. PostgreSQL's is the exact answer; on SQLite, a development engine for PormG, the last digit of such a tie can differ (#1061). Round(x) (no places) gives the same whole number on both. MySQL rounds a double half to even, so there a float is rounded as ROUND(CAST(x AS DECIMAL(65,30)), d), half away from zero from the double's shortest digits (#1130); a float of 1e35 or more is clamped by that cast.

The value reads back as its operand's type on every engine (#1121): a Float64 over a float and a Decimal over a decimal, although PostgreSQL computes ROUND(x::numeric, d) as a numeric. Over an integer it is that integer, whatever the places (#1196): PostgreSQL renders ROUND(x::numeric) cast back to the operand's type and SQLite the integer itself, so it reads back as an integer (its width follows the engine, as an integer column's does) and Round("grid") / 2 divides as integers.

Text is not a number to round: Round(x, d) with d ≠ 0 over a text column, a string or a JSON value raises QueryBuildError when the query is built (on SQLite a negative d meets the capability error first), because the query does not say the text holds a number. Cast it first: Round(Cast(x, FloatField()), d).

A negative precision rounds to tens, hundreds, …: Round(125, -1) is 130 on PostgreSQL and MySQL. SQLite's ROUND has no negative precision (it reads one as 0), so there the query raises BackendCapabilityError when it is built (#1044, #1167). Over an integer the result stays that integer type.

See also Rounding.

RowNumber(; over::WindowSpec = WindowOver())
RowNumber(over::WindowSpec)

Window ROW_NUMBER() — a unique sequential number per row within the window, starting at 1.

Unlike Rank and DenseRank it never repeats a value, which means tied rows get an arbitrary order between them. If the numbering has to be reproducible, add a tiebreaker column to the order_by of the WindowOver.

See also Window Functions.

SearchHeadline(expression, query; config = nothing, start_sel = nothing, stop_sel = nothing,
               max_words = nothing, min_words = nothing, short_word = nothing,
               highlight_all = nothing, max_fragments = nothing, fragment_delimiter = nothing)

expression's text with the words query matches marked (PostgreSQL only): ts_headline, as a String. The expression is cast to text, as each SearchVector field is. query is a SearchQuery, or the search text as a String. config defaults to the query's.

The options are PostgreSQL's (StartSel, StopSel, MaxWords, …), written in snake case. They are checked when the expression is built and sent as one bound parameter: start_sel, stop_sel and fragment_delimiter are strings, highlight_all a Bool, and the rest non-negative integers with 0 < min_words < max_words. Anything else raises InvalidValueError.

M.Race.objects.
  filter("name__@search" => SearchQuery("grand prix"; config = "english")).
  values("year", "hl" => SearchHeadline("name", SearchQuery("grand prix"; config = "english");
                                        start_sel = "<b>", stop_sel = "</b>"))

ts_headline reads the whole document for every row it returns, so filter and limit the rows first. It does not HTML-escape the text: before rendering a headline of user-written text as HTML, see Showing a headline in a web page in the Full-Text Search guide (#1026).

SearchQuery(text; config = nothing, search_type = "plain")

The query side of a full-text search (PostgreSQL only): text parsed into a tsquery. It is the right-hand side of the @search lookup and the query of SearchRank and SearchHeadline.

search_type picks the parser PostgreSQL applies to text:

config names a text-search configuration ("english", "simple", "pg_catalog.portuguese"); with none, the server's default_text_search_config applies.

Queries combine with & (both), | (either) and ~ (not), PostgreSQL's &&, || and !!. Combined queries must share one config, or QueryBuildError is raised:

SearchQuery("senna"; config = "simple") | SearchQuery("prost"; config = "simple")
# (plainto_tsquery('simple'::regconfig, $1::text) || plainto_tsquery('simple'::regconfig, $2::text))

The text is bound as a parameter; the config is checked to be a name and written into the SQL, which is what lets PostgreSQL use an index built on to_tsvector('english', col). A config that is not a name, or an unknown search_type, raises InvalidValueError here, as does text containing a NUL character.

M.Driver.objects.filter("surname__@search" => SearchQuery("senna"; config = "simple"))
M.Race.objects.filter("name__@search" => SearchQuery("\"grand prix\" -british"; search_type = "websearch"))

SearchRank(vector, query; normalization = nothing, cover_density = false, weights = nothing)

How well each row's document matches a query (PostgreSQL only), as a Float64: ts_rank, or ts_rank_cd with cover_density = true. vector is a SearchVector, or the path of a SearchVectorField column (#1021), a stored document: SearchRank("search", q), Django's SearchRank(F("search"), q). query is a SearchQuery, or the search text as a String, which is parsed with the vector's config (with none, for a stored column, which does not record one). normalization is PostgreSQL's integer bitmask (0 to 63) for weighing the document's length; any other value raises InvalidValueError.

weights scores a word by its SearchVector weight label: four numbers from 0 to 1 for the labels D, C, B and A, in that order, as PostgreSQL's ts_rank takes them (its default is [0.1, 0.2, 0.4, 1.0]). Any other shape raises InvalidValueError. Weights only change scores between words labelled differently, so they go with a weighted vector:

vector = SearchVector("name"; config = "english", weight = "A") +
         SearchVector("location"; config = "english", weight = "B")
SearchRank(vector, SearchQuery("monaco"; config = "english"); weights = [0.0, 0.0, 0.2, 1.0])

A sum of vectors with different configs has no single config to parse a String query with, so it needs a SearchQuery; a String raises QueryBuildError.

Project it under a name, then filter and order by that name:

using PormG.Functions: SearchRank, SearchVector, SearchQuery

M.Driver.objects.
  values("forename", "surname",
         "rank" => SearchRank(SearchVector("forename", "surname"; config = "simple"), "ayrton senna")).
  filter("rank__@gte" => 0.01).
  order_by("-rank")

Filter on a threshold rather than > 0: for a query of several words, a row that misses them can score a tiny positive value (1e-20) instead of 0.

SearchVector(fields...; config = nothing, weight = nothing)

The document side of a full-text search (PostgreSQL only): one or more columns, each cast to text, NULL-safe and joined by a space, then parsed into a tsvector. It is the vector of SearchRank.

SearchVector("forename", "surname"; config = "simple")
# to_tsvector('simple'::regconfig, COALESCE(("Tb"."forename")::text, '') || ' ' || COALESCE(("Tb"."surname")::text, ''))

weight labels every word of the document "A", "B", "C" or "D" (setweight), for SearchRank's weights to score; anything else raises InvalidValueError. Two vectors add with + into one document, each keeping its own config and weight:

SearchVector("name"; weight = "A") + SearchVector("location"; weight = "B")
# (setweight(to_tsvector(COALESCE(("Tb"."name")::text, '')), 'A') || setweight(to_tsvector(…), 'B'))

A field is a column path or an expression, as for Lower. A SearchVector is an operand, not a value: projecting one, comparing it or wrapping it in another function raises QueryBuildError, and putting one on the right of a filter pair raises FilterError. To filter a single column, use the @search lookup on it.

Sqrt(column)

Returns the square root of a number.

It reads back as a Decimal over a DecimalField and as a Float64 over an integer or a float, on both engines (#1121), as Django's Sqrt types it. PostgreSQL computes it as a numeric.

Subquery(query::SQLObjectHandler) -> SubqueryObject

Project a scalar correlated subquery as a column of the enclosing SELECT (#92) — one value per outer row, computed by its own sub-SELECT.

The inner query must select exactly one column, and it correlates to the outer row through OuterRef. Always project it with an alias — a bare Subquery(...) inside values raises.

# How many standings rows each driver has — one exact count per driver
standings = M.Driver_standings.objects
standings.filter("driverid" => OuterRef("driverid"))
standings.values("t" => Count("driverstandingsid"))

query = M.Driver.objects
query.values("surname", "total_standings" => Subquery(standings))
df = query |> DataFrame

This is the fan-out-safe way to aggregate across a to-many relation: two Subquery columns over two different relations stay exact, where a joined values(Count(...), Count(...)) would row-multiply (the guard for that is #74).

It is also a filter value (#926), as in Django's filter(grid=Subquery(...)): filter("milliseconds" => Subquery(fastest)) renders "milliseconds" = (SELECT …), and so do F("milliseconds") == Subquery(fastest), a Q/When condition and a join condition. @in/@nin take the query itself instead, unwrapped.

It is also an update value (#1320), as in Django's update(number=Subquery(...)): update("number" => Subquery(latest)) renders SET "number" = (SELECT …). A subquery that is no number is refused into a number field, as an F expression is (#1297).

See also Exists for the boolean form and OuterRef for the correlation.

Substr(column, pos, len = nothing)

Returns part of a string: len characters starting at position pos, counted from 1, or every character from pos on when len is omitted. Substr("surname", 1, 3) over "Hamilton" is "Ham", and Substr("surname", 4) is "ilton". A pos past the end gives "", and a NULL string is NULL. The same on PostgreSQL, SQLite and MySQL (SUBSTR), with pos and len bound as parameters.

pos is an integer from 1 and len an integer from 0, both at most 2147483647; anything else — a pos of 0 or below, a negative len, a Bool, a float, a column — raises InvalidValueError when the expression is built. Outside that range the engines disagree: a negative position counts from the end on SQLite and MySQL and gives "" on PostgreSQL, and a negative length raises on PostgreSQL while SQLite returns the characters before pos. Pass the position you mean, counted from the start.

column is a column path or an expression, and it must be text: a number, a date, a time, a timestamp, a boolean, a uuid or a JSON document raises QueryBuildError when the query is built, on every engine. Convert it to text first — an integer, a date, a time or a uuid with Cast (Cast(x, "text")), a timestamp with ToChar, a boolean with a Case. A key inside a JSON document ("payload__name") is text and is accepted.

Filling the driver code from the surname, in one statement:

M.Driver.objects.
    filter("code__@isnull" => true).
    update("code" => Upper(Substr("surname", 1, 3)))

Sum(column; distinct=false)

Computes the sum of all values in the column.

The sum of integers is an integer on both engines. PostgreSQL widens sum(bigint) to numeric, so over a bigint (an IDField, a BigIntegerField, a ForeignKey, an integer literal, integer arithmetic over one) PormG renders SUM(x)::bigint there: Sum("resultid") reads an Int64 and Sum("resultid") / 2 divides as integers on both engines (#1147). A sum past the bigint range raises, as it does on SQLite.

A sum of a DecimalField reads back as a Decimal, and of a FloatField as a Float64, on both engines (#1121): SQLite computes a decimal sum as a double, which PormG reads as the Decimal it means, to 15 significant digits.

A Sum over a BooleanField is refused with a QueryBuildError on every engine (#953): the query does not say which number a flag counts as. Count the true rows explicitly — Sum(When("is_active" => true, then = 1, otherwise = 0)).

A Sum over a duration is the total duration. A Sum over a date, a timestamp, a time or a UUID is refused with a QueryBuildError on every engine (#1387): none is a number. Sum the number you mean, such as Sum(Extract("time", "hour")).

ToChar(x, format::AbstractString; formatter = nothing)

Format a date/time column as text — PostgreSQL to_char(x, format), SQLite strftime.

Arguments

• x: a field path, F expression, function object, or a vector of field paths.
• format: one of the portable formats below, e.g. "YYYY-MM", "YYYY-MM-DD", "YYYY".
• formatter: an optional Julia-side hook applied to the returned values. Accepts a Function, or a PormGField whose .formatter is used.

Portable formats

Each format renders the same text on both engines for a given instant — PormG spells it for each engine itself (HH is the 24-hour clock on both; the T separator and the .SSS milliseconds render as written). The full list:

A time of day has no date: over a TimeField, a format that writes a year, a month or a day raises QueryBuildError on every engine (#1336). The clock formats ("HH:MI", …) format a time everywhere.

using PormG.Functions: ToChar, Count

# Races per month
query.values("month" => ToChar("date", "YYYY-MM"), "n" => Count("raceid"))

# A race start as the canonical timestamp text, identical on every engine
query.values("start" => ToChar("start_at", "YYYY-MM-DDTHH:MI:SS.SSS"))

Named ToChar since 0.3.0 (previously To_char, with a formater keyword).

See also Functions and Dates.

Trim(column)

Removes leading and trailing whitespace from a string.

Upper(column)

Converts a string to uppercase.

Value(x)

Wraps a literal value for use in SQL queries. It binds as a parameter, except nothing, which renders as NULL.

A string, an integer of any width, a float, a Bool, a Date, DateTime, ZonedDateTime or Time, or a UUID binds on both engines. On SQLite each binds as what its column would store: a date or time as the text a DateField/DateTimeField/TimeField holds, and an integer as Int64. A projected Date, DateTime or Time reads back typed: values("d" => Value(Date(2021, 3, 28))) is a Date, and a DateTime comes back as a UTC ZonedDateTime on SQLite, as a SQLite DateTimeField column does. A ZonedDateTime is an instant: it binds as timestamptz on PostgreSQL and as UTC text on SQLite, and reads back as a ZonedDateTime on both, so Cast(Value(zdt), "date") is the date of that instant in UTC, the session time zone, not the wall-clock date of the offset it was written in. A float binds as a double on SQLite, so a BigFloat or Decimal is narrowed to Float64 there. A value SQLite cannot store as itself (a Symbol, a Rational, an integer beyond Int64, an arbitrary struct) raises InvalidValueError there instead of being bound as a serialized Julia object.

A Sockets.IPv4 / Sockets.IPv6 binds on PostgreSQL as an inet, in the text PostgreSQL prints for it: Value(ip"::FFFF:10.0.0.1") is ::ffff:10.0.0.1. SQLite and MySQL have no network type, so there it raises BackendCapabilityError while the query is built.

update writes Value(x) as x: update("grid" => Value(3)) is update("grid" => 3), with the same checks, formatting and bind (#1320).

using PormG.Functions: Value

"season_label" => Value("2021 season")

When(condition; then = 0, otherwise = missing)

One WHEN condition THEN value branch of a SQL CASE.

condition accepts five forms:

• a lookup pair — When("points__@gt" => 10, then = 1)
• a tuple of pairs, ANDed together — When(("points__@gt" => 10, "grid" => 1), then = 1)
• a Q(...) / Qor(...) object, for OR and nested boolean logic
• a comparison over an F, function or window expression — When(F("grid") < F("positionorder"), then = 1), When(Lower("surname") == "senna", then = 1). It renders exactly as the same expression wrapped in Q(...). An arithmetic or bitwise expression is a value, not a condition, so When(F("laps") + 1) raises QueryBuildError: compare it, as in When((F("laps") + 1) > 0) (#931). A bare boolean column, When(F("is_active")), is a condition.
• a function whose result is boolean — When(Cast("grid", "boolean"), then = 1), a Coalesce/Case with output_field = "boolean", When(Coalesce(F("is_active"), false)) over a BooleanField. A function whose result is known not to be boolean (Lower, Length, Sum, Rank, a Cast to a non-boolean type, a Coalesce over a number column) raises QueryBuildError: compare it, as in When(Lower("surname") == "senna") (#942). A function whose type cannot be named (Lag over a column, a Case with no output_field) is not checked.

then defaults to 0. A plain value in then or the CASE ELSE is bound as a query parameter; a column expression — F("points"), F("points") * 2, Joined(…), a function — renders as SQL, so When("positionorder" => 1, then = F("points")) returns that row's points. then = missing renders as SQL NULL. (otherwise = missing is the default and means no ELSE of this branch's own — see the tip below.)

# Points scored, or 0 for a non-points finish — one call, no Case needed.
"scored" => When("points__@gt" => 0, then = 1, otherwise = 0)

See also Case, Functions and Dates.

WindowOver(partition_by, order_by = []; frame = nothing) -> WindowSpec
WindowOver(; partition_by = [], order_by = [], frame = nothing) -> WindowSpec

Build the OVER (...) clause shared by every window function — this is the constructor you want; WindowSpec is the value it returns.

Arguments

• partition_by: restart the window per group. A field path, an F expression, or a vector/tuple of them. Symbols are accepted and converted.
• order_by: ordering inside each window. Strings use the repo-wide "-field" convention for DESC; SQLOrder objects also work, and add an explicit nulls = :first/:last placement, which a string entry has no room for. With nulls unset the window emits no NULLS clause, which is the rendering every window has always produced. Since #509 an SQLOrder can also carry a CTE column — SQLOrder(CTE("name", "col")), or the equivalent "name__col" path, both of which a bare entry has been able to name since #492 — and a Joined column, which only a handle can name. An SQLOrder's direction is its own orientation, so desc = true on a handle nested inside one is refused; pass orientation = "DESC" instead.
• frame: a frame clause such as "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING". It is SQL grammar rather than a value, so it cannot be a bind parameter: PormG parses it and writes its own spelling back. Accepted: ROWS, RANGE or GROUPS, then one bound or BETWEEN <bound> AND <bound>, optionally followed by EXCLUDE CURRENT ROW | GROUP | TIES | NO OTHERS. A bound is UNBOUNDED PRECEDING, <n> PRECEDING, CURRENT ROW, <n> FOLLOWING or UNBOUNDED FOLLOWING, where <n> is a non-negative integer (under RANGE also a decimal, or INTERVAL '<n> <unit>'). Keywords are case-insensitive. Anything else — including a frame PostgreSQL itself would refuse, such as one whose end comes before its start — raises InvalidValueError when WindowOver is called, on both backends.

Both list arguments accept a bare scalar, so partition_by = "raceid" and partition_by = ["raceid"] are equivalent. An entry of any other type raises QueryBuildError.

using PormG.Functions: WindowOver, Rank

# Rank drivers within each race — the ranking restarts per race.
query = M.Driver_standings.objects
query.filter("raceid__@in" => [305, 306], "points__@gt" => 0)
query.values(
    "raceid", "driverid__surname", "points",
    "race_rank" => Rank(over=WindowOver(
        partition_by=["raceid"],   # restart the ranking for each race
        order_by=["-points"]       # highest points = rank 1
    ))
)

See also Window Functions.

allocate_primary_keys(objct::SQLObjectHandler, table) -> a table of the same kind

Pre-allocate sequential primary key values for rows in table that are missing an auto-generated primary key, and return a new table with the pk column populated.

table is any Tables.jl table (#1344): a DataFrame, a Vector of NamedTuples, a NamedTuple of vectors, a CSV.File, … The result is the same kind of table, built by the input's own Tables.materializer: a DataFrame for a DataFrame, a Vector of NamedTuples for one, and a NamedTuple of vectors for a source with no materializer of its own (a CSV.File). Its columns are copies, so the caller's table is never modified and element writes on either never reach the other; assign the result (df = allocate_primary_keys(q, df)).

Use this when you need the assigned ids before inserting—for example, to wire up foreign key columns in related tables that must be bulk-inserted in the same transaction.

It serves a key the database generates, IDField(). On a model whose key is IDField(generated = false), a key the caller supplies, it raises QueryBuildError whatever table holds: no engine has ids to allocate for such a key, so supply them in the table. A model with no IDField key (a UUIDField or CharField key, or none) gets a copy of table back unchanged.

If table already contains the primary key column with at least one non-blank value, a copy is returned unchanged and no ids are reserved. If the column is absent, or every value in it is blank (missing, nothing, or an empty string), ids are reserved from the database.

If the column contains mixed values—some rows have explicit pk values and some are blank—a @warn is emitted and a copy is still returned unchanged. The blank rows are left as-is and will raise a QueryBuildError when bulk_insert is called. To resolve this you can: (1) provide a pk value for every row, (2) remove the pk column so all ids are allocated automatically, or (3) pre-fill the blank rows before calling this function.

PostgreSQL

Uses nextval(pg_get_serial_sequence(...)) to atomically consume N values from the identity/serial sequence. The reserved ids are guaranteed not to collide with concurrent inserts. Note that if the subsequent bulk insert is never executed (e.g. the transaction is rolled back), the consumed sequence values are not returned—this is normal PostgreSQL sequence behaviour; gaps are harmless.

SQLite

Reads the starting point from max(MAX(pk), sqlite_sequence.seq), assigns the next N ids from there, and bumps the table's sqlite_sequence counter to the end of the reserved range. That keeps both later autoincrement inserts and later allocate_primary_keys() calls from reusing ids that were reserved but not yet inserted. This read-then-write is self-protecting: if it is not already running inside a transaction on this connection it auto-opens one (run_in_transaction, i.e. BEGIN IMMEDIATE + the in-process write lock) so no concurrent writer can claim the same range in between — mirroring bulk_insert/bulk_copy/bulk_update. Wrapping the whole pre-allocation and insert together in run_in_transaction is still recommended: then a rolled-back insert also releases the reserved ids, whereas a standalone allocation whose later insert fails durably burns its range (a harmless gap, exactly like PostgreSQL).

That counter protects a reservation from SQLite's own auto-id inserts only on an AUTOINCREMENT table, which is what PormG's DDL creates. On a table declared INTEGER PRIMARY KEY without it (an imported or legacy schema), SQLite ignores sqlite_sequence and assigns MAX(rowid) + 1, so a later auto-id insert does not see the reservation. A database with no AUTOINCREMENT table at all has no sqlite_sequence: there the ids start at MAX(pk) + 1 and the reservation lives only in the open transaction. On such a table, allocate and insert inside one run_in_transaction.

Arguments

• objct: A SQLObjectHandler (typically M.Model.objects). Only the underlying model is consulted — any filters, ordering, or annotations attached to the handler are ignored, since pk allocation is a table-level operation independent of any query.
• table: The Tables.jl table that will be bulk-inserted.

There is no in-place mode (#1344): a generic Tables.jl source cannot be written in place, so the key column is always on the returned table only.

Notes

• The key column of the returned table holds Int64s (a plain Vector{Int64} in a column table). If the input had a Vector{Union{Missing,Int}} pk column, the missing-able element type is dropped after allocation.
• The PostgreSQL backend currently assumes the model lives in the default search path (typically public). Models in other schemas are not supported by this helper. (_update_sequence no longer shares the limitation: since #344 it resolves the sequence through the table's own relnamespace.)

Example

# Allocate driver ids before building the results table
drivers_df = CSV.File("f1/drivers.csv") |> DataFrame

PormG.run_in_transaction("db_2") do
    drivers_df = allocate_primary_keys(M.Driver.objects, drivers_df)

    results_df = DataFrame(
        driverid  = repeat(drivers_df.driverid, inner=10),
        raceid    = ...,
        ...
    )

    bulk_insert(M.Driver.objects, drivers_df)
    bulk_insert(M.Result.objects, results_df)
end

bulk_copy(objct::SQLObjectHandler, table; kwargs...)

Performs a high-speed bulk insert operation using PostgreSQL's COPY protocol. This is significantly faster than bulk_insert for large datasets.

Arguments

• objct::SQLObjectHandler: The database handler object (e.g., M.Model).
• table: Any Tables.jl table holding the data to be inserted (a DataFrame, a Vector of NamedTuples, …; #1344).
• columns: (Optional) Specifies which columns to insert. Can be a String, a Pair{String, String}, or a Vector of these.
• show_query::Symbol = :execute: A dry-run mode (:sql, :dict, :inspection, :params, :none) returns the COPY command instead of running it (the data stream is not included; :none returns nothing).

The caller's table is never mutated (and a column table is never copied — the pipeline reads its column vectors in place).

Returns

Executed, a NamedTuple (count, rows) (#670): count::Int is the rows copied, summed across chunks and read from each COPY n command tag; rows is always nothing for now. An empty table returns (count = 0, rows = nothing) (nothing under a dry-run show_query).

The COPY protocol cannot express ON CONFLICT — rows that violate a unique constraint make the whole COPY fail. To skip or merge duplicates, use bulk_insert(...; on_conflict = ...) (#123).

Example

bulk_copy(M.Driver, df)

Inserts multiple rows into the database in bulk from any Tables.jl table (#1344): a DataFrame, a Vector of NamedTuples, a NamedTuple of vectors, a CSV.File, …

Arguments

• objct::SQLObjectHandler: The SQL object handler to use for the operation.
• table: The table holding the data to be inserted; any value with Tables.istable(table) == true. Anything else raises QueryBuildError.
• columns: Optional. Specifies the columns to insert and their mappings. Can be nothing, a String, a Pair{String, String}, or a Vector of these. If nothing, all columns of the table are used.
• chunk_size::Integer: Optional. The number of rows to insert in each batch (default: 1000). PostgreSQL binds one array per column, so a positive batch is used as given (a non-positive one falls back to the driver's bind-parameter limit ÷ columns rows: 65,535 under LibPQ and Postgres.jl alike); SQLite binds one parameter per cell and caps it automatically to stay under its bind-parameter limit.
• show_query::Symbol: Optional. Return the generated SQL instead of executing it — :sql, :dict, :inspection, or :params (see Query Inspection). Defaults to :execute (run it).
• on_conflict: Optional (#123). Attaches an ON CONFLICT clause so duplicate rows are skipped or merged instead of erroring (PostgreSQL and SQLite share the syntax). Accepts:
  • nothing (default): no clause — a duplicate key raises, as before.
  • :nothing: ON CONFLICT DO NOTHING (untargeted — any unique violation skips the row).
  • (action = :nothing, target = ["field"]): ON CONFLICT (col) DO NOTHING.
  • (action = :update, target = ["field"], set = ["field"]): ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col, … (upsert).
  target/set take logical model field names (resolved through db_column). set fields must participate in the INSERT column list. target is not required to be declared unique in the model — the database is the source of truth for matching constraints. When on_conflict is set, the duplicate-key → sequence-resync retry is skipped: a conflict is expected there, not a sequence desync, so any duplicate-key error that still surfaces (a different constraint than the target) propagates. On MySQL the clause is ON DUPLICATE KEY UPDATE, which names no target: a targeted :nothing or an :update runs only when the target is a unique key the model declares and the one these rows can conflict on, and raises BackendCapabilityError otherwise (#1267).
• returning: Optional (#671). A field name or a vector of field names whose written values come back in rows — generated primary keys, db_defaults, what a DO UPDATE wrote. Rows are matched to the input by key, never by position: by the on_conflict target when there is one, otherwise by the primary key. A single auto-increment pk the frame does not carry is pre-allocated from its own sequence first (as allocate_primary_keys does), so it can be matched. A model whose pk the database generates by any other means, with no on_conflict target, raises QueryBuildError, as does a target column that is not inserted; an unknown field name raises UnknownFieldError. PostgreSQL reads the rows from RETURNING; SQLite reads them back on the same transaction connection, since PormG never uses SQLite's RETURNING; MySQL, which has none, does the same. MySQL cannot pre-allocate an AUTO_INCREMENT pk, so there that case raises BackendCapabilityError (#1266).

The caller's table is never mutated (and a column table is never copied — the pipeline reads its column vectors in place; a row table is copied into columns once), so there is no copy= knob.

Returns

Executed (show_query = :execute), a NamedTuple (count, rows) (#670):

• count::Int — rows inserted, summed across every chunk. Rows on_conflict = :nothing skipped are not counted, so the input row count minus count is how many were duplicates. An on_conflict = (action = :update, …) upsert counts every row it inserted or updated.
• rows — nothing unless returning is given. Then a table of the kind table is, built by its Tables.materializer (#1344): a DataFrame for a DataFrame, a Vector of NamedTuples for one, a NamedTuple of vectors when its package defines no materializer. It has one column per requested field and one row per input row: row i belongs to input row i, and a row this call did not write (skipped by on_conflict = :nothing, or a later duplicate of a key within the frame) is missing — as is a row whose key contains a NULL, which cannot be looked up. A row the database reports that matches no input key raises QueryBuildError instead of going missing.

An empty table returns (count = 0, rows = nothing) — rows is an empty table of the same kind, with the requested columns, when returning is given — and nothing under a dry-run show_query, since there is no statement. Otherwise a dry-run mode returns the statement, as described above; it pre-allocates nothing, so an auto pk it would allocate is absent from the shown column list.

Examples

```julia include("models.jl") import models as mdl

Basic usage

query = mdl.User |> object df = DataFrame(name=["Alice", "Bob"], age=[30, 25]) bulk_insert(query, df)

With column mapping and excluding unwanted variables

query = mdl.Boook |> object df = DataFrame(title=["Book A", "Book B"], authorname=["Alice", "Bob"], year=[2020, 2021], ignoreme=["x", "y"])

Map DataFrame column "author_name" to model field "author"

Exclude "ignore_me" by not including it in the columns argument

bulkinsert(query, df, columns=["title", "year", "authorname" => "author"])

Only "title", "year", and "author_name" (as field "author") participate in the INSERT;

the DataFrame's columns are never renamed or removed — the mapping is internal.

Generated ids, one per input row

r = bulk_insert(query, df; returning = ["id"]) df.id = r.rows.id ```

Performs a bulk update operation on a database table using the provided Tables.jl table (a DataFrame, a Vector of NamedTuples, …; #1344) and a query object.

Arguments

• objct::SQLObjectHandler: The database handler object.
• table: The table holding the data to be used for the update; any value with Tables.istable(table) == true.
• columns: (Optional) The participating fields and their mappings — the single place a column of the table is mapped to a model field. Each entry is a String (table column == model field) or a Pair{String, String} of "df_col" => "model_field". A Vector of these is accepted. If nothing, columns are auto-detected from the table. Fields selected by match_on are used for matching only and are not SET.
• match_on: (Optional) The per-row match keys that identify which row each input row updates (the SQL merge condition Tb.field = source.col). Bare model field names only — the source column is the columns= mapping for that field when you declared one, otherwise a column of the table with the field's own name. A value PormG auto-populates for a field left out of columns= (auto_now, or a static default when columns= is omitted — an explicit columns= already suppresses static defaults on an update) is not a declared mapping: your same-named column outranks it, and with no caller source at all the call raises UnknownFieldError rather than matching every row against one per-call constant. If omitted, the model primary key(s) are used and must be present in the table, under the same precedence. A match key is matched, never written — it stays out of the SET clause, so using an auto_now field as a key does not refresh that timestamp.
• filters: (Optional) Constant predicates AND'd onto the WHERE clause, applied to every row. Each entry is a Pair{String, T} of "model_field" => value (e.g. "category_id" => 172100, "points__@in" => [18, 25]). When match_on is provided, every filters entry must be such a constant predicate. A per-row match key in filters is rejected with a migration error — move it to match_on.
• show_query::Symbol: (Optional) :execute (the default) runs the update. The dry-run modes (:sql, :dict, :inspection, :params, :none) build the statement without executing it and return it in that form — one result per chunk, as a Vector when there is more than one chunk (:none returns nothing per chunk).
• chunk_size::Integer: (Optional) Number of rows to process per chunk. Defaults to 1000. PostgreSQL binds one array per column, so a positive chunk is used as given (a non-positive one falls back to the driver's bind-parameter limit ÷ columns rows: 65,535 under LibPQ and Postgres.jl alike); SQLite and MySQL bind one parameter per cell and cap it automatically to stay under their bind-parameter limit.

Handler scope

Filters already attached to objct are kept: they are AND'd with the match_on= merge condition and the filters= predicates, so a handler scoped with .filter("year" => 1988) only updates rows of that year. The handler itself is never modified — filters= is not written back onto it. A handler carrying state an UPDATE cannot express raises UnsafeMutationError: limit(), offset(), order_by(), distinct(), aggregate annotations, a CTE (.with), a cjoin/on/cjoin_on join, or a filter on a values() alias. A plain values() projection is ignored. A filter that traverses a relation ("driverid__surname" => …) raises QueryBuildError, as the columns do.

The caller's table is never mutated (and a column table is never copied — the pipeline reads its column vectors in place), so the operation is safe in asynchronous contexts without a copy= knob.

Returns

Executed, a NamedTuple (count, rows) (#670):

• count::Int — rows matched, summed across every chunk: the rows that satisfied the match_on= merge condition, the handler's filters and filters= together. It counts matched rows, as update() does, not only the rows whose values changed. A 0 means nothing matched — say, a frame whose keys belong to a season the handler is not scoped to.
• rows — always nothing for now; reserved for returned values.

An empty table returns (count = 0, rows = nothing) (nothing under a dry-run show_query).

Example

# Update by primary key inferred from the DataFrame
bulk_update(objct, df)

# Set name/dof, matching rows on security_id
bulk_update(objct, df, columns=["name", "dof"], match_on=["security_id"])

# Map differing DataFrame names and add a constant scope guard. ALL df→field
# mappings live in columns=; match_on selects merge keys by model field name
# (a field listed in both is used only for matching — it is not SET).
bulk_update(objct, df,
    columns  = ["new_score" => "points",     # df "new_score" → field "points" (SET)
                "record_id" => "id"],        # df "record_id" → field "id" (match key)
    match_on = ["id"],                       # match Tb.id = source.id
    filters  = ["category_id" => 172100])    # constant: only this category

delete(objct::SQLObjectHandler; show_query=:execute, allow_delete_all=false) -> (total::Int, Dict{String,Integer})

Delete every row the query matches, cascading through foreign-key relationships according to each referencing field's on_delete action. The root delete and every dependent statement run inside one transaction, so a failure part-way through leaves the database untouched.

Arguments

• objct::SQLObjectHandler: the handler carrying the query and its model.
• show_query::Symbol = :execute: :execute runs the delete. :sql, :dict, :inspection and :params build the statements and return them instead of executing them, and :none builds and discards them — see show_query. An unrecognized value raises QueryBuildError when the statements are rendered, which is after the guards below have run.
• allow_delete_all::Bool = false: permit a delete whose query carries no filter. Off by default; see the guard table below.
• table_alias::Union{Nothing, SQLTableAlias} = nothing: accepted for call-signature compatibility with the other terminals; the delete path does not read it.
• connection::Union{Nothing, _SQLEngine} = nothing: execute against this connection pool instead of the one on the model's settings (those two abstract types are the backend markers the concrete pools subtype). Only the pool is overridden; the settings, and therefore the dialect and the change_data flag, still come from the model's connect_key. Whether the delete joins a surrounding transaction is decided by the task-local transaction context, not by this argument.

Returns

• Under :execute: Tuple{Integer, Dict{String, Integer}} — the total rows deleted and a per-table breakdown, e.g. (1, Dict("just_a_test_deletion" => 1)). A query matching nothing returns (0, Dict()); a delete that ran but removed no rows warns.
• Under :sql, :dict, :inspection or :params: the built statement(s) — one per statement the plan emits, returned bare when the plan is a single statement and as a Vector otherwise. A cascade can emit several statements for the same table (one UPDATE per SET_NULL / SET_DEFAULT field, plus its DELETE), so the count tracks statements, not tables.
• Under :none: nothing for each statement, by the same rule — nothing when the plan is a single statement, and a Vector of nothings, one per statement, when it cascades. The statements are built and discarded; on a live pool the key read and the cascade's existence probes still run, as they do for the other inspection modes.

Behavior

Every check below runs before any SQL is generated:

The three query-shape guards share one rationale: the deletion collector walks the complete filtered set so row counts, cascades and constraint handling stay deterministic. Any shape that truncates or collapses that set is refused rather than quietly applied to part of it — there is no "delete the first N rows" form, so filter by primary key to bound a delete.

Dependent rows are then resolved per referencing field, by that field's on_delete:

• CASCADE: the dependents are collected and deleted too, recursing into their own dependents.
• PROTECT / RESTRICT: raises ProtectedError, naming the referencing model and field. The two behave identically apart from the word in the message. The check is existence-driven — it fires only when referencing rows are actually present, so an empty reverse relation does not block the delete.
• SET_NULL: issues UPDATE ... SET <column> = NULL over the dependents instead of deleting them. Declaring it on a null = false field is a contradiction the schema cannot satisfy, and raises ModelDefinitionError.
• SET_DEFAULT: issues UPDATE ... SET <column> = <the field's default> over the dependents. Declaring it on a field with no default is the mirror-image contradiction, and raises ModelDefinitionError too.
• DO_NOTHING: PormG emits nothing for the relation and defers to the database's own constraint.

Only CASCADE walks further down the graph; SET_NULL and SET_DEFAULT do not recurse.

Both contradictions are normally caught earlier, at set_models registration; the checks here are the backstop for models that never passed through it.

An unset on_delete — the default for ForeignKey — produces no ORM statement for that relation, leaving the reference entirely to the database's own constraint. It renders ON DELETE NO ACTION in DDL, so a dependent row is not cascaded by PormG unless its field says so explicitly.

A model reachable by several cascade paths is scoped by all of them: its statement carries one predicate per path, ORed together, and that holds for the UPDATE a SET_NULL / SET_DEFAULT emits just as it does for a DELETE.

Every statement puts the filters on the row being deleted or updated — DELETE FROM "t" AS "Tb" WHERE "Tb"."status" = $1 — rather than only in a pk IN (SELECT …) subquery. A filter that crosses a relation keeps that pk IN (…) as its index-driven selection and adds the same filter as a correlated EXISTS on the row. That makes a filter a fence on PostgreSQL: if a concurrent transaction changes a row so it no longer matches while the delete waits on its lock, the row is left alone (#765). See Filters are a fence on PostgreSQL in Deleting Records.

When the root has a dependent to cascade to (CASCADE, SET_NULL or SET_DEFAULT), a filtered delete resolves the root's primary keys before anything is written: one SELECT "Tb"."<pk>" … WHERE <filter> (FOR UPDATE on PostgreSQL and MySQL), and every statement after it is scoped by those keys (#1342, #1370). Without that, the statements after the first would re-read a filter that the cascade had already changed. A filter that reads another table ("result__grid" => 1, a subquery, an Exists) would see rows the cascade had deleted or re-pointed, and the root could survive its own children. A filter on the root's own columns would see a SET_NULL / SET_DEFAULT update that wrote the root's table, and delete rows the cascade only meant to update. On SQLite and MySQL, which bind a list one value at a time, a large key set runs the cascade in chunks inside the same transaction. A delete with nothing to cascade, and an unfiltered one (allow_delete_all), keep their single statements.

On PostgreSQL and MySQL, before any child statement runs, every collected model that is the parent of an emitted DELETE or UPDATE is locked top-down with SELECT … FOR UPDATE over the same predicate its DELETE uses, so a parent's own columns cannot change after its children are gone (#770). The key read above is the root's lock when it runs. Those statements appear in show_query and inspect_query output as :operation => :lock steps (the key read is a :select step on SQLite). A delete with nothing to cascade emits neither, and SQLite emits no lock at all.

The cascade descends at most 50 levels. Beyond that it raises QueryBuildError naming the models it walked. Usually that means a foreign-key cycle — two models declaring on_delete = CASCADE at each other, or self-referencing rows that form a loop — which would otherwise make the collector descend forever. A genuinely acyclic hierarchy deeper than the ceiling hits it too, and the ceiling is fixed; delete such a graph in stages, from the far end inward.

The collected statements then execute in dependency order inside a single transaction (BEGIN on PostgreSQL, BEGIN IMMEDIATE TRANSACTION on SQLite), so any failure rolls the whole set back. Planning happens inside that transaction too, but on PostgreSQL a concurrent writer can still insert onto a path the planner probed and pruned — see Concurrency in Deleting Records.

Examples

# Delete objects from a model with a specific filter
query = M.Status.objects
query.filter("status" => "Engine")
total, dict = delete(query)

# Build the SQL without executing it
query = M.Just_a_test_deletion.objects
query.filter("test_result__constructorid__name" => "Williams")
sql = delete(query, show_query = :sql)

# Delete related tables (cascading delete)
query = M.Result.objects
query.filter("resultid" => 1)
total, dict = delete(query)

# Delete all objects from a model (use with caution)
query = M.Just_a_test_deletion.objects
total, dict = delete(query; allow_delete_all = true)

See also show_query, inspect_query, and Deleting Records.

delete(row::PormGRow; show_query=:execute) -> (total::Int, Dict{String,Integer})

Delete this fetched row from its table, cascading through the same DeletionCollector as Model.objects.filter(...).delete() — so on_delete behaviour (CASCADE / SETNULL / PROTECT) is identical whether you delete one fetched row or a filtered set. Returns the `(totaldeleted, per-model counts)` tuple of the underlying queryset delete.

The row is located by its primary key, which must have been projected onto the row (it is, for rows from list()/first()/get()). The in-memory row is not mutated — its data becomes stale after the delete.

earliest(objct::SQLObjectHandler, fields...; show_query = :execute) -> PormGRow

Return the earliest row ordered by fields (ascending; a "-field" flips that term to descending). Requires at least one field and raises DoesNotExist when no rows match — the extreme-row counterpart of get, matching Django's earliest(). On a query with limit() or offset() set it raises QueryBuildError, as Django's does (#1053).

earliest(objct::SQLObjectHandler, T::Type, fields...; show_query = :execute) -> T

earliest as a typed read (#1352).

explain_query(q::SQLObjectHandler; analyze = false, buffers = false, verbose = false, connection = nothing) -> Dict

Ask the database how it would run the SELECT that q builds, and return the plan with its facts. The fluent form is q.explain(; …), as q.inspect() is for inspect_query.

It explains the same statement show_query renders, so it is read-only: writes are terminals (.update(), .delete(), .create()), not something a handler can be explained as.

The result is the inspect_query Dict — :sql_text, :parameters, :dialect, … — plus:

The facts are what the plan states. PormG does not guess whether an index is missing: a sequential scan of a small table is the right plan.

• analyze = true executes the query (EXPLAIN ANALYZE) to measure real timing.
• A select_for_update() query needs a transaction to be explained at all, on every engine — with or without analyze — exactly as it does to run.
• buffers / verbose add PostgreSQL's BUFFERS / VERBOSE detail to :plan.
• SQLite has only EXPLAIN QUERY PLAN, and MySQL's EXPLAIN ANALYZE has no format PormG reads: any of the three options raises BackendCapabilityError on both. Each names a table in :seq_scans / :plan by the alias the query gives it.

plan = M.Result.objects.
    filter("driverid__surname" => "Senna").
    values("raceid", "points").
    explain()
plan[:indexes_used]   # ["result_driverid_lunjedbf_idx"] — results found through the driverid index
plan[:seq_scans]      # ["driver"] — the surname filter reads the driver table in full

timed = M.Result.objects.filter("raceid__year" => 1991).values("points").explain(analyze = true)   # PostgreSQL
timed[:execution_time_ms]   # e.g. 0.352

A joined query must still select its columns with .values(...) — explain() builds exactly the statement .list() would run, and refuses the same shapes.

inspect_query(q::SQLObjectHandler) -> Dict

Comprehensive query inspection API that provides full metadata about a query without executing it. Returns a rich dictionary with SQL, parameters, dialect information, and structural metadata.

This is the explicit API for query inspection - use this when you want to examine a query's structure and generated SQL without ambiguity.

Arguments

• q::SQLObjectHandler: The query object to inspect
• operation::Union{Nothing, Symbol} = nothing: Optional operation override (:select, :insert, :update, :delete). If not provided, the operation is detected automatically based on the query structure.

Returns

• Dict: A dictionary containing:
  • :sql_text (String): The generated SQL query
  • :parameters (Vector): The parameterized values in bucket order
  • :dialect (Symbol): The database dialect (:postgresql, :sqlite or :mysql)
  • :model (String): The model/table name
  • :operation (Symbol): The query operation type (:select, :insert, :update, :delete). A cascading :delete returns a Vector of steps; on PostgreSQL that Vector starts with one :lock step (SELECT … FOR UPDATE) per parent model (#770)
  • :bucketing (Symbol): The parameter bucketing strategy (:numbered for PostgreSQL, :positional for SQLite and MySQL)
  • :parameter_count (Int): Number of parameters
  • :parameter_buckets (Dict): Breakdown of parameters by bucket (for positional strategies)

Example

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

inspection = q |> inspect_query()
# Dict with:
# :sql_text => "SELECT ... WHERE drivers.nationality = $1 ORDER BY ..."
# :parameters => ["British"]
# :dialect => :postgresql
# :model => "drivers"
# :operation => :select
# :bucketing => :numbered

latest(objct::SQLObjectHandler, fields...; show_query = :execute) -> PormGRow

Return the latest row ordered by fields (descending; a "-field" flips that term to ascending). Requires at least one field and raises DoesNotExist when no rows match. Django's latest(); latest("f") == earliest("-f"). A sliced query raises QueryBuildError, as for earliest.

latest(objct::SQLObjectHandler, T::Type, fields...; show_query = :execute) -> T

latest as a typed read (#1352).

Return plain Dict{Symbol,Any} rows for framework integrations that need real dictionaries.

Return a JSON string without allocating PormGRow wrappers.

Return model-aware PormGRow objects. Default format.

list(objct::SQLObjectHandler, T::Type; show_query = :execute) -> Vector{T}

The typed read (#1352): every row built into T, a struct or NamedTuple type you declare, so the code after the query is type-stable. With no values(...), the query projects exactly fieldnames(T); with values(...), the projected names (aliases included) must be exactly fieldnames(T), or it raises QueryBuildError before any SQL runs.

struct DriverRow
    driverid::Int64
    surname::String
    code::Union{Nothing, String}
end
rows = M.Driver.objects.filter("nationality" => "Brazilian").order_by("surname").list(DriverRow)

A NULL reads as nothing into a Union{Nothing,X} field and as missing into a Union{Missing,X} one. Integer widths convert, an integer converts to a float or Decimal when exact, and a float widens; any other mismatch, and a NULL into a field that admits neither, raises InvalidValueError naming the field and both types. See Typed rows in the read guide.

object(model::PormGModel) -> ObjectHandler

Wrap a model in an ObjectHandler — the start of every query. M.Driver.objects is the idiomatic spelling; object(M.Driver) is the same thing as a function call.

This docstring is the fluent-API reference. The methods below are synthesized by getproperty, so they have no bindings of their own — ?query.filter cannot work. ?object (or the API reference) is where to look them up.

Chainable methods

Each mutates the handler and returns it, so calls can be chained or accumulated on a variable.

• .filter(pairs...) — add WHERE conditions. Each argument may be a Pair, a Q/Qor, an operator expression, an F expression, or an Exists(subquery). Repeated calls accumulate (ANDed), unlike .values/.order_by, which replace their previous call (#199)
• .values(fields...) — choose/annotate the selected columns; "*" selects the main table. Replaces its previous call, last-call-wins (#199). An alias ("alias" => expr) cannot contain __, the path separator, and raises QueryBuildError (#757)
• .order_by(fields...) — sort; prefix - for descending. Accepts a field path or an alias declared by .values() (#423). Replaces its previous call, matching Django's each order_by() clears previous ordering (#199)
• .limit(n) / .offset(n) — pagination, one clause each. .limit(0) is zero rows, as in SQL; .limit(nothing) removes a limit. A negative value or a Bool raises QueryBuildError, for .page(...) too (#1049)
• .page(limit) / .page(limit, offset) — pagination in one call; .page(n) sets the limit only and leaves any offset already on the handler in place. Those are the only two arities — anything else (no argument, three arguments, a non-Integer, a keyword) raises QueryBuildError, same as .limit(...) / .offset(...) (#272)
• .distinct() — add DISTINCT
• .db("key") — route the query to another connection pool
• .on(path, pairs...; join_type) — add predicates to the ON clause of a join path, joining it if nothing else in the query does. Adds predicates only: without join_type the join keeps the type derived from the relation itself, and an explicit one stays in effect for later on() calls on that path (#474)
• .cjoin("field" => "Model"; filters, join_type) — custom join at query time
• .cjoin_on(model; alias, on, join_type) — anchor-less join where on is the entire ON clause; model is the model object (M.Driver) or its name ("Driver"), and a model registered on another connection is refused (#488). Reference its columns with Joined(alias, column) in any clause. Its alias may equal a relation name on the base model or an on()/cjoin() join path; each stays addressable, and both joins are emitted (#484)
• .with("name" => subquery; join_field, join_type) — define a CTE; call again for a second one. Reference its columns as "name__column", or as CTE(name, path). Its name may equal a join key and each stays addressable (#474); a name equal to a model field makes the shared __ path raise AmbiguousFieldError, and the handle selects the CTE side (#492)
• every join_type above accepts "INNER", "LEFT", "RIGHT" or "FULL"; anything else, "CROSS" included, raises QueryBuildError at the call (#474)
• .select_for_update(; nowait, skip_locked, no_key, of) — SELECT … FOR UPDATE row lock on PostgreSQL (no lock clause on SQLite, where a transaction's BEGIN IMMEDIATE holds the write lock); of limits it to "self", relation paths the query joins, and cjoin_on aliases (FOR UPDATE OF …)
• .copy() — deep copy, to branch a chain without disturbing the original

Terminal methods

Each executes and returns a result. Every one below except .inspect() and .explain() takes show_query = :sql / :pretty / :dict / :params to render instead of executing; .inspect() is already an inspection call and takes operation = / connection = instead, and .explain() takes analyze = / buffers = / verbose =.

• .list() → Vector{PormGRow}; .list(:dict) → Vector{Dict}; .list(:json) → JSON String
• .list(T) → Vector{T} for a struct or NamedTuple type T (the typed read, #1352); .first(T), .last(T), .get(T, pairs...), .earliest(T, f...), .latest(T, f...) likewise
• query |> DataFrame — preferred for analytical queries
• .get(pairs...) — exactly one row, or DoesNotExist / MultipleObjectsReturned
• .first() / .last() — one row or nothing, using the ordering already on the query (.last inverts it, falling back to primary-key descending when none is set)
• .earliest(fields...) / .latest(fields...) — replace the ordering with fields (ascending / descending) and take one row; at least one field is required, and an empty queryset raises DoesNotExist rather than returning nothing
• .count(column = nothing; distinct = false) / .exists() — checks without fetching rows
• .aggregate(pairs...) — whole-queryset aggregation with no GROUP BY; returns a NamedTuple
• .create(pairs...) — insert one row, returned as a PormGRow
• .update(pairs...) — update every matching row
• .get_or_create(lookup...; defaults) / .update_or_create(lookup...; defaults) → (row, created)
• .delete() — delete every matching row
• .inspect() — the inspect_query metadata Dict
• .explain(; analyze = false) — the database's plan for the SELECT, as an explain_query Dict

Examples

using PormG, DataFrames
using PormG.Functions: Count

# Accumulate on a variable — clearest for multi-step queries
query = M.Result.objects
query.filter("driverid__surname" => "Senna", "positionorder" => 1)
query.values("raceid__year", "raceid__name", "constructorid__name")

df    = query |> DataFrame
wins  = query.count()
any_  = query.exists()

# Inline chain — trailing dots; a leading dot on the next line is a ParseError
podiums = M.Result.objects.
    filter("raceid__year" => 2020, "positionorder__@lte" => 3).
    values("driverid__surname", "n" => Count("resultid")).
    order_by("-n").
    limit(10).
    list()