From harness-claude
Executes raw SQL safely in Drizzle ORM using sql template tags, db.execute(), and placeholders. Use for CTEs, window functions, recursive queries, database-specific functions, and embedding SQL fragments.
npx claudepluginhub intense-visions/harness-engineering --plugin harness-claudeThis skill uses the workspace's default tool permissions.
> Execute raw SQL safely in Drizzle with the sql template tag, db.execute(), and placeholder()
Execute type-safe raw SQL in Prisma using $queryRaw, $executeRaw, and Prisma.sql template tag. Use for CTEs, window functions, complex joins, bulk operations, data migrations, and database-specific features.
Provides expertise in Drizzle ORM for TypeScript: schema design, relational queries, Drizzle Kit migrations, and serverless integrations with Neon, Supabase, PlanetScale.
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.
Execute raw SQL safely in Drizzle with the sql template tag, db.execute(), and placeholder()
sql template tag for type-safe, parameterized SQL:import { sql } from 'drizzle-orm';
const result = await db.execute(sql`SELECT * FROM users WHERE email = ${email}`);
The ${email} is automatically parameterized — never string-interpolated.
const result = await db.execute<{ id: string; email: string }>(
sql`SELECT id, email FROM users WHERE role = ${role}`
);
// result.rows is typed as { id: string; email: string }[]
sql fragments within queries:const users = await db
.select({
id: users.id,
fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
postCount: sql<number>`(SELECT count(*) FROM posts WHERE posts.author_id = ${users.id})`,
})
.from(users);
sql.raw() for trusted table or column names:const column = validColumns.includes(sortBy) ? sortBy : 'created_at';
const direction = order === 'desc' ? sql.raw('DESC') : sql.raw('ASC');
const result = await db
.select()
.from(users)
.orderBy(sql`${sql.raw(column)} ${direction}`);
Always validate dynamic identifiers against an allowlist.
sql.placeholder() for prepared statements:const prepared = db
.select()
.from(users)
.where(eq(users.role, sql.placeholder('role')))
.prepare('users_by_role');
const admins = await prepared.execute({ role: 'admin' });
const mods = await prepared.execute({ role: 'moderator' });
const result = await db.execute(sql`
WITH active_users AS (
SELECT id, name FROM users WHERE is_active = true
)
SELECT au.name, count(p.id) as post_count
FROM active_users au
LEFT JOIN posts p ON p.author_id = au.id
GROUP BY au.name
ORDER BY post_count DESC
`);
import { sql } from 'drizzle-orm';
const stats = await db
.select({
totalViews: sql<number>`sum(${posts.viewCount})`,
avgViews: sql<number>`avg(${posts.viewCount})::int`,
maxViews: sql<number>`max(${posts.viewCount})`,
})
.from(posts)
.where(eq(posts.published, true));
const results = await db
.select({
id: users.id,
tier: sql<string>`CASE
WHEN ${users.points} > 1000 THEN 'gold'
WHEN ${users.points} > 500 THEN 'silver'
ELSE 'bronze'
END`,
})
.from(users);
The sql template tag is Drizzle's escape hatch for expressing SQL that the query builder cannot handle. It integrates seamlessly with Drizzle's type system and parameterization.
How parameterization works: When you write sql\WHERE email = ${email}`, Drizzle generates WHERE email = $1(PostgreSQL) orWHERE email = ?` (MySQL/SQLite) and passes the value as a parameter. This prevents SQL injection for all interpolated values.
sql.raw() bypasses parameterization. It inserts the string directly into the SQL. Use only for trusted, validated identifiers (table names, column names, SQL keywords). Never use it for user input.
Type assertions: sql<number>\count(*)`asserts the result type. Drizzle does not verify this at runtime — if the SQL returns a string but you assertnumber`, you get a runtime error. Validate with Zod for critical paths.
sql.empty: An empty SQL fragment useful for conditional query building:
const filter = userId ? sql`WHERE author_id = ${userId}` : sql.empty;
Trade-offs:
db.execute() returns driver-specific result shapes — PostgreSQL returns { rows, rowCount }, MySQL returns different shapeshttps://orm.drizzle.team/docs/sql