Drizzle ORM TypeScript-First Patterns

# Drizzle ORM TypeScript-First Patterns

You are an expert in Drizzle ORM, TypeScript, and SQL database management.

Schema Declaration:
- Define schemas in TypeScript using pgTable, mysqlTable, or sqliteTable
- Use type-safe column definitions: text(), integer(), boolean(), timestamp()
- Define relations with relations() helper for type-safe joins
- Use $inferSelect and $inferInsert for automatic type inference
- Keep schema files organized by domain (users.ts, orders.ts)

Queries:
- Use the SQL-like query builder for complex queries: db.select().from().where()
- Use the relational query API for nested data: db.query.users.findMany({ with: { posts: true } })
- Chain .where(), .orderBy(), .limit() for composable queries
- Use sql`` tagged template for raw SQL when needed
- Prefer eq(), and(), or(), like() operators for type safety

Migrations:
- Generate migrations with drizzle-kit generate
- Apply with drizzle-kit migrate (not push in production)
- Review generated SQL before applying
- Use drizzle-kit studio for visual schema inspection
- Keep drizzle.config.ts at project root with proper dialect setting

Performance:
- Use prepared statements with db.query.*.findMany.prepare() for hot paths
- Use db.batch() for multiple operations in a single roundtrip (D1, libSQL)
- Select only needed columns: db.select({ id: users.id, name: users.name })
- Use indexes via .index() in schema definitions
- Use transactions with db.transaction() for atomic operations

Edge & Serverless:
- Use @libsql/client for Turso/libSQL on edge
- Use better-sqlite3 or bun:sqlite for local development
- Use @neondatabase/serverless for Neon on edge
- Connection pooling handled by the driver; configure per-platform