createEncryptionOperators
Create Drizzle query operators (`eq`, `lt`, `gt`, etc.) that work with encrypted columns. The returned operators encrypt query values before passing them to ...
Function: createEncryptionOperators()
function createEncryptionOperators(encryptionClient): {
eq: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
ne: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
gt: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
gte: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
lt: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
lte: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
between: (left, min, max) => SQL<unknown> | Promise<SQL<unknown>>;
notBetween: (left, min, max) => SQL<unknown> | Promise<SQL<unknown>>;
like: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
ilike: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
notIlike: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;
jsonbPathQueryFirst: (left, right) => Promise<SQL<unknown>>;
jsonbGet: (left, right) => Promise<SQL<unknown>>;
jsonbPathExists: (left, right) => Promise<SQL<unknown>>;
inArray: (left, right) => Promise<SQL<unknown>>;
notInArray: (left, right) => Promise<SQL<unknown>>;
asc: (column) => SQL;
desc: (column) => SQL;
and: (...conditions) => Promise<SQL<unknown>>;
or: (...conditions) => Promise<SQL<unknown>>;
exists: (subquery) => SQL;
notExists: (subquery) => SQL;
isNull: (value) => SQL;
isNotNull: (value) => SQL;
not: (condition) => SQL;
arrayContains: {
<T> (column, values): SQL;
<TColumn> (column, values): SQL;
<T> (column, values): SQL;
};
arrayContained: {
<T> (column, values): SQL;
<TColumn> (column, values): SQL;
<T> (column, values): SQL;
};
arrayOverlaps: {
<T> (column, values): SQL;
<TColumn> (column, values): SQL;
<T> (column, values): SQL;
};
};Defined in: .tmp-stack/packages/stack/src/drizzle/operators.ts:975
Creates a set of encryption-aware operators that automatically encrypt values for encrypted columns before using them with Drizzle operators.
For equality and text search operators (eq, ne, like, ilike, inArray, etc.): Values are encrypted and then passed to regular Drizzle operators, which use PostgreSQL's built-in operators for eql_v2_encrypted types.
For order and range operators (gt, gte, lt, lte, between, notBetween): Values are encrypted and then use eql_v2.* functions (eql_v2.gt(), eql_v2.gte(), etc.) which are required for ORE (Order-Revealing Encryption) comparisons.
Parameters
encryptionClient
The EncryptionClient instance
Returns
An object with all Drizzle operators wrapped for encrypted columns
eq()
eq: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;Equality operator - encrypts value for encrypted columns.
Requires either equality or orderAndRange to be set on EncryptedColumnConfig.
Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users with a specific email address.
const condition = await ops.eq(usersTable.email, 'user@example.com')
const results = await db.select().from(usersTable).where(condition)ne()
ne: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;Not equal operator - encrypts value for encrypted columns.
Requires either equality or orderAndRange to be set on EncryptedColumnConfig.
Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users whose email address is not a specific value.
const condition = await ops.ne(usersTable.email, 'user@example.com')
const results = await db.select().from(usersTable).where(condition)gt()
gt: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;Greater than operator for encrypted columns with ORE index.
Requires orderAndRange to be set on EncryptedColumnConfig.
Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users older than a specific age.
const condition = await ops.gt(usersTable.age, 30)
const results = await db.select().from(usersTable).where(condition)gte()
gte: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;Greater than or equal operator for encrypted columns with ORE index.
Requires orderAndRange to be set on EncryptedColumnConfig.
Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users older than or equal to a specific age.
const condition = await ops.gte(usersTable.age, 30)
const results = await db.select().from(usersTable).where(condition)lt()
lt: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;Less than operator for encrypted columns with ORE index.
Requires orderAndRange to be set on EncryptedColumnConfig.
Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users younger than a specific age.
const condition = await ops.lt(usersTable.age, 30)
const results = await db.select().from(usersTable).where(condition)lte()
lte: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;Less than or equal operator for encrypted columns with ORE index.
Requires orderAndRange to be set on EncryptedColumnConfig.
Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users younger than or equal to a specific age.
const condition = await ops.lte(usersTable.age, 30)
const results = await db.select().from(usersTable).where(condition)between()
between: (left, min, max) => SQL<unknown> | Promise<SQL<unknown>>;Between operator for encrypted columns with ORE index.
Requires orderAndRange to be set on EncryptedColumnConfig.
Parameters
left
SQLWrapper
min
unknown
max
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users within a specific age range.
const condition = await ops.between(usersTable.age, 20, 30)
const results = await db.select().from(usersTable).where(condition)notBetween()
notBetween: (left, min, max) => SQL<unknown> | Promise<SQL<unknown>>;Not between operator for encrypted columns with ORE index.
Requires orderAndRange to be set on EncryptedColumnConfig.
Parameters
left
SQLWrapper
min
unknown
max
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users outside a specific age range.
const condition = await ops.notBetween(usersTable.age, 20, 30)
const results = await db.select().from(usersTable).where(condition)like()
like: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;Like operator for encrypted columns with free text search.
Requires freeTextSearch to be set on EncryptedColumnConfig.
[!IMPORTANT] Case sensitivity on encrypted columns depends on the EncryptedColumnConfig. Ensure that the column is configured for case-insensitive search if needed.
Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users with email addresses matching a pattern.
const condition = await ops.like(usersTable.email, '%@example.com')
const results = await db.select().from(usersTable).where(condition)ilike()
ilike: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;ILike operator for encrypted columns with free text search.
Requires freeTextSearch to be set on EncryptedColumnConfig.
[!IMPORTANT] Case sensitivity on encrypted columns depends on the EncryptedColumnConfig. Ensure that the column is configured for case-insensitive search if needed.
Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
Example
Select users with email addresses matching a pattern (case-insensitive).
const condition = await ops.ilike(usersTable.email, '%@example.com')
const results = await db.select().from(usersTable).where(condition)notIlike()
notIlike: (left, right) => SQL<unknown> | Promise<SQL<unknown>>;Parameters
left
SQLWrapper
right
unknown
Returns
SQL<unknown> | Promise<SQL<unknown>>
jsonbPathQueryFirst()
jsonbPathQueryFirst: (left, right) => Promise<SQL<unknown>>;JSONB path query first operator for encrypted columns with searchable JSON.
Requires searchableJson to be set on EncryptedColumnConfig.
Encrypts the JSON path selector and calls eql_v2.jsonb_path_query_first(),
casting the parameter to eql_v2_encrypted.
Parameters
left
SQLWrapper
right
unknown
Returns
Promise<SQL<unknown>>
Throws
If the column does not have searchableJson enabled.
jsonbGet()
jsonbGet: (left, right) => Promise<SQL<unknown>>;JSONB get operator for encrypted columns with searchable JSON.
Requires searchableJson to be set on EncryptedColumnConfig.
Encrypts the JSON path selector and uses the -> operator,
casting the parameter to eql_v2_encrypted.
Parameters
left
SQLWrapper
right
unknown
Returns
Promise<SQL<unknown>>
Throws
If the column does not have searchableJson enabled.
jsonbPathExists()
jsonbPathExists: (left, right) => Promise<SQL<unknown>>;JSONB path exists operator for encrypted columns with searchable JSON.
Requires searchableJson to be set on EncryptedColumnConfig.
Encrypts the JSON path selector and calls eql_v2.jsonb_path_exists(),
casting the parameter to eql_v2_encrypted.
Parameters
left
SQLWrapper
right
unknown
Returns
Promise<SQL<unknown>>
Throws
If the column does not have searchableJson enabled.
inArray()
inArray: (left, right) => Promise<SQL<unknown>>;Parameters
left
SQLWrapper
right
unknown[] | SQLWrapper
Returns
Promise<SQL<unknown>>
notInArray()
notInArray: (left, right) => Promise<SQL<unknown>>;Parameters
left
SQLWrapper
right
unknown[] | SQLWrapper
Returns
Promise<SQL<unknown>>
asc()
asc: (column) => SQL;Parameters
column
SQLWrapper
Returns
SQL
desc()
desc: (column) => SQL;Parameters
column
SQLWrapper
Returns
SQL
and()
and: (...conditions) => Promise<SQL<unknown>>;Parameters
conditions
...(
| SQLWrapper
| SQL<unknown>
| Promise<SQL<unknown>>
| undefined)[]
Returns
Promise<SQL<unknown>>
or()
or: (...conditions) => Promise<SQL<unknown>>;Parameters
conditions
...(
| SQLWrapper
| SQL<unknown>
| Promise<SQL<unknown>>
| undefined)[]
Returns
Promise<SQL<unknown>>
exists()
exists: (subquery) => SQL;Test whether a subquery evaluates to have any rows.
Examples
// Users whose `homeCity` column has a match in a cities
// table.
db
.select()
.from(users)
.where(
exists(db.select()
.from(cities)
.where(eq(users.homeCity, cities.id))),
);Parameters
subquery
SQLWrapper
Returns
SQL
See
notExists for the inverse of this test
notExists()
notExists: (subquery) => SQL;Test whether a subquery doesn't include any result rows.
Examples
// Users whose `homeCity` column doesn't match
// a row in the cities table.
db
.select()
.from(users)
.where(
notExists(db.select()
.from(cities)
.where(eq(users.homeCity, cities.id))),
);Parameters
subquery
SQLWrapper
Returns
SQL
See
exists for the inverse of this test
isNull()
isNull: (value) => SQL;Test whether an expression is NULL. By the SQL standard,
NULL is neither equal nor not equal to itself, so
it's recommended to use isNull and notIsNull for
comparisons to NULL.
Examples
// Select cars that have no discontinuedAt date.
db.select().from(cars)
.where(isNull(cars.discontinuedAt))Parameters
value
SQLWrapper
Returns
SQL
See
isNotNull for the inverse of this test
isNotNull()
isNotNull: (value) => SQL;Test whether an expression is not NULL. By the SQL standard,
NULL is neither equal nor not equal to itself, so
it's recommended to use isNull and notIsNull for
comparisons to NULL.
Examples
// Select cars that have been discontinued.
db.select().from(cars)
.where(isNotNull(cars.discontinuedAt))Parameters
value
SQLWrapper
Returns
SQL
See
isNull for the inverse of this test
not()
not: (condition) => SQL;Negate the meaning of an expression using the not keyword.
Examples
// Select cars _not_ made by GM or Ford.
db.select().from(cars)
.where(not(inArray(cars.make, ['GM', 'Ford'])))Parameters
condition
SQLWrapper
Returns
SQL
arrayContains()
arrayContains: {
<T> (column, values): SQL;
<TColumn> (column, values): SQL;
<T> (column, values): SQL;
};Call Signature
<T>(column, values): SQL;Test that a column or expression contains all elements of the list passed as the second argument.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript" and "ORM".
db.select().from(posts)
.where(arrayContains(posts.tags, ['Typescript', 'ORM']))Type Parameters
T
T
Parameters
column
Aliased<T>
values
SQLWrapper | Placeholder<string, any> | T
Returns
SQL
See
- arrayContained to find if an array contains all elements of a column or expression
- arrayOverlaps to find if a column or expression contains any elements of an array
Call Signature
<TColumn>(column, values): SQL;Test that a column or expression contains all elements of the list passed as the second argument.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript" and "ORM".
db.select().from(posts)
.where(arrayContains(posts.tags, ['Typescript', 'ORM']))Type Parameters
TColumn
TColumn extends Column<ColumnBaseConfig<ColumnDataType, string>, object, object>
Parameters
column
TColumn
values
SQLWrapper | Placeholder<string, any> | TColumn["_"]["data"]
Returns
SQL
See
- arrayContained to find if an array contains all elements of a column or expression
- arrayOverlaps to find if a column or expression contains any elements of an array
Call Signature
<T>(column, values): SQL;Test that a column or expression contains all elements of the list passed as the second argument.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript" and "ORM".
db.select().from(posts)
.where(arrayContains(posts.tags, ['Typescript', 'ORM']))Type Parameters
T
T extends SQLWrapper
Parameters
column
Exclude<T,
| Column<ColumnBaseConfig<ColumnDataType, string>, object, object>
| Aliased<unknown>>
values
unknown[] | SQLWrapper
Returns
SQL
See
- arrayContained to find if an array contains all elements of a column or expression
- arrayOverlaps to find if a column or expression contains any elements of an array
arrayContained()
arrayContained: {
<T> (column, values): SQL;
<TColumn> (column, values): SQL;
<T> (column, values): SQL;
};Call Signature
<T>(column, values): SQL;Test that the list passed as the second argument contains all elements of a column or expression.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript", "ORM" or both,
// but filtering posts that have additional tags.
db.select().from(posts)
.where(arrayContained(posts.tags, ['Typescript', 'ORM']))Type Parameters
T
T
Parameters
column
Aliased<T>
values
SQLWrapper | Placeholder<string, any> | T
Returns
SQL
See
- arrayContains to find if a column or expression contains all elements of an array
- arrayOverlaps to find if a column or expression contains any elements of an array
Call Signature
<TColumn>(column, values): SQL;Test that the list passed as the second argument contains all elements of a column or expression.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript", "ORM" or both,
// but filtering posts that have additional tags.
db.select().from(posts)
.where(arrayContained(posts.tags, ['Typescript', 'ORM']))Type Parameters
TColumn
TColumn extends Column<ColumnBaseConfig<ColumnDataType, string>, object, object>
Parameters
column
TColumn
values
SQLWrapper | Placeholder<string, any> | TColumn["_"]["data"]
Returns
SQL
See
- arrayContains to find if a column or expression contains all elements of an array
- arrayOverlaps to find if a column or expression contains any elements of an array
Call Signature
<T>(column, values): SQL;Test that the list passed as the second argument contains all elements of a column or expression.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript", "ORM" or both,
// but filtering posts that have additional tags.
db.select().from(posts)
.where(arrayContained(posts.tags, ['Typescript', 'ORM']))Type Parameters
T
T extends SQLWrapper
Parameters
column
Exclude<T,
| Column<ColumnBaseConfig<ColumnDataType, string>, object, object>
| Aliased<unknown>>
values
unknown[] | SQLWrapper
Returns
SQL
See
- arrayContains to find if a column or expression contains all elements of an array
- arrayOverlaps to find if a column or expression contains any elements of an array
arrayOverlaps()
arrayOverlaps: {
<T> (column, values): SQL;
<TColumn> (column, values): SQL;
<T> (column, values): SQL;
};Call Signature
<T>(column, values): SQL;Test that a column or expression contains any elements of the list passed as the second argument.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript", "ORM" or both.
db.select().from(posts)
.where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']))Type Parameters
T
T
Parameters
column
Aliased<T>
values
SQLWrapper | Placeholder<string, any> | T
Returns
SQL
See
- arrayContains to find if a column or expression contains all elements of an array
- arrayContained to find if an array contains all elements of a column or expression
Call Signature
<TColumn>(column, values): SQL;Test that a column or expression contains any elements of the list passed as the second argument.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript", "ORM" or both.
db.select().from(posts)
.where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']))Type Parameters
TColumn
TColumn extends Column<ColumnBaseConfig<ColumnDataType, string>, object, object>
Parameters
column
TColumn
values
SQLWrapper | Placeholder<string, any> | TColumn["_"]["data"]
Returns
SQL
See
- arrayContains to find if a column or expression contains all elements of an array
- arrayContained to find if an array contains all elements of a column or expression
Call Signature
<T>(column, values): SQL;Test that a column or expression contains any elements of the list passed as the second argument.
Throws
The argument passed in the second array can't be empty: if an empty is provided, this method will throw.
Examples
// Select posts where its tags contain "Typescript", "ORM" or both.
db.select().from(posts)
.where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']))Type Parameters
T
T extends SQLWrapper
Parameters
column
Exclude<T,
| Column<ColumnBaseConfig<ColumnDataType, string>, object, object>
| Aliased<unknown>>
values
unknown[] | SQLWrapper
Returns
SQL
See
- arrayContains to find if a column or expression contains all elements of an array
- arrayContained to find if an array contains all elements of a column or expression
Example
// Initialize operators
const ops = createEncryptionOperators(encryptionClient)
// Equality search - automatically encrypts and uses PostgreSQL operators
const results = await db
.select()
.from(usersTable)
.where(await ops.eq(usersTable.email, 'user@example.com'))
// Range query - automatically encrypts and uses eql_v2.gte()
const olderUsers = await db
.select()
.from(usersTable)
.where(await ops.gte(usersTable.age, 25))