> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/drizzle-team/drizzle-orm/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Expressions

> Build dynamic SQL queries with Drizzle's powerful sql template tag and expression builders

## 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:

```typescript theme={null}
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:

```typescript theme={null}
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:

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
// 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

```typescript theme={null}
// 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

```typescript theme={null}
// 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

```typescript theme={null}
// 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:

```typescript theme={null}
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:

```typescript theme={null}
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:

```typescript theme={null}
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:

```typescript theme={null}
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:

```typescript theme={null}
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

```typescript theme={null}
// 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

```typescript theme={null}
// 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

```typescript theme={null}
// 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:

```typescript theme={null}
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:

```typescript theme={null}
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:

```typescript theme={null}
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:

```typescript theme={null}
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

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

### Indexing

Ensure expressions can use indexes:

```typescript theme={null}
// ✓ 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:

```typescript theme={null}
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:

```typescript theme={null}
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:

```typescript theme={null}
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

<Steps>
  <Step title="Use sql for database-specific features">
    Don't reinvent the wheel - leverage your database's capabilities.
  </Step>

  <Step title="Always specify types">
    Use `sql<Type>` to maintain type safety in your application.
  </Step>

  <Step title="Prefer query builder when possible">
    Only use raw SQL when the query builder doesn't support your use case.
  </Step>

  <Step title="Test SQL expressions">
    Raw SQL isn't validated at compile time, so write tests.
  </Step>

  <Step title="Document complex expressions">
    Add comments explaining what your SQL expressions do.
  </Step>
</Steps>

## Common Pitfalls

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

```typescript theme={null}
// ✗ 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}`
);
```

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

## Real-World Example

Comprehensive example combining multiple concepts:

```typescript theme={null}
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`));
```