SQL Expressions - Drizzle ORM

Overview

Drizzle provides the sql template tag for writing raw SQL expressions while maintaining type safety and proper parameter binding. This allows you to:

Extend the query builder: Add functionality not yet supported by Drizzle
Use database-specific features: Leverage PostgreSQL, MySQL, or SQLite functions
Build dynamic expressions: Construct complex WHERE clauses and calculations
Maintain type safety: Specify return types for SQL expressions

The SQL Template Tag

Basic Usage

Use the sql template tag for raw SQL:

import { sql } from 'drizzle-orm';
import { users } from './schema';

const result = await db.select().from(users).where(
  sql`${users.name} ILIKE ${'%john%'}`
);

Type Safety

Specify the return type of SQL expressions:

import { sql } from 'drizzle-orm';

const count = await db
  .select({
    total: sql<number>`count(*)`,
    avgAge: sql<number>`avg(${users.age})`,
    names: sql<string[]>`array_agg(${users.name})`,
  })
  .from(users);

// TypeScript knows the types:
console.log(count[0].total);    // number
console.log(count[0].avgAge);   // number
console.log(count[0].names);    // string[]

Parameter Binding

Drizzle automatically handles parameter binding:

const searchTerm = 'john';
const minAge = 18;

const result = await db.select().from(users).where(
  sql`${users.name} ILIKE ${`%${searchTerm}%`} AND ${users.age} >= ${minAge}`
);

// Generates parameterized query:
// WHERE name ILIKE $1 AND age >= $2
// Parameters: ['%john%', 18]

Common Patterns

String Functions

import { sql } from 'drizzle-orm';

// Case-insensitive search
const users = await db.select().from(users).where(
  sql`LOWER(${users.name}) = LOWER(${'John'})`
);

// Concatenation
const result = await db.select({
  fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
}).from(users);

// Pattern matching
const matches = await db.select().from(users).where(
  sql`${users.email} ~ ${'^[a-z]+@example\\.com$'}`
);

Date and Time Functions

// Current timestamp
await db.update(users)
  .set({ lastSeen: sql`now()` })
  .where(eq(users.id, 1));

// Date arithmetic
const recentUsers = await db.select().from(users).where(
  sql`${users.createdAt} > now() - interval '7 days'`
);

// Extract date parts
const usersByMonth = await db
  .select({
    month: sql<number>`EXTRACT(MONTH FROM ${users.createdAt})`,
    count: sql<number>`count(*)`,
  })
  .from(users)
  .groupBy(sql`EXTRACT(MONTH FROM ${users.createdAt})`);

Mathematical Operations

// Basic math
const products = await db.select({
  name: products.name,
  discountedPrice: sql<number>`${products.price} * 0.9`,
  taxAmount: sql<number>`${products.price} * 0.08`,
  total: sql<number>`${products.price} * 1.08`,
}).from(products);

// Rounding
const rounded = await db.select({
  rounded: sql<number>`ROUND(${products.price}, 2)`,
  ceiling: sql<number>`CEIL(${products.price})`,
  floor: sql<number>`FLOOR(${products.price})`,
}).from(products);

JSON Operations

// PostgreSQL JSONB operators
const result = await db.select().from(users).where(
  sql`${users.metadata}->>'theme' = ${'dark'}`
);

// Extract nested JSON
const themes = await db.select({
  userId: users.id,
  theme: sql<string>`${users.metadata}->'settings'->>'theme'`,
}).from(users);

// JSON array contains
const withTag = await db.select().from(posts).where(
  sql`${posts.tags} @> ${JSON.stringify(['typescript'])}`
);

Array Operations

// PostgreSQL array functions
const result = await db.select().from(users).where(
  sql`${'admin'} = ANY(${users.roles})`
);

// Array length
const withRoles = await db.select({
  name: users.name,
  roleCount: sql<number>`array_length(${users.roles}, 1)`,
}).from(users);

// Array aggregation
const tags = await db.select({
  allTags: sql<string[]>`array_agg(DISTINCT ${posts.tags})`,
}).from(posts);

Advanced Techniques

Window Functions

Perform complex analytical queries:

const rankedUsers = await db.select({
  name: users.name,
  score: users.score,
  rank: sql<number>`RANK() OVER (ORDER BY ${users.score} DESC)`,
  runningTotal: sql<number>`SUM(${users.score}) OVER (ORDER BY ${users.createdAt})`,
  avgInGroup: sql<number>`AVG(${users.score}) OVER (PARTITION BY ${users.departmentId})`,
}).from(users);

Common Table Expressions (CTEs)

Build complex queries with WITH clauses:

import { sql } from 'drizzle-orm';

const averageAge = db
  .$with('average_age')
  .as(
    db.select({
      avg: sql<number>`avg(${users.age})`.as('avg_age'),
    }).from(users)
  );

const aboveAverage = await db
  .with(averageAge)
  .select({
    name: users.name,
    age: users.age,
  })
  .from(users)
  .where(
    sql`${users.age} > (SELECT avg_age FROM ${averageAge})`
  );

Subqueries

Embed queries within queries:

const avgPostCount = db
  .select({
    avg: sql<number>`avg(post_count)`.as('avg'),
  })
  .from(
    db
      .select({
        postCount: sql<number>`count(*)`.as('post_count'),
      })
      .from(posts)
      .groupBy(posts.userId)
      .as('user_posts')
  );

const result = await avgPostCount;

CASE Expressions

Conditional logic in SQL:

const categorized = await db.select({
  name: users.name,
  ageGroup: sql<string>`
    CASE
      WHEN ${users.age} < 18 THEN 'minor'
      WHEN ${users.age} < 65 THEN 'adult'
      ELSE 'senior'
    END
  `,
  status: sql<string>`
    CASE ${users.verified}
      WHEN true THEN 'verified'
      ELSE 'unverified'
    END
  `,
}).from(users);

Dynamic Operators

Build conditional WHERE clauses:

function searchUsers(filters: {
  name?: string;
  minAge?: number;
  maxAge?: number;
}) {
  const conditions: SQL[] = [];

  if (filters.name) {
    conditions.push(sql`${users.name} ILIKE ${`%${filters.name}%`}`);
  }

  if (filters.minAge !== undefined) {
    conditions.push(sql`${users.age} >= ${filters.minAge}`);
  }

  if (filters.maxAge !== undefined) {
    conditions.push(sql`${users.age} <= ${filters.maxAge}`);
  }

  return db
    .select()
    .from(users)
    .where(conditions.length > 0 ? and(...conditions) : undefined);
}

Database-Specific Functions

PostgreSQL

// Full-text search
const results = await db.select().from(posts).where(
  sql`to_tsvector('english', ${posts.content}) @@ to_tsquery('english', ${'drizzle & orm'})`
);

// Generate series
const dates = await db.select({
  date: sql<Date>`generate_series::date`,
}).from(sql`generate_series(
  current_date - interval '30 days',
  current_date,
  '1 day'
)`);

// UUID generation
await db.insert(users).values({
  id: sql`gen_random_uuid()`,
  name: 'John',
});

MySQL

// String functions
const result = await db.select({
  name: users.name,
  initials: sql<string>`CONCAT(
    SUBSTRING(${users.firstName}, 1, 1),
    SUBSTRING(${users.lastName}, 1, 1)
  )`,
}).from(users);

// IF statement
const categorized = await db.select({
  name: users.name,
  status: sql<string>`IF(${users.age} >= 18, 'adult', 'minor')`,
}).from(users);

SQLite

// String manipulation
const upper = await db.select({
  name: sql<string>`upper(${users.name})`,
  length: sql<number>`length(${users.name})`,
}).from(users);

// Date functions
const formatted = await db.select({
  date: sql<string>`strftime('%Y-%m-%d', ${users.createdAt})`,
}).from(users);

Combining with Query Builder

WHERE Clauses

Mix SQL expressions with Drizzle operators:

import { eq, and } from 'drizzle-orm';

const result = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.verified, true),
      sql`${users.lastLogin} > now() - interval '30 days'`
    )
  );

ORDER BY

Custom sorting logic:

const sorted = await db
  .select()
  .from(users)
  .orderBy(
    sql`CASE
      WHEN ${users.role} = 'admin' THEN 1
      WHEN ${users.role} = 'moderator' THEN 2
      ELSE 3
    END`,
    users.name
  );

HAVING Clauses

Filter aggregated results:

const activeUsers = await db
  .select({
    userId: posts.userId,
    postCount: sql<number>`count(*)`,
  })
  .from(posts)
  .groupBy(posts.userId)
  .having(sql`count(*) > 10`);

Using SQL.raw

Raw SQL Execution

Execute completely custom SQL:

import { sql } from 'drizzle-orm';

// Execute DDL
await db.execute(sql`
  CREATE INDEX CONCURRENTLY idx_users_email 
  ON users(email)
`);

// Execute with parameters
await db.execute(sql`
  UPDATE users 
  SET last_login = NOW() 
  WHERE id = ${userId}
`);

// Raw queries
const result = await db.execute<{ count: number }>(sql`
  SELECT COUNT(*) as count 
  FROM users 
  WHERE created_at > ${startDate}
`);

Performance Considerations

SQL expressions are sent directly to the database, so their performance depends on your SQL syntax and database optimization.

Indexing

Ensure expressions can use indexes:

// ✓ GOOD: Can use index on email column
const users = await db.select().from(users).where(
  sql`${users.email} = ${'john@example.com'}`
);

// ✗ BAD: Cannot use index (function wraps column)
const users = await db.select().from(users).where(
  sql`LOWER(${users.email}) = ${'john@example.com'}`
);

// ✓ BETTER: Use expression index or store lowercase
// CREATE INDEX idx_users_email_lower ON users(LOWER(email));
const users = await db.select().from(users).where(
  sql`LOWER(${users.email}) = ${'john@example.com'}`
);

Query Planning

Use EXPLAIN to understand query performance:

const plan = await db.execute(sql`
  EXPLAIN ANALYZE
  SELECT * FROM users
  WHERE ${users.age} > 18
`);

console.log(plan);

Type Inference

Aliasing SQL Expressions

Create reusable typed expressions:

import { sql } from 'drizzle-orm';

const lowerEmail = sql<string>`LOWER(${users.email})`.as('lower_email');

const result = await db
  .select({
    id: users.id,
    lowerEmail,
  })
  .from(users);

// Type is inferred:
// { id: number; lowerEmail: string }[]

MapWith for Custom Decoding

Transform values returned from the database:

const usersWithParsedMeta = await db
  .select({
    id: users.id,
    metadata: sql<string>`${users.metadata}`
      .mapWith((value) => JSON.parse(value)),
  })
  .from(users);

// metadata is automatically parsed as object

Best Practices

Use sql for database-specific features

Don’t reinvent the wheel - leverage your database’s capabilities.

Always specify types

Use sql<Type> to maintain type safety in your application.

Prefer query builder when possible

Only use raw SQL when the query builder doesn’t support your use case.

Test SQL expressions

Raw SQL isn’t validated at compile time, so write tests.

Document complex expressions

Add comments explaining what your SQL expressions do.

Common Pitfalls

Be careful with SQL injection when building dynamic queries. Always use parameter binding, not string concatenation.

// ✗ UNSAFE: SQL injection vulnerability
const name = userInput;
const result = await db.select().from(users).where(
  sql.raw(`name = '${name}'`) // DON'T DO THIS!
);

// ✓ SAFE: Proper parameter binding
const result = await db.select().from(users).where(
  sql`${users.name} = ${userInput}`
);

When working with complex SQL expressions, test them directly in your database client first, then integrate them into Drizzle.

Real-World Example

Comprehensive example combining multiple concepts:

import { sql, and, desc } from 'drizzle-orm';
import { users, posts, comments } from './schema';

// Complex analytics query
const userEngagement = await db
  .select({
    userId: users.id,
    userName: users.name,
    totalPosts: sql<number>`COUNT(DISTINCT ${posts.id})`,
    totalComments: sql<number>`COUNT(DISTINCT ${comments.id})`,
    avgPostLength: sql<number>`AVG(LENGTH(${posts.content}))`,
    engagementScore: sql<number>`
      (COUNT(DISTINCT ${posts.id}) * 10) +
      (COUNT(DISTINCT ${comments.id}) * 5)
    `,
    lastActive: sql<Date>`
      GREATEST(
        MAX(${posts.createdAt}),
        MAX(${comments.createdAt})
      )
    `,
    isActive: sql<boolean>`
      GREATEST(
        MAX(${posts.createdAt}),
        MAX(${comments.createdAt})
      ) > NOW() - INTERVAL '30 days'
    `,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .leftJoin(comments, eq(users.id, comments.userId))
  .groupBy(users.id)
  .having(sql`COUNT(DISTINCT ${posts.id}) > 0`)
  .orderBy(desc(sql`engagement_score`));

​Overview

​The SQL Template Tag

​Basic Usage

​Type Safety

​Parameter Binding

​Common Patterns

​String Functions

​Date and Time Functions

​Mathematical Operations

​JSON Operations

​Array Operations

​Advanced Techniques

​Window Functions

​Common Table Expressions (CTEs)

​Subqueries

​CASE Expressions

​Dynamic Operators

​Database-Specific Functions

​PostgreSQL

​MySQL

​SQLite

​Combining with Query Builder

​WHERE Clauses

​ORDER BY

​HAVING Clauses

​Using SQL.raw

​Raw SQL Execution

​Performance Considerations

​Indexing

​Query Planning

​Type Inference

​Aliasing SQL Expressions

​MapWith for Custom Decoding

​Best Practices

​Common Pitfalls

​Real-World Example

Overview

The SQL Template Tag

Basic Usage

Type Safety

Parameter Binding

Common Patterns

String Functions

Date and Time Functions

Mathematical Operations

JSON Operations

Array Operations

Advanced Techniques

Window Functions

Common Table Expressions (CTEs)

Subqueries

CASE Expressions

Dynamic Operators

Database-Specific Functions

PostgreSQL

MySQL

SQLite

Combining with Query Builder

WHERE Clauses

ORDER BY

HAVING Clauses

Using SQL.raw

Raw SQL Execution

Performance Considerations

Indexing

Query Planning

Type Inference

Aliasing SQL Expressions

MapWith for Custom Decoding

Best Practices

Common Pitfalls

Real-World Example