Skip to main content

Query Analysis

Catch SQL type errors at test time, not runtime.

Query Analysis is foundations-jdbc's answer to the question: "How do I know my SQL queries will actually work?" Inspired by doobie's type checking, it verifies that your code's types match what the database expects — before your code ever runs in production.

The Problem​

Traditional JDBC gives you no compile-time or test-time feedback about your queries. You write SQL, you guess at types, and you pray. Errors show up as:

  • ClassCastException in production
  • Silent data truncation
  • NullPointerException from nullable columns you forgot about
  • Mysterious "wrong number of parameters" errors

The Solution​

Query Analysis uses JDBC metadata to verify your queries against the actual database schema. It compares vendor type names (e.g., int4, varchar, timestamptz) directly — no JDBC integer code mapping needed. Run it in your test suite, and you'll know immediately when:

  1. Parameter types don't match — You're passing a String where the database expects an Integer
  2. Column types don't match — Your RowCodec expects a timestamp but the column is a date
  3. Nullability is wrong — The column is nullable but your type isn't Optional
  4. Counts are off — Your RowCodec expects 5 columns but the query returns 4

Basic Usage​

AnalyzableScanner scans a package and discovers every query, template, and operation. QueryChecker verifies them all against the database. Together, they give you a single test that covers your entire data layer:

fun allQueriesTypeCheck() {
    val analyzables = AnalyzableScanner.scan("com.myapp.db")
    val checker = QueryChecker.create(transactor)
    checker.checkAll(analyzables)
}

Add a new query anywhere in the package, and it's automatically included in the next test run. No manual list maintenance.

What the Scanner Discovers​

The scanner finds everything that returns an Analyzable type — this includes Operation, Template, and RowTemplate. It discovers both fields and methods:

// Properties — discovered automatically
val allCities: Operation<List<City>> =
    sql { "SELECT id, name FROM cities" }
        .query(cityCodec.all())

// No-arg methods — discovered automatically
fun activeCities(): Operation<List<City>> =
    sql { "SELECT id, name FROM cities WHERE active" }
        .query(cityCodec.all())

// Methods with parameters — dummy arguments constructed automatically
fun findByName(name: String): Operation<City?> =
    sql { "SELECT id, name FROM cities WHERE name = ${PgTypes.text(name)}" }
        .query(cityCodec.maxOne())

// Templates — also discovered
val findById: Template<Int, City?> =
    Fragment.of("SELECT id, name FROM cities WHERE id = ")
        .param(PgTypes.int4)
        .query(cityCodec.maxOne())

Discovery rules​

WhatHow it's found
FieldsAny instance field whose type implements Analyzable
No-arg methodsCalled directly, return value collected
Methods with parametersDummy arguments constructed automatically, method invoked
TemplatesDiscovered like any other Analyzable field or method return
Private/static methodsSkipped — only public instance members are scanned

How classes are instantiated​

The scanner handles all three JVM languages:

SourceHow it's found
Java classesInstantiated via no-arg constructor (or Transactor constructor). Fields and methods are scanned.
Kotlin classesSame as Java — instantiated via no-arg constructor. Properties and methods are scanned.
Kotlin objectsDiscovered via INSTANCE singleton. Properties and methods are scanned.
Scala classesSame as Java — instantiated via no-arg constructor. Fields and methods are scanned.
Scala objectsDiscovered via MODULE$ singleton. Fields and methods are scanned.

The scanner recurses into subpackages, so scan("com.myapp") finds queries in com.myapp.users, com.myapp.orders, etc.

Each discovered query is automatically named ClassName.fieldName or ClassName.methodName (e.g. UserRepo.findById), so error reports pinpoint exactly which query failed.

tip

For classes that need a database connection at construction time, pass a Transactor to the scanner:

AnalyzableScanner.scan("com.myapp.db", transactor)

The scanner will try constructors that accept a Transactor parameter.

How dummy arguments work​

When the scanner encounters a method with parameters, it constructs dummy values to invoke the method. The actual argument values typically don't matter — the scanner only needs the method's return value (an Operation or Template) to extract its SQL and type information. If a method branches on its arguments and returns structurally different operations, use manual() directives to provide meaningful values.

The scanner can construct dummies for:

TypeDummy value
Primitives (int, boolean, etc.)Default values (0, false, etc.)
String""
BigDecimal, BigIntegerZERO
UUIDnew UUID(0, 0)
LocalDate, Instant, etc.Epoch / 2000-01-01
Optional, List, Set, MapEmpty
ArraysEmpty array
EnumsFirst constant
RecordsRecursive construction of components
Classes with constructorsTries shortest constructor first

If a parameter type can't be constructed (e.g., an interface like Runnable, or an abstract class), the scanner will fail with an error. You must handle these methods explicitly using Scan Directives — either skip() to exclude them or manual() to provide the arguments yourself.

Getter deduplication​

In Kotlin and Scala, properties generate both a backing field and a getter method. The scanner automatically deduplicates these — if a field named query exists, a no-arg method named query() (Scala-style) or getQuery() (Kotlin-style) is treated as a getter and skipped.

Methods with parameters are never treated as getters, even if they share a name with a field.

Scan Directives​

When the scanner encounters a method it can't auto-invoke — for example, a parameter is an interface type, or you need specific argument values — it fails with an error telling you which method and why. Scan directives tell the scanner how to handle these methods.

skip() — exclude a method​

Use skip() when a method shouldn't be type-checked at all:

fun checkWithDirectives() {
    val analyzables = AnalyzableScanner.scan(
        "com.myapp.reports",

        // Skip a method entirely — it won't be type-checked
        skip(ReportRepo::class.java, "generateReport")
    )

    val checker = QueryChecker.create(transactor)
    checker.checkAll(analyzables)
}

manual() — provide specific arguments​

Use manual() when you want a method to be type-checked but the scanner can't construct the right arguments. You provide a variant name, call the method yourself, and pass the result:

fun checkWithManualDirective() {
    val repo = ReportRepo()

    val analyzables = AnalyzableScanner.scan(
        "com.myapp.reports",

        // Provide specific arguments for a method
        manual(
            ReportRepo::class.java, "filteredReport", "defaults",
            repo.filteredReport(ReportRepo.ReportFilter("all", 100)))
    )

    val checker = QueryChecker.create(transactor)
    checker.checkAll(analyzables)
}

You can provide multiple manual variants for the same method — each gets its own type check:

ScanDirective.manual(repo::search, "by-name", new Filter("alice", 10)),
ScanDirective.manual(repo::search, "all", new Filter("", 100))

Each variant appears in reports as ClassName.methodName[variantName].

instance() — add objects from outside the scan package​

Use instance() to include objects that live outside the scanned package, or that need special construction:

fun checkWithExternalObjects() {
    val external = ExternalRepo()

    val analyzables = AnalyzableScanner.scan(
        "com.myapp.db",

        // Add an object from outside the scanned package
        instance(external),

        // Add with per-instance overrides
        instance(external) {
            skip(ExternalRepo::class.java, "generateReport")
        }
    )

    val checker = QueryChecker.create(transactor)
    checker.checkAll(analyzables)
}

The instance() directive also supports per-instance overrides — you can skip or provide manual entries for specific methods on that instance.

Manual Check​

Some queries can't be discovered by the scanner — for example, queries built dynamically inside methods, or queries in classes that require constructor arguments the scanner can't provide. Use checker.check() to verify these individually:

fun checkQueryManually() {
    val query: Operation.Query<List<User>> =
        sql { """
            SELECT id, name, email
            FROM users
            WHERE id = ${PgTypes.int4(1)}
        """ }
            .query(userRowCodec.all())

    val checker = QueryChecker.create(transactor)
    checker.check(query)
}

Named Queries​

Give your queries names for clearer error reports:

fun analyzeNamedQuery() {
    val query =
        sql { """
            SELECT id, name, email
            FROM users
            WHERE id = ${PgTypes.int4(userId)}
        """ }
            .query(userRowCodec.all())
            .named("findUserById")

    // The name shows up in the error report
    val analysis =
        QueryAnalyzer.analyze(query, connection)
            .single()

    if (!analysis.succeeded()) {
        throw AssertionError(analysis.report())
    }
}

Named queries show the name in the report header, making it easy to find which query failed in a large test suite. The scanner names queries automatically (ClassName.fieldName), so naming is mainly useful for manual checks.

Reading the Report​

When analysis fails, you get a detailed report showing exactly what went wrong:

╔══════════════════════════════════════════════════════════════════════════════╗
║  Query Analysis Report                                                       ║
╚══════════════════════════════════════════════════════════════════════════════╝

SQL (findUserById):
  SELECT id, name, created_at, status FROM users WHERE id = ?

┌─ Parameters ─────────────────────────────────────────────────────────────────┐
│  ✓ param[1]: int4                 → int4                                     │
└──────────────────────────────────────────────────────────────────────────────┘

┌─ Columns ────────────────────────────────────────────────────────────────────┐
│  ✓ col[1]: int4                   → id : int4                                │
│  ✓ col[2]: text                   → name : varchar                           │
│  ✗ col[3]: int4                   → created_at : timestamptz                 │
│  ✗ col[4]: (missing)              → status : varchar                         │
└──────────────────────────────────────────────────────────────────────────────┘

✗ 2 error(s) found:

  1. Column 3 'created_at': type mismatch
     │ Declared: int4 (accepts: int4)
     │ Returned: timestamptz
     └ The declared type does not match the returned vendor type "timestamptz"

  2. Column 4 'status' is returned by query (varchar) but not declared in RowCodec

Error Types​

Parameter Type Mismatch​

When you pass a parameter of the wrong type:

Parameter 1: type mismatch
  │ Declared: text (accepts: text)
  │ Expected: int4
  └ The declared type does not match the expected vendor type "int4"

Fix: Change the parameter type to match what the database expects.

Column Type Mismatch​

When your RowCodec expects a different type than the database returns:

Column 2 'price': type mismatch
  │ Declared: int4 (accepts: int4)
  │ Returned: numeric
  └ The declared type does not match the returned vendor type "numeric"

Fix: Use the correct DbType in your RowCodec. Here, use PgTypes.numeric instead of PgTypes.int4.

Nullability Mismatch​

When a nullable column isn't wrapped in Optional:

Column 3 'email': nullability mismatch
  │ The database says this column is nullable
  │ But the type text is not Optional
  └ Use .opt() to make the type nullable, .nullableOk() to suppress this warning,
    or ensure the column is NOT NULL

Fix: Use .opt() on the type: PgTypes.text.opt() instead of PgTypes.text. Or use .nullableOk() if you know it's safe (see Escape Hatches).

Missing Column​

When your RowCodec expects more columns than the query returns:

Column 5 is declared in RowCodec (boolean) but not returned by query

Fix: Either add the missing column to your SELECT, or remove it from your RowCodec.

Extra Column​

When the query returns more columns than your RowCodec expects:

Column 4 'updated_at' is returned by query (timestamptz) but not declared in RowCodec

Fix: Either add the column to your RowCodec, or remove it from your SELECT.

Escape Hatches​

Sometimes strict type checking is too strict. Two escape hatches let you selectively relax checking:

.nullableOk() — Suppress Nullability Warnings​

Use when you know a column won't be null in practice, even though the database says it could be. Common with outer joins:

data class OrderRow(val userId: Int, val userName: String, val orderTotal: BigDecimal)

// The LEFT JOIN makes o.total nullable in the result set,
// but .nullableOk() tells analysis we'll handle it
val orderCodec: RowCodec<OrderRow> =
    RowCodec.builder<OrderRow>()
        .field(PgTypes.int4, OrderRow::userId)
        .field(PgTypes.text, OrderRow::userName)
        .field(PgTypes.numeric.nullableOk(), OrderRow::orderTotal)
        .build(::OrderRow)

fun analyzeLeftJoin() {
    val query =
        sql { """
            SELECT u.id, u.name, o.total
            FROM users u
            LEFT JOIN orders o ON u.id = o.user_id
        """ }
            .query(orderCodec.all())

    val analysis: QueryAnalysis =
        QueryAnalyzer.analyze(query, connection).single()
    if (!analysis.succeeded()) {
        throw AssertionError(analysis.report())
    }
}

.unchecked() — Skip Type Checking Entirely​

Use when you know the type is correct but the database metadata disagrees, or for computed columns with unpredictable types:

data class Stats(val name: String, val count: Int)

// .unchecked() skips type checking entirely for this column
val statsCodec: RowCodec<Stats> =
    RowCodec.builder<Stats>()
        .field(PgTypes.text, Stats::name)
        .field(PgTypes.int4.unchecked(), Stats::count)
        .build(::Stats)

Routine Analysis​

Verify stored procedures and functions against the database:

// Verify a stored function matches the database definition
fun checkStoredFunction() {
    val addUser =
        Procedure.buildFunction(
            "add_user",
            listOf(
                ParamDef.input(PgTypes.text),
                ParamDef.input(PgTypes.text)
            ),
            PgTypes.int4
        )

    val checker = QueryChecker.create(transactor)
    checker.checkRoutine(addUser)
}

Routine analysis checks:

  • The routine exists in the database
  • Parameter count matches
  • Parameter types match (by vendor type name)
  • Parameter modes match (IN, OUT, INOUT)
  • Return type matches (for functions)

Analyzing Composed Operations​

When you compose operations with .combine()/.combineWith(), .then(), or Operation.ifEmpty(), the checker walks the entire operation tree and verifies every SQL statement:

fun analyzeComposedOperation() {
    // Build a composed operation
    val transaction: Operation<*> =
        insertUser.on("Alice")
            .thenIgnore(allUsers)

    // Analyze every SQL statement in the tree — one call
    val results: List<QueryAnalysis> =
        QueryAnalyzer.analyze(transaction, conn)

    for (analysis in results) {
        if (!analysis.succeeded()) {
            System.err.println(analysis.report())
        }
    }
}

This walks the entire operation tree and returns one QueryAnalysis per SQL statement found.

Dynamic SQL Analysis​

When a template uses .optionally(), analysis automatically expands all 2^N structural variants. Each variant is prepared against the database and verified independently.

For example, a template with 3 optional predicates produces 8 combinations — all checked with a single checker.check() call:

name filteremail filteractive flagSQL WHERE clause
absentabsentabsentWHERE 1=1 ORDER BY name
presentabsentabsentWHERE 1=1 AND name ILIKE ? ORDER BY name
absentpresentabsentWHERE 1=1 AND email ILIKE ? ORDER BY name
presentpresentabsentWHERE 1=1 AND name ILIKE ? AND email ILIKE ? ORDER BY name
absentabsentpresentWHERE 1=1 AND active = TRUE ORDER BY name
presentabsentpresentWHERE 1=1 AND name ILIKE ? AND active = TRUE ORDER BY name
absentpresentpresentWHERE 1=1 AND email ILIKE ? AND active = TRUE ORDER BY name
presentpresentpresentWHERE 1=1 AND name ILIKE ? AND email ILIKE ? AND active = TRUE ORDER BY name

If any variant has a type error, the analysis report tells you exactly which combination failed and why.

Further Reading​

See Query Analysis Reference for internals, database support matrix, and API reference.