Drizzle Raw SQL

Drizzle Raw SQL

Execute raw SQL safely in Drizzle with the sql template tag, db.execute(), and placeholder()

When to Use

• Running SQL that Drizzle's query builder cannot express (CTEs, window functions, recursive queries)
• Calling database-specific functions or operators
• Embedding raw SQL fragments within Drizzle queries
• Performing bulk operations with custom SQL

Instructions

1. Use the 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.

2. Type the result with a generic parameter:

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 }[]

3. Use 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);

4. Dynamic identifiers — use 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.

5. Use 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' });

6. Common Table Expressions (CTEs):

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
`);

7. Aggregate functions in select:

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));

8. Conditional SQL:

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);

Details

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:

• Raw SQL bypasses Drizzle's type inference — selected columns are typed by your generic assertion, not the schema
• db.execute() returns driver-specific result shapes — PostgreSQL returns { rows, rowCount }, MySQL returns different shapes
• Mixing raw SQL with the query builder works but requires careful column reference handling

Source

https://orm.drizzle.team/docs/sql

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.