Drizzle ORM best practices

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