--- title: "Conditions & Operators" description: "Using Conditions & Operators in Tauri SQLite ORM." canonical_url: "https://docs.ctrl-neo.dev/en/tauri-sqlite-orm/querying-data/conditions-and-operators" --- # Conditions & Operators > Using Conditions & Operators in Tauri SQLite ORM. Condition operators are the functions you use to build filtering logic for your queries. They are passed to the `.where()` clause of `select`, `update`, and `delete` statements to create powerful, type-safe SQL conditions. Each operator function returns a special `{ sql, params }` object that the query builder uses to safely construct the final query. All operators are imported from the root of the `@type32/tauri-sqlite-orm` package. ```ts import {eq, and, or, not, like, inArray /* etc. */} from '@type32/tauri-sqlite-orm'; ``` ## Logical Operators These operators are used to combine multiple conditions.
Operator Description Example Usage
and(...conditions) Combines multiple conditions with a logical AND . where(and(eq(users._.columns.status, 'active'), gt(users._.columns.loginCount, 10)))
or(...conditions) Combines multiple conditions with a logical OR . where(or(eq(users._.columns.role, 'admin'), eq(users._.columns.role, 'owner')))
not(condition) Negates a single condition. where(not(eq(users._.columns.status, 'banned')))
## Comparison Operators These operators compare a column's value to a provided value.
Operator Description Example Usage
eq(column, value, alias?) Equal ( = ). The optional third argument is for table aliases, useful in Joins . where(eq(users._.columns.id, 1))
ne(column, value, alias?) Not equal ( != ). where(ne(users._.columns.status, 'banned'))
gt(column, value) Greater than ( > ). where(gt(posts._.columns.viewCount, 1000))
gte(column, value) Greater than or equal to ( >= ). where(gte(users._.columns.age, 21))
lt(column, value) Less than ( < ). where(lt(products._.columns.stock, 5))
lte(column, value) Less than or equal to ( <= ). where(lte(tasks._.columns.priority, 2))
between(column, min, max) Between two values ( BETWEEN ). where(between(products._.columns.price, 10, 50))
## Value Checking Operators These operators are used for pattern matching, list containment, and `NULL` checks.
Operator Description Example Usage
like(column, pattern) String pattern matching ( LIKE ). Use % as a wildcard character. where(like(users._.columns.email, '%@domain.com'))
ilike(column, pattern) Case-insensitive LIKE (uses COLLATE NOCASE ). where(ilike(users._.columns.name, '%john%'))
startsWith(column, value) String starts with value (helper for LIKE 'value%' ). where(startsWith(users._.columns.email, 'admin'))
endsWith(column, value) String ends with value (helper for LIKE '%value' ). where(endsWith(users._.columns.email, '@domain.com'))
contains(column, value) String contains value (helper for LIKE '%value%' ). where(contains(posts._.columns.title, 'TypeScript'))
inArray(column, values) Checks if a column's value is within an array of values ( IN ). where(inArray(users._.columns.status, ['pending', 'reviewing']))
notIn(column, values) Checks if a column's value is NOT in an array ( NOT IN ). where(notIn(users._.columns.role, ['admin', 'owner']))
isNull(column) Checks if a column's value is NULL . where(isNull(posts._.columns.publishedAt))
isNotNull(column) Checks if a column's value is NOT NULL . where(isNotNull(posts._.columns.publishedAt))
## Subquery Operators These operators work with subqueries. See [Advanced Topics](../6.advanced-topics/) for more on subqueries.
Operator Description Example Usage
exists(subquery) Checks if subquery returns any rows. where(exists(subquery(...)))
notExists(subquery) Checks if subquery returns no rows. where(notExists(subquery(...)))
inArray(column, subquery) Column value is in subquery results. where(inArray(users._.columns.id, subquery(...)))
notIn(column, subquery) Column value not in subquery results. where(notIn(users._.columns.id, subquery(...)))
eqSubquery(column, subquery) Column equals subquery result (scalar). where(eqSubquery(posts._.columns.views, subq)))
gtSubquery(column, subquery) Column greater than subquery result. where(gtSubquery(orders._.columns.total, avg)))
gteSubquery(column, subquery) Column greater than or equal to subquery. where(gteSubquery(users._.columns.score, min)))
ltSubquery(column, subquery) Column less than subquery result. where(ltSubquery(products._.columns.price, max)))
lteSubquery(column, subquery) Column less than or equal to subquery. where(lteSubquery(users._.columns.age, avg)))
## Combining Operators The real power comes from composing these operators to create complex queries. Since `and()` and `or()` accept multiple conditions, you can nest them to build precise filtering logic. ```ts import {db} from '$lib/db'; import {users, posts} from '$lib/db/schema'; import {and, or, eq, isNull, gte, startsWith, between} from '@type32/tauri-sqlite-orm'; // Find all posts that are either unpublished OR have more than 100 likes const popularOrUnpublishedPosts = await db.select(posts) .where( or( isNull(posts._.columns.publishedAt), gte(posts._.columns.likeCount, 100) ) ) .all(); // Find all active admin users const activeAdmins = await db.select(users) .where( and( eq(users._.columns.status, 'active'), eq(users._.columns.role, 'admin') ) ) .all(); // Find products in price range with specific name pattern const filteredProducts = await db.select(products) .where( and( between(products._.columns.price, 20, 100), startsWith(products._.columns.name, 'Premium') ) ) .all(); ``` ## Sitemap See the full [sitemap](https://docs.ctrl-neo.dev/sitemap.md) for all pages.