---
description: Best practices for using Drizzle ORM with PostgreSQL
globs:
alwaysApply: false
---
# Drizzle ORM Best Practices
Standards for using Drizzle ORM with PostgreSQL. Auto-included for TypeScript and TSX files in db directories.
<rule>
name: drizzle_orm_best_practices
description: Best practices for using Drizzle ORM with PostgreSQL. Auto-included for TypeScript and TSX files in db directories.
globs: ["**/db/*.{ts,tsx}", "**/server/**/*.{ts,tsx}"]
filters:
- type: file_extension
pattern: "\.(ts|tsx)$"
- type: path
pattern: ".*/(?:db|server)/.*"
actions:
- type: suggest
message: |
Follow these Drizzle ORM best practices:
1. Schema Definition:
- Use proper column types
- Export types for each schema
- Define explicit relationships
- Use proper naming conventions
- Use ULID for ID fields with $defaultFn:
- `// Bad: Using serial or UUID without $defaultFn
id: serial('id').primaryKey(),
// or
id: uuid('id').primaryKey().defaultRandom(),
// Good: Using ULID with $defaultFn
import { ulid } from "ulid";
id: text("id")
.primaryKey()
.$defaultFn(() => ulid()),
`
- Use `.$type<T>()` for JSON field type definitions and for any column that stores a value with a TypeScript type (enums, unions, objets, etc.):
- `// Bad: Column without type information
vote: varchar("vote", { length: 8 }).notNull(),
// Good: Column with explicit type
vote: varchar("vote", { length: 8 }).notNull().$type<MessageFeedbackVote>(),
// Good: JSON field with type definition
settings: json('settings').$type<UserSettings>()
`
- Avoid using deprecated third argument in pgTable:
- `// Bad: Using deprecated third argument
export const users = pgTable('users', {
// columns
}, (table) => {
return {
uniqueIdx: uniqueIndex('unique_idx').on(table.email)
};
});
// Good: Using the new API without third argument
export const users = pgTable('users', {
// columns
});
// Create indexes separately
export const usersEmailIndex = uniqueIndex('users_email_idx').on(users.email);
`
2. Type Exports:
- Export table types using typeof
- Export inferred types for each table
- Export relationship types
- Use proper type naming conventions
3. Relationships:
- Define explicit relationships using references:
- `// Good: One-to-many relationship
export const users = pgTable('users', {
id: serial('id').primaryKey(),
teamId: integer('team_id').references(() => teams.id, { onDelete: 'cascade' })
});
// Good: Many-to-many relationship
import { primaryKey } from 'drizzle-orm/pg-core';
export const userToProjects = pgTable('user_to_projects', {
userId: integer('user_id').references(() => users.id),
projectId: integer('project_id').references(() => projects.id),
}, (table) => [
primaryKey({ columns: [table.userId, table.projectId] })
]);
// Good: One-to-one relationship
export const users = pgTable('users', {
id: serial('id').primaryKey(),
// Other columns
});
export const profiles = pgTable('profiles', {
id: serial('id').primaryKey(),
userId: integer('user_id').notNull().unique().references(() => users.id, { onDelete: 'cascade' })
});
`
- Implement relations setup in queries:
- `import { relations } from 'drizzle-orm';
export const usersRelations = relations(users, ({ one, many }) => ({
profile: one(profiles, {
fields: [users.id],
references: [profiles.userId],
}),
posts: many(posts),
team: one(teams, {
fields: [users.teamId],
references: [teams.id],
}),
}));
export const teamsRelations = relations(teams, ({ many }) => ({
users: many(users),
}));
`
4. Query Building:
- Use type-safe queries
- Implement proper joins
- Use prepared statements
- Handle complex queries properly
5. Performance & Security:
- Use proper indexes
- Implement query optimization
- Use parameterized queries
- Handle sensitive data properly
6. Validators:
- Place validators unrelated to database schemas in `/validators` folder
- Use Drizzle Zod for schema validation:
- `import { createInsertSchema, createSelectSchema } from 'drizzle-zod';
// Generate Zod schema from Drizzle schema
export const insertUserSchema = createInsertSchema(users);
export const selectUserSchema = createSelectSchema(users);
// Extend generated schema with additional validations
export const extendedUserSchema = insertUserSchema.extend({
email: z.string().email("Invalid email format"),
password: z.string().min(8, "Password must be at least 8 characters"),
});
// For validators unrelated to DB schemas, create them in /validators folder
// /validators/contactForm.ts
export const contactFormSchema = z.object({
name: z.string().min(2, "Name must be at least 2 characters"),
email: z.string().email("Invalid email format"),
message: z.string().min(10, "Message must be at least 10 characters"),
});
`
7. Indexing Best Practices:
- When to add indexes:
- Add indexes on columns used frequently in WHERE clauses
- Add indexes on columns used in ORDER BY or GROUP BY
- Add indexes on foreign key columns
- Avoid over-indexing as it slows down writes and increases storage
- Types of indexes to use:
- `// Primary key index (automatically created)
id: serial('id').primaryKey(),
// Unique index for emails
export const usersEmailIndex = uniqueIndex('users_email_idx').on(users.email);
// Multi-column index for composite lookups
export const orderDateCustomerIndex = index('order_date_customer_idx')
.on(orders.orderDate, orders.customerId);
// Partial index (with condition)
export const activeUsersIndex = index('active_users_idx', {
where: eq(users.isActive, true)
}).on(users.lastLogin);
// Full-text search index (for text columns)
export const productsSearchIndex = index('products_search_idx')
.using('gin')
.on(sql`to_tsvector('english', ${products.description})`);
`
- Naming conventions for indexes:
- Use format: table_column(s)_idx
- For unique indexes: table_column(s)_unique_idx
- For multi-column: table_column1_column2_idx
8. File Organization (Modular Table Pattern):
**PREFERRED: One folder per table structure**
9. `/db
├── index.ts # Export db client
├── schema
│ ├── index.ts # Export all schema modules
│ ├── auth/ # Authentication tables (can contain multiple related tables)
│ │ ├── schema.ts # Table definitions
│ │ ├── relation.ts # Relationships
│ │ ├── type.ts # Types and validators
│ │ └── index.ts # Module exports
│ ├── user-preferences/ # Single table module
│ │ ├── schema.ts
│ │ ├── relation.ts
│ │ ├── type.ts
│ │ └── index.ts
│ └── notification-settings/ # Single table module
│ ├── schema.ts
│ ├── relation.ts
│ ├── type.ts
│ └── index.ts
└── migrations/ # Migration files
`
10. **Modular Structure Guidelines:**
- Each table gets its own folder OR related tables share a folder (like auth)
- Each folder contains 4 files: schema.ts, relation.ts, type.ts, index.ts
- NO COMMENTS in schema files - code should be self-explanatory
- Use descriptive variable and function names instead of comments
11. **File Templates:**
1. **schema.ts** - Table definitions only:
12. `import { pgTable, text, timestamp, boolean } from "drizzle-orm/pg-core";
import { ulid } from "ulid";
export const userPreferences = pgTable("user_preferences", {
id: text("id")
.primaryKey()
.$defaultFn(() => ulid()),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" })
.unique(),
language: text("language").notNull().default("en-US"),
timezone: text("timezone").notNull().default("GMT+01:00"),
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().defaultNow(),
});
`
2. **relation.ts** - Relationships only:
13. `import { relations } from "drizzle-orm";
import { user } from "../auth/schema";
import { userPreferences } from "./schema";
export const userPreferencesRelations = relations(
userPreferences,
({ one }) => ({
user: one(user, {
fields: [userPreferences.userId],
references: [user.id],
}),
})
);
`
3. **type.ts** - Types and validators only:
14. `import { createInsertSchema } from "drizzle-zod";
import { userPreferences } from "./schema";
export type UserPreferences = typeof userPreferences.$inferSelect;
export type NewUserPreferences = typeof userPreferences.$inferInsert;
export const userPreferencesSchema = createInsertSchema(userPreferences).omit({
userId: true,
id: true,
});
`
4. **index.ts** - Module exports:
15. `export * from "./schema";
export * from "./relation";
export * from "./type";
`
16. **Cross-Module References:**
- Import tables from other modules using relative paths:
- `import { user } from "../auth/schema";
`
- Main schema index exports everything:
- `// db/schema/index.ts
export * from "./auth";
export * from "./user-preferences";
export * from "./notification-settings";
`
17. **Legacy Pattern (avoid for new projects):**
- Single files per table group (users.ts, posts.ts)
- Mixed concerns in single files
18. Integration with tRPC:
- Creating bridges between Drizzle and tRPC:
- `// server/routers/users/queries/getUser.ts
import { db } from '@/server/db';
import { users } from '@/server/db/schema';
import { eq } from 'drizzle-orm';
export async function getUser({
userId,
db
}: {
userId: string,
db: typeof db
}) {
// Use Drizzle queries inside tRPC procedure implementations
return await db.query.users.findFirst({
where: eq(users.id, userId),
with: {
profile: true,
posts: true
}
});
}
// server/routers/users/router.ts
import { router, protectedProcedure } from '@/server/trpc';
import { getUser } from './queries/getUser';
import { getUserInputSchema } from './validators';
export const usersRouter = router({
getUser: protectedProcedure
.input(getUserInputSchema)
.query(async ({ ctx, input }) => {
const { db } = ctx;
// Pass db from context to the implementation function
return await getUser({ userId: input.userId, db });
})
});
`
- Consistent error handling:
- `export async function createUser({
input,
db
}: {
input: NewUser,
db: typeof db
}) {
try {
// Attempt to create user with Drizzle
const [user] = await db.insert(users)
.values(input)
.returning();
return { success: true, user };
} catch (error) {
// Handle specific database errors
if (error.code === '23505') { // Unique violation
throw new TRPCError({
code: 'CONFLICT',
message: 'User with this email already exists'
});
}
// Re-throw other errors for tRPC to handle
throw new TRPCError({
code: 'INTERNAL_SERVER_ERROR',
message: 'Failed to create user'
});
}
}
`
- Type safety between Drizzle and tRPC:
- `// Leverage Drizzle's type system with tRPC
import { users } from '@/server/db/schema';
import { z } from 'zod';
import { createInsertSchema } from 'drizzle-zod';
// Create Zod schema from Drizzle schema for tRPC input validation
export const userInputSchema = createInsertSchema(users).omit({
id: true, // Remove auto-generated fields
createdAt: true
});
// Use in tRPC procedure
export const createUser = protectedProcedure
.input(userInputSchema)
.mutation(async ({ ctx, input }) => {
// Input is fully typed from Drizzle schema
return await createUserImpl({ input, db: ctx.db });
});
`
examples:
- input: |
// Bad: Schema without type exports and relationships
export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name'),
email: varchar('email', { length: 255 })
});
- // Good: Schema with type exports and relationships
import { InferModel } from 'drizzle-orm';
import { pgTable, serial, text, varchar, foreignKey } from 'drizzle-orm/pg-core';
- export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
email: varchar('email', { length: 255 }).notNull().unique(),
teamId: integer('team_id').references(() => teams.id, { onDelete: 'cascade' })
});
- export const teams = pgTable('teams', {
id: serial('id').primaryKey(),
name: text('name').notNull()
});
- // Type exports
export type User = InferModel<typeof users>;
export type NewUser = InferModel<typeof users, 'insert'>;
export type Team = InferModel<typeof teams>;
export type NewTeam = InferModel<typeof teams, 'insert'>;
output: "Define schemas with proper type exports and relationships"
- input: |
// Bad: Not using relationships in queries
const user = await db.select().from(users).where(eq(users.id, userId));
- // Good: Using relationships in queries
const userWithTeam = await db.select({
id: users.id,
name: users.name,
team: {
id: teams.id,
name: teams.name
}
})
.from(users)
.leftJoin(teams, eq(users.teamId, teams.id))
.where(eq(users.id, userId));
output: "Use proper relationship queries with type safety"
- input: |
// Bad: JSON field without type information
export const users = pgTable('users', {
id: serial('id').primaryKey(),
settings: json('settings')
});
- // Good: JSON field with type definition
interface UserSettings {
theme: 'light' | 'dark';
notifications: boolean;
language: string;
}
- export const users = pgTable('users', {
id: serial('id').primaryKey(),
settings: json('settings').$type<UserSettings>()
});
output: "Define type information for JSON fields using .$type<T>()"
- input: |
// Bad: Not using drizzle-zod for validation
export const createUserSchema = z.object({
name: z.string().min(2),
email: z.string().email(),
age: z.number().min(18)
});
- // Good: Using drizzle-zod for validation
import { createInsertSchema } from 'drizzle-zod';
- export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
email: varchar('email', { length: 255 }).notNull().unique(),
age: integer('age').notNull()
});
- // Generate base schema
export const insertUserSchema = createInsertSchema(users);
- // Extend with additional validations
export const createUserSchema = insertUserSchema.extend({
name: z.string().min(2, "Name must be at least 2 characters"),
email: z.string().email("Invalid email format"),
age: z.number().min(18, "Must be at least 18 years old")
});
output: "Use drizzle-zod to generate and extend validation schemas from database tables"
- input: |
// Bad: Not using indexes for frequently queried columns
export const users = pgTable('users', {
id: serial('id').primaryKey(),
email: varchar('email', { length: 255 }).notNull().unique(),
status: varchar('status', { length: 50 }).notNull()
});
- // Good: Adding proper indexes for query optimization
export const users = pgTable('users', {
id: serial('id').primaryKey(),
email: varchar('email', { length: 255 }).notNull().unique(),
status: varchar('status', { length: 50 }).notNull()
});
- // Create a regular index for frequent status filtering
export const usersStatusIndex = index('users_status_idx').on(users.status);
- // Create a composite index for queries that filter by status and sort by created_at
export const usersStatusCreatedAtIndex = index('users_status_created_at_idx')
.on(users.status, users.createdAt);
output: "Add appropriate indexes for frequent query patterns"
- input: |
// Bad: Disorganized schema files and exports
// db/users.ts
export const users = pgTable('users', {
// columns
});
// db/profiles.ts
export const profiles = pgTable('profiles', {
// columns
});
- ` // Good: Organized schema files with clear structure
// server/db/schema/index.ts
export * from './users';
export * from './posts';
`
- // server/db/schema/users.ts
import { pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';
import { relations, index } from 'drizzle-orm';
- // 1. Table definitions
export const users = pgTable('users', {
id: serial('id').primaryKey(),
email: varchar('email', { length: 255 }).notNull(),
name: text('name').notNull()
});
- // 2. Relationships
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts)
}));
- // 3. Indexes
export const usersEmailIndex = index('users_email_idx').on(users.email);
- // 4. Type exports
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
output: "Organize schema files with clear structure and exports"
- ` - input: |
// Bad: Mixed concerns in single file
// db/schema/users.ts
export const users = pgTable('users', { /* columns */ });
export const userRelations = relations(users, ({ many }) => ({ /* relations */ }));
export type User = typeof users.$inferSelect;
export const userSchema = createInsertSchema(users);
// Good: Modular structure with separated concerns
// db/schema/users/schema.ts
export const users = pgTable('users', { /* columns */ });
// db/schema/users/relation.ts
export const userRelations = relations(users, ({ many }) => ({ /* relations */ }));
// db/schema/users/type.ts
export type User = typeof users.$inferSelect;
export const userSchema = createInsertSchema(users);
// db/schema/users/index.ts
export * from "./schema";
export * from "./relation";
export * from "./type";
`
- output: "Use modular folder structure for better organization and separation of concerns"
- input: |
// Bad: Disconnected Drizzle and tRPC logic
// tRPC procedure with direct database access
const getUserPosts = protectedProcedure
.input(z.object({ userId: z.string() }))
.query(async ({ ctx, input }) => {
const posts = await ctx.db.select()
.from(posts)
.where(eq(posts.userId, input.userId));
return posts;
});
- // Good: Bridging Drizzle with tRPC using clean implementation functions
// server/routers/posts/queries/getUserPosts.ts
export async function getUserPosts({
userId,
db
}: {
userId: string,
db: DB
}) {
return await db.query.posts.findMany({
where: eq(posts.userId, userId),
orderBy: [desc(posts.createdAt)],
with: {
author: {
columns: {
id: true,
name: true
}
}
}
});
}
- // server/routers/posts/router.ts
export const postsRouter = router({
getUserPosts: protectedProcedure
.input(z.object({ userId: z.string() }))
.query(async ({ ctx, input }) => {
return await getUserPosts({ userId: input.userId, db: ctx.db });
})
});
output: "Create a clean bridge between Drizzle and tRPC with implementation functions"
metadata:
priority: high
version: 1.3
</rule>
