{"id":"3de06a76-851b-409d-80f9-21f17c0e09a8","shortId":"6bq5yT","kind":"skill","title":"typescript-drizzle-orm","tagline":"Type-safe SQL with Drizzle ORM in TypeScript. Use when defining database schemas, writing queries, setting up relations, running migrations, or working with PostgreSQL/MySQL/SQLite/Cloudflare D1/Durable Objects data layers.","description":"# Drizzle ORM\n\nLightweight, type-safe ORM with SQL-like and relational query APIs for PostgreSQL, MySQL, SQLite, Cloudflare D1, and Durable Objects.\n\n## Quick Start (PostgreSQL)\n\n```typescript\nimport {\n  pgTable,\n  serial,\n  text,\n  integer,\n  timestamp,\n  boolean,\n  varchar,\n  uuid,\n  primaryKey,\n  unique,\n  index\n} from 'drizzle-orm/pg-core'\n\nexport const users = pgTable('users', {\n  id: serial('id').primaryKey(),\n  name: text('name').notNull(),\n  email: varchar('email', { length: 255 }).notNull().unique(),\n  age: integer('age'),\n  isActive: boolean('is_active').default(true),\n  createdAt: timestamp('created_at').defaultNow().notNull(),\n  updatedAt: timestamp('updated_at').$onUpdate(() => new Date()),\n})\n\nexport const posts = pgTable('posts', {\n  id: serial('id').primaryKey(),\n  title: text('title').notNull(),\n  content: text('content'),\n  authorId: integer('author_id')\n    .notNull()\n    .references(() => users.id, { onDelete: 'cascade' }),\n  createdAt: timestamp('created_at').defaultNow().notNull(),\n})\n```\n\nSee [references/postgresql.md](./references/postgresql.md) for detailed PostgreSQL patterns.\n\n## Quick Start (SQLite/D1)\n\n```typescript\nimport { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'\n\nexport const users = sqliteTable('users', {\n  id: text('id').primaryKey(),\n  name: text('name').notNull(),\n  email: text('email').notNull(),\n  isActive: integer('is_active', { mode: 'boolean' }).default(true),\n  createdAt: text('created_at').notNull(),\n})\n\nexport const posts = sqliteTable('posts', {\n  id: text('id').primaryKey(),\n  title: text('title').notNull(),\n  content: text('content'),\n  authorId: text('author_id')\n    .notNull()\n    .references(() => users.id, { onDelete: 'cascade' }),\n  createdAt: text('created_at').notNull(),\n})\n```\n\nSee [references/sqlite.md](./references/sqlite.md) for SQLite patterns and [references/cloudflare.md](./references/cloudflare.md) for D1 and Durable Objects.\n\n## Type Inference\n\n```typescript\n// Infer types from schema - no manual interfaces needed\nexport type User = typeof users.$inferSelect\nexport type NewUser = typeof users.$inferInsert\n\nexport type Post = typeof posts.$inferSelect\nexport type NewPost = typeof posts.$inferInsert\n```\n\n## Relations\n\n```typescript\nimport { relations } from 'drizzle-orm'\n\nexport const usersRelations = relations(users, ({ many }) => ({\n  posts: many(posts),\n}))\n\nexport const postsRelations = relations(posts, ({ one }) => ({\n  author: one(users, {\n    fields: [posts.authorId],\n    references: [users.id],\n  }),\n}))\n```\n\n## SQL-like Queries\n\n```typescript\nimport { eq, and, or, gt, like, isNull, desc, asc } from 'drizzle-orm'\n\n// Select all\nconst allUsers = await db.select().from(users)\n\n// Select specific columns\nconst names = await db.select({ name: users.name }).from(users)\n\n// Where clause\nconst activeUsers = await db\n  .select()\n  .from(users)\n  .where(eq(users.isActive, true))\n\n// Multiple conditions\nconst filtered = await db\n  .select()\n  .from(users)\n  .where(and(\n    eq(users.isActive, true),\n    gt(users.age, 18)\n  ))\n\n// Like/pattern matching\nconst matching = await db\n  .select()\n  .from(users)\n  .where(like(users.email, '%@example.com'))\n\n// Order and limit\nconst recent = await db\n  .select()\n  .from(posts)\n  .orderBy(desc(posts.createdAt))\n  .limit(10)\n\n// Joins\nconst postsWithAuthors = await db\n  .select({\n    postTitle: posts.title,\n    authorName: users.name,\n  })\n  .from(posts)\n  .leftJoin(users, eq(posts.authorId, users.id))\n```\n\n## Relational Queries\n\n```typescript\n// Requires schema with relations passed to drizzle()\nconst db = drizzle(pool, { schema })\n\n// Find many with relations\nconst usersWithPosts = await db.query.users.findMany({\n  with: {\n    posts: true,\n  },\n})\n\n// Partial columns + nested relations\nconst partial = await db.query.users.findMany({\n  columns: {\n    id: true,\n    name: true,\n  },\n  with: {\n    posts: {\n      columns: {\n        title: true,\n        createdAt: true,\n      },\n    },\n  },\n})\n\n// Find first\nconst user = await db.query.users.findFirst({\n  where: eq(users.id, 1),\n  with: { posts: true },\n})\n\n// Exclude columns\nconst withoutEmail = await db.query.users.findMany({\n  columns: {\n    email: false, // exclude\n  },\n})\n```\n\n## Insert\n\n```typescript\n// Single insert\nconst [newUser] = await db\n  .insert(users)\n  .values({ name: 'Alice', email: 'alice@example.com' })\n  .returning()\n\n// Multiple insert\nawait db.insert(users).values([\n  { name: 'Bob', email: 'bob@example.com' },\n  { name: 'Carol', email: 'carol@example.com' },\n])\n\n// Upsert (on conflict)\nawait db\n  .insert(users)\n  .values({ name: 'Alice', email: 'alice@example.com' })\n  .onConflictDoUpdate({\n    target: users.email,\n    set: { name: 'Alice Updated' },\n  })\n```\n\n## Update\n\n```typescript\nawait db\n  .update(users)\n  .set({ isActive: false })\n  .where(eq(users.id, 1))\n\n// Update with returning\nconst [updated] = await db\n  .update(users)\n  .set({ name: 'New Name' })\n  .where(eq(users.id, 1))\n  .returning()\n```\n\n## Delete\n\n```typescript\nawait db.delete(users).where(eq(users.id, 1))\n\n// Delete with returning\nconst [deleted] = await db\n  .delete(users)\n  .where(eq(users.id, 1))\n  .returning()\n```\n\n## Transactions\n\n```typescript\nawait db.transaction(async (tx) => {\n  const [user] = await tx\n    .insert(users)\n    .values({ name: 'Alice', email: 'alice@example.com' })\n    .returning()\n\n  await tx.insert(posts).values({\n    title: 'First Post',\n    authorId: user.id,\n  })\n})\n```\n\n## Entity Pattern\n\nDomain entities encapsulate data transformations between API, domain, and database layers.\n\n```typescript\nimport type { InferInsertModel, InferSelectModel } from 'drizzle-orm'\nimport type { users } from './schema'\n\ntype UserRecord = InferSelectModel<typeof users>\ntype UserInsert = InferInsertModel<typeof users>\n\nclass UserEntity {\n  public readonly id: string\n  public readonly name: string\n  public readonly email: string\n  public readonly createdAt: Date\n\n  private constructor(data: UserEntityData) {\n    Object.assign(this, data)\n  }\n\n  // API request → Entity\n  static fromRequest(rq: CreateUserRequest, id?: string): UserEntity {\n    return new UserEntity({\n      id: id ?? crypto.randomUUID(),\n      name: rq.name,\n      email: rq.email,\n      createdAt: new Date(),\n    })\n  }\n\n  // DB record → Entity\n  static fromRecord(record: UserRecord): UserEntity {\n    return new UserEntity({\n      id: record.id,\n      name: record.name,\n      email: record.email,\n      createdAt: record.createdAt,\n    })\n  }\n\n  // Entity → DB insert\n  toRecord(): UserInsert {\n    return {\n      id: this.id,\n      name: this.name,\n      email: this.email,\n      createdAt: this.createdAt,\n    }\n  }\n\n  // Entity → API response\n  toResponse(): UserResponse {\n    return {\n      id: this.id,\n      name: this.name,\n      email: this.email,\n      createdAt: this.createdAt.toISOString(),\n    }\n  }\n}\n```\n\nSee [references/entity-pattern.md](./references/entity-pattern.md) for detailed examples.\n\n## Repository Pattern\n\nRepositories wrap database access with error handling and business logic.\n\n```typescript\nimport { eq, and } from 'drizzle-orm'\nimport { users } from './schema'\nimport { UserEntity } from './entities/UserEntity'\n\nclass UserRepo {\n  constructor(private db: DrizzleDB) {}\n\n  async getById(id: string): Promise<UserEntity> {\n    const record = await this.db.query.users.findFirst({\n      where: eq(users.id, id),\n    })\n    if (!record) throw new NotFoundError('User not found')\n    return UserEntity.fromRecord(record)\n  }\n\n  async create(entity: UserEntity): Promise<UserEntity> {\n    try {\n      const [record] = await this.db\n        .insert(users)\n        .values(entity.toRecord())\n        .returning()\n      return UserEntity.fromRecord(record)\n    } catch (error) {\n      throw handleDBError(error, { userId: entity.id })\n    }\n  }\n\n  async update(entity: UserEntity): Promise<UserEntity> {\n    const [record] = await this.db\n      .update(users)\n      .set(entity.toRecord())\n      .where(eq(users.id, entity.id))\n      .returning()\n    if (!record) throw new NotFoundError('User not found')\n    return UserEntity.fromRecord(record)\n  }\n}\n```\n\nSee [references/repository-pattern.md](./references/repository-pattern.md) for detailed examples.\n\n## Database-Specific Guides\n\nFor database-specific patterns, connection setup, migrations, and testing:\n\n- **[PostgreSQL patterns](./references/postgresql.md)** - Connection, migrations, column types, error codes, optimistic locking\n- **[SQLite patterns](./references/sqlite.md)** - Schema definition, type differences, better-sqlite3 testing\n- **[Cloudflare D1 & Durable Objects](./references/cloudflare.md)** - D1 connection, DO SQLite, testing with vitest-pool-workers, D1 vs DO decision guide\n\n## Guidelines\n\n1. Define schema in dedicated `schema.ts` file(s)\n2. Use `$inferSelect` and `$inferInsert` for types - don't duplicate\n3. Always define relations for nested queries with `db.query`\n4. Pass `{ schema }` to `drizzle()` to enable relational queries\n5. Use SQL-like API (`db.select()`) for complex joins\n6. Use relational API (`db.query`) for nested data fetching\n7. Foreign keys need explicit `references(() => table.column)`\n8. Use `returning()` to get inserted/updated/deleted rows\n9. Wrap database access in Repository classes for error handling\n10. Use Entity classes for all data transformations (fromRequest, fromRecord, toRecord, toResponse)\n11. Add `lockVersion` column for optimistic locking on mutable resources\n12. Handle DB errors with specific error types (23505=conflict, 23503=not found, 40001/OC000=retry)","tags":["typescript","drizzle","orm","atelier","martinffx","agent-skills","agentic-coding","anthropic","claude-code","claude-skills","code-review","codex"],"capabilities":["skill","source-martinffx","skill-typescript-drizzle-orm","topic-agent-skills","topic-agentic-coding","topic-anthropic","topic-claude-code","topic-claude-skills","topic-code-review","topic-codex","topic-codex-skill","topic-opencode","topic-prompt-engineering","topic-sdd","topic-spec-driven-development"],"categories":["atelier"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/martinffx/atelier/typescript-drizzle-orm","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add martinffx/atelier","source_repo":"https://github.com/martinffx/atelier","install_from":"skills.sh"}},"qualityScore":"0.461","qualityRationale":"deterministic score 0.46 from registry signals: · indexed on github topic:agent-skills · 23 github stars · SKILL.md body (9,555 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T19:05:24.917Z","embedding":null,"createdAt":"2026-05-10T07:03:13.421Z","updatedAt":"2026-05-18T19:05:24.917Z","lastSeenAt":"2026-05-18T19:05:24.917Z","tsv":"'/entities/userentity':783 '/pg-core':78 '/references/cloudflare.md':240,914 '/references/entity-pattern.md':752 '/references/postgresql.md':154,890 '/references/repository-pattern.md':870 '/references/sqlite.md':234,901 '/schema':648,779 '/sqlite-core':171 '1':478,553,570,580,593,931 '10':405,1010 '11':1022 '12':1032 '18':377 '2':939 '23503':1042 '23505':1040 '255':96 '3':949 '4':958 '40001/oc000':1045 '5':967 '6':977 '7':986 '8':993 '9':1000 'access':761,1003 'activ':105,192 'activeus':351 'add':1023 'age':99,101 'alic':504,531,539,609 'alice@example.com':506,533,611 'allus':332 'alway':950 'api':48,630,680,737,972,980 'asc':324 'async':599,790,814,839 'author':139,220,304 'authorid':137,218,620 'authornam':414 'await':333,342,352,365,382,396,409,444,455,473,486,498,510,525,543,559,574,586,597,603,613,797,822,846 'better':907 'better-sqlite3':906 'bob':515 'bob@example.com':517 'boolean':68,103,194 'busi':766 'carol':519 'carol@example.com':521 'cascad':145,226 'catch':832 'class':655,784,1006,1013 'claus':349 'cloudflar':53,910 'code':896 'column':339,450,457,464,483,488,893,1025 'complex':975 'condit':362 'conflict':524,1041 'connect':883,891,916 'const':80,122,173,203,290,299,331,340,350,363,380,394,407,433,442,453,471,484,496,557,584,601,795,820,844 'constructor':674,786 'content':134,136,215,217 'creat':110,148,199,229,815 'createdat':108,146,197,227,467,671,700,720,734,748 'createuserrequest':686 'crypto.randomuuid':695 'd1':54,242,911,915,925 'd1/durable':30 'data':32,627,675,679,984,1016 'databas':17,633,760,875,880,1002 'database-specif':874,879 'date':120,672,702 'db':353,366,383,397,410,434,499,526,544,560,587,703,723,788,1034 'db.delete':575 'db.insert':511 'db.query':957,981 'db.query.users.findfirst':474 'db.query.users.findmany':445,456,487 'db.select':334,343,973 'db.transaction':598 'decis':928 'dedic':935 'default':106,195 'defaultnow':112,150 'defin':16,932,951 'definit':903 'delet':572,581,585,588 'desc':323,402 'detail':156,754,872 'differ':905 'domain':624,631 'drizzl':3,10,34,76,169,287,327,432,435,642,774,962 'drizzle-orm':75,168,286,326,641,773 'drizzledb':789 'duplic':948 'durabl':56,244,912 'email':92,94,185,187,489,505,516,520,532,610,667,698,718,732,746 'enabl':964 'encapsul':626 'entiti':622,625,682,705,722,736,816,841,1012 'entity.id':838,855 'entity.torecord':827,851 'eq':317,358,372,420,476,551,568,578,591,770,800,853 'error':763,833,836,895,1008,1035,1038 'exampl':755,873 'example.com':390 'exclud':482,491 'explicit':990 'export':79,121,172,202,257,263,269,275,289,298 'fals':490,549 'fetch':985 'field':307 'file':937 'filter':364 'find':438,469 'first':470,618 'foreign':987 'found':810,864,1044 'fromrecord':707,1019 'fromrequest':684,1018 'get':997 'getbyid':791 'gt':320,375 'guid':877,929 'guidelin':930 'handl':764,1009,1033 'handledberror':835 'id':84,86,126,128,140,177,179,207,209,221,458,659,687,693,694,714,728,742,792,802 'import':62,163,283,316,636,644,769,776,780 'index':73 'infer':247,249 'inferinsert':268,280,943 'inferinsertmodel':638,654 'inferselect':262,274,941 'inferselectmodel':639,651 'insert':492,495,500,509,527,605,724,824 'inserted/updated/deleted':998 'integ':66,100,138,166,190 'interfac':255 'isact':102,189,548 'isnul':322 'join':406,976 'key':988 'layer':33,634 'leftjoin':418 'length':95 'lightweight':36 'like':44,313,321,388,971 'like/pattern':378 'limit':393,404 'lock':898,1028 'lockvers':1024 'logic':767 'mani':294,296,439 'manual':254 'match':379,381 'migrat':25,885,892 'mode':193 'multipl':361,508 'mutabl':1030 'mysql':51 'name':88,90,181,183,341,344,460,503,514,518,530,538,564,566,608,663,696,716,730,744 'need':256,989 'nest':451,954,983 'new':119,565,691,701,712,806,860 'newpost':277 'newus':265,497 'notfounderror':807,861 'notnul':91,97,113,133,141,151,184,188,201,214,222,231 'object':31,57,245,913 'object.assign':677 'onconflictdoupd':534 'ondelet':144,225 'one':303,305 'onupd':118 'optimist':897,1027 'order':391 'orderbi':401 'orm':4,11,35,40,77,170,288,328,643,775 'partial':449,454 'pass':430,959 'pattern':158,237,623,757,882,889,900 'pgtabl':63,82,124 'pool':436,923 'post':123,125,204,206,271,273,279,295,297,302,400,417,447,463,480,615,619 'postgresql':50,60,157,888 'postgresql/mysql/sqlite/cloudflare':29 'posts.authorid':308,421 'posts.createdat':403 'posts.title':413 'postsrel':300 'postswithauthor':408 'posttitl':412 'primarykey':71,87,129,180,210 'privat':673,787 'promis':794,818,843 'public':657,661,665,669 'queri':20,47,314,424,955,966 'quick':58,159 'readon':658,662,666,670 'recent':395 'record':704,708,796,804,813,821,831,845,858,867 'record.createdat':721 'record.email':719 'record.id':715 'record.name':717 'refer':142,223,309,991 'references/cloudflare.md':239 'references/entity-pattern.md':751 'references/postgresql.md':153 'references/repository-pattern.md':869 'references/sqlite.md':233 'relat':23,46,281,284,292,301,423,429,441,452,952,965,979 'repositori':756,758,1005 'request':681 'requir':426 'resourc':1031 'respons':738 'retri':1046 'return':507,556,571,583,594,612,690,711,727,741,811,828,829,856,865,995 'row':999 'rq':685 'rq.email':699 'rq.name':697 'run':24 'safe':7,39 'schema':18,252,427,437,902,933,960 'schema.ts':936 'see':152,232,750,868 'select':329,337,354,367,384,398,411 'serial':64,85,127 'set':21,537,547,563,850 'setup':884 'singl':494 'skill' 'skill-typescript-drizzle-orm' 'source-martinffx' 'specif':338,876,881,1037 'sql':8,43,312,970 'sql-like':42,311,969 'sqlite':52,236,899,918 'sqlite/d1':161 'sqlite3':908 'sqlitet':164,175,205 'start':59,160 'static':683,706 'string':660,664,668,688,793 'table.column':992 'target':535 'test':887,909,919 'text':65,89,131,135,165,178,182,186,198,208,212,216,219,228 'this.createdat':735 'this.createdat.toisostring':749 'this.db':823,847 'this.db.query.users.findfirst':798 'this.email':733,747 'this.id':729,743 'this.name':731,745 'throw':805,834,859 'timestamp':67,109,115,147 'titl':130,132,211,213,465,617 'topic-agent-skills' 'topic-agentic-coding' 'topic-anthropic' 'topic-claude-code' 'topic-claude-skills' 'topic-code-review' 'topic-codex' 'topic-codex-skill' 'topic-opencode' 'topic-prompt-engineering' 'topic-sdd' 'topic-spec-driven-development' 'torecord':725,1020 'torespons':739,1021 'transact':595 'transform':628,1017 'tri':819 'true':107,196,360,374,448,459,461,466,468,481 'tx':600,604 'tx.insert':614 'type':6,38,246,250,258,264,270,276,637,645,649,652,894,904,945,1039 'type-saf':5,37 'typeof':260,266,272,278 'typescript':2,13,61,162,248,282,315,425,493,542,573,596,635,768 'typescript-drizzle-orm':1 'uniqu':72,98 'updat':116,540,541,545,554,558,561,840,848 'updatedat':114 'upsert':522 'use':14,940,968,978,994,1011 'user':81,83,174,176,259,261,267,293,306,336,347,356,369,386,419,472,501,512,528,546,562,576,589,602,606,646,777,808,825,849,862 'user.id':621 'userent':656,689,692,710,713,781,817,842 'userentity.fromrecord':812,830,866 'userentitydata':676 'userid':837 'userinsert':653,726 'userrecord':650,709 'userrepo':785 'userrespons':740 'users.age':376 'users.email':389,536 'users.id':143,224,310,422,477,552,569,579,592,801,854 'users.isactive':359,373 'users.name':345,415 'usersrel':291 'userswithpost':443 'uuid':70 'valu':502,513,529,607,616,826 'varchar':69,93 'vitest':922 'vitest-pool-work':921 'vs':926 'withoutemail':485 'work':27 'worker':924 'wrap':759,1001 'write':19","prices":[{"id":"b73f7c2b-8041-4f03-87ce-37cd7905ecd4","listingId":"3de06a76-851b-409d-80f9-21f17c0e09a8","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"martinffx","category":"atelier","install_from":"skills.sh"},"createdAt":"2026-05-10T07:03:13.421Z"}],"sources":[{"listingId":"3de06a76-851b-409d-80f9-21f17c0e09a8","source":"github","sourceId":"martinffx/atelier/typescript-drizzle-orm","sourceUrl":"https://github.com/martinffx/atelier/tree/main/skills/typescript-drizzle-orm","isPrimary":false,"firstSeenAt":"2026-05-10T07:03:13.421Z","lastSeenAt":"2026-05-18T19:05:24.917Z"}],"details":{"listingId":"3de06a76-851b-409d-80f9-21f17c0e09a8","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"martinffx","slug":"typescript-drizzle-orm","github":{"repo":"martinffx/atelier","stars":23,"topics":["agent-skills","agentic-coding","anthropic","claude-code","claude-skills","code-review","codex","codex-skill","opencode","prompt-engineering","sdd","spec-driven-development"],"license":"mit","html_url":"https://github.com/martinffx/atelier","pushed_at":"2026-05-18T06:56:45Z","description":"An atelier for Opencode, Claude Code, and other coding agents: spec-driven workflows, deep thinking, and code quality.","skill_md_sha":"f4247b0ef98f4c179c9e6ce0c459b427b6351b73","skill_md_path":"skills/typescript-drizzle-orm/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/martinffx/atelier/tree/main/skills/typescript-drizzle-orm"},"layout":"multi","source":"github","category":"atelier","frontmatter":{"name":"typescript-drizzle-orm","description":"Type-safe SQL with Drizzle ORM in TypeScript. Use when defining database schemas, writing queries, setting up relations, running migrations, or working with PostgreSQL/MySQL/SQLite/Cloudflare D1/Durable Objects data layers."},"skills_sh_url":"https://skills.sh/martinffx/atelier/typescript-drizzle-orm"},"updatedAt":"2026-05-18T19:05:24.917Z"}}