# Drizzle relations

<Callout type='error'>
This page explains concepts available on drizzle versions `1.0.0-beta.1` and higher.
</Callout>

<Npm>
drizzle-orm@beta
drizzle-kit@beta -D
</Npm>

<br/>

<Prerequisites>  
  - **Relations Fundamentals** - get familiar with the concepts of foreign key constraints, soft relations, database normalization, etc - [read here](/docs/rqb-fundamentals)
  - **Declare schema** - get familiar with how to define drizzle schemas - [read here](/docs/sql-schema-declaration)
  - **Database connection** - get familiar with how to connect to database using drizzle - [read here](/docs/get-started-postgresql)
</Prerequisites>

The sole purpose of Drizzle relations is to let you query your relational data in the most simple and concise way:

<CodeTabs items={["Relational queries", "Select with joins"]}>
<Section>
```ts
import { drizzle } from 'drizzle-orm/…';
import { defineRelations } from 'drizzle-orm';
import * as p from 'drizzle-orm/pg-core';

export const users = p.pgTable('users', {
	id: p.integer().primaryKey(),
	name: p.text().notNull()
});

export const posts = p.pgTable('posts', {
	id: p.integer().primaryKey(),
	content: p.text().notNull(),
	ownerId: p.integer('owner_id'),
});

const relations = defineRelations({ users, posts }, (r) => ({
	posts: {
		author: r.one.users({
			from: r.posts.ownerId,
			to: r.users.id,
		}),
	}
}))

const db = drizzle(client, { relations });

const result = db.query.posts.findMany({
  with: {
    author: true,
  },
});
```
```ts
[{
  id: 10,
  content: "My first post!",
  author: {
    id: 1,
    name: "Alex"
  }
}]
```
</Section>
<Section>
```ts
import { drizzle } from 'drizzle-orm/…';
import { eq } from 'drizzle-orm';
import { posts, users } from './schema';

const db = drizzle(client);

const res = await db.select()
                    .from(posts)
                    .leftJoin(users, eq(posts.ownerId, users.id))
                    .orderBy(posts.id)
const mappedResult =  
```
</Section>
</CodeTabs>

### `one()`
Here is a list of all fields available for `.one()` in drizzle relations

```ts {3-11}
const relations = defineRelations({ users, posts }, (r) => ({
	posts: {
		author: r.one.users({
			from: r.posts.ownerId,
			to: r.users.id,
			optional: false,
      alias: 'custom_name',
			where: {
				verified: true,
			}
		}),
	}
}))
```

- `author` key is a custom key that appears in the `posts` object when using Drizzle relational queries.
- `r.one.users` defines that `author` will be a single object from the `users` table rather than an array of objects.
- `from: r.posts.ownerId` specifies the table from which we are establishing a soft relation. 
In this case, the relation starts from the `ownerId` column in the `posts` table.
- `to: r.users.id` specifies the table to which we are establishing a soft relation. 
In this case, the relation points to the `id` column in the `users` table.
- `optional: false` at the type level makes the `author` key in the posts object `required`. 
This should be used when you are certain that this specific entity will always exist.
- `alias` is used to add a specific alias to relationships between tables. If you have multiple identical relationships between two tables, you should 
differentiate them using `alias`
- `where` condition can be used for polymorphic relations. It fetches relations based on a `where` statement.
For example, in the case above, only `verified authors` will be retrieved. Learn more about polymorphic relations [here](/docs/relations-schema-declaration#polymorphic-relations).

### `many()`

Here is a list of all fields available for `.many()` in drizzle relations

```ts {3-11}
const relations = defineRelations({ users, posts }, (r) => ({
	users: {
		feed: r.many.posts({
			from: r.users.id,
			to: r.posts.ownerId,
			optional: false,
      alias: 'custom_name',
			where: {
				approved: true,
			}
		}),
	}
}))
```

- `feed` key is a custom key that appears in the `users` object when using Drizzle relational queries.
- `r.many.posts` defines that `feed` will be an array of objects from the `posts` table rather than just an object
- `from: r.users.id` specifies the table from which we are establishing a soft relation. 
In this case, the relation starts from the `id` column in the `users` table.
- `to: r.posts.ownerId` specifies the table to which we are establishing a soft relation. 
In this case, the relation points to the `ownerId` column in the `posts` table.
- `optional: false` at the type level makes the `feed` key in the posts object `required`. 
This should be used when you are certain that this specific entity will always exist.
- `alias` is used to add a specific alias to relationships between tables. If you have multiple identical relationships between two tables, you should 
differentiate them using `alias`
- `where` condition can be used for polymorphic relations. It fetches relations based on a `where` statement.
For example, in the case above, only `approved posts` will be retrieved. Learn more about polymorphic relations [here](/docs/relations-schema-declaration#polymorphic-relations).

## ---

### One-to-one
Drizzle ORM provides you an API to define `one-to-one` relations between tables with the `defineRelations` function.

An example of a `one-to-one` relation between users and users, where a user can invite another (this example uses a self reference):

```typescript copy {10-17}
import { pgTable, serial, text, boolean } from 'drizzle-orm/pg-core';
import { defineRelations } from 'drizzle-orm';

export const users = pgTable('users', {
	id: integer().primaryKey(),
	name: text(),
	invitedBy: integer('invited_by'),
});

export const relations = defineRelations({ users }, (r) => ({
	users: {
		invitee: r.one.users({
			from: r.users.invitedBy,
			to: r.users.id,
		})
	}
}));
```

Another example would be a user having a profile information stored in separate table. In this case, because the foreign key is stored in the "profile_info" table, the user relation have neither fields or references. This tells Typescript that `user.profileInfo` is nullable:

```typescript copy {15-22}
import { pgTable, serial, text, integer, jsonb } from 'drizzle-orm/pg-core';
import { defineRelations } from 'drizzle-orm';

export const users = pgTable('users', {
	id: integer().primaryKey(),
	name: text(),
});

export const profileInfo = pgTable('profile_info', {
	id: serial().primaryKey(),
	userId: integer('user_id').references(() => users.id),
	metadata: jsonb(),
});

export const relations = defineRelations({ users, profileInfo }, (r) => ({
	users: {
		profileInfo: r.one.profileInfo({
			from: r.users.id,
			to: r.profileInfo.userId,
		})
	}
}));

const user = await db.query.posts.findFirst({ with: { profileInfo: true } });
//____^? type { id: number, profileInfo: { ... } | null  }
```

### One-to-many
Drizzle ORM provides you an API to define `one-to-many` relations between tables with `defineRelations` function. 

Example of `one-to-many` relation between users and posts they've written:

```typescript copy {15-25}
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { defineRelations } from 'drizzle-orm';

export const users = pgTable('users', {
	id: integer('id').primaryKey(),
	name: text('name'),
});

export const posts = pgTable('posts', {
	id: integer('id').primaryKey(),
	content: text('content'),
	authorId: integer('author_id'),
});

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

Now lets add comments to the posts:
```typescript copy {9-14,22,27-32}
...

export const posts = pgTable('posts', {
	id: integer('id').primaryKey(),
	content: text('content'),
	authorId: integer('author_id'),
});

export const comments = pgTable("comments", {
  id: integer().primaryKey(),
  text: text(),
  authorId: integer("author_id"),
  postId: integer("post_id"),
});

export const relations = defineRelations({ users, posts, comments }, (r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
    comments: r.many.comments(),
  },
  users: {
    posts: r.many.posts(),
  },
  comments: {
    post: r.one.posts({
      from: r.comments.postId,
      to: r.posts.id,
    }),
  },
}));
```


### Many-to-many
Drizzle ORM provides you an API to define `many-to-many` relations between tables through so called `junction` or `join` tables,
they have to be explicitly defined and store associations between related tables.  

Example of `many-to-many` relation between users and groups we are using `through` to bypass junction table selection and directly select many `groups` for each `user`.
```typescript copy {27-39}
import { defineRelations } from 'drizzle-orm';
import { integer, pgTable, primaryKey, text } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: integer().primaryKey(),
  name: text(),
});

export const groups = pgTable('groups', {
  id: integer().primaryKey(),
  name: text(),
});

export const usersToGroups = pgTable(
  'users_to_groups',
  {
    userId: integer('user_id')
      .notNull()
      .references(() => users.id),
    groupId: integer('group_id')
      .notNull()
      .references(() => groups.id),
  },
  (t) => [primaryKey({ columns: [t.userId, t.groupId] })],
);

export const relations = defineRelations({ users, groups, usersToGroups },
  (r) => ({
    users: {
      groups: r.many.groups({
        from: r.users.id.through(r.usersToGroups.userId),
        to: r.groups.id.through(r.usersToGroups.groupId),
      }),
    },
    groups: {
      participants: r.many.users(),
    },
  })
);
```

**Query example:**
```ts
const res = await db.query.users.findMany({
  with: { 
    groups: true 
  },
});

// response type
type Response = {
  id: number;
  name: string | null;
  groups: {
    id: number;
    name: string | null;
  }[];
}[];
```

<Callout type='error' title='Relational Queries v1'>
Previously, you would need to query through a `junction` table and then map it out for every response

❌ You don't need to do it now!
```ts
const response = await db._query.users.findMany({
	with: {
		usersToGroups: {
			columns: {},
			with: {
				groups: true,
			},
		},
	},
});

// response type
type Response = {
  id: number;
  name: string | null;
  usersToGroups: {
    groups: {
       id: number;
       name: string | null;
    }
  }[];
}[];
```
</Callout>

### Predefined filters

Predefined `where` statements in Drizzle's relation definitions are a type of polymorphic relations implementation, but it's not fully it. Essentially, they allow you to
connect tables not only by selecting specific columns but also through custom `where` statements. Let's look at some examples:

We can define a relation between `groups` and `users` so that when querying group's users, we only retrieve those whose `verified` column is set to `true`
<CodeTabs items={["Relations", "Schema"]}>
<Section>
```ts
import { defineRelations } from "drizzle-orm";
import * as p from "drizzle-orm/pg-core";
import * as schema from './schema';

export const relations = defineRelations(schema,(r) => ({
    groups: {
      verifiedUsers: r.many.users({
        from: r.groups.id.through(r.usersToGroups.groupId),
        to: r.users.id.through(r.usersToGroups.userId),
        where: {
          verified: true,
        },
      }),
    },
  })
);

...

await db.query.groups.findMany({
    with: {
      verifiedUsers: true,
    },
});
```
</Section>
<Section>
```ts
import { defineRelations } from "drizzle-orm";
import * as p from "drizzle-orm/pg-core";

export const users = p.pgTable("users", {
  id: p.integer().primaryKey(),
  name: p.text().notNull(),
  verified: p.boolean().notNull(),
});

export const groups = p.pgTable("groups", {
  id: p.integer().primaryKey(),
  title: p.text().notNull(),
});

export const usersToGroups = p.pgTable(
  "users_to_groups",
  {
    userId: p
      .integer("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    groupId: p
      .integer("group_id")
      .notNull()
      .references(() => groups.id, { onDelete: "cascade" }),
  },
  (t) => [p.primaryKey({ columns: [t.groupId, t.userId] })]
);
```
</Section>
</CodeTabs>

<Callout type='warning'>
You can only specify filters on the target (to) table. So in this example, the where clause will only include columns from the `users` table since we are establishing a relation **TO** users

```ts {7}
export const relations = defineRelations(schema,(r) => ({
    groups: {
      verifiedUsers: r.many.users({
        from: r.groups.id.through(r.usersToGroups.groupId),
        to: r.users.id.through(r.usersToGroups.userId),
        where: {
          verified: true,
        },
      }),
    },
  })
);
```
</Callout>

## ---

### Relations Parts

In a case you need to separate relations config into several parts you can use `defineRelationsPart` helpers

```ts
import { defineRelations, defineRelationsPart } from 'drizzle-orm';
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    invitee: r.one.users({
      from: r.users.invitedBy,
      to: r.users.id,
    }),
    posts: r.many.posts(),
  }
}));

export const part = defineRelationsPart(schema, (r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  }
}));
```

and then you can provide it to the db instance

```ts
const db = drizzle(process.env.DB_URL, { relations: { ...relations, ...part } })
```

<Callout type='warning'>
There are a few rules you would need to follow to make sure it `defineRelationsParts` works as expected

**Rule 1**: If you specify reltions with parts, when passing it to drizzle db function you would need to specify it in the right order(main relations goes first)
```ts
// ✅
const db = drizzle(process.env.DB_URL, { relations: { ...relations, ...part } })

// ❌
const db = drizzle(process.env.DB_URL, { relations: { ...part, ...relations } })
```

<Callout collapsed="Why it's important?">
Even if there will be no type or runtime error, this is how "..." works with objects. As long as main relation 
recursively infer all tables names, so it can be available in autocomplete. Here is an example:

```ts
export const relations = defineRelations(schema, (r) => ({
  users: {
    invitee: r.one.users({
      from: r.users.invitedBy,
      to: r.users.id,
    }),
    posts: r.many.posts(),
  }
}));

export const part = defineRelationsPart(schema, (r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  }
}));
```

Here `relations` and `part` can be represented and this object:
```json
// relations
{
  "users": {"invitee": {...}, "posts": {...}},
  // added here, so all tables from schema will exist in autocomplete
  "posts": {}
}

// part
{
  "posts": {"author": {...}}
}
```

Having `{ ...relations, ...part }` will result in 
```json
{
  "users": {"invitee": {...}, "posts": {...}},
  "posts": {"author": {...}}
}
```

and having `{ ...relations, ...part }` will result in
```json
{
  "users": {"invitee": {...}, "posts": {...}},
  // As you can see in the final object, posts relations information will be lost
  "posts": {}
}
```

</Callout>

**Rule 2**: You should have min relations, so drizzle can infer all of the table for autocomplete. If you want to have only parts, then 
one of your parts should be empty, like this:

```ts
export const mainPart = defineRelationsPart(schema);
```

In this case, all tables will be inferred correctly, and you'll have complete information about your schema
</Callout>

## ---

### Performance

When working with relations in Drizzle ORM, especially in applications with 
significant data or complex queries, optimizing database performance is crucial.  
Indexes play a vital role in speeding up data retrieval, particularly when querying 
related data. This section outlines recommended indexing strategies for each type 
of relationship defined using Drizzle ORM.

##### One-to-one Relationships

In a one-to-one relationship, like the "user invites user" example or the
"user has profile info" example, the key performance consideration is efficient joining 
of the related tables.

<Callout>
For optimal performance in one-to-one relationships, you should create an index 
on the foreign key column in the table that is being referenced 
(the "target" table in the relation).
</Callout>

<Callout collapsed='Why it is important'>
When you query data with related one-to-one information, Drizzle performs a JOIN operation. An index on the foreign key column allows the database to quickly 
locate the related row in the target table, significantly speeding up the join process.
</Callout>

**Example:**
```typescript
import * as p from 'drizzle-orm/pg-core';
import { defineRelations } from 'drizzle-orm';

export const users = p.pgTable('users', {
	id: p.integer().primaryKey(),
	name: p.text(),
});

export const profileInfo = p.pgTable('profile_info', {
	id: p.integer().primaryKey(),
	userId: p.integer('user_id').references(() => users.id),
	metadata: p.jsonb(),
});

export const relations = defineRelations({ users, profileInfo }, (r) => ({
	users: {
		profileInfo: r.one.profileInfo({
			from: r.users.id,
			to: r.profileInfo.userId,
		})
	}
}));
```

To optimize queries fetching user data along with their profile information, 
you should create an index on the `userId` column in the `profile_info` table.

```typescript {13-15,21}
import * as p from 'drizzle-orm/pg-core';
import { defineRelations } from 'drizzle-orm';

export const users = p.pgTable('users', {
	id: p.integer().primaryKey(),
	name: p.text(),
});

export const profileInfo = pgTable('profile_info', {
	id: p.integer().primaryKey(),
	userId: p.integer('user_id').references(() => users.id),
	metadata: p.jsonb(),
}, (table) => [
  p.index('profile_info_user_id_idx').on(table.userId)
]);

export const relations = defineRelations({ users, profileInfo }, (r) => ({
	users: {
		profileInfo: r.one.profileInfo({
			from: r.users.id,
			to: r.profileInfo.userId,
		})
	}
}));
```
```sql
CREATE INDEX idx_profile_info_user_id ON profile_info (user_id);
```

#### One-to-many Relationships

Similar to one-to-one relationships, one-to-many relations benefit significantly 
from indexing to optimize join operations. Consider the "users and posts" example where one user can have many posts.

<Callout>
For one-to-many relationships, create an index on the foreign key column in the table that represents the "many" side of the relationship (the table with the foreign key referencing the "one" side).
</Callout>

<Callout collapsed='Why it is important'>
When you fetch a user with their posts or posts with their authors, joins are performed.
Indexing the foreign key (`authorId` in `posts` table) allows the database to efficiently 
retrieve all posts associated with a given user or quickly find the author of a post.
</Callout>

**Example:**
```typescript
import * as p from "drizzle-orm/pg-core";
import { defineRelations } from 'drizzle-orm';

export const users = p.pgTable('users', {
	id: p.integer().primaryKey(),
	name: p.text(),
});

export const posts = p.pgTable('posts', {
	id: p.integer().primaryKey(),
	content: p.text(),
	authorId: p.integer('author_id'),
});

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

To optimize queries involving users and their posts, create an index on the `authorId` column in the `posts` table.

```typescript {13-15}
import * as p from "drizzle-orm/pg-core";
import { defineRelations } from 'drizzle-orm';

export const users = p.pgTable('users', {
	id: p.integer().primaryKey(),
	name: p.text(),
});

export const posts = p.pgTable('posts', {
	id: p.integer().primaryKey(),
	content: p.text(),
	authorId: p.integer('author_id'),
}, (t) => [
  index('posts_author_id_idx').on(table.authorId)
]);

export const relations = defineRelations({ users, posts }, (r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  },
  users: {
    posts: r.many.posts(),
  },
}));
```
```sql
CREATE INDEX idx_posts_author_id ON posts (author_id);
```

#### Many-to-many Relationships

Many-to-many relationships, implemented using junction tables, require a slightly 
more nuanced indexing strategy to ensure optimal query performance. 
Consider the "users and groups" example with the `usersToGroups` junction table.

<Callout>
For many-to-many relationships, it is generally recommended to create the following 
indexes on the junction table:
1.  **Index on each foreign key column individually:** This optimizes queries that 
filter or join based on a single side of the relationship 
(e.g., finding all groups for a user OR all users in a group).
2.  **Composite index on both foreign key columns together:** This is crucial for 
efficiently resolving the many-to-many relationship itself. It speeds up queries that need to find the connections between both entities.
</Callout>

<Callout collapsed='Why it is important'>
When querying many-to-many relations, especially when using `through` in Drizzle ORM, the database needs to efficiently navigate the junction table.

- Indexes on individual foreign key columns (`userId`, `groupId` in `usersToGroups`) help when you are querying from one side to find the other (e.g., "find groups for a user").
- The composite index on `(userId, groupId)` in `usersToGroups` is particularly important for quickly finding all relationships defined in the junction table. This is used when Drizzle ORM resolves the `many-to-many` relation to fetch related entities.
</Callout>

**Example:**

In the "users and groups" example, the `usersToGroups` junction table connects `users` and `groups`.

```typescript
import { defineRelations } from 'drizzle-orm';
import * as p from 'drizzle-orm/pg-core';

export const users = p.pgTable('users', {
  id: p.integer().primaryKey(),
  name: p.text(),
});

export const groups = p.pgTable('groups', {
  id: p.integer().primaryKey(),
  name: p.text(),
});

export const usersToGroups = p.pgTable(
  'users_to_groups',
  {
    userId: p.integer('user_id')
      .notNull()
      .references(() => users.id),
    groupId: p.integer('group_id')
      .notNull()
      .references(() => groups.id),
  },
  (t) => [p.primaryKey({ columns: [t.userId, t.groupId] })],
);

export const relations = defineRelations({ users, groups, usersToGroups },
  (r) => ({
    users: {
      groups: r.many.groups({
        from: r.users.id.through(r.usersToGroups.userId),
        to: r.groups.id.through(r.usersToGroups.groupId),
      }),
    },
    groups: {
      participants: r.many.users(),
    },
  })
);
```

To optimize queries for users and groups, create indexes on `usersToGroups` table as follows:

```typescript {26-28}
import { defineRelations } from 'drizzle-orm';
import * as p from 'drizzle-orm/pg-core';

export const users = p.pgTable('users', {
  id: p.integer().primaryKey(),
  name: p.text(),
});

export const groups = p.pgTable('groups', {
  id: p.integer().primaryKey(),
  name: p.text(),
});

export const usersToGroups = p.pgTable(
  'users_to_groups',
  {
    userId: p.integer('user_id')
      .notNull()
      .references(() => users.id),
    groupId: p.integer('group_id')
      .notNull()
      .references(() => groups.id),
  },
  (t) => [
    p.primaryKey({ columns: [t.userId, t.groupId] }),
    p.index('users_to_groups_user_id_idx').on(table.userId),
    p.index('users_to_groups_group_id_idx').on(table.groupId),
    p.index('users_to_groups_composite_idx').on(table.userId, table.groupId),
  ],
);

export const relations = defineRelations({ users, groups, usersToGroups },
  (r) => ({
    users: {
      groups: r.many.groups({
        from: r.users.id.through(r.usersToGroups.userId),
        to: r.groups.id.through(r.usersToGroups.groupId),
      }),
    },
    groups: {
      participants: r.many.users(),
    },
  })
);
```
```sql
CREATE INDEX idx_users_to_groups_user_id ON users_to_groups (user_id);
CREATE INDEX idx_users_to_groups_group_id ON users_to_groups (group_id);
CREATE INDEX idx_users_to_groups_composite ON users_to_groups (userId, groupId);
```

By applying these indexing strategies, you can significantly improve the performance of your Drizzle ORM applications when working with relational data, especially as your data volume grows and your queries become more complex. Remember to choose the indexes that best suit your specific query patterns and application needs.

## ---

### Foreign keys

You might've noticed that `relations` look similar to foreign keys — they even have a `references` property. So what's the difference?

While foreign keys serve a similar purpose, defining relations between tables, they work on a different level compared to `relations`.

Foreign keys are a database level constraint, they are checked on every `insert`/`update`/`delete` operation and throw an error if a constraint is violated.
On the other hand, `relations` are a higher level abstraction, they are used to define relations between tables on the application level only.
They do not affect the database schema in any way and do not create foreign keys implicitly.

What this means is `relations` and foreign keys can be used together, but they are not dependent on each other.
You can define `relations` without using foreign keys (and vice versa), which allows them to be used with databases that do not support foreign keys.

The following two examples will work exactly the same in terms of querying the data using Drizzle relational queries.

<CodeTabs items={["schema1.ts", "schema2.ts"]}>
<CodeTab>
```ts {15}
export const users = p.pgTable("users", {
  id: p.integer().primaryKey(),
  name: p.text(),
});

export const profileInfo = p.pgTable("profile_info", {
  id: p.integer().primaryKey(),
  userId: p.integer("user_id"),
  metadata: p.jsonb(),
});

export const relations = defineRelations({ users, profileInfo }, (r) => ({
  users: {
    profileInfo: r.one.profileInfo({
      from: r.users.id,
      to: r.profileInfo.userId,
    }),
  },
}));
```
</CodeTab>
<CodeTab>
```ts {15}
export const users = p.pgTable("users", {
  id: p.integer().primaryKey(),
  name: p.text(),
});

export const profileInfo = p.pgTable("profile_info", {
  id: p.integer().primaryKey(),
  userId: p.integer("user_id").references(() => users.id),
  metadata: p.jsonb(),
});

export const relations = defineRelations({ users, profileInfo }, (r) => ({
  users: {
    profileInfo: r.one.profileInfo({
      from: r.users.id,
      to: r.profileInfo.userId,
    }),
  },
}));
```
</CodeTab>
</CodeTabs>

### Disambiguating relations

Drizzle also provides the `alias` option as a way to disambiguate
relations when you define multiple of them between the same two tables. For
example, if you define a `posts` table that has the `author` and `reviewer`
relations.

```ts {19,22,29,34}
import { pgTable, integer, text } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const users = pgTable('users', {
	id: integer('id').primaryKey(),
	name: text('name'),
});

export const posts = pgTable('posts', {
	id: integer('id').primaryKey(),
	content: text('content'),
	authorId: integer('author_id'),
	reviewerId: integer('reviewer_id'),
});
 
export const relations = defineRelations({ users, posts }, (r) => ({
  users: {
    posts: r.many.posts({
      alias: "author",
    }),
    reviewedPosts: r.many.posts({
      alias: "reviewer",
    }),
  },
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
      alias: "author",
    }),
    reviewer: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
      alias: "reviewer",
    }),
  },
}));
```

### Troubleshooting



Source: https://orm.drizzle.team/docs/relations

import Tab from '@mdx/Tab.astro';
import Tabs from '@mdx/Tabs.astro';
import IsSupportedChipGroup from '@mdx/IsSupportedChipGroup.astro';
import Callout from '@mdx/Callout.astro';
import Section from '@mdx/Section.astro';

import CodeTab from '@mdx/CodeTab.astro';
import CodeTabs from '@mdx/CodeTabs.astro';