drizzle-orm

name	drizzle-orm
description	Drizzle ORM best practices. Use when configuring databases, designing schemas, writing queries, or handling migrations with Drizzle ORM, particularly with Cloudflare D1 or SQLite. NOT for database setup without an explicit engine. Require engine specification (Postgres/MySQL/SQLite). Use for ORM-managed migrations.

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: '[e-mail]', 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.