Transparent Column-Level Database Encryption with Prisma & TypeORM

1. Limits of Disk-Level TDE

Storage-volume encryption and database TDE protect data on disk. They do not protect against:

• Over-privileged access: any account with SELECT permission reads sensitive columns in plaintext.
• SQL injection: the engine decrypts blocks for every query, so an injection returns plaintext rows.
• Replicas, logs and dumps: replication streams, query logs and exports may contain plaintext values.

Encrypting sensitive fields in the application means the database only stores ciphertext for those fields.

2. How the Adapters Encrypt Fields

1. Key: the application supplies one 256-bit key as a 64-character hex string. The adapters do not fetch or wrap keys through a KMS.
2. Encryption: each configured value is serialised to UTF-8 and sealed with XChaCha20-Poly1305 using a random 24-byte nonce.
3. Encoding: the stored value is base64 of nonce || ciphertext || tag, so the column must be a text type large enough to hold it.
4. Searchable fields (Prisma only): fields listed as deterministic are stored as an HMAC-SHA256 of the value instead. They support equality lookups but cannot be decrypted.

3. Prisma Client Extension

createFieldEncryptionExtension() returns a Prisma Client extension ($extends); createEncryptionMiddleware() offers the older $use style.

import { PrismaClient } from "@prisma/client";
import { createFieldEncryptionExtension } from "@sebastienrousseau/crypto-prisma";
export const prisma = new PrismaClient().$extends(
createFieldEncryptionExtension({
key: process.env.FIELD_ENCRYPTION_KEY!, // 64 hex characters
encryptedFields: [
{ model: "AccountHolder", fields: ["nationalId", "bankAccountNumber"] },
],
deterministicFields: ["nationalId"], // HMAC, searchable, not decryptable
}),
);

The algorithm option is accepted but ignored: values are always sealed with XChaCha20-Poly1305.

4. TypeORM Column Decorator

@sebastienrousseau/crypto-typeorm provides an @EncryptedColumn() decorator backed by a column transformer, and an EncryptionSubscriber:

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";
import { EncryptedColumn } from "@sebastienrousseau/crypto-typeorm";
@Entity()
export class CorporateTreasuryAccount {
@PrimaryGeneratedColumn("uuid")
id: string;
@Column()
legalEntityName: string;
// Key from options.encrypt.key or the TYPEORM_ENCRYPTION_KEY env var
@EncryptedColumn({ encrypt: { key: process.env.COLUMN_ENCRYPTION_KEY! } })
beneficiaryRoutingNumber: string;
}

5. Key Management and Rotation

The adapters take a raw key from the application. They have no KMS integration, no key versioning in the stored value, and no rotation tooling.

To rotate a key today, read each row with the old key and write it back with the new one in an application-level migration. If you need envelope encryption, generate and unwrap data keys with @sebastienrousseau/crypto-kms (AWS KMS is implemented) and pass the unwrapped key to the adapter.

6. Performance

No query-overhead measurements are published. The table in earlier versions of this paper did not come from committed benchmark code and has been withdrawn. Measure the overhead in your own environment before adopting the adapters on hot paths.

7. Migrating Existing Data

When the Prisma adapter cannot decrypt a stored value (for example, a legacy plaintext row), it returns the stored value unchanged. That behaviour allows a gradual migration:

1. Change the target column to a text type large enough for the base64 ciphertext.
2. Deploy the adapter. New writes are encrypted; unencrypted legacy values are still returned as stored.
3. Run a batch job that reads and rewrites legacy rows so they are encrypted.
4. Verify that no plaintext rows remain. Note that a value that fails authentication is also returned unchanged rather than raising an error.