pgmalli

The applied PostgreSQL schema, as malli schemas.

pgmalli reads a database once (through psql), writes one EDN file per schema, and your application loads those files as a malli registry. Tables, columns, types, defaults, keys and constraints are all in there, so code and tests can be written and checked against the real contract without a database at hand.

(require '[pgmalli.core :as pgmalli] '[malli.core :as m] '[malli.error :as me] '[malli.generator :as mg])

(def registry (pgmalli/registry "public"))

;; a row as read from the database
(m/validate :pg.public/users row {:registry registry})

;; what an INSERT may carry: no identity or generated columns, defaults optional, closed map
(-> (m/explain :pg.public.users/insert {:email "x" :status "closed"} {:registry registry})
    me/humanize)
;; => {:closed_at ["chk_closed_at_matches_status"]}

;; test data that satisfies the constraints
(mg/generate :pg.public.users/insert {:registry registry})

;; enum values are strings; cast them when the row goes to SQL (HoneySQL shown)
{:insert-into :users :values [(update row :status #(vector :cast % :user_status))]}

Setup

io.github.chaploud/pgmalli {:mvn/version "..."}   ; deps.edn or bb.edn

Generating needs PostgreSQL 16 or later and psql, wherever generate or check runs (the development machine and CI; JVM base images rarely ship it). Connection settings are psql's own (PGHOST, PGDATABASE, PGUSER, PGPASSWORD, ~/.pgpass) or the :db map. Applications only need the generated files.

clojure -M -m pgmalli.main generate   # writes resources/pgmalli/<schema>.edn
clojure -M -m pgmalli.main check      # exit 1 when the files no longer match the database

Both read pgmalli.edn in the working directory when present:

{:schemas ["public"]                 ; default
 :out-dir "resources/pgmalli"        ; default; keep it on the classpath
 :overrides {"chk_legacy_flag" {:skip "removed together with the column in 2027"}
             "chk_scores" [:ref :app/scores-consistent]}
 :db {:host "localhost" :port 5432 :db "app_dev" :user "app"}}   ; optional; :password too, else PGPASSWORD or ~/.pgpass

The same from Clojure: (pgmalli/generate! config) and (pgmalli/stale config).

Convention: regenerate right after migrating and commit the files; in CI assert (nil? (pgmalli/stale config)).

What you get

For a schema public, the registry contains:

name	schema
:pg.public/<enum>	[:enum ...]
:pg.public/<domain>	base type with the domain's CHECKs applied (as column patterns, else [:pg/check-value expr]), [:maybe ...] unless the domain is NOT NULL. A domain's NOT NULL and DEFAULT reach the columns of that type
:pg.public/<table>	a valid row: [:map ...], wrapped in [:and ...] with the table's constraints when it has any
:pg.public/<view>	a row of a view or materialized view: columns and types, every column [:maybe ...], :pg/view on the map; no insert schema, not part of datasets
:pg.public.<table>/insert	what an INSERT may carry: identity ALWAYS and generated columns removed, identity BY DEFAULT, defaulted and nullable columns optional, {:closed true}; the table's constraints see an omitted column as what the database stores in it (its literal default, else NULL)
:pg/check, :pg/check-value, :pg/bytes, :pg/smallint, :pg/integer	the schema types behind [:pg/check expr], [:pg/check-value expr], [:pg/bytes {:min :max}] and the two bounded integers

Column schemas carry provenance in their properties: :pg/type, :pg/default (a literal or the default expression as data), :pg/identity (:always, :default or :serial), :pg/generated, :pg/constraint (names of the CHECKs that shaped it). Literal defaults also set malli's :default. The map carries :pg/table ("public.users"), :pg/primary-key, :pg/unique ({:columns [...]}, with :nulls-distinct false for NULLS NOT DISTINCT) and :pg/foreign-keys ({:columns [...] :table "public.groups" :to [...]}, with :match :full for MATCH FULL), composite keys included. Files generated by pgmalli 0.1 are refused when loaded; regenerate them.

Identifiers that are not plain names (Order Items) become string keys. Spelling is left as the database has it.

PostgreSQL to malli

PostgreSQL	malli
NOT NULL	no [:maybe ...]
column of an enum or domain type	[:ref :pg.<schema>/<type>]
smallint, integer	:pg/smallint, :pg/integer: schema types with the PostgreSQL range (CHECK bounds narrow them through :min and :max); bigint is :int, exactly a long
numeric(p, s)	[:and decimal? [:> -10^(p-s)] [:< 10^(p-s)]] (the scale rounds, it does not reject)
CHECK (col IN (...)), CHECK (col = 'x')	[:enum ...]; several on one column intersect; uuid values as #uuid
CHECK (col NOT IN (...)), CHECK (col <> 'x')	[:and <type> [:not [:enum ...]]], or removed from an [:enum ...]
CHECK (col >= a AND col <= b), BETWEEN, one-sided bounds	[:int {:min a :max b}]; :double likewise, an exclusive bound as [:and :double [:> a]]; numeric as [:and decimal? [:>= a] [:<= b]]
CHECK (length(trim(col)) > 0)	[:and [:string {:min 1}] [:re "\S"]]; col <> '' is [:string {:min 1}]
varchar(n), CHECK (length(col) <= n)	[:string {:max n}]; bounds from several sources only tighten
CHECK (cardinality(col) BETWEEN a AND b), CHECK (array_length(col, 1) <= n)	[:vector {:min a :max b} <T>]
CHECK (jsonb_typeof(col) = 'object')	:map ('array' becomes [:sequential :any])
CHECK (col ~ 're')	[:and :string [:re "re"]] (~* adds (?i); POSIX classes such as [[:digit:]] in their Java form)
CHECK (col LIKE 'a%')	[:and :string [:re "^\Qa\E.*$"]] (ILIKE adds (?i))
CHECK (col IS NULL OR <any of the above>)	[:maybe ...]
CHECK (col IS NOT NULL)	no [:maybe ...]
column patterns joined with AND	each part
CHECK (status = 'a' AND ... OR status = 'b' AND ...)	[:multi {:dispatch :status} ["a" [:map ...]] ["b" [:map ...]]]
CHECK (x IS NULL OR y = 'v' AND ...) and other ORs of column patterns	[:or [:map ...] [:map ...]]
any other CHECK (score <= total, arithmetic, CASE, jsonb operators), or one of the above on a column whose type has no such rendering (col = '2020-01-01'::date)	[:pg/check expr]: the expression as data, validated by a schema type pgmalli registers
domain CHECK outside the patterns	[:pg/check-value expr] on the domain, VALUE as :VALUE
NOT VALID CHECK (table or domain)	kept in :unrendered
date, time, timetz, timestamp, timestamptz, interval	:time/local-date, :time/local-time, :time/offset-time, :time/local-date-time, :time/instant, :time/duration
json, jsonb	:any (:map or [:sequential :any] when a CHECK pins the type)
bytea	bytes?; with CHECK (octet_length(col) = n) [:pg/bytes {:min n :max n}], a type that generates byte arrays of that length
T[]	[:vector <T>]
other types (ranges, inet, money, xml, extensions)	[:any {:pg/type ...}], listed in :unrendered

(pgmalli/unrendered "public") lists the facts that have no rendering, each with the constraint's expression as data. Give them one through :overrides, keyed by constraint name: a malli schema ([:ref :app/name] defined in your own registry, for instance) or {:skip "reason"}.

:pg/check keeps the expression as data ([:<= :score :total], HoneySQL-style) and evaluates it as PostgreSQL would: NULL passes, AND, OR and COALESCE stop at the first decisive operand, integer division truncates, casts convert (dates, timestamps, intervals of fixed length, uuids, jsonb, arrays and the schema's own enum and domain literals included), now() is the validation time, and an expression the database would fail on (division by zero, a cast that does not parse) fails the row. The vocabulary covers comparison, logic, IN, BETWEEN, IS DISTINCT FROM, arithmetic, the common string, numeric, array and jsonb functions and operators, LIKE, regexes and CASE; a CHECK outside it (user-defined functions, casts to range or geometric types, composite fields, AT TIME ZONE) stays in :unrendered; the column patterns of such a CHECK that did render stay applied, since each conjunct of an AND is necessary on its own, while a :multi or :or is never enforced in part. A column missing from the map is NULL to it (its literal default in an insert schema). Rows hold the registry's types: java.time values, UUID, jsonb as maps (string or keyword keys) and vectors. A CHECK comparing timestamp with timestamptz or now() converts in the JVM's zone; give the transformer the zone your JDBC driver used when it turned timestamps into Instants (the JVM's, unless you configured otherwise), so both read the same clock.

Working with the registry

(pgmalli/registry "public" "auth")          ; several schemas, plus malli's defaults, malli.util and malli.experimental.time
(pgmalli/columns registry :pg.public/users) ; the [:map ...] alone, for malli.util
(m/decode :pg.public/users jdbc-row {:registry registry} (pgmalli/transformer))
                                            ; JDBC (java.sql.Date / Timestamp, Instant) and string values into the registry's types
(pgmalli/transformer {:zone (java.time.ZoneId/of "UTC")})
                                            ; zone for Instants landing in timestamp (without time zone) columns; default the JVM's
(pgmalli/column registry :pg.public/users :nick)      ; => [:maybe [:string {:max 40 :pg/type "character varying"}]]
(pgmalli/non-null (pgmalli/column registry :pg.public/users :nick))   ; what a non-NULL value must be

Rows as the driver returns them

next.jdbc's result builders shape a row differently from the database's: keys may be qualified by the table, NULL columns may be missing, timestamps may arrive as Instants. as-read gives the [:map ...] of that shape, so results can be validated as they are.

(pgmalli/as-read registry :pg.public/users {:qualified? true        ; :users/id, as as-maps builds
                                            :nil-columns :absent    ; next.jdbc.optional drops NULLs
                                            :time :instant})        ; read-as-instant
;; => [:map {...} [:users/id [:pg/integer ...]] [:users/nick {:optional true} [:string ...]] ...]

:kebab? true matches the kebab builders; :time :local matches read-as-local.

Queries checked against the registry

pgmalli.honeysql reads HoneySQL query data, no database needed: the tables must exist, the columns a query selects, returns, inserts, sets or compares must exist, an INSERT must carry the columns its insert schema requires, an enum literal must be one of the enum's values. From the same data it derives the types of the query's parameters and of the rows it returns, as a malli function schema for malli.instrument or malli.dev. CTEs, subqueries and table functions are opaque: their columns exist but have no type. A subquery's columns are resolved in the subquery first, then in the statements around it.

(require '[pgmalli.honeysql :as h])

(h/check registry {:insert-into :users :values [{:group_id 1 :mood "angry"}]})
;; => [{:kind :missing-required-column :table "public.users" :column "score"}
;;     {:kind :enum-literal :column :mood :value "angry" :allowed #{"happy" "sad"}}]

(h/query-schema registry '[id] '{:select [:id :nick] :from [:users] :where [:= :id id]}
                {:qualified? true :nil-columns :absent :time :instant})
;; => [:=> [:cat [:int {...}]] [:sequential [:map [:users/id [:int {...}]] [:users/nick {:optional true} [:string {...}]]]]]

Options: :schema for unqualified table names (default "public"); :qualified?, :kebab?, :nil-columns and :time as in as-read. Date and timestamp columns are inst? (what the driver returns, and a schema malli's default registry reads); :time :instant or :local gives the malli.experimental.time types instead, which :malli/schema metadata cannot take.

Schemas where the registry cannot follow

:malli/schema metadata, malli.dev/start! and other tools read schemas through malli's default registry. portable gives the named schema as data that registry reads (with malli.experimental.time added for the time types): the schema's own enums and domains inlined, pgmalli's types as their malli counterparts, generation hints dropped. The CHECKs only pgmalli evaluates (:pg/check, :pg/check-value) are left out.

(defn find-user
  {:malli/schema [:=> [:cat (pgmalli/non-null (pgmalli/column registry :pg.public/users :id))]
                  [:maybe (pgmalli/portable registry :pg.public/users)]]}
  [id] ...)

Datasets (fixtures, seeds) are checked as a whole: primary keys and unique constraints (NULLS NOT DISTINCT respected) within a table, foreign keys (MATCH FULL respected) across tables, including tables of other schemas in the registry. Each constraint is a check of its own, named in the error. The generator points references at generated rows, solving references that share columns together, and handles self-references; tables in a reference cycle are not supported. When a registry is loaded it adds generation hints (:gen/min, :gen/max; the files carry none) so key columns are small positive integers, strings short and times recent.

(def dataset (pgmalli/dataset-schema registry))          ; {"public.groups" [...] "public.users" [...]}
(m/validate dataset {"public.groups" [{:id 1 ...}] "public.users" [{:group_id 1 ...}]} {:registry registry})
(clojure.test.check.generators/sample (pgmalli/dataset-generator registry {:rows 5 :except #{"public.audit_log"}}))
;; :rows wanted per table, picked from many more candidates; a reference that finds no fitting
;; row grows its target table; :except leaves tables out (no kept table may reference them)
(-> dataset meta :pgmalli/short)
;; => {"public.jobs" {:wanted 5 :got 0 :reasons [["{:params [\"chk_jobs_params\"]}" 200]]}}
;; tables that came out short, with what their candidate rows failed on

Rows come from the column schemas, with the branch of a :multi or :or filled in from its own fragment; a :pg/check holds by rejection, so a CHECK that wants structured jsonb leaves its table short. A dataset of 71 tables and 250 rows takes about a second; for a fixture, generate once with a fixed seed, keep the result as EDN, and let tests read that.

(clojure.test.check.generators/generate (pgmalli/dataset-generator registry {:rows 5}) 30 42)

dataset-schema and dataset-generator are built at runtime and contain functions; the generated files stay data.

Contract

Kept compatible; a change bumps the minor version.

1. The config keys :schemas :out-dir :overrides :db.
2. The generated file: {:schema :database-version :registry :unrendered :skipped} and the registry names above (insert schemas are derived at load time, not stored).
3. The fact vocabulary of :unrendered (pgmalli.impl.pattern).

pgmalli.impl.* may change without notice.

Scope

Tables (regular and partition parents), views and materialized views, columns, CHECK, PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, enum and domain types. Indexes, triggers, policies and privileges are not read. Expressions are read in the form PostgreSQL's deparser prints them.

Development

See CONTRIBUTING.md. The suite includes property-based round trips through PostgreSQL: expressions are stored as CHECK constraints and read back, and :pg/check verdicts are compared with PostgreSQL's own on generated rows.

License

MIT