Hasql-check: check your hasql-th SQL against a real PostgreSQL schema

Announcements
1

We love and make extensive use of hasql-th, as a big chunk of our application is written in pure SQL. I personally think that we have been spoiled with this piece of technology that other languages could only dream about and only recently started enjoying. Today we want to contribute back a piece of tooling that has saved our backs a few times already.

hasql-th checks that the SQL in a quasiquote parses. It cannot check that it means anything against your schema: the misspelled column, the one a migration dropped, the int8 you annotated int4. Those are found at runtime, by the first request that happens to hit that statement.

hasql-check asks Postgres instead. It finds the hasql-th quotes in your source and PREPAREs each statement against a live database:

$ hasql-check src/Shop/Customers.hs
Error in src/Shop/Customers.hs:12:5
ERROR:  column "emial" does not exist
LINE 1: SELECT emial :: text FROM customer WHERE id = $1 :: int8
               ^
HINT:  Perhaps you meant to reference the column "customer.email".

@pranaysashank wrote the first version of the checker at work four years ago. It has been guarding our statements ever since, and has caught a good number of bugs that would otherwise have reached production.

Three ways to use it

1. As a command, in CI. Point it at a database with your migrations applied and give it every module; it exits 1 when a statement does not prepare. Two further checks are opt-in:

  • --nullability — a column annotated non-null that can in fact be null, because the column is nullable or because it comes from the nullable side of an outer join. (hasql decodes nickname :: text as Text; a NULL there fails at runtime, for that one row.)
  • --types — an annotation that narrows a value: total_cents :: int4 on a bigint column, or an int4 parameter assigned to a smallint column. Both work until a value does not fit.

An existing codebase will have findings, so you can record them with --write-baseline and fail CI only on new ones. Entries are keyed by a hash of the statement, so moving or reformatting a quote keeps its entry and changing its SQL drops it. A single finding can be allowed in place with a comment above the quote: -- hasql-check: allow nullable(nickname).

2. As a drop-in for Hasql.TH, while GHC compiles. Hasql.Check.TH re-exports hasql-th’s quasiquoters and checks each statement as it expands:

import Hasql.Check.TH    -- instead of: import Hasql.TH

With HASQL_CHECK_DATABASE_URL set, a bad statement is a GHC error at the quote, in the build output and in your editor. Without the variable nothing is checked, so CI and release builds behave exactly as with Hasql.TH, and if the database cannot be reached the build warns once and carries on. Each check is about a millisecond, over one connection shared by every quote.

3. Without annotations at all — the new part in 0.5. Hasql.Check.Inferred.TH has quoters that take plain SQL and get the parameter and result types from the Haskell type the statement is used at:

import Hasql.Check.Inferred.TH

profile :: Statement Int64 (Maybe (Text, Maybe Text))
profile = [maybeStatement| select email, nickname from customer where id = $1 |]

The quoter annotates the parameters and casts the result columns to the types they are read as, in a projection around your statement, which is left as written:

WITH hasql_check_result (c1, c2) AS (SELECT email, nickname FROM customer WHERE id = $1 :: int8)
SELECT c1 :: text, c2 :: text FROM hasql_check_result

So a varchar column can be read as Text and an int4 one as Int64, which hasql’s decoders otherwise refuse at runtime. The type need not be on the binding, as GHC infers it from how the rows are used, like any other value.

Your own types work through one class: a newtype deriving Value is sent as what it wraps, deriving (Value) via Domain "quantity" Int16 casts to a domain so Postgres checks its constraints, and deriving (Value) via PgEnum "payment_state" PaymentState is a Postgres enum whose labels are the constructor names — which the checker compares with the enum’s, per statement: a label the Haskell type cannot read fails where the statement reads it, one the database does not have fails where it is sent.

Those types are only known after GHC has type-checked the module, which is after the quoter ran. So a companion package, hasql-check-ghc, gets them from GHC. Hasql.Check.Plugin checks the statements while the module compiles:

build-depends: hasql-check-ghc
ghc-options:   -fplugin=Hasql.Check.Plugin

src/Shop/Newtypes.hs:35:34: error:
    hasql-check: the statement does not fit HASQL_CHECK_DATABASE_URL's schema:
      column 1 (nickname) is text, but is read as Shop.Newtypes.Nickname (bigint), which Postgres converts it to only when told to, and can fail to at runtime

And hasql-check-hie reads the same types out of GHC’s .hie files for the CI run. It needs no database, so it can run inside a sandboxed build such as a Nix derivation, with only the checking step outside it:

$ cabal build --ghc-options='-fwrite-ide-info -hiedir=.hie'
$ hasql-check-hie $(find .hie -name '*.hie') > statements.jsonl
$ hasql-check --inferred statements.jsonl --nullability --types

Before and after

The types used to be written twice, in SQL and in Haskell, with nothing checking either against the schema:

import Hasql.TH

orderTotal :: Statement Int64 (Maybe Int32)
orderTotal =
    [maybeStatement|
        select total_cents :: int4
        from orders
        where id = $1 :: int8
    |]

Now the SQL says what it does, the Haskell type says what comes back, and total_cents being a bigint is a warning rather than an overflow in production:

import Hasql.Check.Inferred.TH

orderTotal :: Statement Int64 (Maybe Int32)
orderTotal = [maybeStatement| select total_cents from orders where id = $1 |]

Warning in src/Shop/Orders.hs:15:14
  column 1 (total_cents) is read as integer, but is a bigint: it may overflow

Limitations

  • The plugin and hasql-check-hie use GHC’s own library, so they follow GHC releases: this one supports 9.12 and 9.14. The hasql-check library and command do not depend on the GHC API, and are tested on 9.10 too.
  • The quoters without annotations do not take composite types yet, nor arrays of more than one dimension.
  • The nullability and narrowing checks skip statements they cannot take apart (UNIONs, SELECT *), and views and expressions such as count(*), about which Postgres does not say whether they can be null.

Links

Bug reports and questions welcome on the issue tracker.

7 Likes
2

Very cool, this feels like a lighter weight version of Nikita’s pGenie?