Querying Data

Select

Selecting data using Tauri SQLite ORM.

The db.select() method is your entry point for fetching data from the database. It returns a query builder that allows you to specify which columns to retrieve, how to filter, and how to order the results.

Fetching All Records

To get all rows from a table, start with db.select() and end with .all().

import { db } from '$lib/db';
import { users } from '$lib/db/schema';

// Fetches every column for every user
const allUsers = await db.select(users).all();
// allUsers is typed as: { id: number; fullName: string; ... }[]

Fetching a Single Record

If you only expect one result, chaining .get() is more convenient. It returns the first matching record or undefined if no records are found. It implicitly adds LIMIT 1 to your query.

import { eq } from '@type32/tauri-sqlite-orm';

const user = await db.select(users).where(eq(users._.columns.id, 1)).get();
// user is typed as: { id: number; fullName: string; ... } | undefined

Tip: You can also use .first() which is an alias for .get() with clearer semantics:

const user = await db.select(users).where(eq(users._.columns.id, 1)).first();

Partial Select

To improve performance, it's good practice to only select the columns you need. You can do this by passing an array of column names as the second argument to db.select(). The return type will automatically adjust to only include the selected fields.

const userNames = await db.select(users, ['id', 'fullName']).all();

// userNames is typed as: { id: number; fullName: string; }[]

Filtering with `.where()`

The .where() clause filters your results. It accepts a condition object from one of the operators.

import { and, eq, like } from '@type32/tauri-sqlite-orm';

// Find active users with a ".com" email address
const activeUsers = await db.select(users)
  .where(
    and(
      eq(users._.columns.status, 'active'),
      like(users._.columns.email, '%.com')
    )
  )
  .all();

Ordering and Pagination

You can control the order and limit the number of results using these common clauses.

.orderBy(column, direction?): Sorts the results. Direction can be 'ASC' (default) or 'DESC'.
.limit(count): Limits the query to count results.
.offset(count): Skips the first count results.

// Get the 10 most recently created users
const recentUsers = await db.select(users)
  .orderBy(users._.columns.createdAt, 'DESC')
  .limit(10)
  .all();

// Get the second page of users (10 per page)
const pageTwo = await db.select(users)
  .limit(10)
  .offset(10) // 10 * (page_number - 1)
  .all();

Easy Pagination with `.paginate()`

Instead of manually calculating limit/offset, you can use the .paginate() helper:

// Get page 1 with 20 items per page
const result = await db.select(users)
  .orderBy(users._.columns.createdAt, 'DESC')
  .paginate(1, 20);

console.log(result);
/*
{
  data: [...],           // The actual user records
  total: 150,            // Total number of users
  page: 1,               // Current page
  pageSize: 20,          // Items per page
  totalPages: 8,         // Total pages
  hasNextPage: true,     // Can go to next page
  hasPrevPage: false     // Can go to previous page
}
*/

Utility Methods

`.exists()` - Check if Rows Exist

Efficiently check if any rows match your query without fetching data:

// Check if a user exists
const userExists = await db.select(users)
  .where(eq(users._.columns.email, 'john@example.com'))
  .exists();
// Returns: boolean

if (userExists) {
  console.log('User already exists!');
}

`.count()` - Count Matching Rows

Get the number of rows that match your query:

// Count all users
const totalUsers = await db.select(users).count();
// Returns: number

// Count active users
const activeCount = await db.select(users)
  .where(eq(users._.columns.isActive, true))
  .count();

`.pluck()` - Extract Single Column Values

Get an array of values from a single column:

// Get all user emails
const emails = await db.select(users).pluck('email');
// Returns: string[]

// Get IDs of active users
const activeIds = await db.select(users)
  .where(eq(users._.columns.isActive, true))
  .pluck('id');
// Returns: number[] (or string[] depending on column type)

Aggregations

The query builder also supports grouping and aggregation. Use groupBy() with aggregate functions from @type32/tauri-sqlite-orm/aggregates.

import { count, avg, sum, min, max } from '@type32/tauri-sqlite-orm';

// These functions are type-safe and can be used with raw SQL
// For more complex aggregations, see the Advanced Topics section

For more complex aggregations that require aliasing, you may need to use joins or raw SQL. See Joins and Raw SQL.

Edit this pageorReport an issue

Migrations

Migrations are the process of keeping your database schema synchronized with your application's schema definitions in code. This ORM provides a powerful migration system that can handle both simple and complex schema changes.

Insert

Inserting data using Tauri SQLite ORM.