Column Encryption

Enabling It

Mark a column encrypted: true. Nothing else changes: reads and writes take the plain value and the ORM encrypts on save and decrypts on load.

import { defineModel, DataTypes } from "stabilize-orm";

export const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.INTEGER, required: true, unique: true },
    email: { type: DataTypes.STRING, length: 255, required: true },
    // Stored as ciphertext; you only ever handle the plaintext.
    nationalId: { type: DataTypes.STRING, length: 512, encrypted: true },
  },
});

const user = await userRepo.create({
  email: "ada@example.com",
  nationalId: "AB123456C",
});
// user.nationalId === "AB123456C" - decrypted for you
// In the table it is "v3:9f2b7c1e:F3k...==:9dQ...==:Xm1...=="

Size the column for the ciphertext, not the plaintext. The value is stored as v3:<keyId>:<iv>:<tag>:<ciphertext> — the id in hex, the other three parts Base64 — so the stored string is roughly 1.4× the plaintext plus about 55 characters of framing. A STRING default of 255 is not enough for a long secret — raise length.

A column can be encrypted and renamed. { name: "diagnosis_code", encrypted: true } stores ciphertext in diagnosis_code and decrypts it on the way back — as with any renamed column, the value reads back under the column name, not the property name.

The Key

A key is looked for in two places, and the first one that yields an active key wins:

Supplying the key yourself is the option that travels — it survives a redeploy, a container replacement and a fresh filesystem, because it lives in the place you already keep secrets:

# 64 hex characters = 32 bytes. Generate with: openssl rand -hex 32
ORM_ENCRYPTION_KEY=9f2b7c1e4a8d3f605b1c9e2a7d4f8b3c6e0a5d1f7b2c8e4a9d3f6b0c1e5a72d4

If nothing is supplied, the ORM does not fail — it generates a key and writes it to the key file at mode 0600, then warns with code STABILIZE_ENCRYPTION_KEY_GENERATED. That makes a first run work with no configuration at all:

{
  "active": "9f2b7c1e4a8d3f605b1c9e2a7d4f8b3c6e0a5d1f7b2c8e4a9d3f6b0c1e5a72d4",
  "retired": []
}

A file may also hold the key on its own, without the JSON envelope, since that is the obvious thing to write by hand:

Keys are read on every call, not captured once when the module loads, so an application that sets the variable after importing the ORM still works — and changing the environment at runtime changes which key is used. Only the key file is cached, and only until its mtime and size change, so editing it also takes effect without a restart.

A key of the wrong size is rejected with the byte length it found: anything that is not exactly 32 bytes or 64 hex characters is an error. Add .stabilize/ to your .gitignore — committing that file hands every encrypted column to anyone who can read the repository.

How Values Are Stored

Encryption is AES-256-GCM, and the stored format carries everything needed to decrypt — including which key to use:

GCM rather than CBC because it authenticates the ciphertext: a value that was truncated or edited in the database fails to decrypt instead of silently returning corrupted plaintext.

The key id is a digest of the key, not a label assigned to it — so the same key has the same id whether it arrived through the environment or through a file, and moving a key between the two does not orphan the rows it wrote. Being one-way, the id can sit in plaintext beside the ciphertext without narrowing a search for the key itself.

The IV is random per write, so encrypting the same plaintext twice produces two different stored strings. That is what you want cryptographically, and it has a consequence you must design around — see below.

You Cannot Query an Encrypted Column

// Both of these find nothing, even though the row exists:
await userRepo.findBy({ nationalId: "AB123456C" });   // []
await userRepo.findOneBy({ nationalId: "AB123456C" }); // null

// And this does NOT prevent a duplicate:
//   nationalId: { ..., encrypted: true, unique: true }

The workable patterns, in order of preference:

• Keep a separate deterministic digest column — for example a SHA-256 HMAC of the value under a second key — and query that. It is not reversible, so it does not undo the encryption, and it is stable, so equality and uniqueness both work.
• Store the value in an unencrypted column you accept is public and encrypt only the part that must stay secret.
• Decrypt in application code and filter there. Only viable on small tables — it reads every row.

Where It Applies

Decryption is applied by a row transform attached to the read builders, which means coverage is not uniform across the API. This table is worth knowing before you reach for a helper:

What Happens When Decryption Fails

A failure means the key is wrong, was rotated away, or the ciphertext was altered. Because GCM authenticates, all three are detected. Rather than returning null — which would make a tampered value look like an empty field — the read throws:

import { StabilizeError } from "stabilize-orm";

try {
  await userRepo.findOne(id);
} catch (error) {
  if (error instanceof StabilizeError && error.code === "DECRYPTION_ERROR") {
    // The message names the offending column.
    // Usually: the key is missing, wrong, or was rotated without being
    // kept in the ring -- or the row was never re-saved after the rotation.
  }
  throw error;
}

The two failures read differently, which is worth knowing before you go looking in the wrong place. A v3: value names its key, so a key that is simply not configured says so and tells you where to add it. Only the older formats — which do not name a key — fall back to trying every key in the ring, and report that none of them worked.

Rotating a Key

Because every value names the key that wrote it, a new key can be introduced without re-encrypting the whole table at once. Retired keys keep decrypting; only the active key encrypts. Add the old key to the file's retired list and put the new one in active:

{
  "active": "<the new 64-hex key>",
  "retired": ["<the previous 64-hex key>"]
}

A deployment that keeps its secrets in the environment and has no file can pass them the other way instead:

ORM_ENCRYPTION_KEY=<new key>
ORM_ENCRYPTION_KEYS_OLD=<previous key>

A row still encrypted under a retired key reads normally. Re-save it to move it onto the active key:

import { activeKeyId } from "stabilize-orm/utils/encryption";

// Before the rotation, note which key new writes use:
const before = activeKeyId();

// With the old key in "retired", every row still reads. Each write
// moves it onto the active key.
await userRepo.each(userRepo.find(), async (user) => {
  await userRepo.update(user.id, { nationalId: user.nationalId });
});

console.log(activeKeyId() === before ? "still the same key" : "rotated");

Then drop the retired key once nothing refers to it. A value whose key is missing fails with an error naming the id, so you can tell whether anything is still using it rather than guessing.

activeKeyId() returns the digest of the key new values are being written with — which is also the id sitting in the third field of every v3: value, so you can check a rotation against the data rather than trusting that it finished.

Data Written by Earlier Versions

Two older formats are still read, and nothing writes them any more. Rows written before key ids existed carry a v2: prefix. Rows written before that used unauthenticated AES-256-CBC, in the shape iv:ciphertext.

Neither names a key, so there is nothing to look up — each is tried against every key in the ring until one works. That is reliable rather than a guess: GCM's authentication tag makes a wrong key fail loudly, and CBC at least fails its padding.

To read them, put the built-in key in the ring and re-save:

{
  "active": "<your own key>",
  "retired": ["f71a3c8e9b12d5a49c0a3f98b1f2e46d"]
}

// Reads now understand both old formats and the new one. Each write
// uses the active key and the current v3 format.
await userRepo.each(userRepo.find(), async (user) => {
  await userRepo.update(user.id, { nationalId: user.nationalId });
});

This is a real cost, not a formality: reads have to run under the old key for the whole duration of the re-save, so schedule it as a migration rather than flipping a variable in place. Rows that are never re-saved stay in the old format and stop being readable the moment the old key leaves the ring.

Caching

Decryption happens before a row is written to the cache, so a cached entity holds plaintext and is served decrypted. That makes the cache itself a place where the value is not encrypted — if the cache backend is Redis, secure the connection and treat the cache as sensitive, or leave caching off for models with encrypted columns.