Testing Your App

An application that depends on PormG wants to know, before an upgrade reaches production, whether the new version builds different SQL for the queries the application actually runs. The cheapest check is an offline golden-SQL suite: the application's real model files, a connection that renders SQL and never connects, inspect_query on the application's real query shapes, and the expected SQL pinned in the test. It opens no socket, it is deterministic, and it runs in seconds.

This page is the recipe. It pairs the offline suite with a small integration test that covers what SQL text cannot show, and it ends with the upgrade loop that uses both.

The fixture: `register_offline`

One call per connection key, before anything touches .objects:

using Test, PormG

PormG.Configuration.register_offline("db"; adapter = :postgres)

Configuration.register_offline (API reference) registers key as a connection that renders the dialect's SQL and refuses to run anything. Its keywords are:

Keyword	Default	Meaning
`adapter`	`:postgres`	The dialect: `:postgres` or `:sqlite`. MySQL has no offline form yet, and `:mysql` raises `InvalidConfigurationError`.
`server_version`	`nothing`	SQLite only: the SQLite version the SQL is rendered for, in SQLite's packed form (`3_045_000` for 3.45.0). See below.
`django_prefix`	`nothing`	The table prefix, when the app's models were imported from Django with one: the same value its `connection.yml` would give.
`change_data`	`true`	The `connection.yml` setting of the same name, which defaults to `false` there. Leave it `true` to inspect `update` and `create`.

An application with several keys registers each one: register_offline("db"), register_offline("db_archive"), and so on. Configuration.unregister_connection(key) removes one. Registering the same key again replaces the offline entry.

No driver package is needed. On PostgreSQL the SQL depends on no server version. On SQLite a few clauses depend on the version: NULLS FIRST/LAST renders natively from 3.30 and is emulated below it, window functions and RIGHT/FULL joins are refused below their versions, and a bulk statement is chunked by the version's bind-parameter limit. An offline SQLite key renders them for the server_version it states, never for the SQLite library of the machine that runs the suite, so two CI images pin the same SQL (#1452):

PormG.Configuration.register_offline("db"; adapter = :sqlite, server_version = 3_045_000)

Without server_version, a query that needs the version raises InvalidConfigurationError naming it, using SQLite or not. A bulk write always needs it, whatever its row count, since it is chunked by the bind limit. Every other query renders. State the version your production SQLite runs.

A feature the stated version is too old for raises BackendCapabilityError, as it would on that SQLite, and the message names the key and the version you gave it rather than a library on your machine (#1467):

PormG.Configuration.register_offline("db"; adapter = :sqlite, server_version = 3_024_000)
# a window function on a model bound to "db":
# BackendCapabilityError: A window function needs SQLite 3.25.0 or newer, for window functions (`OVER (…)`); the offline connection "db" renders for SQLite 3.24.0, the version `register_offline` was given (`server_version = 3024000`).

The SQL needs no driver, but the first calls are faster with one. Since #1732 the PostgreSQL terminals are precompiled in the driver extensions, so an offline :postgres key with no driver loaded runs its first calls on run-time inference; load LibPQ or Postgres in the test environment to get the precompiled path.

Load the model files after it

PormG.@import_models "db/models.jl" models
import .models as M

The models bind to the offline key the same way they bind to a loaded configuration: the folder "db" matches the key "db". Models.set_models(mod, "db") does the same for a module that is already defined.

Import the model files, not the application. When using App runs the app's own Configuration.load("db"; env = …), in its module body or its __init__, that load raises InvalidConfigurationError: a key registered offline is never replaced by a live pool, and force = true does not change that (#1451). Point @import_models at the app's models files, and keep whatever the suite needs from the app (a function that builds a query, say) in a file that does not load a configuration.

Without the fixture, the suite does not quietly reach a database either. Outside a REPL, registering models against a folder that nothing loaded raises InvalidConfigurationError (#1226). Before 0.9.0, PormG loaded the folder itself, so an "offline" suite opened a real pool on its first .objects. If your suite hits that error, add register_offline. Do not add a load, and do not pass implicit = true. A REPL still loads the folder implicitly, with a warning, so a suite you include from one needs register_offline there too.

Pin the SQL

inspect_query builds a query without running it and returns its SQL text and its bound parameters. Pin both. Squash the whitespace first, so that a release that only reflows the SQL does not fail the suite:

squash(sql) = replace(strip(sql), r"\s+" => " ")

@testset "Senna's results from 1988: two joins, two bound values" begin
    q = M.Result.objects.
        filter("driverid__surname" => "Senna", "raceid__year__@gte" => 1988).
        values("raceid__year", "points")
    info = inspect_query(q)
    @test squash(info[:sql_text]) ==
          "SELECT \"Tb_1\".\"year\" as \"raceid__year\", \"Tb\".\"points\" as \"points\" " *
          "FROM \"result\" as \"Tb\" " *
          "INNER JOIN \"race\" AS \"Tb_1\" ON \"Tb\".\"raceid\" = \"Tb_1\".\"raceid\" " *
          "INNER JOIN \"driver\" AS \"Tb_2\" ON \"Tb\".\"driverid\" = \"Tb_2\".\"driverid\" " *
          "WHERE \"Tb_2\".\"surname\" = \$1 AND \"Tb_1\".\"year\" >= \$2"
    @test info[:parameters] == ["Senna", 1988]
end

Pin a query where the application builds it, in the shape the application uses. One testset per distinct shape is enough. The point is to cover each join path, aggregate, subquery and write the application issues, not each value it binds.

Writes are pinned the same way. Every terminal takes show_query = :dict, which returns the same dictionary as inspect_query without running anything:

@testset "Zeroing one race's points: an UPDATE with the WHERE bound first" begin
    info = M.Result.objects.filter("raceid" => 1).update("points" => 0, show_query = :dict)
    @test squash(info[:sql_text]) ==
          "UPDATE \"result\" AS \"Tb\" SET \"points\" = \$2 WHERE \"Tb\".\"raceid\" = \$1"
    @test info[:parameters] == [1, 0]
end

The order of info[:parameters] is the order the placeholders are numbered. On PostgreSQL it can differ from the order the values appear in the text. Above, $1 is the WHERE value. Pinning both is what catches a value bound to the wrong placeholder.

A cascading delete() pins its plan, not its key set. When other models reference the one being deleted, a live database is read to plan the cascade: the root's primary keys, and which related tables hold rows. An offline key reads nothing (#1453). delete(show_query = :dict) returns the root-key read as written, then every related table's statement, scoped by an empty key list (= ANY($1) bound to [] on PostgreSQL, (1 = 0) on SQLite). Pin which statements the cascade runs and in what order. Which rows it reaches is the integration suite's to check. With no rows to read, the plan assumes related rows exist, so a PROTECT or RESTRICT relation refuses the dry run with ProtectedError. Pin that refusal by its type.

Refusals are pinned by type. When the application relies on PormG refusing something, pin it with the error type, never with the message text:

@testset "An offline key renders SQL but never runs it" begin
    @test_throws InvalidConfigurationError M.Driver.objects.filter("surname" => "Senna").list()
end

The error types are listed on the Error Handling page.

What it catches, and what it cannot

The offline suite catches	It cannot catch
A change in the rendered SQL: joins, aliases, grouping, ordering, the clauses a function compiles to	The Julia type a read returns (`Float64` vs `Decimal`, a `Vector`, a `DateTime` vs a `ZonedDateTime`)
A change in the bound parameters, their order or their values	The rows a query returns
A query that now raises at build time, or no longer raises	Anything the engine decides: a collation, an index, a constraint, a server-version difference

The right column needs a database. Cover it with one integration test per shape, not per query, against the application's test database:

using Test, PormG, SQLite

PormG.Configuration.load("db"; env = "test")
PormG.@import_models "db/models.jl" models
import .models as M

@testset "Senna's 1991 season: 16 rows, points read as Float64" begin
    rows = M.Result.objects.
        filter("driverid__surname" => "Senna", "raceid__year" => 1991).
        values("raceid__round", "points").
        list()
    @test length(rows) == 16
    @test first(rows).points isa Float64
    @test sum(r -> r.points, rows) == 96.0
end

Keep that file in its own process, apart from the offline suite. A key is either offline or loaded: a load of a key the offline suite registered raises InvalidConfigurationError (#1451).

Why not a hand-written mock backend

Before register_offline existed (#1229), applications built this fixture by hand:

# ✗ a type-only backend written into PormG's internal dictionary
struct DummyPostgres <: PormG.PormGPostgres end
PormG.config["db"] = PormG.Configuration.Settings(connections = DummyPostgres(), …)

That writes PormG.config and calls the Settings constructor, both internal. Their fields (db_def_folder has to match the models folder, for one) can change in any release with no upgrade entry. It also subtypes an abstract backend with a struct that is not a pool, so a query that reaches a pool method or a server-version check fails with an error about the backend, not about the query. Replace it with register_offline. To ask which keys are registered, call Configuration.loaded_keys(), not the dictionary.

The upgrade loop

With both suites green on the PormG version the application pins today:

Bump the pin in the application's Project.toml [compat], then Pkg.update("PormG").
Read what changed. PormG.upgrade_guide(from = v"0.9"), with the version the application was pinned to, lists every entry since. It reads the change log shipped with the PormG that is now installed, so it comes after the update. See Upgrading PormG.
Run the offline suite. Each failing pin is a diff between the SQL the application issued and the SQL it will issue now.
Read each diff against the entries. If an entry explains it and its before → after applies to the call, edit the call. If the entry says the new SQL is the fix, re-pin the new SQL. A diff that no entry explains is a behavior change that shipped without warning: report it at PormG's issues. Whitespace never shows up as a diff, because the pins are squashed.
Run the integration shapes, for what the SQL text cannot show.

Pin each SQL string by running the query, never by writing it from memory. After step 4, the pins record what the new version builds, and the next upgrade starts from them.