Drizzle ORM查询

Schema Definition

• - Export every table from schema file — queries fail silently if table isn\'t exported
• Use $inferSelect for query return types, $inferInsert for insert input — they differ (select has defaults filled, insert has optionals)
• Define relations() in a separate call, not inline with table — Drizzle separates schema from relations

Query Syntax Traps

• - Conditions use functions, not objects: where: eq(users.id, 5) not where: { id: 5 } — Prisma syntax doesn\'t work
• Combine conditions with and() / or(): INLINECODE7
• INLINECODE8 for relational queries with with:, db.select().from(users) for SQL-like — mixing them causes type errors

Migrations

• - drizzle-kit push is dev-only (destructive) — production needs drizzle-kit generate then INLINECODE13
• Schema changes require regenerating migrations — editing generated SQL breaks the migration hash
• Set strict: true in drizzle.config.ts to catch schema drift before it hits production

Driver-Specific

• - PostgreSQL: use pgTable, imports from INLINECODE16
• MySQL: use mysqlTable, imports from INLINECODE18
• SQLite: use sqliteTable, imports from INLINECODE20
• Mixing imports across drivers compiles but fails at runtime with cryptic errors

Performance

• - Wrap multi-query operations in db.transaction(async (tx) => {}) — Drizzle doesn\'t auto-batch
• Use .prepare() for queries executed repeatedly — skips query building overhead
• Add .limit() to every findMany() / select() — no default limit means full table scans

Common Mistakes

• - Forgetting await on queries returns a Promise, not results — TypeScript doesn\'t catch this if you ignore the return
• INLINECODE27 is required to get inserted/updated rows back — without it you get { rowCount } only
• JSON columns: PostgreSQL uses jsonb(), MySQL uses json() — wrong function = wrong serialization