Drizzle ORM is a modern TypeScript-based query builder and ORM that offers a clean, type-safe way to interact with databases. One common scenario when querying data is applying conditions dynamically—such as filtering published posts only when required. In this article, we’ll look at how to use a conditional WHERE clause in Drizzle ORM.

Dynamic Where Conditions in Drizzle

Consider a scenario where you need to fetch a post by its ID. However, in some cases, you may want to include only published posts. Instead of writing separate queries, you can dynamically adjust the WHERE condition like this:

const publishedOnly = true // or false
const whereCondition = publishedOnly
    ? and(eq(post.id, id), eq(post.status, "published"))
    : eq(post.id, id);

const rows = await db
    .select()
    .from(post)
    .where(whereCondition);

Explanation

1. Define the condition dynamically:

• If publishedOnly is true, the WHERE clause ensures that both post.id matches id AND post.status is published.
• If publishedOnly is false, it only checks post.id = id without filtering by status.

2. Execute the query:

• The db.select().from(post).where(whereCondition) statement runs the query using the dynamically created condition.

Conclusion

By using conditional logic in your WHERE clauses, you can keep your queries concise and flexible without duplicating code. Drizzle’s type safety ensures that your conditions remain predictable and maintainable. Whether you’re filtering published posts or implementing other conditional filters, this approach will keep your queries clean and efficient.