CipherStash Stack - Drizzle ORM Integration

Guide for integrating CipherStash field-level encryption with Drizzle ORM using @cipherstash/stack-drizzle (EQL v3). Provides Drizzle-native encrypted column factories and query operators that transparently encrypt search values — Drizzle never sees plaintext in a query.

In EQL v3 every encrypted column is a concrete Postgres domain (public.eql_v3_text_search, public.eql_v3_integer_ord, ...) whose query capabilities are fixed by the type you pick — there is no capability config object. See the stash-encryption skill's "Schema Definition" section (the types catalog) for the full catalog and capability suffixes (Eq, Ord/OrdOre, Match, Search, Json).

When to Use This Skill

Adding field-level encryption to a Drizzle ORM project
Defining encrypted columns in Drizzle table schemas with the v3 types.* factories
Querying encrypted data with type-safe, auto-encrypting operators
Sorting, filtering, and encrypted-JSONB querying on encrypted columns
Migrating an existing plaintext column to encrypted
Building Express/Hono/Next.js APIs with encrypted Drizzle queries

Installation

npm install @cipherstash/stack @cipherstash/stack-drizzle drizzle-orm

Version note: npx stash init is the preferred install path — it pins every @cipherstash/* package to the versions matching your CLI release. If you install manually as above, verify what actually resolved (node -p "require('@cipherstash/stack/package.json').version"): bare dist-tag installs can lag behind a release, and stash init will warn on the version skew.

The Drizzle integration ships as its own first-party package, @cipherstash/stack-drizzle, which depends on @cipherstash/stack. Install both. The v3 surface documented here is exported from the @cipherstash/stack-drizzle package root. It is distinct from the older, separate @cipherstash/drizzle package (which is @cipherstash/protect-based, with different symbol names) — that package is deprecated and no longer published; do not install it. This package replaces it.

Database Setup

Runner note. stash init adds stash to the project as a dev dependency, so stash <command> runs through whichever package manager the project uses (Bun, pnpm, Yarn, or npm) — examples in this skill show this bare form. Before init has run, prefix with your package manager's one-shot runner: bunx, pnpm dlx, yarn dlx, or npx. The CLI's behaviour is identical across all of them.

Install the EQL v3 SQL

EQL (Encrypt Query Language) provides the PostgreSQL functions and domains that make encrypted columns searchable. Two ways to install version 3:

Direct install — run the SQL straight against the database (quick, good for dev):

stash eql install

Migration (preferred for real projects) — generate a Drizzle custom migration that carries the EQL v3 install SQL, so it lands in your migration history and ships to every environment through drizzle-kit migrate:

stash eql migration --drizzle              # writes a custom migration into drizzle/
stash eql migration --drizzle --supabase   # also grants eql_v3 to anon/authenticated/service_role

The generated migration also installs the cs_migrations tracking schema, so a single drizzle-kit migrate covers everything stash encrypt … needs — no out-of-band stash eql install. EQL v3 ships one SQL bundle for every target including Supabase; --supabase only adds the PostgREST/RLS role grants (harmless when you connect directly as postgres). Requires drizzle-kit installed and configured.

Your drizzle.config.ts decides the output directory: stash runs drizzle-kit generate --custom with no --out (drizzle-kit reads its config file or command-line options, never both — passing --out makes it demand --schema and --dialect too and abort), then follows the path drizzle-kit prints. --out is only the fallback directory to search. If your config reads DATABASE_URL — the usual dotenv -e .env.local -- drizzle-kit … shape — you do not need that wrapper here: stash loads .env/.env.local itself and passes the URL it resolves into the child process.

Changing an existing plaintext column to an encrypted one. drizzle-kit generate emits an in-place ALTER TABLE … ALTER COLUMN … SET DATA TYPE eql_v3_<name>, which Postgres rejects — there is no cast from text/numeric to an EQL domain. (On drizzle-kit 0.31.0 and later the emitted type is also mangled to "undefined"."eql_v3_<name>", since a customType has no typeSchema.) Repair it with:

stash eql repair --drizzle                     # sweep drizzle/, rewrite the invalid statements
stash eql repair --drizzle --dry-run           # preview first; writes nothing

The repair is add-only: it adds a staged <column>_encrypted column and leaves the source column in place, so it never emits DROP COLUMN or RENAME COLUMN and is safe to apply on a populated table. It repairs only what it can prove — the swept directory must also contain the migration that declared the column, so the sweep can see the source type. A statement it cannot place, one whose column is already encrypted, one whose encrypted twin already exists, or one outside the strict matcher (a hand-authored SET DATA TYPE … USING …) is left untouched, and the command exits non-zero so you review the directory before running drizzle-kit migrate. Applying the swept migration only adds the column — encrypting the data is the staged flow in Migrating an Existing Column to Encrypted below.

stash eql migration --drizzle runs the identical sweep over its output directory, but only sees migrations that already exist when it runs — and this broken ALTER COLUMN is normally generated afterwards. Reach for eql repair; there is no reason to generate a second EQL install migration to trigger a repair.

If you have already run drizzle-kit migrate, pass --database-url. Repair then reads drizzle.__drizzle_migrations and leaves applied migrations alone, listing them and exiting non-zero. Rewriting a migration the database has already run would leave its .sql describing a shape that database never got from it, so a fresh CI or staging database replaying the file diverges from yours — silently. This is rare but real: an ALTER to an EQL domain is normally un-runnable, so its migration failed and is safe to rewrite, but a jsonb column changed to an EQL domain on an empty table applies successfully. Without a URL (and without DATABASE_URL) the repair proceeds and warns that it could not verify applied state — the journal proves a migration exists, not that it ran. If your drizzle.config.ts sets migrations.table or migrations.schema, add --migrations-table <[schema.]table>: the probe cannot discover a renamed ledger, and without it the check finds nothing at the default relation and reports applied state as unverified rather than claiming nothing is applied.

Reconcile your schema after a sweep — drizzle-kit generate will not tell you to. The sweep repairs SQL and nothing else, so once you apply the swept migration three artefacts disagree:

| | email | email_encrypted | |---|---|---| | database | text (unchanged) | eql_v3_text_search | | schema.ts | declared as eql_v3_text_search | absent | | meta/*_snapshot.json | declared as eql_v3_text_search | absent |

drizzle-kit generate diffs schema.ts against the snapshot — it never reads .sql and never introspects the database. Those two still agree, so the diff is empty and it emits nothing. (Introspection is drizzle-kit push, a different command.) So nothing surfaces the divergence, and every consequence through the ORM is silent:

reads of users.email hand plaintext to the customType.fromDriver that expects an EQL envelope
writes push an EQL envelope into a text column and succeed, storing ciphertext in a plaintext column
email_encrypted is unreachable — it is in no Drizzle schema

Reconciling is three steps, and the middle one is not optional.

1. Fix schema.ts: declare the encrypted column under its own name, and set the source column back to its plaintext type. It is currently declared as the encrypted domain — that declaration is exactly what made drizzle-kit generate emit the invalid ALTER in the first place.

export const users = pgTable('users', {
  email: text('email').notNull(),                        // back to plaintext
  email_encrypted: types.TextSearch('email_encrypted'),  // the twin the sweep added
})

2. Run drizzle-kit generate, then delete the regenerated ADD COLUMN from the migration it writes. The snapshot has never seen email_encrypted, so generate always emits ADD COLUMN "email_encrypted" for it — and the swept migration already adds that column, so applying both fails at migrate time with column "email_encrypted" already exists. Deleting that one statement keeps the snapshot advance, which is the entire point of the step. (ADD COLUMN IF NOT EXISTS works too.) Anything else generate emits — such as a now no-op SET DATA TYPE text on the source column — can stay.

This is not avoidable by editing the schema more carefully: the snapshot can only learn about the twin from a generate that also emits SQL to create it. drizzle-kit push would introspect and skip it, but that bypasses your migration history.

3. Run drizzle-kit migrate, then backfill through the staged flow below before switching reads across.

If you have not yet applied the swept migration, the cleaner option is to revert schema.ts, remove that migration entirely (its .sql, its meta/*_snapshot.json, and its entry in meta/_journal.json), and follow Migrating an Existing Column to Encrypted below from a clean start — generate then produces the ADD COLUMN itself and there is nothing to delete.

Column Storage

Each encrypted column is a concrete Postgres domain named public.eql_v3_<name>:

CREATE TABLE users (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  email public.eql_v3_text_search,   -- equality + order/range + free-text
  age public.eql_v3_integer_ord,     -- equality + order/range
  profile public.eql_v3_json_search,        -- encrypted-JSONB queries
  role VARCHAR(50)                   -- non-encrypted columns are normal types
);

You don't usually hand-write this: the types.* factories below emit the domain as the column's SQL type, so drizzle-kit generate produces the ADD COLUMN "email" "eql_v3_text_search" DDL for you. The generated type is unqualified (eql_v3_text_search, not public.eql_v3_text_search): drizzle-kit wraps a custom type's whole name in one pair of quotes, which would turn a schema-qualified name into the invalid identifier "public.eql_v3_text_search". The bare name resolves via the search path because the domains live in public — so keep public on the search path (the default), and don't hand-edit the generated type back to a qualified name.

Encrypted predicates need functional indexes over the eql_v3.* extractors, and Drizzle does not add them on its own — spread encryptedIndexes(t) into the table definition to derive them per column; see Indexing Encrypted Columns below.

Schema Definition

Use the types namespace from @cipherstash/stack-drizzle to define encrypted columns. Each factory maps 1:1 to a Postgres domain, and the column's query capabilities are fixed by the type:

import { pgTable, integer, timestamp, varchar } from "drizzle-orm/pg-core"
import { types } from "@cipherstash/stack-drizzle"

const usersTable = pgTable("users", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

  email: types.TextSearch("email"),      // equality + order/range + free-text
  age: types.IntegerOrd("age"),          // equality + order/range
  notes: types.Text("notes"),            // storage only — encrypt/decrypt, no queries
  profile: types.Json("profile"),        // encrypted-JSONB containment + selector

  // Non-encrypted columns
  role: varchar("role", { length: 50 }),
  createdAt: timestamp("created_at").defaultNow(),
})

Capability suffixes at a glance (full catalog: stash-encryption skill, "Schema Definition" — the types namespace):

| Factory shape | Domain | Enables | |---|---|---| | types.Text, types.Integer, ... (no suffix) | eql_v3_text, ... | Storage only | | types.TextEq, types.IntegerEq, ... | eql_v3_text_eq, ... | eq, ne, inArray, notInArray | | types.IntegerOrd, types.TimestampOrd, ... | eql_v3_integer_ord, ... | equality + gt/gte/lt/lte/between/asc/desc | | types.IntegerOrdOre, ... | eql_v3_integer_ord_ore, ... | as Ord, with block-ORE ordering (superuser-only install — see Sorting) | | types.TextMatch | eql_v3_text_match | matches (fuzzy free-text) only | | types.TextSearch | eql_v3_text_search | equality + order/range + matches | | types.Json | eql_v3_json_search | contains + selector (encrypted JSONB) |

Value families: Integer/Smallint/Numeric/Real/Double (number), Bigint (bigint), Date/Timestamp (Date), Text (string), Boolean (boolean, storage only), Json (a JSON document — object or array, not a top-level scalar).

makeEqlV3Column(builder) wraps a column builder from @cipherstash/stack/eql/v3 (e.g. makeEqlV3Column(v3types.TextEq("email"))) — types.TextEq("email") from the Drizzle package is shorthand for the same thing.

Initialization

1. Extract Schema from Drizzle Table

import { extractEncryptionSchema, createEncryptionOperators } from "@cipherstash/stack-drizzle"
import { Encryption } from "@cipherstash/stack/v3"

// Convert the Drizzle table definition to a CipherStash v3 schema
const usersSchema = extractEncryptionSchema(usersTable)

The extracted schema keeps each column's concrete domain, so it types exactly like a hand-written encryptedTable({...}): InferPlaintext<typeof usersSchema> is a precise per-column plaintext map, non-encrypted columns (id, plain text() helpers) are excluded from it and pass through model operations untouched, and usersSchema.email addresses the column at its own type. Do not cast an extracted schema to AnyV3Table or annotate model rows with Record<string, unknown> to make an insert compile — that discards the checking and hides real mismatches.

That precision depends on passing the concrete table type produced by pgTable(). If a table has already been widened to PgTable, or any encrypted column is an ordinary customType column detected from its EQL SQL domain, extraction returns the safe widened AnyV3Table type because the complete concrete domain map is no longer available to TypeScript. This applies to mixed tables with both types.* and ordinary customType encrypted columns. Runtime extraction still discovers and uses all of those encrypted columns.

2. Initialize the Encryption Client

const encryptionClient = await Encryption({
  schemas: [usersSchema],
})

Encryption returns a strongly-typed client: plaintext types are pinned to each column's domain, and query methods only accept queryable columns.

3. Create Query Operators

const ops = createEncryptionOperators(encryptionClient)

createEncryptionOperators(client, { lockContext, audit }) optionally sets defaults applied to every operand encryption; the async encrypting operators (eq, ne, inArray, notInArray, gt/gte/lt/lte, between/notBetween, matches, contains, and all methods returned by selector(...)) also take an optional trailing { lockContext, audit } argument per call. Top-level asc/desc and the passthrough operators (isNull, isNotNull, not, and, or, exists, notExists) encrypt nothing and take no such argument.

4. Create Drizzle Instance

import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"

const db = drizzle({ client: postgres(process.env.DATABASE_URL!) })

Insert Encrypted Data

Rows are pre-encrypted with the client before db.insert — Drizzle only ever handles the encrypted EQL envelope:

// Single insert
const encrypted = await encryptionClient.encryptModel(
  { email: "alice@example.com", age: 30, role: "admin" },
  usersSchema,
)
if (!encrypted.failure) {
  await db.insert(usersTable).values(encrypted.data)
}

// Bulk insert
const encrypted = await encryptionClient.bulkEncryptModels(
  [
    { email: "alice@example.com", age: 30, role: "admin" },
    { email: "bob@example.com", age: 25, role: "user" },
  ],
  usersSchema,
)
if (!encrypted.failure) {
  await db.insert(usersTable).values(encrypted.data)
}

Query Encrypted Data

Operators auto-encrypt their plaintext operands into EQL v3 query terms — you pass plaintext, the emitted SQL compares encrypted values. Comparison operators are async (they encrypt), so await them (or hand them lazily to ops.and/ops.or, below).

Equality

const results = await db
  .select()
  .from(usersTable)
  .where(await ops.eq(usersTable.email, "alice@example.com"))

Free-Text Search (`matches`)

matches(col, needle) is fuzzy bloom-token matching on a TextMatch/TextSearch column — not SQL pattern matching. There are no like/ilike operators on the v3 surface, by design; don't pass % wildcards.

const results = await db
  .select()
  .from(usersTable)
  .where(await ops.matches(usersTable.email, "alice"))

Semantics to know:

Fuzzy and one-sided. The needle's downcased token set is bloom-tested as a subset of the column's — order- and multiplicity-insensitive. A match may be a false positive; a non-match never is. Re-check candidates after decryption if you need exactness.
Case-insensitive, and matches substrings of 3 characters or more.
Short needles are rejected. A needle shorter than the tokenizer's token length (3 by default) produces no tokens and would silently match every row, so the operator throws EncryptionOperatorError instead.

Range Queries

const results = await db
  .select()
  .from(usersTable)
  .where(await ops.gte(usersTable.age, 18))

const results = await db
  .select()
  .from(usersTable)
  .where(await ops.between(usersTable.age, 18, 65))

Array Membership

const results = await db
  .select()
  .from(usersTable)
  .where(await ops.inArray(usersTable.email, [
    "alice@example.com",
    "bob@example.com",
  ]))

inArray/notInArray reject an empty list and encrypt the whole list in a single batch crossing.

Sorting

// Sort by encrypted column (sync — no await needed)
const results = await db
  .select()
  .from(usersTable)
  .orderBy(ops.asc(usersTable.age))

const results = await db
  .select()
  .from(usersTable)
  .orderBy(ops.desc(usersTable.age))

ops.asc/ops.desc emit ORDER BY eql_v3.ord_term(col) (ord_term_ore(col) for the *OrdOre domains). The ORE-flavoured domains require the installer to create a custom operator class — supported on self-hosted Postgres and on AWS RDS/Aurora, but not on cloud-hosted Supabase (the one confirmed platform whose install role cannot create operator classes; the installer disables the *OrdOre domains there). Prefer the plain Ord domains when unsure; ordering works everywhere EQL v3 installs.

Encrypted-JSONB Containment (`contains`)

contains(col, subDoc) on a types.Json column is exact encrypted containment (jsonb @> semantics, no false positives). The needle is a ciphertext-free query_json term. Array containment is position-independent — { roles: ["admin"] } matches any document whose roles array includes "admin":

const results = await db
  .select()
  .from(usersTable)
  .where(await ops.contains(usersTable.profile, { roles: ["admin"] }))

An empty-object needle ({}) is rejected — doc @> '{}' holds for every document, so it would silently match every row. Omit the predicate if you want all rows.

types.Json carries no equality or ordering: eq/gt/asc on a Json column throw.

JSONPath Selector-with-Constraint (`selector`)

ops.selector(col, path) returns comparison methods bound to the encrypted value at a JSONPath inside a types.Json column. Its unique power over contains is ordering at a path:

// col->'$.age' > 25
const results = await db
  .select()
  .from(usersTable)
  .where(await ops.selector(usersTable.profile, "$.age").gt(25))

// col->'$.user' = 'zoe@example.com'
const results = await db
  .select()
  .from(usersTable)
  .where(await ops.selector(usersTable.profile, "$.user").eq("zoe@example.com"))

// ORDER BY eql_v3.ord_term of the encrypted leaf at $.age
const ordered = await db
  .select()
  .from(usersTable)
  .orderBy(await ops.selector(usersTable.profile, "$.age").asc())

Available methods: .eq, .ne, .gt, .gte, .lt, .lte, .asc, .desc. Rules:

Paths are dot-notation object keys only ("$.a.b"). Array-index and wildcard syntax ($.items[0]) is rejected.
Leaves are JSON scalars only: string, number, or boolean. An object or array leaf is rejected — use contains for sub-object matching. A boolean leaf is rejected under the ordering methods (booleans have no ordering). Serialize Date/bigint to the representation actually stored in the JSON document.
A scalar needle does not match an array at the path. selector(col, "$.tags").eq("a") will not match { tags: ["a"] } — use contains(col, { tags: ["a"] }) for that.
Absent-path semantics: eq and the ordering methods exclude rows whose document lacks the path; ne includes them ("not equal to X" covers "has no X").
ORDER BY absent paths are SQL NULL. PostgreSQL's normal NULL placement applies (ASC puts them last; DESC puts them first unless the query overrides NULL placement).
No ciphertext in selector predicates. Equality uses a value-selector containment needle (and can use the functional GIN index); ordering uses a selector hash plus a ciphertext-free scalar query term.

Batched Conditions (and / or)

Use ops.and() and ops.or() to combine encrypted conditions. Pass the operators lazily (no await) so they resolve concurrently, then await the outer call:

const results = await db
  .select()
  .from(usersTable)
  .where(
    await ops.and(
      ops.gte(usersTable.age, 18),              // no await — lazy
      ops.lte(usersTable.age, 65),
      ops.matches(usersTable.email, "example"),
      eq(usersTable.role, "admin"),             // mix with regular Drizzle ops
    ),
  )

const results = await db
  .select()
  .from(usersTable)
  .where(
    await ops.or(
      ops.eq(usersTable.email, "alice@example.com"),
      ops.eq(usersTable.email, "bob@example.com"),
    ),
  )

Both accept undefined conditions, which are filtered out — useful for conditional query building:

await ops.and(
  maybeEmail ? ops.eq(usersTable.email, maybeEmail) : undefined,
  ops.gte(usersTable.age, 18),
)

NULLs and Non-Encrypted Columns

A null operand throws — use ops.isNull(col) / ops.isNotNull(col) for NULL checks.
No plaintext-column fallback. Every v3 operator requires an encrypted v3 column and throws EncryptionOperatorError otherwise. Use regular Drizzle operators (eq, gte, ...) for non-encrypted columns — mixing the two inside ops.and/ops.or is fine.

Decrypt Results

Selected rows hold encrypted envelopes; decrypt with the client. The v3 decryptModel/bulkDecryptModels take the schema table as the second argument:

// Single model
const decrypted = await encryptionClient.decryptModel(results[0], usersSchema)
if (!decrypted.failure) {
  console.log(decrypted.data.email) // "alice@example.com"
}

// Bulk decrypt
const decrypted = await encryptionClient.bulkDecryptModels(results, usersSchema)
if (!decrypted.failure) {
  for (const user of decrypted.data) {
    console.log(user.email)
  }
}

Date columns are reconstructed to real Date instances on decrypt; bigint columns round-trip as native bigint. Non-schema fields pass through unchanged. Drizzle decrypts through the same typed client as every other integration, so the one caveat applies here too: a stored value that does not parse as a date is handed back unchanged rather than as an Invalid Date, even though the declared type is Date — guard with instanceof Date before calling Date methods on a column whose stored values you don't control.

Indexing Encrypted Columns

Drizzle emits the encrypted query operators, but no index DDL — without functional indexes over the eql_v3.* term extractors, every encrypted predicate sequential-scans. encryptedIndexes derives the recommended indexes for every encrypted column in the table from its domain, so a schema column can't be forgotten:

import { encryptedIndexes, types } from "@cipherstash/stack-drizzle"
import { integer, pgTable } from "drizzle-orm/pg-core"

export const users = pgTable(
  "users",
  {
    id: integer("id").primaryKey(),
    email: types.TextEq("email"),
    createdAt: types.TimestampOrd("created_at"),
    bio: types.TextSearch("bio"),
  },
  (t) => [...encryptedIndexes(t)],
)

The indexes are named <table>_<column>_<capability> and ride the normal drizzle-kit generate → migrate flow like any other index. What each column yields is fixed by its domain:

| Column type | Indexes emitted | |---|---| | types.TextEq / numeric/date/timestamp *Eq | <col>_eq — btree on eql_v3.eq_term | | numeric/date/timestamp *Ord | <col>_ord — btree on eql_v3.ord_term; serves =, range, and ORDER BY (the injective ordering term answers equality — those domains have no eq_term) | | numeric/date/timestamp *OrdOre | <col>_ord_ore — btree on eql_v3.ord_term_ore (needs the ORE opclass — not on Supabase) | | types.TextOrd | <col>_eq + <col>_ord — text ordering terms are non-injective, so equality rides eq_term | | types.TextOrdOre | <col>_eq + <col>_ord_ore (needs the ORE opclass — not on Supabase) | | types.TextMatch | <col>_match — GIN on eql_v3.match_term | | types.TextSearch | <col>_eq + <col>_ord + <col>_match | | types.Json | <col>_json — GIN on (eql_v3.to_ste_vec_query(col)::jsonb) jsonb_path_ops | | bare types.T, types.Boolean | none — storage-only, no term to index |

To hand-pick instead (custom names, a subset, or a field-level selector index on encrypted JSON — those can't be derived, the selector hash is data, not schema), declare individual expression indexes with the same extractor expressions:

import { sql } from "drizzle-orm"
import { index } from "drizzle-orm/pg-core"

;(t) => [index("users_email_eq").using("btree", sql`eql_v3.eq_term(${t.email})`)]

Run ANALYZE <table> after the migration applies — an expression index gathers no statistics at CREATE INDEX time. For when to create indexes during a rollout (after backfill, before switching reads), engagement rules, and EXPLAIN verification, see the stash-indexing skill.

Dropping to raw SQL? db.execute(sql`…`) bypasses the operators this integration emits, so you own the operand casts yourself — an encrypted predicate needs its needle cast to the column's eql_v3.query_* domain, and the driver's parameter-binding rules differ between pg and postgres-js. The stash-postgres skill is the reference for both.

Migrating an Existing Column to Encrypted

The hard case: a Drizzle table that already exists in production with live data in a plaintext column you want to encrypt. You can't just change the column type — that would drop the data and break NOT NULL constraints.

CipherStash splits this into two named steps with a hard production-deploy gate between them: an encryption rollout (schema-add + dual-write code) and a cutover step (backfill + switch reads by name + drop). The stash-encryption skill is the canonical reference for the lifecycle; this section walks the Drizzle-specific shape.

EQL version note. The CLI rollout tooling (stash encrypt *, and the underlying @cipherstash/migrate) now mutates EQL v3 only. A public.eql_v3_* target is required for backfill and drop. Legacy eql_v2_encrypted columns and migration history remain visible in status, but mutation commands reject them. The v3 lifecycle is schema-add → dual-write → deploy gate → backfill → switch the app to the encrypted column by name → drop, with no rename.

Where am I? Run stash status first (substitute the runner per the note above). It shows you which Drizzle tables/columns are mid-rollout, which are post-deploy, and what the next move is. Re-run after every transition.

Starting state

You have:

// src/db/schema.ts
export const users = pgTable('users', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
  email: text('email').notNull(),  // plaintext, populated, NOT NULL
})

And an INSERT INTO users (email) VALUES (...) somewhere in your app code.

Step 1 — Encryption rollout (one PR, one deploy)

Everything below lands in one PR. The deploy of that PR is the gate.

Schema-add: declare the encrypted twin

Add an email_encrypted column alongside email. Crucially, the encrypted column is nullable at creation — never .notNull(), because rows that already exist will have NULL in this column until backfill catches them.

// src/db/schema.ts
import { types } from '@cipherstash/stack-drizzle'

export const users = pgTable('users', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
  email: text('email').notNull(),                    // unchanged
  email_encrypted: types.TextSearch('email_encrypted'),  // new — nullable
})

Update the encryption client to harvest the encrypted columns from the table:

// src/encryption/index.ts
import { Encryption } from '@cipherstash/stack/v3'
import { extractEncryptionSchema } from '@cipherstash/stack-drizzle'
import { users } from '../db/schema'

const usersEncryptionSchema = extractEncryptionSchema(users)

export const encryptionClient = await Encryption({ schemas: [usersEncryptionSchema] })

Generate the migration with drizzle-kit generate. The generated SQL should be a single ALTER TABLE ... ADD COLUMN "email_encrypted" "eql_v3_text_search"; — drizzle-kit emits the bare domain name, which resolves to the public.eql_v3_text_search domain via search_path (a schema-qualified custom type would be quoted as one identifier and fail, so the bare name is deliberate). Apply with drizzle-kit migrate. (This requires the EQL v3 SQL to be installed first — see Database Setup.)

Dual-writing: write to both columns from app code

Find every code path that writes to users.email and update it to encrypt and also write to email_encrypted:

// Before
await db.insert(users).values({ email: input.email })

// After
const encrypted = await encryptionClient.encryptModel({ email_encrypted: input.email }, usersEncryptionSchema)
if (encrypted.failure) throw new Error(encrypted.failure.message)

await db.insert(users).values({
  email: input.email,                                  // plaintext — keep writing
  email_encrypted: encrypted.data.email_encrypted,     // encrypted twin — new
})

Same shape for UPDATE: if your app updates email, it must also re-encrypt and update email_encrypted in the same statement.

The dual-write rule. Every persistence path that mutates this row writes both columns, in the same transaction, on every code branch. Insert sites, update sites, upserts, ON CONFLICT clauses, seeders, fixtures, CSV importers, admin actions, background jobs, third-party webhook handlers — all of them. A single missed branch means rows inserted in production after deploy land in plaintext only, and backfill won't catch them. Grep for every site that touches users.email before declaring this step done.

After this phase, existing rows still have email_encrypted = NULL. App reads still come from email. Nothing has broken.

⛔ Deploy gate

Stop. Ship this PR to production. The deployed environment must be running the dual-write code before any cutover-step work is safe.

When the deploy is live:

stash status        # verify the rollout is recorded
stash plan          # detects dual-writes are live; drafts the cutover plan

stash impl will refuse to run a cutover-step plan if cs_migrations has no dual_writing event for users.email. That refusal is the safety net for cases where someone runs cutover work locally before the code is actually live.

Step 2 — Encryption cutover

Once dual-writes are live in production and cs_migrations records dual_writing:

Backfill: encrypt the historical rows

stash encrypt backfill --table users --column email
# (Interactive: answer 'yes' to the dual-write confirmation prompt.)
# (CI: pass --confirm-dual-writes-deployed instead.)

Resumable, idempotent, chunked. The CLI walks the table in keyset-pagination order, encrypts each chunk via the encryption client, and writes the ciphertext into email_encrypted inside transactions that also checkpoint to cs_migrations. SIGINT-safe.

If something goes wrong (e.g. you discover the dual-write code wasn't actually live when backfill ran), re-run with --force to re-encrypt every row regardless of current state.

Switch reads to the encrypted column

The EQL v3 encrypted column keeps its own name. Switch the application to it by name, verify reads, then drop the plaintext column. There is no rename command.

Point your read paths at email_encrypted and decrypt the selected envelopes with the encryption client. This is the moment that breaks read paths if they aren't decrypting.

// Before
const rows = await db.select().from(users).where(eq(users.id, id))
const email = rows[0].email

// After
const rows = await db.select().from(users).where(eq(users.id, id))
const decrypted = await encryptionClient.decryptModel(rows[0], usersEncryptionSchema)
if (decrypted.failure) throw new Error(decrypted.failure.message)
const email = decrypted.data.email_encrypted

For queries that filter on the column, switch to the encrypted operators from createEncryptionOperators — eq, matches, gte, etc. (See ## Query Encrypted Data above.) Deploy, confirm reads decrypt correctly, then drop the plaintext column.

Drop: remove the plaintext column

Once read paths are updated and you're confident reads are decrypting correctly, generate the drop migration:

stash encrypt drop --table users --column email

The CLI emits a Drizzle migration file with the drop. For a v3 column it drops the original plaintext column, email — there was no rename, so no email_plaintext exists. The generated SQL is not a bare ALTER TABLE: it is a DO $stash_drop$ block that takes LOCK TABLE users IN ACCESS EXCLUSIVE MODE, re-counts rows where email IS NOT NULL AND email_encrypted IS NULL at apply time, RAISE EXCEPTIONs if any remain, and only then executes the ALTER TABLE ... DROP COLUMN. So a row written after generation can't be silently destroyed. Requires the column to be in the backfilled phase, plus a live coverage check at generation time.

Review and apply with drizzle-kit migrate, then update the schema to its final shape — the encrypted column is the only one left:

// src/db/schema.ts (final, EQL v3)
export const users = pgTable('users', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
  email_encrypted: types.TextSearch('email_encrypted'),
})

Also remove the dual-write code from app paths — the plaintext column is gone; only the encrypted column is written now.

Inspecting progress at any time

stash status         # quest log: where each rollout is, what to do next
stash encrypt status # raw per-column phase, EQL state, backfill progress
stash encrypt plan   # diffs your migrations.json intent vs observed state

All three are read-only.

Complete Operator Reference

All comparison/containment operators auto-encrypt their operands and are async; asc/desc and the passthroughs are sync.

Encrypted Operators (async)

| Operator | Usage | Required column capability (domain suffix) | |---|---|---| | eq(col, value) | Equality | equality (Eq, Ord, OrdOre, TextSearch) | | ne(col, value) | Not equal | equality | | gt / gte / lt / lte (col, value) | Comparison | order/range (Ord, OrdOre, TextSearch) | | between(col, min, max) | Inclusive range | order/range | | notBetween(col, min, max) | Negated range | order/range | | inArray(col, values) / notInArray(col, values) | Membership (single-batch encryption; empty list rejected) | equality | | matches(col, needle) | Fuzzy free-text token match (short needles rejected) | free-text (TextMatch, TextSearch) | | contains(col, subDoc) | Exact encrypted-JSONB containment ({} rejected) | Json | | selector(col, path).eq/ne/gt/gte/lt/lte(value) | JSONPath selector-with-constraint (dot-notation paths, scalar leaves) | Json | | selector(col, path).asc()/desc() | ORDER BY a scalar JSONPath leaf (missing paths are SQL NULL) | Json |

Sort Operators (sync)

| Operator | Usage | Required capability | |---|---|---| | asc(col) | ORDER BY eql_v3.ord_term(col) ascending | order/range | | desc(col) | ORDER BY eql_v3.ord_term(col) descending | order/range |

(ord_term_ore for *OrdOre domains — needs the ORE opclass; available on RDS/Aurora and self-hosted, not on Supabase.)

Logical Operators (async, concurrent)

| Operator | Description | |---|---| | and(...conditions) | Conjunction — accepts lazy (un-awaited) operators and undefined, resolves concurrently | | or(...conditions) | Disjunction — same |

Passthrough Operators (sync, no encryption)

isNull, isNotNull, not, exists, notExists — re-exported from Drizzle and work identically.

Other v3 Exports

types, makeEqlV3Column, getEqlV3Column, isEqlV3Column, extractEncryptionSchema, createEncryptionOperators, EncryptionOperatorError, and the codec helpers v3ToDriver / v3FromDriver / EqlV3CodecError — all from @cipherstash/stack-drizzle.

Error Handling

Operators throw EncryptionOperatorError (exported from @cipherstash/stack-drizzle) whenever the query cannot be answered safely:

the column is not an encrypted v3 column (there is no plaintext fallback);
the column's domain lacks the operator's capability (e.g. ordering a TextEq column, eq on a Json column);
the operand is null (use isNull/isNotNull), an empty list (inArray), an empty object (contains), or a too-short needle (matches);
a selector path is malformed / uses array syntax, or its leaf value is a non-scalar;
operand encryption itself fails.

import { EncryptionOperatorError } from "@cipherstash/stack-drizzle"

class EncryptionOperatorError extends Error {
  context?: {
    tableName?: string
    columnName?: string
    operator?: string
  }
}

There is no EncryptionConfigError on the v3 path — capability problems surface as EncryptionOperatorError with the offending column/table/operator in context.

Encryption client operations (encryptModel, bulkDecryptModels, ...) don't throw — they return Result objects with data or failure. Check .failure before using .data.

EQL v2 removed. @cipherstash/stack-drizzle no longer ships an EQL v2 authoring surface: the pre-v3 encryptedType config-flag columns and the like/ilike operators are gone, and the ./v3 subpath has collapsed into the package root (@cipherstash/stack-drizzle). All exports documented here are EQL v3. Existing v2 ciphertext is still decryptable via @cipherstash/stack; only the Drizzle-side authoring/query-building of new v2 columns is removed. stash init --drizzle generates an EQL v3 migration via stash eql migration --drizzle.