CTRL Docs
Querying Data

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.

import {eq, and, or, not, like, inArray /* etc. */} from '@type32/tauri-sqlite-orm';

Logical Operators

These operators are used to combine multiple conditions.

OperatorDescriptionExample 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.

OperatorDescriptionExample 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.

OperatorDescriptionExample 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 for more on subqueries.

OperatorDescriptionExample 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.

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();

Delete

Deleting data using Tauri SQLite ORM.

Relations

Relations in Tauri SQLite ORM.

Copyright © 2026