atelier-typescript-drizzle-orm_skill

Overview

This skill provides a TypeScript-first atelier for building type-safe SQL models and queries with Drizzle ORM. It guides schema definitions, relational queries, migrations, and patterns for PostgreSQL, MySQL, SQLite, Cloudflare D1 and Durable Objects. The focus is on spec-driven development, clear entity and repository patterns, and predictable, typed database access.

How this skill works

You define tables with db-core helpers (pgTable, sqliteTable, column types) and infer TypeScript types from schema ($inferSelect, $inferInsert). Relations are declared with relations(...) and passed into drizzle() so you can use a relational query API (db.query.*) for nested loading and db.select()/SQL-like API for joins. The skill demonstrates inserts, updates, deletes, transactions, upserts, and migration-aware patterns across supported adapters.

When to use it

• Designing and maintaining database schemas in TypeScript
• Implementing type-safe queries and joins for relational data
• Building repositories and entities that encapsulate DB logic
• Running migrations or adapting to PostgreSQL/MySQL/SQLite/D1/DO backends
• Writing tests that assert DB behavior with typed records

Best practices

• Keep schema declarations in dedicated schema files and infer types—avoid duplicating interfaces
• Define relations explicitly and pass schema to drizzle() to enable nested queries
• Wrap DB access in Repository classes to centralize error handling and business rules
• Use Entity classes to transform between API, domain, and DB shapes (fromRequest/fromRecord/toRecord/toResponse)
• Use returning() for insert/update/delete to get canonical DB records and avoid manual mapping
• Add lockVersion or similar optimistic locking for mutable resources and handle DB-specific error codes

Example use cases

• Create a users/posts schema with foreign keys and cascade deletes, then fetch posts with author data via relational queries
• Implement an upsert flow for unique-email users with onConflictDoUpdate and returning() to obtain the saved record
• Use transactions to atomically create a user and their initial posts, rolling back on failure
• Write repository methods that map DB errors to domain errors (conflict, not found, retryable)
• Port an app from SQLite to PostgreSQL by reusing typed schema and adapting minor column type differences