ConstLIST data type - DuckDB native variable-length list (elementType[])
STRUCT data type - DuckDB native nested record (STRUCT(field TYPE, ...))
MAP data type - DuckDB native key/value map (MAP(keyType, valueType))
UNION data type - DuckDB native tagged union (UNION(tag TYPE, ...))
ALGORITHM data type for hash algorithms Used for storing hashed values (passwords, checksums, etc.) Supports BCRYPT, MD5, SHA1, SHA256, SHA384, SHA512, ARGON2, PBKDF2, SCRYPT, etc.
// Store password hash using BCRYPT (recommended for passwords)
password: DataTypes.ALGORITHM({ algorithm: 'BCRYPT', cost: 12 })
// Store checksum using SHA256
fileChecksum: DataTypes.ALGORITHM({ algorithm: 'SHA256' })
// Use with VIRTUAL for computed hash fields
passwordHash: DataTypes.VIRTUAL({
returnType: DataTypes.ALGORITHM({ algorithm: 'BCRYPT' }),
set(value: string) {
this.password = hashPassword(value);
}
})
Readonlyand: symbolReadonlyor: symbolReadonlynot: symbolReadonlyeq: symbolReadonlyis: symbolReadonlyne: symbolReadonlygt: symbolReadonlygte: symbolReadonlylt: symbolReadonlylte: symbolReadonlylike: symbolReadonlynotLike: symbolReadonlyiLike: symbolReadonlynotILike: symbolReadonlystartsWith: symbolReadonlynotStartsWith: symbolReadonlyendsWith: symbolReadonlynotEndsWith: symbolReadonlysubstring: symbolReadonlynotSubstring: symbolReadonlycol: symbolReadonlyin: symbolReadonlynotIn: symbolReadonlybetween: symbolReadonlynotBetween: symbolReadonlyisNull: symbolReadonlyisNotNull: symbolReadonlyexists: symbolReadonlynotExists: symbolReadonlyregexp: symbolReadonlynotRegexp: symbolReadonlyiRegexp: symbolReadonlynotIRegexp: symbolReadonlyany: symbolReadonlyall: symbolReadonlycontainsKey: symbolReadonlycontainsKeyPath: symbolReadonlycontainsPath: symbolReadonlystrictLeft: symbolReadonlystrictRight: symbolReadonlynoExtendRight: symbolReadonlynoExtendLeft: symbolReadonlyadj: symbolReadonlynotAdj: symbolReadonlyarrayContains: symbolArray contains - checks if an array contains all specified elements PostgreSQL: @>
ReadonlyarrayContainedBy: symbolArray contained by - checks if an array is contained by the specified elements PostgreSQL: <@
ReadonlyarrayOverlaps: symbolArray overlaps - checks if arrays have common elements PostgreSQL: &&
ReadonlyarrayAny: symbolArray ANY - checks if any element matches the condition PostgreSQL: ANY
ReadonlyarrayAll: symbolArray ALL - checks if all elements match the condition PostgreSQL: ALL
Readonlycontains: symbolJSON contains - checks if JSON document contains the specified value PostgreSQL: @> MySQL: JSON_CONTAINS SQLite: JSON_EXTRACT
ReadonlycontainedBy: symbolJSON is contained in - checks if the value is contained in the JSON column PostgreSQL: <@
ReadonlykeyExists: symbolJSON key exists - checks if a key (or array index) exists in a JSON object/array
Readonlyoverlap: symbolJSON overlap - checks if JSON arrays overlap (have common elements) PostgreSQL: &&
Readonlymatch: symbolColumn reference - reference another column in a WHERE clause Used for comparing one column to another
Readonly$json: symbolReadonlyjsonPath: symbolJSON path explicit operator - explicit JSON path with field and path separated Convenience method combining field and path in one call
// Query with field and path separated
User.findAll({
where: {
data: { [Op.jsonPath('settings', 'theme')]: 'dark' }
}
})
// SQLite/MySQL: WHERE json_extract(data, '$.settings.theme') = 'dark'
// PostgreSQL: WHERE data->>'settings'->>'theme' = 'dark'
// Check if path exists (no value comparison)
User.findAll({
where: {
data: { [Op.jsonPath('settings', 'theme')]: { [Op.ne]: null } }
}
})
ReadonlyjsonContains: symbolJSON contains - checks if a JSON column contains a specific value or object PostgreSQL: @> (contains), MySQL: JSON_CONTAINS, SQLite: json_each
ReadonlyjsonHasKey: symbolJSON has key - checks if a JSON column has a specific key PostgreSQL: ? (jsonb_exists), MySQL: JSON_CONTAINS_PATH, SQLite: json_each
ReadonlyjsonbExtract: symbolJSONB extract - extracts a JSON object field (returns JSON) PostgreSQL: -> operator
ReadonlyjsonbExtractText: symbolJSONB extract text - extracts a JSON object field as text (returns text) PostgreSQL: ->> operator
ReadonlyjsonbExtractPath: symbolJSONB extract path - extracts JSON by path (returns JSON) PostgreSQL: #> operator
ReadonlyjsonbExtractPathText: symbolJSONB extract path text - extracts JSON by path as text (returns text) PostgreSQL: #>> operator
ReadonlyjsonConcat: symbolJSONB concatenation - concatenates two JSONB values PostgreSQL: || operator
ReadonlyjsonDelete: symbolJSONB delete key - deletes a key from JSONB object PostgreSQL: - operator
ReadonlyjsonDeletePath: symbolJSONB delete by path - deletes a key from JSONB by path PostgreSQL: #- operator
ReadonlyjsonPathExists: symbolJSON path exists - checks if a JSON path exists and returns boolean PostgreSQL: @? operator
ReadonlyjsonPathQuery: symbolJSON path query - evaluates JSON path and returns result PostgreSQL: @@ operator
ReadonlyjsonTypeOf: symbolJSON type of - returns the type of a JSON value PostgreSQL: json_typeof function
Create a JSON column path reference for querying Returns a Symbol that can be used as a computed property key
// Using as computed property key (recommended)
User.findAll({
where: {
settings: { [Op.json('$.theme')]: 'dark' }
}
})
// SQL: WHERE json_extract(settings, '$.theme') = 'dark'
// Using with path prefix
User.findAll({
where: {
data: { [Op.json('address.city')]: 'NYC' }
}
})
// SQL: WHERE json_extract(data, '$.address.city') = 'NYC'
Create a JSON key path query Useful for accessing specific keys in a JSON/JSONB column
// Access JSON key 'name' from data column
User.findAll({
where: {
data: { [Op.key]: 'name' }
}
})
// Compare JSON key to a value
User.findAll({
where: {
data: { [Op.key]: { path: 'name', value: 'John' } }
}
})
// Nested key access
User.findAll({
where: {
data: { [Op.key]: { path: 'profile.settings.theme', value: 'dark' } }
}
})
Create a raw SQL literal Useful for embedding raw SQL expressions like NOW(), CURRENT_TIMESTAMP, etc.
// Use with update to set current timestamp
User.update({ lastLogin: Op.literal('NOW()') }, { where: { ... } })
// Use in where clause to compare with current time
User.findAll({
where: {
createdAt: { [Op.lt]: Op.literal('NOW()') }
}
})
// Compare column to a literal value
User.findAll({
where: {
updatedAt: { [Op.gt]: Op.literal('createdAt') }
}
})
// Use with Op.where
User.findAll({ where: Op.where(Op.col('created_at'), '>', Op.literal('NOW()')) })
Create a CAST expression for type casting SQL: CAST(value AS type)
// CAST('2023-01-01' AS DATE)
User.findAll({
attributes: [[Op.cast('2023-01-01', 'DATE'), 'dateOnly']]
})
// CAST(column AS INTEGER)
User.findAll({
attributes: [[Op.cast(col('createdAt'), 'INTEGER'), 'dateInt']]
})
// CAST to DECIMAL
User.findAll({
attributes: [[Op.cast(col('price'), 'DECIMAL(10,2)'), 'priceDecimal']]
})
Create an EXTRACT expression for extracting date parts SQL: EXTRACT(part FROM field)
// EXTRACT(YEAR FROM createdAt)
User.findAll({
attributes: [[Op.extract('createdAt', 'year'), 'year']]
})
// EXTRACT(MONTH FROM order_date)
User.findAll({
attributes: [[Op.extract('orderDate', 'month'), 'month']]
})
// EXTRACT(HOUR FROM timestamp)
User.findAll({
attributes: [[Op.extract('createdAt', 'hour'), 'hour']]
})
Create a CONVERT expression for type conversion SQL: CONVERT(value, type) for MySQL, CAST for other dialects
Create a where clause with a column reference Supports both simple equality (2 args) and explicit operator (3 args)
Optionalvalue: any// Simple equality (2 args) - defaults to =
Op.where(Op.col('username'), 'john')
// => { $where: { $col: 'username', $eq: 'john' } }
// Compare column to a value with explicit operator
Op.where(Op.col('user.id'), '=', 1)
// => { $where: { $col: 'user.id', $eq: 1 } }
// Compare column to another column
Op.where(Op.col('balance'), '>', Op.col('credit_limit'))
// => { $where: { $col: 'balance', $gt: { $col: 'credit_limit' } } }
// Using with Op.eq symbol
Op.where(Op.col('user.id'), Op.eq, 1)
// => { $where: { $col: 'user.id', $eq: 1 } }
// Using with findAll
User.findAll({ where: Op.where(Op.col('username'), 'john') })
User.findAll({ where: Op.where('status', 'active') })
Create an ascending order expression SQL: ORDER BY field ASC
Create a descending order expression SQL: ORDER BY field DESC
Create a random order expression SQL: ORDER BY RANDOM() (SQLite/PostgreSQL) or ORDER BY RAND() (MySQL)
Check if a value is not null Returns true if the value is not null or undefined Useful as a predicate for filtering arrays
// Filter out null values from an array
const values = [1, null, 2, undefined, 3];
const notNullValues = values.filter(Op.isNotNull);
// => [1, 2, 3]
// Using in array filter with objects
const users = [{ name: 'John', age: null }, { name: 'Jane', age: 25 }];
const withAge = users.filter(u => Op.isNotNull(u.age));
// => [{ name: 'Jane', age: 25 }]
Create a full-text search MATCH AGAINST condition (MySQL) SQL: MATCH(columns) AGAINST(searchTerm [IN NATURAL LANGUAGE MODE | IN BOOLEAN MODE])
Optionaloptions: { mode?: "boolean" | "natural" }// Natural language mode search
Article.findAll({
where: {
[Op.matchAgainst(['title', 'body'])]: 'database'
}
})
// SQL: WHERE MATCH(title, body) AGAINST('database' IN NATURAL LANGUAGE MODE)
// Boolean mode search
Article.findAll({
where: {
[Op.matchAgainst(['title', 'body'], { mode: 'boolean' })]: '+mysql -oracle'
}
})
// SQL: WHERE MATCH(title, body) AGAINST('+mysql -oracle' IN BOOLEAN MODE)
Create a full-text search condition (alias for matchAgainst with explicit mode) SQL: MATCH(columns) AGAINST(searchTerm IN NATURAL LANGUAGE MODE)
Create a PostgreSQL tsvector expression SQL: to_tsvector(config, column)
Create a PostgreSQL tsquery expression SQL: to_tsquery(config, query)
ReadonlystDistance: symbolST_Distance - calculate distance between two geometries MySQL: ST_Distance(geom1, geom2) PostgreSQL: ST_Distance(geom1, geom2) - for geometry, ST_Distance(geog1, geog2) for geography
ReadonlystWithin: symbolST_Within - check if geometry A is within geometry B MySQL: ST_Within(geom1, geom2) PostgreSQL: ST_Within(geom1, geom2)
ReadonlystContains: symbolST_Contains - check if geometry A contains geometry B MySQL: ST_Contains(geom1, geom2) PostgreSQL: ST_Contains(geom1, geom2)
ReadonlystIntersects: symbolST_Intersects - check if two geometries intersect MySQL: ST_Intersects(geom1, geom2) PostgreSQL: ST_Intersects(geom1, geom2)
ReadonlystDWithin: symbolST_DWithin - check if geometries are within a given distance PostgreSQL: ST_DWithin (for both geometry and geography) MySQL 8.0+: ST_Distance_Sphere or ST_DWithin (with care)
ReadonlystCrosses: symbolST_Crosses - check if two geometries cross MySQL: ST_Crosses(geom1, geom2) PostgreSQL: ST_Crosses(geom1, geom2)
ReadonlystOverlaps: symbolST_Overlaps - check if two geometries overlap MySQL: ST_Overlaps(geom1, geom2) PostgreSQL: ST_Overlaps(geom1, geom2)
ReadonlystTouches: symbolST_Touches - check if two geometries touch MySQL: ST_Touches(geom1, geom2) PostgreSQL: ST_Touches(geom1, geom2)
ReadonlystEquals: symbolST_Equals - check if two geometries are equal MySQL: ST_Equals(geom1, geom2) PostgreSQL: ST_Equals(geom1, geom2)
ReadonlystIsValid: symbolST_IsValid - check if a geometry is valid MySQL: ST_IsValid(geom) PostgreSQL: ST_IsValid(geom)
ENUM data type