Drizzle ORM patterns for PostgreSQL schema definition, relations, queries, and mutations. Use when writing or modifying database schema, services, or any code interacting with the database.
Tables are defined in src/database/schema.ts.
import {
boolean,
integer,
pgTable,
serial,
text,
timestamp,
varchar,
unique,
} from "drizzle-orm/pg-core";
// Reusable timestamps helper
export const timestamps = {
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().defaultNow(),
};
export const chapters = pgTable("chapters", {
id: serial().primaryKey(),
title: varchar().notNull(),
description: text().notNull(),
mascotId: integer().notNull(),
...timestamps,
});
export const quizzes = pgTable(
"quizzes",
{
id: serial().primaryKey(),
chapterId: integer()
.references(() => chapters.id, { onDelete: "cascade" })
.notNull(),
title: varchar().notNull(),
level: integer().notNull(),
difficulty: varchar({ enum: ["easy", "medium", "hard"] }).notNull(),
...timestamps,
},
(t) => [unique("levels_quiz_unique").on(t.id, t.level)],
);
| Type | Usage |
|---|---|
serial() | Auto-incrementing integer (primary key) |
integer() | Integer |
varchar() | Variable-length string |
varchar({ enum: [...] }) | String with allowed values |
text() | Unlimited text |
boolean() | Boolean |
timestamp() | Timestamp |
timestamp("col_name") | Timestamp with custom column name |
.primaryKey() // Primary key
.notNull() // NOT NULL
.default(value) // Default value
.defaultNow() // DEFAULT NOW() (timestamps)
.references(() => otherTable.id) // Foreign key
.references(() => otherTable.id, { onDelete: "cascade" }) // With cascade
pgTable("name", { columns }, (t) => [
unique("constraint_name").on(t.col1, t.col2), // Unique constraint
]);
Relations are defined separately from tables, at the bottom of schema.ts.
import { relations } from "drizzle-orm";
export const chaptersRelations = relations(chapters, ({ many }) => ({
quizzes: many(quizzes),
questions: many(questions),
}));
export const quizzesRelations = relations(quizzes, ({ one, many }) => ({
chapter: one(chapters, {
fields: [quizzes.chapterId],
references: [chapters.id],
}),
questions: many(questions),
}));
one(targetTable, { fields: [thisTable.fk], references: [targetTable.pk] }) — belongs-tomany(targetTable) — has-many (no fields/references needed)Use db.query.<table> for queries with relations. This is the preferred query method.
const allQuizzes = await db.query.quizzes.findMany({
where: eq(quizzes.chapterId, chapterId),
with: {
chapter: true, // eager load relation
questions: true, // eager load relation
},
orderBy: [desc(quizzes.createdAt)],
limit: 10,
offset: 0,
});
const quiz = await db.query.quizzes.findFirst({
where: eq(quizzes.id, id),
with: {
questions: {
with: { options: true }, // nested eager loading
},
},
extras: {
numberOfQuestions: sql<number>`(
SELECT count(*) FROM questions WHERE "quizId" = quizzes.id
)`.as("numberOfQuestions"),
},
});
// Returns undefined if not found