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})

Setup

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

Generating needs PostgreSQL 16 or later and psql; connection settings are psql's own (PGHOST, PGDATABASE, PGUSER, PGPASSWORD, ~/.pgpass). 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

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.<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	the schema types behind [:pg/check expr], [:pg/check-value expr] and [:pg/bytes {:min :max}]

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	[:int {:min ... :max ...}] with the type's range; bigint is :int
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, so keep the transformer's :zone at its default when such CHECKs exist.

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

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. Generating a dataset for a schema of tens of tables takes seconds to a minute: 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), columns, CHECK, PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, enum and domain types. Views, 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