---
title: "Relations"
description: "Relations in Tauri SQLite ORM."
canonical_url: "https://docs.ctrl-neo.dev/en/tauri-sqlite-orm/advanced-topics/working-with-relations"
---
# 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`.

```typescript
// 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.

```ts
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.

```ts
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

```typescript
// 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:

```ts
// 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](#4-relations-v2-api-definerelations) below.

### Adding Data to Many-to-Many Relations

Insert records into the junction table:

```ts
// 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`:

```ts
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():**

```ts
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.

```ts
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](https://docs.ctrl-neo.dev/6.advanced-topics/6.type-safety-and-inference#inferrelationalselectmodel) 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.


## Sitemap

See the full [sitemap](https://docs.ctrl-neo.dev/sitemap.md) for all pages.