Run equality, free-text, range, and JSON queries on encrypted data in PostgreSQL

Searchable encryption

CipherStash lets you run queries on encrypted data in PostgreSQL without decrypting it first — and it's 410,000x faster than homomorphic encryption.

Operation	Homomorphic	CipherStash	Speedup
Encrypt	1.97 ms	48 us	~41x
a == b	111 ms	238 ns	~466,000x
a > b	192 ms	238 ns	~807,000x

Every decryption event is logged in ZeroKMS, giving you an audit trail for compliance with SOC 2 and BDSG.

Prerequisites

Install the EQL custom types and functions in your PostgreSQL database
Define your encryption schema with the appropriate search indexes

EQL (Encrypt Query Language) is a set of PostgreSQL extensions that enable searching and sorting on encrypted data. It provides custom data types, comparison functions, and index support for encrypted values.

Any encrypted column must use the eql_v2_encrypted type:

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email eql_v2_encrypted
);

The `encryptQuery` function

Encrypt a query term so you can search encrypted data in PostgreSQL:

search.ts

const term = await client.encryptQuery("user@example.com", {
  column: schema.email,
  table: schema,
})

if (term.failure) {
  // Handle the error
}

console.log(term.data) // encrypted query term

Batch queries

Encrypt multiple query terms in a single call:

search.ts

const terms = await client.encryptQuery([
  { value: "user@example.com", column: schema.email, table: schema },
  { value: "18", column: schema.age, table: schema },
])

Query types

Exact matching

Use .equality() for exact match lookups:

search.ts

const term = await client.encryptQuery("user@example.com", {
  column: schema.email,
  table: schema,
})

const result = await pgClient.query(
  "SELECT * FROM users WHERE email_encrypted = $1",
  [term.data]
)

Free-text search

Use .freeTextSearch() for text-based searches:

search.ts

const term = await client.encryptQuery("example", {
  column: schema.email,
  table: schema,
})

const result = await pgClient.query(
  "SELECT * FROM users WHERE email_encrypted LIKE $1",
  [term.data]
)

Sorting and range queries

Use .orderAndRange() for sorting and range operations:

search.ts

const result = await pgClient.query(
  "SELECT * FROM users ORDER BY eql_v2.ore_block_u64_8_256(age_encrypted) ASC"
)

JSONB queries with `.searchableJson()`

For columns storing JSON data, .searchableJson() is the recommended approach. It automatically infers the correct query operation from the plaintext value type.

schema.ts

const documents = encryptedTable("documents", {
  metadata: encryptedColumn("metadata_encrypted").searchableJson(),
})

Auto-inference

Plaintext type	Inferred operation	Use case
`string` (e.g. `'$.user.email'`)	`steVecSelector`	JSONPath selector queries
`object` (e.g. `{ role: 'admin' }`)	`steVecTerm`	Containment queries
`array` (e.g. `['admin', 'user']`)	`steVecTerm`	Containment queries
`null`	Returns `null`	Null handling

JSONPath selector queries

Pass a string to query by JSON path:

search.ts

const pathTerm = await client.encryptQuery("$.user.email", {
  column: documents.metadata,
  table: documents,
})

// Nested path
const nestedTerm = await client.encryptQuery("$.user.profile.role", {
  column: documents.metadata,
  table: documents,
})

// Array index
const arrayTerm = await client.encryptQuery("$.items[0].name", {
  column: documents.metadata,
  table: documents,
})

Use the toJsonPath helper to convert dot-notation paths:

search.ts

import { toJsonPath } from "@cipherstash/stack"

toJsonPath("user.email")     // '$.user.email'
toJsonPath("$.user.email")   // '$.user.email' (unchanged)

Containment queries

Pass an object or array to query by containment:

search.ts

// Key-value containment
const roleTerm = await client.encryptQuery({ role: "admin" }, {
  column: documents.metadata,
  table: documents,
})

// Nested object containment
const nestedTerm = await client.encryptQuery(
  { user: { profile: { role: "admin" } } },
  { column: documents.metadata, table: documents }
)

// Array containment
const tagsTerm = await client.encryptQuery(["admin", "user"], {
  column: documents.metadata,
  table: documents,
})

Bare numbers and booleans are not supported as top-level searchableJson query values. Wrap them in an object or array.

Use the buildNestedObject helper to construct nested containment queries:

search.ts

import { buildNestedObject } from "@cipherstash/stack"

buildNestedObject("user.role", "admin")
// Returns: { user: { role: 'admin' } }

Using JSONB queries in SQL

Specify returnType: 'composite-literal' for direct use in SQL:

search.ts

const term = await client.encryptQuery([{
  value: "$.user.email",
  column: documents.metadata,
  table: documents,
  returnType: "composite-literal",
}])

const result = await pgClient.query(
  "SELECT * FROM documents WHERE cs_ste_vec_v2(metadata_encrypted) @> $1",
  [term.data[0]]
)

Implementation example

Using the `pg` client

search.ts

import { Client } from "pg"
import { Encryption } from "@cipherstash/stack"
import { encryptedTable, encryptedColumn } from "@cipherstash/stack/schema"

const schema = encryptedTable("users", {
  email: encryptedColumn("email_encrypted")
    .equality()
    .freeTextSearch()
    .orderAndRange(),
})

const pgClient = new Client({ /* connection details */ })
const client = await Encryption({ schemas: [schema] })

// Insert encrypted data
const encryptedData = await client.encryptModel({ email: "user@example.com" }, schema)
await pgClient.query(
  "INSERT INTO users (email_encrypted) VALUES ($1::jsonb)",
  [encryptedData.data.email_encrypted]
)

// Search encrypted data
const searchTerm = await client.encryptQuery("example.com", {
  column: schema.email,
  table: schema,
})
const result = await pgClient.query(
  "SELECT * FROM users WHERE email_encrypted LIKE $1",
  [searchTerm.data]
)

// Decrypt results
const decryptedData = await client.bulkDecryptModels(result.rows)

Best practices

Choose the right indexes — Use .equality() for exact matches (most efficient), .freeTextSearch() for text search, and .orderAndRange() for sorting (most expensive). Only enable what you need.
Use parameterized queries — Always use parameterized queries to prevent SQL injection.
Use bulk operations — Use bulkEncryptModels and bulkDecryptModels when working with multiple records.
Handle errors — Always check for failures with any @cipherstash/stack method.

Searchable encryption

Searchable encryption

Prerequisites

What is EQL?

The `encryptQuery` function

Batch queries

Query types

Exact matching

Free-text search

Sorting and range queries

JSONB queries with `.searchableJson()`

Auto-inference

JSONPath selector queries

Containment queries

Using JSONB queries in SQL

Implementation example

Using the `pg` client

Best practices

On this page