Drizzle ORM Guidelines

This skill provides opinionated standards for using Drizzle ORM, optimized for TypeScript and edge databases like Cloudflare D1.

1. Core Principles

• Single Source of Truth: Define your database schema in TypeScript. Let Drizzle generate the SQL migrations.
• Explicit Returns: Always explicitly select the columns you need using .select({ id: table.id }). Avoid select * in production queries to minimize payload size and improve performance.
• Relational Queries: Use Drizzle's Relational Query API (db.query) for complex joined data fetching, but use the Core API (db.select().from()) for mutations and simple lookups.
• Type Inference: Use typeof schema.table.$inferSelect and $inferInsert to generate application types directly from the schema.

2. Schema Definition

Always define schemas with precise types and constraints.

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date()),
})

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

3. Querying Patterns

Inserts and Returning

const result = await db
  .insert(users)
  .values({ email: '[email protected]', name: 'Test' })
  .returning() // Supported in D1

Relational Queries (Read-Heavy)

Ensure you define relations in your schema file:

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

Fetch deeply nested data easily:

const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: true,
  },
})

4. Migrations (Cloudflare D1)

Always generate migrations locally and apply them via Wrangler. Never execute db.execute() with raw migration SQL at runtime.

1. Generate: npx drizzle-kit generate
2. Apply Locally: npx wrangler d1 migrations apply my-db --local
3. Apply Remote: npx wrangler d1 migrations apply my-db --remote

5. Security & Safety

• SQL Injection: Drizzle is inherently safe against SQL injection as long as you use its builder functions (eq(), like(), insert()). Do not use sql\\`` with raw unescaped input.
• Transactions: Use db.transaction() for multi-step operations to ensure data integrity.
• Migration Commits: Always commit the generated .sql migration files to version control.