Advanced Topics

Relations

Relations in Tauri SQLite ORM.

Relations are a powerful, declarative feature that lets you query data from multiple tables and receive it as a clean, nested JSON object. This is the ORM's high-level, "batteries-included" approach to handling joins, abstracting away the underlying SQL complexity.

Instead of flat rows, you get results that mirror your data's structure, like a user object containing an array of their posts.

1. Defining Relations

To use this feature, you must first define the relationships between your tables using the relations helper. This should be done in your schema.ts file, alongside your table definitions.

There are two types of relations you can define: one and many.

one(foreignTable, config): Defines a many-to-one or one-to-one relationship. It signifies that the current table "belongs to" one record from the foreign table. You must specify which local and foreign columns connect the tables using fields and references (column references).
many(foreignTable): Defines a one-to-many relationship. It signifies that the current table can be associated with many records from the foreign table. This is a "back-reference" and is inferred from a one relation defined on the other table.

For many-to-many relationships, use a junction table with one and many relations, or use the v2 defineRelations API with through().

Example: Users and Posts

Let's expand our schema to include relations between users and posts.

// src/lib/db/schema.ts
import { sqliteTable, text, integer, relations } from '@type32/tauri-sqlite-orm';

// ---- Tables ----
export const users = sqliteTable('users', {
  id: integer('id').primaryKey().autoincrement(),
  fullName: text('full_name').notNull(),
});

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey().autoincrement(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull().references(() => users._.columns.id),
});


// ---- Relations ----

// Define relations for the 'posts' table
export const postsRelations = relations(posts, ({ one }) => ({
  // A post has ONE author.
  // The relation is named 'author'.
  // Use column references: fields = local FK, references = foreign PK
  author: one(users, {
    fields: [posts._.columns.authorId],
    references: [users._.columns.id],
  }),
}));

// Define relations for the 'users' table
export const usersRelations = relations(users, ({ many }) => ({
  // A user can have MANY posts.
  // The relation is named 'posts'.
  // The ORM will automatically find the corresponding 'one' relation on the 'posts' table.
  posts: many(posts),
}));

2. Querying with `include`

Once relations are defined, you can fetch them using the .include() method on a select query. You pass an object specifying which relations to load.

One-to-Many (`many`) Example

Let's fetch a user and all of their posts.

const userWithPosts = await db.select(users)
  .where(eq(users._.columns.id, 1))
  .include({ posts: true }) // Request the 'posts' relation
  .get();

/*
Result Shape:
{
  id: 1,
  fullName: 'Jane Doe',
  posts: [
    { id: 101, title: 'My First Post', authorId: 1 },
    { id: 102, title: 'Another Post', authorId: 1 }
  ]
}
*/

Many-to-One (`one`) Example

Now let's fetch a post and include its author.

const postWithAuthor = await db.select(posts)
  .where(eq(posts._.columns.id, 101))
  .include({ author: true }) // Request the 'author' relation
  .get();

/*
Result Shape:
{
  id: 101,
  title: 'My First Post',
  authorId: 1,
  author: {
    id: 1,
    fullName: 'Jane Doe'
  }
}
*/

Under the hood, .include() generates the necessary LEFT JOIN statements and then processes the flat results into these convenient nested objects for you.

3. Many-to-Many Relations (via Junction Table)

Many-to-many relationships require a junction table (also called a pivot or join table) that connects two tables. For example, posts can have many tags, and tags can be on many posts.

In the v1 relations() API, model many-to-many by defining one and many on the junction table, then use nested .include() with with:

Example: Posts and Tags

// src/lib/db/schema.ts
import { sqliteTable, text, integer, relations } from '@type32/tauri-sqlite-orm';

// ---- Tables ----
export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey().autoincrement(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull().references(() => users._.columns.id),
});

export const tags = sqliteTable('tags', {
  id: integer('id').primaryKey().autoincrement(),
  name: text('name').notNull().unique(),
});

// Junction table for posts ↔ tags
export const postTags = sqliteTable('post_tags', {
  postId: integer('post_id').notNull().references(() => posts._.columns.id),
  tagId: integer('tag_id').notNull().references(() => tags._.columns.id),
});

// ---- Relations ----

export const postsRelations = relations(posts, ({ one, many }) => ({
  user: one(users, {
    fields: [posts._.columns.authorId],
    references: [users._.columns.id],
  }),
  postTags: many(postTags),
}));

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, {
    fields: [postTags._.columns.postId],
    references: [posts._.columns.id],
  }),
  tag: one(tags, {
    fields: [postTags._.columns.tagId],
    references: [tags._.columns.id],
  }),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postTags: many(postTags),
}));

Querying Many-to-Many Relations

Use nested .include() with with to load through the junction:

// Fetch posts with their tags (via postTags junction)
const postsWithTags = await db.select(posts)
  .include({
    postTags: {
      with: { tag: true },
    },
  })
  .all();

/*
Result Shape:
[
  {
    id: 1,
    title: 'My Post',
    postTags: [
      { postId: 1, tagId: 1, tag: { id: 1, name: 'javascript' } },
      { postId: 1, tagId: 2, tag: { id: 2, name: 'typescript' } }
    ]
  }
]
*/

For a direct posts.include({ tags: true }) without nested junction data, use the Relations v2 API with through()—see Relations v2 API below.

Adding Data to Many-to-Many Relations

Insert records into the junction table:

// First, create posts and tags
const post = await db.insert(posts)
  .values({ title: 'My Post', authorId: userId })
  .returningFirst();

const tag = await db.insert(tags)
  .values({ name: 'typescript' })
  .returningFirst();

// Then create the relationship via the junction table
await db.insert(postTags)
  .values({ postId: post!.id, tagId: tag!.id })
  .execute();

4. Relations v2 API (defineRelations)

For a Drizzle-style single-place definition with from/to and many-without-one support, use defineRelations:

import { sqliteTable, integer, text, defineRelations } from '@type32/tauri-sqlite-orm';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey().autoincrement(),
  fullName: text('full_name').notNull(),
});

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey().autoincrement(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull().references(() => users._.columns.id),
});

const schema = { users, posts };

defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts({ from: r.users.id, to: r.posts.authorId }),
  },
  posts: {
    author: r.one.users({ from: r.posts.authorId, to: r.users.id }),
  },
}));

// Pass schema to TauriORM - relations are already applied to tables
const orm = new TauriORM(db, schema);

Benefits of v2:

One place for all relations
from/to instead of fields/references
Many-without-one: define r.many.posts({ from, to }) without needing one on the other table
Many-to-many via through(): direct relation without nesting through junction
optional: false on one relations: type-level required (T not T | null)
where: (alias) => Condition on many relations: predefined filters
Use defineRelationsPart to split large schemas

Many-to-many with through():

defineRelations(schema, (r) => ({
  posts: {
    user: r.one.users({ from: r.posts.userId, to: r.users.id }),
    postTags: r.many.postTags({ from: r.posts.id, to: r.postTags.postId }),
    tags: r.many.tags({
      from: through(r.posts.id, r.postTags.postId, postTags),
      to: through(r.tags.id, r.postTags.tagId, postTags),
    }),
  },
}));

// Query: posts.include({ tags: true }) — direct, no nested postTags

The v1 relations() API remains fully supported for backward compatibility.

5. Typing Relational Results

Use InferRelationalSelectModel to infer the result type of queries that use .include(). This gives you full type safety and autocompletion for nested relation data.

import { InferSelectModel, InferRelationalSelectModel } from '@type32/tauri-sqlite-orm';
import { users, usersRelations, posts } from '$lib/db/schema';

export type User = InferSelectModel<typeof users>;

const withPosts = { posts: true } as const;
export type UserWithPosts = InferRelationalSelectModel<
  typeof users,
  typeof usersRelations,
  typeof withPosts
>;
// UserWithPosts = User & { posts: Post[] }

For nested includes, pass a map of table name → relations as the fourth parameter. See Type Safety & Inference for full details.

6. Key Points

Primary Keys Required: The ORM uses primary keys to properly group and deduplicate results when loading relations.
Junction Table: For many-to-many relations, always create a separate junction table to store the relationships.
Bidirectional Relations: You can define relations from both sides (e.g., students → courses AND courses → students), allowing you to query from either direction.
Result Shape:
- one relations return a single object (or null if not found)
- many and manyToMany relations return an array (empty array if no related records)
Performance: Relations use LEFT JOINs, so the main query will still return results even if there are no related records.

Edit this pageorReport an issue

Conditions & Operators

Using Conditions & Operators in Tauri SQLite ORM.

Error Handling & Safety

Error handling, WHERE validation, and safety features