From harness-claude
Compose type-safe SQL queries with Drizzle's fluent builder for select, insert, update, delete, joins, subqueries, and aggregations in TypeScript.
npx claudepluginhub intense-visions/harness-engineering --plugin harness-claudeThis skill uses the workspace's default tool permissions.
> Compose type-safe SQL with Drizzle's fluent query builder for select, insert, update, and delete
Provides expertise in Drizzle ORM for TypeScript: schema design, relational queries, Drizzle Kit migrations, and serverless integrations with Neon, Supabase, PlanetScale.
Defines type-safe database schemas, queries, relations, and migrations using Drizzle ORM in TypeScript for PostgreSQL, MySQL, SQLite, Cloudflare D1, and Durable Objects.
Guides Drizzle ORM type-safe schema design, relational queries, prepared statements, migrations, and transactions. Use for database schema, queries, migrations, and performance optimization in TypeScript.
Share bugs, ideas, or general feedback.
Compose type-safe SQL with Drizzle's fluent query builder for select, insert, update, and delete
import { eq, desc } from 'drizzle-orm';
const allUsers = await db.select().from(users);
const activeUsers = await db
.select()
.from(users)
.where(eq(users.isActive, true))
.orderBy(desc(users.createdAt))
.limit(20);
const names = await db
.select({
id: users.id,
name: users.name,
})
.from(users);
// Type: { id: string; name: string | null }[]
const result = await db
.select({
userName: users.name,
postTitle: posts.title,
})
.from(users)
.innerJoin(posts, eq(users.id, posts.authorId))
.where(eq(posts.published, true));
const result = await db
.select({
user: users,
latestPost: posts,
})
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId));
// Type: { user: User; latestPost: Post | null }[]
await db.insert(users).values({
email: 'alice@example.com',
name: 'Alice',
});
// Bulk insert
await db.insert(users).values([
{ email: 'bob@example.com', name: 'Bob' },
{ email: 'carol@example.com', name: 'Carol' },
]);
// Insert with returning
const [newUser] = await db
.insert(users)
.values({ email: 'dave@example.com', name: 'Dave' })
.returning();
onConflictDoUpdate:await db
.insert(users)
.values({ email: 'alice@example.com', name: 'Alice Updated' })
.onConflictDoUpdate({
target: users.email,
set: { name: 'Alice Updated' },
});
await db.update(users).set({ name: 'New Name', updatedAt: new Date() }).where(eq(users.id, userId));
// Update with returning
const [updated] = await db
.update(users)
.set({ isActive: false })
.where(eq(users.id, userId))
.returning();
await db.delete(posts).where(eq(posts.authorId, userId));
const [deleted] = await db.delete(users).where(eq(users.id, userId)).returning();
import { sql } from 'drizzle-orm';
const subquery = db
.select({
authorId: posts.authorId,
postCount: sql<number>`count(*)`.as('post_count'),
})
.from(posts)
.groupBy(posts.authorId)
.as('post_counts');
const result = await db
.select({
name: users.name,
postCount: subquery.postCount,
})
.from(users)
.leftJoin(subquery, eq(users.id, subquery.authorId));
import { count, avg, sum, max, min } from 'drizzle-orm';
const stats = await db
.select({
totalPosts: count(),
avgViews: avg(posts.viewCount),
})
.from(posts)
.where(eq(posts.published, true));
The Drizzle query builder mirrors SQL syntax closely. Every method returns a new query object (immutable), and the final result is obtained by awaiting the query.
Two query APIs: Drizzle offers the SQL-like builder (db.select().from()) and the relational query API (db.query.table.findMany()). Use the SQL-like builder for joins, aggregations, and complex queries. Use the relational API for simple CRUD with nested includes.
Type inference: The query builder infers result types from the selected columns. Selecting { name: users.name } returns { name: string | null }[]. Left joins correctly type nullable columns.
.returning() support: Available on PostgreSQL and SQLite. MySQL does not support RETURNING — use insertId from the result instead.
Prepared statements:
const prepared = db
.select()
.from(users)
.where(eq(users.id, sql.placeholder('id')))
.prepare();
const user = await prepared.execute({ id: userId });
Prepared statements are compiled once and reused, reducing parsing overhead for repeated queries.
Trade-offs:
.as() — forgetting this causes TypeScript errorshttps://orm.drizzle.team/docs/select