Drizzle ORM

Relations

Declare Drizzle relations over your converted tables.

Declare relations with Drizzle to nest query results; they emit no SQL. Declare your schema with the slim packages, convert it with toDrizzle, then add relations in your query file.

import * as DZ from '@mionjs/drizzle-orm-pg-core';
import {tableRef, type InferSelectModel} from '@mionjs/drizzle-orm';

export const authors = DZ.pgTable('authors', {
  id: DZ.uuid('id', {defaultRandom: true, primaryKey: true}),
  name: DZ.varchar('name', {length: 100, notNull: true}),
});

export const posts = DZ.pgTable('posts', {
  id: DZ.uuid('id', {defaultRandom: true, primaryKey: true}),
  authorId: DZ.uuid('author_id', {
    notNull: true,
    references: [() => tableRef(authors, 'id'), {onDelete: 'cascade'}],
  }),
  title: DZ.varchar('title', {length: 200, notNull: true}),
});

// route and client models
export type Author = InferSelectModel<typeof authors>;
export type Post = InferSelectModel<typeof posts>;

// start-nested-model
export interface AuthorWithPosts extends Author {
  posts: Post[];
}
// end-nested-model

import {authors, posts} from './drizzle-relations-schema-example.ts';
import {toDrizzle} from '@mionjs/drizzle-orm-pg-core/drizzle';
import {relations} from 'drizzle-orm';
import type {ExtractTablesWithRelations} from 'drizzle-orm';
import type {PgDatabase, PgQueryResultHKT} from 'drizzle-orm/pg-core';

const authorsDb = toDrizzle(authors);
const postsDb = toDrizzle(posts);

// relations need real Drizzle tables
const authorsRelations = relations(authorsDb, ({many}) => ({
  posts: many(postsDb),
}));
const postsRelations = relations(postsDb, ({one}) => ({
  author: one(authorsDb, {
    fields: [postsDb.authorId],
    references: [authorsDb.id],
  }),
}));

// passed to drizzle(client, {schema})
export const schema = {
  authors: authorsDb,
  posts: postsDb,
  authorsRelations,
  postsRelations,
};

declare const db: PgDatabase<
  PgQueryResultHKT,
  typeof schema,
  ExtractTablesWithRelations<typeof schema>
>;

// nested results
export async function authorsWithPosts() {
  return db.query.authors.findMany({with: {posts: true}});
}

Why Relations Stay in the Query File

Your schema file stays free of Drizzle types, so a client importing its row types never type-checks or bundles your database code. The query file already loads Drizzle, so relations cost nothing extra there.

Nested Results in Your API

Build nested API results from slim models, not Drizzle's inferred query type. This plain data type validates, survives the JSON round trip and reaches your client without loading Drizzle.

export interface AuthorWithPosts extends Author {
  posts: Post[];
}

Foreign Keys

A relation is not a foreign key. For the database to enforce the link, declare it in the schema with the references setting or foreignKey() (see Indexes and Constraints).