Drizzle migrations on a TLS-only Postgres what sslmode=require checks, what the migrator skips, and a Rust preflight gate

Encrypted is not verified, and applied is not checked. One gate compares the folder with the table before every migrate.

The problem

A small team keeps its database schema in a TypeScript file. It generates SQL migrations from that file with drizzle-kit, the command-line tool of Drizzle ORM (an object-relational mapper, a library that maps database tables to code). A deploy job applies the migrations to a managed PostgreSQL cluster. The provider shape here is DigitalOcean Managed PostgreSQL, whose default connection string names user doadmin and database defaultdb and ends in sslmode=require. The lab reproduced that shape with a local PostgreSQL 17 server that accepts only TLS connections, not a real cluster.

The team pastes the string into the deploy job, and the first failure arrives at once. The drizzle-kit migrate step exits 1 with no error text, only a security warning and a spinner (lines trimmed; [exit 1] is the exit code, printed by the test script):

[⣷] applying migrations...(node:22141) Warning: SECURITY WARNING: The SSL modes 'prefer', 'require', and 'verify-ca' are treated as aliases for 'verify-full'.
[⣷] applying migrations...[exit 1]

Once the connection is fixed, a quieter failure follows: a migration merged from a feature branch never runs, yet migrate still prints "migrations applied successfully!". Nobody looks for a cause until a query hits the missing table.

The idea, in plain words

TLS (Transport Layer Security) encrypts a connection, but only certificate verification proves the server is the one you meant to reach. The sslmode setting controls the checking, and each client library reads the same value its own way, so the fix is to pin verify-full and give the client the provider's CA (certificate authority) file. Drizzle's migrator keeps a table of applied migrations but decides what to run by comparing one timestamp. A deploy therefore needs a separate check that compares the migrations folder with that table, file by file, before migrating. That check is a short Rust program with a strict exit-code contract. Nix, a package manager and build tool, builds it and wraps it in a checked shell command that a deploy job can call. Then nix flake check, which builds every check declared in the project's flake.nix, proves the contract against a real PostgreSQL on every change. The rule to carry: encryption is not identity, and a migrations table is not a ledger.

What sslmode checks, client by client

A server certificate binds a public key to a host name under a CA's signature, and the CA is a party the client has agreed to trust. Host name verification checks that the dialed name appears in the certificate. libpq is PostgreSQL's C client library, used by psql. Eavesdropping is passive reading of traffic; MITM (man-in-the-middle) is an attacker on the path who relays traffic while impersonating the server. The PostgreSQL documentation lists what each libpq sslmode protects against:

The documentation adds that "if a root CA file exists, the behavior of sslmode=require will be the same as that of verify-ca". It recommends verify-full "in most security-sensitive environments". The provider's default string uses require, which "encrypts traffic in transit but does not verify the server identity". Standard Edition clusters ship a CA certificate that you save locally and reference with sslrootcert or PGSSLROOTCERT. Advanced Edition clusters use the system trust store, so libpq needs sslrootcert=system.

The lab ran six psql commands against the local server:

In the second run the connection used TLSv1.3 even though the client had no CA file, so nothing verified who answered. The fifth run shows verification working: the certificate is issued for localhost, so a connection by IP address fails the name check even with the right CA. The sixth run fails because the lab's CA is private and the system trust store does not contain it.

Node's driver reads the same string differently: node-postgres (pg 8.x) treats require as an alias for verify-full, as the opening warning says. The warning's other suggestion, uselibpqcompat=true&sslmode=require, switches on libpq semantics, which the pg-connection-string documentation maps to rejectUnauthorized: false unless a root certificate is given. In the lab, plain require failed with UNABLE_TO_VERIFY_LEAF_SIGNATURE, while the compat string connected and printed connected, ssl = true, without verifying anything.

Symptom: drizzle-kit migrate prints the SECURITY WARNING, a spinner line, and exits 1 with no error message.

Cause: drizzle-kit used the pg driver ("Using 'pg' driver for database querying"), which verified the certificate. The lab's private CA is not in Node's trust store, and drizzle-kit did not print the connection error. Running the same connection through pg directly prints UNABLE_TO_VERIFY_LEAF_SIGNATURE: unable to verify the first certificate; if the root CA is installed locally, try running Node.js with --use-system-ca.

Fix: Use sslmode=verify-full&sslrootcert=<path to the downloaded CA>. The second migrate run in the lab, with exactly that string, printed "migrations applied successfully!". Avoid uselibpqcompat=true&sslmode=require, because that only restores encryption without verification.

Generate, migrate, and the two records Drizzle keeps

A schema file is the TypeScript declaration of the tables. A migration is a SQL file that moves a database from one schema version to the next. Drizzle needs both, because the schema says where the database should end up and the migrations are the steps.

// src/schema.ts
import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";

export const notes = pgTable("notes", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  body: text("body").notNull().default(""),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.ts",
  out: "./drizzle",
  dbCredentials: { url: process.env.DATABASE_URL! },
});

The schema declares a four-column notes table; the config names the dialect, the schema file and the ./drizzle output folder. The connection string comes from DATABASE_URL, so credentials stay out of the repository and each environment passes its own string. In the generated SQL below, serial becomes an auto-incrementing key and defaultNow() becomes DEFAULT now().

Running drizzle-kit generate --name create_notes wrote three files:

$ find drizzle -type f
drizzle/meta/0000_snapshot.json
drizzle/meta/_journal.json
drizzle/0000_create_notes.sql

$ cat drizzle/0000_create_notes.sql
CREATE TABLE "notes" (
	"id" serial PRIMARY KEY NOT NULL,
	"title" text NOT NULL,
	"body" text DEFAULT '' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);

The SQL file is one CREATE TABLE statement that mirrors the four schema columns, with each constraint spelled out. Nothing in the folder records whether any database has run it; that record lives in the database itself. The meta directory holds _journal.json, the ordered index of migrations, and a snapshot of the schema. After the second migration, the journal looked like this:

{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1792691061837,
      "tag": "0000_create_notes",
      "breakpoints": true
    },
    {
      "idx": 1,
      "version": "7",
      "when": 1792691063432,
      "tag": "0001_add_pinned",
      "breakpoints": true
    }
  ]
}

Each entry has an idx position, a format version, a tag that names the SQL file, a breakpoints flag, and when, a millisecond timestamp that drizzle-kit writes at generate time. The entry order and the when values are what the migrator consults later.

On migrate, drizzle-kit reads the migration files, fetches the history from the database, picks the migrations not yet applied, and applies them. The history lives in a table named __drizzle_migrations in the drizzle schema, with columns id, hash and created_at. In the installed drizzle-orm 0.45.4 source, hash is the SHA-256 digest (a 256-bit cryptographic fingerprint) of the whole .sql file. created_at is the journal's when, and the file is split on --> statement-breakpoint into separate statements. The lab checked this after the first migrate:

 id |    hash12    |  created_at   
----+--------------+---------------
  1 | 23d2d0839b50 | 1792691061837
(1 row)

23d2d0839b508b38b66e02217363b16ae7aa5b68ecde2d7847454f9c80e94586  drizzle/0000_create_notes.sql

The row's hash prefix 23d2d0839b50 equals the start of the shasum -a 256 digest of 0000_create_notes.sql, and created_at equals the first journal entry's when. So the table records which file content ran and under which timestamp, and the gate built below recomputes exactly those two values. Adding a pinned boolean column to the schema and generating again wrote 0001_add_pinned.sql, a single line: ALTER TABLE "notes" ADD COLUMN "pinned" boolean DEFAULT false NOT NULL;. The next migrate applied it.

Symptom: The Drizzle docs show one folder per migration, for example 20242409125510_premium_mister_fear, but the project has flat 0000_name.sql files plus meta/_journal.json.

Cause: The layout depends on the drizzle-kit version. Version 0.31.11 wrote the flat layout in this lab.

Fix: Treat meta/_journal.json as the index of what exists and in what order. Any tooling should read the journal rather than guess from file names, as the gate below does.

What the migrator compares, and the two edits it never notices

Sam Newman describes schema changes as version-controlled delta scripts that are "run in strict order in an idempotent manner", meaning each step can be repeated without extra effect. The DevOps Handbook says the scripts behind "database migration" belong in version control with the application. Drizzle's migrator rule breaks the ordering promise, and the decision fits in four lines of drizzle-orm 0.45.4 source (excerpted, in source order):

select id, hash, created_at from ... order by created_at desc limit 1
await session.transaction(async (tx) => {
if (!lastDbMigration || Number(lastDbMigration.created_at) < migration.folderMillis) {
insert into ... ("hash", "created_at") values(${migration.hash}, ${migration.folderMillis})

In plain words, the migrator reads only the newest applied row, ordered by created_at, then applies every newer entry inside one transaction. A journal entry runs only if its when (called folderMillis in the source) is newer than that row. Every run re-reads and re-hashes every file, but the stored hash is never compared with the new one. That makes two edits invisible, and both are lab-proven.

The first simulates a merge. A teammate's branch migration, 0002_add_tags, gets a when between entries 0 and 1 and is appended to the journal with jq:

{"idx":0,"when":1792691061837,"tag":"0000_create_notes"}
{"idx":1,"when":1792691063432,"tag":"0001_add_pinned"}
{"idx":2,"when":1792691062634,"tag":"0002_add_tags"}

migrate printed "migrations applied successfully!", yet the follow-up query returned tags_exists as f. The newest applied row carries 1792691063432, which is larger than the new entry's 1792691062634, so the entry was skipped. The second edit appended a CREATE INDEX statement to the already-applied 0000_create_notes.sql. migrate again reported success, and index_exists was f, because the entry's when already had a row and nothing compared the edited file's hash with the stored one.

Symptom: migrate reports success, but the table or index from a migration does not exist.

Cause: Either the migration's when is older than the newest applied row (a merged branch), or the file was edited after it ran and the hash is never re-checked.

Fix: Never edit an applied migration; write a new one. After merging, regenerate a branch migration on top of the main line so it gets a fresh when. Gate the deploy on a check that compares the folder with the table, built next. The lab tested these two cases only.

The preflight gate: a cargo script, then a Nix package

A cargo script is a single .rs file that cargo, Rust's build tool, compiles and runs directly. Its dependency list sits in a ---cargo fence at the top, and it runs as cargo +nightly -Zscript file.rs. It needs the nightly toolchain and is unstable, so the lab ran the gate this way while iterating, then moved the same code into a normal Cargo project for packaging. The gate starts with its dependencies and the shape of the journal:

#!/usr/bin/env -S cargo +nightly -Zscript
---cargo
[package]
edition = "2024"

[dependencies]
postgres = "0.19"
postgres-native-tls = "0.5"
native-tls = "0.2"
serde = { version = "1", features = ["derive"] }
serde_json = "1"
sha2 = "0.10"
---

#[derive(Deserialize)]
struct Journal {
    entries: Vec<Entry>,
}

#[derive(Deserialize)]
struct Entry {
    tag: String,
    when: i64,
}

The fence declares the Rust edition and six crates: the PostgreSQL client, its TLS adapter, the TLS library, serde and serde_json for JSON, and sha2 for SHA-256. The two structs keep only the journal fields the gate needs, tag and when, so that unrelated journal fields can change without breaking the parse. Next comes the TLS connector and the read of the migration history:

let mut tls = TlsConnector::builder();
if let Ok(ca_path) = env::var("PGSSLROOTCERT") {
    let pem = fs::read(&ca_path).unwrap_or_else(|e| fail(format!("{ca_path}: {e}")));
    let ca = Certificate::from_pem(&pem).unwrap_or_else(|e| fail(format!("{ca_path}: {e}")));
    tls.add_root_certificate(ca);
}
let tls = MakeTlsConnector::new(tls.build().unwrap_or_else(|e| fail(e.to_string())));
let mut db = postgres::Client::connect(&url, tls).unwrap_or_else(|e| fail(format!("connect: {e:?}")));

let rows = db
    .query("select hash, created_at from drizzle.__drizzle_migrations order by created_at", &[])
    .unwrap_or_else(|e| fail(format!("query: {e}")));

If PGSSLROOTCERT is set, the gate reads that PEM file (a text format for certificates) and adds it to the connector's trusted roots. Every failure goes through fail, which exits with code 3, so a bad path, a TLS error and a failed query all land in one "connection or usage" bucket. The comparison is a loop over the journal:

let (mut pending, mut drift) = (0, 0);
for e in &journal.entries {
    let file = format!("{dir}/{}.sql", e.tag);
    let sql = fs::read(&file).unwrap_or_else(|err| fail(format!("{file}: {err}")));
    let hash = sha256_hex(&sql);
    match applied.iter().find(|(_, at)| *at == e.when) {
        Some((h, _)) if *h == hash => println!("APPLIED   {}", e.tag),
        Some(_) => {
            drift += 1;
            println!("MODIFIED  {}  file changed after it was applied (sha256 differs)", e.tag)
        }
        None if e.when > last => {
            pending += 1;
            println!("PENDING   {}  migrate will apply it", e.tag)
        }
        None => {
            drift += 1;
            println!("SKIPPED   {}  when={} is older than the last applied ({last}); migrate will never run it", e.tag, e.when)
        }
    }
}
for (h, at) in &applied {
    if !journal.entries.iter().any(|e| e.when == *at) {
        drift += 1;
        println!("UNKNOWN   created_at={at} hash={}...  applied but not in this folder", &h[..12]);
    }
}
println!("summary: {} in journal, {} applied rows, {pending} pending, {drift} drift", journal.entries.len(), applied.len());
exit(if drift > 0 { 2 } else if pending > 0 { 1 } else { 0 })

Each journal entry is matched to a database row by when, then classified by whether the stored hash equals the file's current SHA-256. An entry with no row is PENDING when it is newer than every applied row, which is the only case the migrator would run, and SKIPPED otherwise. A final loop reports UNKNOWN for any applied row that no journal entry explains. The last line turns the counts into the exit code:

Run against the lab database, the gate caught every situation from the two sections above:

The Rust client has its own reading of sslmode: the postgres crate only knows disable, prefer and require, and its native-tls connector uses the system trust roots, add_root_certificate adds to that set, and certificate and host name validation stay on unless code calls the danger_accept_invalid_* methods. So sslmode=require in this client still verifies.

Symptom: The gate exits 3 with code: -25318, message: "One or more certificates required to validate this certificate cannot be found." while psql connects with the same URL.

Cause: libpq's require does not verify the certificate, and native-tls does. The private CA is unknown to the system store.

Fix: Set PGSSLROOTCERT to the CA file. That run printed the two APPLIED lines and exited 0. Connecting by 127.0.0.1 with the CA set failed with -67602 and "A host name mismatch has occurred.", the same name check psql reported.

The packaging is one flake.nix:

{
  description = "drizzle-gate: refuse a deploy when the migrations folder and the database disagree";

  inputs.nixpkgs.url = "github:NixOS/nixpkgs/nixos-unstable";

  outputs = { self, nixpkgs }:
    let
      systems = [ "aarch64-darwin" "x86_64-linux" "aarch64-linux" ];
      forAll = f: nixpkgs.lib.genAttrs systems (system: f nixpkgs.legacyPackages.${system});
    in
    {
      packages = forAll (pkgs: rec {
        drizzle-gate = pkgs.rustPlatform.buildRustPackage {
          pname = "drizzle-gate";
          version = "0.1.0";
          src = pkgs.lib.fileset.toSource {
            root = ./.;
            fileset = pkgs.lib.fileset.unions [ ./Cargo.toml ./Cargo.lock ./src ];
          };
          cargoLock.lockFile = ./Cargo.lock;
        };

        # The command a deploy job runs before it migrates.
        db-preflight = pkgs.writeShellApplication {
          name = "db-preflight";
          runtimeInputs = [ drizzle-gate ];
          text = ''
            dir=''${1:-drizzle}
            set +e
            drizzle-gate "$dir"
            code=$?
            set -e
            case "$code" in
              0) echo "preflight: database matches $dir" ;;
              1) echo "preflight: pending migrations only, safe to migrate" ;;
              *) echo "preflight: refusing to deploy (drizzle-gate exit $code)" >&2; exit "$code" ;;
            esac
          '';
        };

        default = db-preflight;
      });

      # Prove the exit-code contract against a real, throwaway PostgreSQL inside the build sandbox.
      checks = forAll (pkgs: {
        package = self.packages.${pkgs.stdenv.hostPlatform.system}.drizzle-gate;
        gate-against-postgres = pkgs.runCommand "gate-against-postgres"
          { nativeBuildInputs = [ pkgs.postgresql_17 pkgs.jq self.packages.${pkgs.stdenv.hostPlatform.system}.drizzle-gate ]; }
          ''
            export PGDATA=$TMPDIR/pg PGHOST=$TMPDIR
            initdb -U gate --auth=trust >/dev/null
            pg_ctl -o "-k $TMPDIR -c listen_addresses=" -l $TMPDIR/pg.log -w start >/dev/null
            psql -U gate -d postgres -qc 'create schema drizzle; create table drizzle.__drizzle_migrations (id serial primary key, hash text not null, created_at bigint)'
            mig=${./fixtures/drizzle}
            rec() { # rec <journal index>: record entry N as applied, the way the migrator does
              tag=$(jq -r ".entries[$1].tag" $mig/meta/_journal.json)
              when=$(jq ".entries[$1].when" $mig/meta/_journal.json)
              hash=$(sha256sum "$mig/$tag.sql" | cut -d' ' -f1)
              psql -U gate -d postgres -qc "insert into drizzle.__drizzle_migrations (hash, created_at) values ('$hash', $when)"
            }
            expect() {
              set +e; DATABASE_URL="postgresql://gate@/postgres?host=$TMPDIR&sslmode=disable" drizzle-gate $mig; got=$?; set -e
              echo "[exit $got, want $1]"; test "$got" -eq "$1"
            }
            rec 0; expect 1
            rec 1; expect 0
            psql -U gate -d postgres -qc "update drizzle.__drizzle_migrations set hash = 'edited' where id = 1"; expect 2
            pg_ctl -m fast stop >/dev/null
            touch $out
          '';
      });
    };
}

buildRustPackage compiles the crate, fetching each dependency as a fixed-output derivation (a download Nix accepts only if its content hash matches) from the versions in Cargo.lock. The checkPhase runs cargo test by default; this crate has no unit tests, so the PostgreSQL check below carries the testing. writeShellApplication writes an executable to the store, checks it with shellcheck and bash -n, sets errexit, nounset and pipefail, and puts runtimeInputs on PATH. Under errexit the shell would abort at the gate's non-zero exit before the case could read it, so the wrapper switches it off with set +e, captures code=$?, and switches it back on.

The gate-against-postgres check starts PostgreSQL 17 in the build's temporary directory, listening only on a Unix socket (listen_addresses= is empty), records rows the way the migrator does, and expects exits 1, 0 and 2. The check connects with sslmode=disable over the socket, so it exercises the comparison logic but not the TLS path. The lab machine ran Nix with sandbox = false, so build isolation was not tested. The first nix flake check -L run ended like this:

gate-against-postgres> [exit 1, want 1]
gate-against-postgres> [exit 0, want 0]
gate-against-postgres> [exit 2, want 2]
✅ checks.aarch64-darwin.package
✅ checks.aarch64-darwin.gate-against-postgres
warning: The check omitted these incompatible systems: aarch64-linux, x86_64-linux
Use '--all-systems' to check all.
[nix exit 0]

Each [exit N, want N] line is the expect helper printing the gate's real exit code beside the expected one, and test fails the build on any mismatch, so a wrong code would turn the check red. By default nix flake check runs only the current system's checks, so Linux was omitted; --all-systems would cover it, and the lab did not run that. The generated wrapper starts like this:

#!/nix/store/i1pdjlwb27ki88zssg5q2h696v1r1m15-bash-5.3p20/bin/bash
set -o errexit
set -o nounset
set -o pipefail

export PATH="/nix/store/7yhma11g342m8n1cgdpnfj5i18vjs337-drizzle-gate-0.1.0/bin:$PATH"

dir=${1:-drizzle}
set +e
drizzle-gate "$dir"
code=$?
set -e

The three options appear as plain set -o lines, and the gate's store path is prepended to PATH, so the command finds drizzle-gate wherever it is installed. The doubled quotes in ''${1:-drizzle} in flake.nix were Nix's escape for a literal ${, which is why the generated script shows a plain ${1:-drizzle} default. Its runtime closure, listed by nix path-info -r, has four paths: libiconv, drizzle-gate, bash and db-preflight itself. That means the deploy host needs no Rust compiler. In the lab, the in-sync run printed "preflight: database matches drizzle" and exited 0. After a one-line edit to 0000_create_notes.sql, the run printed "preflight: refusing to deploy (drizzle-gate exit 2)" and exited 2.

Putting it together

The deploy job runs in this order, which closes both failures from the start.

1. Connect with sslmode=verify-full and sslrootcert pointing at the downloaded CA. On Advanced Edition, use sslrootcert=system with libpq 16 or later. The Rust crate accepts only disable, prefer and require for sslmode, so the gate gets the same URL with sslmode=require plus PGSSLROOTCERT. The lab never passed verify-full to the gate.
2. Run db-preflight drizzle.
3. On exit 0 or 1, run drizzle-kit migrate. On exit 2, stop and fix the folder by regenerating the branch migration or reverting the edit. On exit 3, fix the connection.
4. Point migrations at the cluster's direct connection, not a PgBouncer pool (a proxy that shares a few database connections among many clients). The provider's pool documentation says session mode is the one for prepared statements, advisory locks and other session-level features, and warns that pg_dump through a transaction-mode pool causes errors. Running migrations through a pool was not tested here.

Verified here: a local PostgreSQL 17.11 shaped like the provider, three clients, the migrator's behavior on drizzle-kit 0.31.11 and drizzle-orm 0.45.4, and the Nix build and check on aarch64-darwin with the Nix sandbox off. Not tested: a real DigitalOcean cluster or API call, the Advanced Edition system-trust path, a connection pool, and Linux.

Check yourself

Sources

• DigitalOcean docs, "How to Connect to PostgreSQL Database Clusters": https://docs.digitalocean.com/products/databases/postgresql/how-to/connect/
• PostgreSQL documentation, "libpq SSL Support": https://www.postgresql.org/docs/current/libpq-ssl.html
• pg-connection-string README (node-postgres): https://www.npmjs.com/package/pg-connection-string
• DigitalOcean docs, "Manage Connection Pools" (PostgreSQL): https://docs.digitalocean.com/products/databases/postgresql/how-to/manage-connection-pools/
• Drizzle docs, "drizzle-kit migrate": https://orm.drizzle.team/docs/drizzle-kit-migrate
• Drizzle docs, "Migrations fundamentals": https://orm.drizzle.team/docs/migrations
• drizzle-orm 0.45.4 (npm package), files migrator.js and pg-core/dialect.js
• The Cargo Book, "Unstable features: script": https://doc.rust-lang.org/nightly/cargo/reference/unstable.html#script
• docs.rs, postgres 0.19.14, Config: https://docs.rs/postgres/latest/postgres/config/struct.Config.html
• docs.rs, tokio-postgres 0.7.18, SslMode: https://docs.rs/tokio-postgres/latest/tokio_postgres/config/enum.SslMode.html
• docs.rs, native-tls 0.2.18, TlsConnectorBuilder: https://docs.rs/native-tls/latest/native_tls/struct.TlsConnectorBuilder.html
• Nixpkgs manual, "Compiling Rust applications with Cargo" (buildRustPackage): https://nixos.org/manual/nixpkgs/stable/#compiling-rust-applications-with-cargo
• Nixpkgs manual, writeShellApplication: https://nixos.org/manual/nixpkgs/stable/#trivial-builder-writeShellApplication
• Nix manual, nix flake check: https://nix.dev/manual/nix/latest/command-ref/new-cli/nix3-flake-check.html
• Gene Kim, Jez Humble, Patrick Debois, John Willis, The DevOps Handbook, chapter "Create the Foundations of Our Deployment Pipeline", section "Create Our Single Repository of Truth for the Entire System" (tome PDF pp. 174-175)
• Sam Newman, Building Microservices, 2nd ed., chapter 3 "Splitting the Monolith", section "Tooling" (tome PDF p. 117)