Database API

Overview

The db object provides a Prisma-style API for interacting with your database. It's passed to your function handlers and provides full type safety based on your schema.

import { query, z } from '../_generated/db';

export const list = query({
  args: z.object({ limit: z.number().optional() }),
  handler: async ({ args, db }) => {
    return db.users.findMany({ limit: args.limit });
  },
});

Reading data

findMany

Retrieve multiple rows from a table. Optionally filter and sort results.

// Get all rows
const allPosts = await db.posts.findMany();

// Filter with where clause
const published = await db.posts.findMany({
  where: { published: true },
});

// Filter with multiple conditions
const userPosts = await db.posts.findMany({
  where: {
    authorId: 'user-123',
    published: true,
  },
});

// Sort results
const recent = await db.posts.findMany({
  orderBy: { createdAt: 'desc' },
});

// Limit and offset for pagination
const page = await db.posts.findMany({
  limit: 10,
  offset: 20,
});

findFirst

Retrieve the first row matching the criteria, or null if none found.

const admin = await db.users.findFirst({
  where: { role: 'admin' },
});

const latest = await db.posts.findFirst({
  where: { authorId: 'user-123' },
  orderBy: { createdAt: 'desc' },
});

findById

Retrieve a single row by its primary key. Returns null if not found.

const user = await db.users.findById('user-123');
const post = await db.posts.findById(42);

if (!user) {
  throw new Error('User not found');
}

count

Count rows matching the criteria.

const total = await db.posts.count();

const published = await db.posts.count({
  where: { published: true },
});

Writing data

insert

Insert a new row and return the created record.

const newUser = await db.users.insert({
  id: 'user-456',
  email: '[email protected]',
  name: 'Alice',
});

const post = await db.posts.insert({
  title: 'Hello World',
  content: 'My first post',
  authorId: 'user-456',
});

insertMany

Insert multiple rows in a single operation.

const newTags = await db.tags.insertMany([
  { name: 'typescript' },
  { name: 'sqlite' },
  { name: 'realtime' },
]);

update

Update rows matching the criteria and return the count of affected rows.

const updated = await db.posts.update({
  where: { _id: 'post-abc-123' },
  data: {
    title: 'Updated Title',
    updatedAt: new Date(),
  },
});

await db.posts.update({
  where: { authorId: 'user-123' },
  data: { published: false },
});

upsert

Insert a row or update it if it already exists. The where clause determines which field to match on — you can upsert on any field, not just the primary key.

const user = await db.users.upsert({
  where: { email: '[email protected]' },
  create: {
    id: 'user-789',
    email: '[email protected]',
    name: 'Bob',
  },
  update: {
    name: 'Bob Updated',
    lastLoginAt: new Date(),
  },
});

How it works: The upsert uses findFirst internally to check if a record matching the where clause exists. If found, it updates that record; if not, it creates a new one.

delete

Delete rows matching the criteria and return the count of deleted rows.

const deleted = await db.posts.delete({
  where: { _id: 'post-abc-123' },
});

await db.sessions.delete({
  where: { expiresAt: { lt: new Date() } },
});

deleteMany

Delete multiple rows matching the criteria.

const deleted = await db.posts.deleteMany({
  where: { authorId: 'user-123' },
});
console.log(`Deleted ${deleted} posts`);

deleteAll

Delete all rows in a table. Use with caution!

const deleted = await db.tempData.deleteAll();

deleteById

Delete a single row by its primary key.

const deleted = await db.posts.deleteById(42);

if (!deleted) {
  throw new Error('Post not found');
}

System columns

Every table automatically includes system columns that Tether manages for you:

Column	Description
_id	UUID primary key, auto-generated on insert if not provided
_createdAt	ISO 8601 timestamp, auto-set when record is created
_updatedAt	ISO 8601 timestamp, auto-updated on every modification

const post = await db.posts.insert({
  title: 'Hello World',
});
console.log(post._id); // e.g. '550e8400-e29b-41d4-a716-446655440000'

Return values

Method	Returns
findMany	Array of records (may be empty)
findFirst / findById	Single record or null
count	Number of matching records
insert	The created record with all fields
insertMany	Array of created records
update	Number of affected rows
upsert	The created or updated record
delete	Number of deleted rows
deleteMany	Number of deleted rows
deleteAll	Number of deleted rows
deleteById	Boolean indicating success

Where clauses

Filter data using various comparison operators:

Syntax	Description
{ field: value }	Equals
{ field: { ne: value } }	Not equals
{ field: { gt: value } }	Greater than
{ field: { gte: value } }	Greater than or equal
{ field: { lt: value } }	Less than
{ field: { lte: value } }	Less than or equal
{ field: { in: [...] } }	In array
{ field: { notIn: [...] } }	Not in array
{ field: { contains: 'str' } }	Contains substring
{ field: { startsWith: 'str' } }	Starts with
{ field: { endsWith: 'str' } }	Ends with
{ field: null }	Is null
{ field: { ne: null } }	Is not null

Combining conditions

// AND (default — all conditions must match)
const results = await db.posts.findMany({
  where: {
    published: true,
    authorId: 'user-123',
  },
});

// OR
const results = await db.posts.findMany({
  where: {
    OR: [
      { status: 'draft' },
      { status: 'review' },
    ],
  },
});

// Complex conditions
const results = await db.posts.findMany({
  where: {
    published: true,
    OR: [
      { title: { contains: 'TypeScript' } },
      { tags: { contains: 'typescript' } },
    ],
  },
});

Ordering

Sort results using orderBy:

const posts = await db.posts.findMany({
  orderBy: { createdAt: 'desc' },
});

const posts = await db.posts.findMany({
  orderBy: [
    { published: 'desc' },
    { createdAt: 'desc' },
  ],
});

Including related data

Use include to fetch related records along with your query results. Tether automatically detects relationships through foreign keys and supports arbitrary nesting depth.

Belongs-to (many-to-one)

When your table has a foreign key column pointing to another table, include returns the related record as a nested object.

// Posts table has an "authorId" column referencing "users"
const posts = await db.posts.findMany({
  include: { users: true },
});
// Result: [{ _id: '...', title: '...', authorId: 'user-1', author: { _id: 'user-1', name: 'Alice' } }]

The relation key is derived from the foreign key column — authorId becomes author, categoryId becomes category. You can also include by relation key:

const posts = await db.posts.findMany({
  include: { author: true },
});

Has-many (one-to-many)

When another table has a foreign key pointing back to your table, include returns an array of related records.

// Comments table has a "postId" column referencing "posts"
const posts = await db.posts.findMany({
  include: { comments: true },
});
// Result: [{ _id: '...', title: '...', comments: [{ _id: '...', text: '...', postId: '...' }] }]

Nested includes

Include related data at any depth by passing an object with its own include:

// Posts → comments → likes (3 levels deep)
const posts = await db.posts.findMany({
  include: {
    comments: {
      include: {
        likes: true,
      },
    },
  },
});

// Posts → author + comments → user (mixed directions)
const posts = await db.posts.findMany({
  include: {
    author: true,
    comments: {
      include: {
        user: true,
      },
    },
  },
});

Note: Nesting is limited to 5 levels deep to prevent runaway queries. Each include level runs a separate batched query, so performance scales well even with many related records.

Include with other options

Includes work alongside all other query options:

const posts = await db.posts.findMany({
  where: { published: true },
  orderBy: { createdAt: 'desc' },
  limit: 10,
  include: {
    author: true,
    comments: true,
  },
});

Includes also work with findFirst, findUnique, and findById:

const post = await db.posts.findById('post-123', {
  include: { comments: { include: { likes: true } } },
});

Pagination

Use limit and offset for pagination:

const pageSize = 20;
const page = 3;

const posts = await db.posts.findMany({
  orderBy: { createdAt: 'desc' },
  limit: pageSize,
  offset: (page - 1) * pageSize,
});

const total = await db.posts.count();