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. It is 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 EQL in your PostgreSQL database using CipherStash Forge:
```
npx @cipherstash/cli db install
```
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:

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

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

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

Properties

Property	Description
`value`	The value to search for
`column`	The column to search in
`table`	The table to search in
`queryType`	(optional) The query type — auto-inferred from the column's indexes when omitted
`returnType`	(optional) The output format — `'eql'` (default), `'composite-literal'`, or `'escaped-composite-literal'`

`returnType` options

`returnType`	Return type	Description
`'eql'` (default)	`Encrypted` object	Raw EQL JSON payload. Use with parameterized queries (`$1`) or ORMs that accept JSON objects.
`'composite-literal'`	`string`	PostgreSQL composite literal format `("json")`. Use with Supabase `.eq()` or other APIs that require a string value.
`'escaped-composite-literal'`	`string`	Escaped composite literal `"(\"json\")"`. Use when the query string will be embedded inside another string or JSON value.

Batch queries

Encrypt multiple query terms in a single call:

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

Query types

Exact matching

Use .equality() for exact match lookups:

const term = await client.encryptQuery("[email protected]", {
  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:

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:

If your PostgreSQL database does not support EQL Operator families, use the eql_v2.ore_block_u64_8_256() function for ORDER BY. Databases with Operator family support can use ORDER BY directly on the encrypted column name.

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.

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:

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:

import { toJsonPath } from "@cipherstash/stack"

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

Containment queries

Pass an object or array to query by containment:

// 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. For orderAndRange queries, bare numbers are supported directly. Wrap them in an object or array for searchableJson.

// Wrong for searchableJson: will fail (works for orderAndRange)
await client.encryptQuery(42, { column: documents.metadata, table: documents })

// Correct — wrap in an object
await client.encryptQuery({ value: 42 }, { column: documents.metadata, table: documents })

Use the buildNestedObject helper to construct nested containment queries:

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:

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

Batch JSONB queries

Use encryptQuery with an array to encrypt multiple JSONB query terms in a single call. Each item can have a different plaintext type:

const terms = await client.encryptQuery([
  {
    value: "$.user.email",        // string -> JSONPath selector
    column: documents.metadata,
    table: documents,
  },
  {
    value: { role: "admin" },     // object -> containment
    column: documents.metadata,
    table: documents,
  },
  {
    value: ["tag1", "tag2"],      // array -> containment
    column: documents.metadata,
    table: documents,
  },
])

Advanced: Explicit query types

For advanced use cases, you can specify the query type explicitly instead of relying on auto-inference:

Approach	`queryType`	When to use
searchableJson (recommended)	`'searchableJson'` or omitted	Auto-infers from plaintext type. Use for most JSONB queries.
steVecSelector (explicit)	`'steVecSelector'`	When you want to be explicit about JSONPath selector queries.
steVecTerm (explicit)	`'steVecTerm'`	When you want to be explicit about containment queries.

// Explicit steVecSelector
const selectorTerm = await client.encryptQuery("$.user.email", {
  column: documents.metadata,
  table: documents,
  queryType: "steVecSelector",
})

// Explicit steVecTerm
const containTerm = await client.encryptQuery({ role: "admin" }, {
  column: documents.metadata,
  table: documents,
  queryType: "steVecTerm",
})

Implementation example

Using the `pg` client

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({ connectionString: process.env.DATABASE_URL })
const client = await Encryption({ schemas: [schema] })

// Insert encrypted data
const encryptedData = await client.encryptModel({ email: "[email protected]" }, 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

Schema design

Use .equality() for exact matches (most efficient)
Use .freeTextSearch() for text-based searches (more expensive)
Use .orderAndRange() for numerical data and sorting (most expensive)
Only enable features you need to minimize performance impact
Use eql_v2_encrypted column type in your database schema for encrypted columns

Security

Never store unencrypted sensitive data
Keep your CipherStash secrets secure
Use parameterized queries to prevent SQL injection

Performance

Index your encrypted columns appropriately
Use bulk operations (bulkEncryptModels, bulkDecryptModels) when working with multiple records
Monitor query performance and consider the impact of search operations on your database
Cache frequently accessed data

Error handling

Always check for failures with any @cipherstash/stack method
Handle encryption errors aggressively
Handle decryption errors gracefully

Searchable encryption

Searchable encryption

Prerequisites

What is EQL?

The `encryptQuery` function

Properties

`returnType` options

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

Batch JSONB queries

Advanced: Explicit query types

Implementation example

Using the `pg` client

Best practices

Schema design

Security

Performance

Error handling

On this page