Drizzle soft relations

This guide assumes familiarity with:

Relations Fundamentals - get familiar with the concepts of foreign key constraints, soft relations, database normalization, etc - read here
Declare schema - get familiar with how to define drizzle schemas - read here
Database connection - get familiar with how to connect to database using drizzle - read here

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

Relational queries

Select with joins

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,
  },
});

[{
  id: 10,
  content: "My first post!",
  author: {
    id: 1,
    name: "Alex"
  }
}]

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 =

`one()`

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

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.

`many()`

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

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.

---

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):

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:

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:

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:

...

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.

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:

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;
  }[];
}[];

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!

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;
    }
  }[];
}[];

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

Relations

Schema

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,
    },
});

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] })]
);

IMPORTANT

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

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,
        },
      }),
    },
  })
);

---

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.

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).

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.

Example:

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.

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,
		})
	}
}));

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.

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).

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.

Example:

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.

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(),
  },
}));

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.

For many-to-many relationships, it is generally recommended to create the following indexes on the junction table:

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).
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.

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.

Example:

In the “users and groups” example, the usersToGroups junction table connects users and groups.

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:

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(),
    },
  })
);

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.

schema1.ts

schema2.ts

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,
    }),
  },
}));

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,
    }),
  },
}));

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.

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",
    }),
  },
}));

Drizzle soft relations

one()

many()

---

One-to-one

One-to-many

Many-to-many

Predefined filters

---

Performance

One-to-one Relationships

One-to-many Relationships

Many-to-many Relationships

---

Foreign keys

Disambiguating relations

Troubleshooting

`one()`

`many()`