Stash Drizzle

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:

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

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.