drizzle

Drizzle ORM Best Practices

Overview

Aspect	Recommendation
Version	Drizzle ORM latest
Database	PostgreSQL, MySQL, SQLite
Type Safety	Full TypeScript inference
Migrations	Drizzle Kit

---

Project Structure

src/
├── db/
│   ├── index.ts           # Database client
│   ├── schema/
│   │   ├── index.ts       # Export all schemas
│   │   ├── users.ts
│   │   └── posts.ts
│   └── migrations/        # Generated migrations
├── drizzle.config.ts
└── ...

---

Schema Definition

Basic Schema

// db/schema/users.ts
import { pgTable, text, timestamp, boolean, pgEnum } from 'drizzle-orm/pg-core'
import { createId } from '@paralleldrive/cuid2'

export const roleEnum = pgEnum('role', ['user', 'admin'])

export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => createId()),
  email: text('email').notNull().unique(),
  name: text('name'),
  passwordHash: text('password_hash').notNull(),
  role: roleEnum('role').default('user').notNull(),
  emailVerified: boolean('email_verified').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
})

export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

// db/schema/posts.ts
import { pgTable, text, timestamp, boolean, index } from 'drizzle-orm/pg-core'
import { createId } from '@paralleldrive/cuid2'
import { relations } from 'drizzle-orm'
import { users } from './users'

export const posts = pgTable('posts', {
  id: text('id').primaryKey().$defaultFn(() => createId()),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').default(false).notNull(),
  authorId: text('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  authorIdx: index('posts_author_idx').on(table.authorId),
}))

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}))

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

export type Post = typeof posts.$inferSelect
export type NewPost = typeof posts.$inferInsert