TypeORM Query Builder Patterns (2026)

Last updated: April 17, 2026

Mastering TypeORM Query Builder: Advanced Patterns for Complex Database Operations

The TypeORM Query Builder is one of the most powerful tools in the TypeORM ecosystem, allowing developers to construct complex database queries programmatically. While basic CRUD operations are straightforward, mastering advanced Query Builder patterns will improve your application’s data access layer to enterprise-grade quality. This guide explores sophisticated techniques for building dynamic, efficient, and maintainable database queries.

Understanding the Query Builder Foundation

Before diving into advanced patterns, let’s establish a solid foundation. The Query Builder in TypeORM allows you to build SQL queries using a fluent, chainable API. Unlike traditional repositories that abstract away SQL complexity, the Query Builder gives you fine-grained control over every aspect of your queries.

const users = await dataSource
 .getRepository(User)
 .createQueryBuilder("user")
 .where("user.age > :age", { age: 18 })
 .getMany();

This basic pattern forms the backbone of all advanced techniques this guide covers.

Query Builder vs Repository Methods: When to Use Each

Before reaching for the Query Builder, it helps to know when the simpler find API is sufficient and when it isn’t. Using the wrong tool increases code complexity without benefit.

Scenario	Use find / findOne	Use Query Builder
Simple field equality filter	Yes	Overkill
Load a relation alongside the entity	Yes (relations)	Optional
Filter on a related entity’s field	No	Yes
Aggregate functions (SUM, COUNT, AVG)	No	Yes
GROUP BY with HAVING	No	Yes
Subqueries	No	Yes
Dynamic filter conditions at runtime	Sometimes	Yes
Raw SQL fragments	No	Yes
Multiple joins with different conditions	No	Yes
Pagination with total count in one query	No	Yes

A useful heuristic: if you can express the query without SQL concepts like JOINs, GROUP BY, or subqueries, start with find. Once you need any of those, move to the Query Builder.

Claude Code is effective at making this decision for you. Give it your use case and ask:

I need to find all users who have placed at least 3 orders in the last 30 days and whose total spend exceeds $500. Should I use the TypeORM find API or the Query Builder?

Subqueries: Powerful Nested Data Retrieval

Subqueries allow you to nest queries within queries, enabling complex data filtering and aggregation that would be difficult or impossible with simple JOINs. TypeORM’s Query Builder makes subqueries intuitive and type-safe.

Correlated Subqueries for Row-Specific Analysis

Correlated subqueries reference the outer query’s table, enabling row-by-row analysis. This pattern is invaluable for finding records that meet conditions relative to other records in the same dataset.

const usersWithLatestOrder = await dataSource
 .getRepository(User)
 .createQueryBuilder("user")
 .where((qb) => {
 const subQuery = qb
 .subQuery()
 .select("MAX(order.createdAt)")
 .from(Order, "order")
 .where("order.userId = user.id")
 .getQuery();
 return `user.lastOrderDate = ${subQuery}`;
 })
 .getMany();

This query finds all users whose last order matches the maximum order date for each user, a perfect example of correlated subquery power.

Subqueries in WHERE Clauses for Complex Filtering

You can also use subqueries directly in WHERE conditions to filter based on aggregated data:

const highValueCustomers = await dataSource
 .getRepository(User)
 .createQueryBuilder("user")
 .where("user.id IN " +
 dataSource
 .getRepository(Order)
 .createQueryBuilder("order")
 .select("order.userId")
 .groupBy("order.userId")
 .having("SUM(order.amount) > :threshold", { threshold: 10000 })
 .getQuery()
 )
 .getMany();

This finds all customers whose total order amount exceeds $10,000.

Subqueries in SELECT for Computed Columns

A lesser-known pattern is using subqueries inside the SELECT clause to compute per-row aggregate values without a GROUP BY on the outer query. This is useful when you need the full entity object alongside a computed field:

const usersWithOrderStats = await dataSource
 .getRepository(User)
 .createQueryBuilder("user")
 .addSelect((qb) => {
 return qb
 .subQuery()
 .select("COUNT(order.id)")
 .from(Order, "order")
 .where("order.userId = user.id")
 .andWhere("order.status = 'completed'")
 }, "user_completedOrderCount")
 .addSelect((qb) => {
 return qb
 .subQuery()
 .select("COALESCE(SUM(order.amount), 0)")
 .from(Order, "order")
 .where("order.userId = user.id")
 }, "user_totalSpend")
 .getRawAndEntities()

The getRawAndEntities() method returns both the mapped entity instances and the raw row data (including your computed columns) in a single round-trip:

const { entities, raw } = await query.getRawAndEntities()
const enriched = entities.map((user, i) => ({
 ...user,
 completedOrderCount: parseInt(raw[i].user_completedOrderCount, 10),
 totalSpend: parseFloat(raw[i].user_totalSpend)
}))

Ask Claude Code to generate this pattern for your specific entities:

I have User and Order entities. Write a TypeORM Query Builder query that returns all users enriched with their completed order count and total spend using SELECT subqueries.

Advanced Join Patterns

Joins are essential for combining data from multiple tables, but TypeORM offers advanced patterns beyond simple inner joins.

Left Join with Custom Conditions

Sometimes you need LEFT JOINs with specific conditions beyond the default relationship mapping:

const usersWithPendingOrders = await dataSource
 .getRepository(User)
 .createQueryBuilder("user")
 .leftJoinAndSelect("user.orders", "order", "order.status = :status", { status: "pending" })
 .loadRelationCountAndMap("user.pendingOrderCount", "user.orders", "order", (qb) =>
 qb.where("order.status = :status", { status: "pending" })
 )
 .getMany();

This retrieves all users with their pending orders (if any) and counts how many pending orders each user has.

Multiple Joins with Aliases

For complex reporting queries, you often need to join the same table multiple times with different conditions:

const userActivityReport = await dataSource
 .getRepository(User)
 .createQueryBuilder("user")
 .leftJoin("user.orders", "completedOrders", "completedOrders.status = 'completed'")
 .leftJoin("user.orders", "returnedOrders", "returnedOrders.status = 'returned'")
 .select([
 "user.id",
 "user.name",
 "COUNT(completedOrders.id) as completedCount",
 "COUNT(returnedOrders.id) as returnedCount"
 ])
 .groupBy("user.id")
 .addGroupBy("user.name")
 .getRawMany();

This pattern is particularly useful for analytics dashboards where you need to aggregate the same related entity under different conditions.

Three-Level Deep Joins

When your domain model has deeply nested relationships, you need to chain joins carefully to avoid N+1 queries:

// Fetch orders with their line items and each line item's product details
const ordersWithFullDetails = await dataSource
 .getRepository(Order)
 .createQueryBuilder("order")
 .innerJoinAndSelect("order.user", "user")
 .leftJoinAndSelect("order.lineItems", "item")
 .leftJoinAndSelect("item.product", "product")
 .leftJoinAndSelect("product.category", "category")
 .where("order.status = :status", { status: "completed" })
 .andWhere("order.createdAt > :since", { since: thirtyDaysAgo })
 .orderBy("order.createdAt", "DESC")
 .getMany()

This single query replaces what would otherwise be four separate queries (order -> user, order -> items, item -> product, product -> category). Claude Code can analyze your existing data-fetching code and spot N+1 patterns, then generate the equivalent deep join query.

Join Type Comparison

Understanding which join type to use is critical for both correctness and performance:

Join Type	TypeORM method	Returns rows when…	Use when…
INNER JOIN	innerJoin / innerJoinAndSelect	Both sides match	Relationship is required
LEFT JOIN	leftJoin / leftJoinAndSelect	Left side always; right side if match	Relationship is optional
RIGHT JOIN	rightJoin / rightJoinAndSelect	Right side always; left side if match	Rarely needed; invert to LEFT JOIN
Cross join	Raw SQL via addFrom	Every combination	Cartesian products, testing

Ask Claude:

My query returns fewer rows than expected. I have a User leftJoin to Orders but I'm getting no users without orders. Why and how do I fix it?

Dynamic Query Construction

Building queries conditionally based on runtime parameters is a common requirement. The Query Builder excels at this through its fluent API.

Conditional Where Clauses

Build WHERE clauses dynamically based on input parameters:

function buildUserSearchQuery(
 builder: SelectQueryBuilder<User>,
 filters: UserSearchFilters
): SelectQueryBuilder<User> {
 if (filters.minAge) {
 builder = builder.andWhere("user.age >= :minAge", { minAge: filters.minAge });
 }
 if (filters.maxAge) {
 builder = builder.andWhere("user.age <= :maxAge", { maxAge: filters.maxAge });
 }
 if (filters.name) {
 builder = builder.andWhere("LOWER(user.name) LIKE :name", {
 name: `%${filters.name.toLowerCase()}%`
 });
 }
 if (filters.status) {
 builder = builder.andWhere("user.status = :status", { status: filters.status });
 }
 return builder;
}

This pattern allows complete flexibility while maintaining query safety through parameterization.

Using Brackets for Complex OR/AND Logic

When you need to combine AND and OR conditions across multiple fields, use TypeORM’s Brackets helper to control precedence:

import { Brackets } from 'typeorm'
const results = await dataSource
 .getRepository(Product)
 .createQueryBuilder("product")
 .where("product.active = :active", { active: true })
 .andWhere(new Brackets((qb) => {
 qb.where("product.category = :cat1", { cat1: "electronics" })
 .orWhere("product.category = :cat2", { cat2: "computers" })
 }))
 .andWhere(new Brackets((qb) => {
 qb.where("product.price < :maxPrice", { maxPrice: 1000 })
 .orWhere("product.onSale = :onSale", { onSale: true })
 }))
 .getMany()

Without Brackets, chaining .orWhere at the top level can produce incorrect SQL like WHERE active = true AND category = 'electronics' OR category = 'computers', which evaluates differently than intended. The Brackets wrapper generates parentheses in the output SQL, enforcing the grouping you want.

Building a Reusable Filter Utility

For applications with many filterable list endpoints, build a generic filter application helper:

type FilterOperator = 'eq' | 'neq' | 'gt' | 'gte' | 'lt' | 'lte' | 'like' | 'in' | 'isNull'
interface QueryFilter {
 field: string
 operator: FilterOperator
 value?: unknown
}
function applyFilters<T>(
 qb: SelectQueryBuilder<T>,
 alias: string,
 filters: QueryFilter[]
): SelectQueryBuilder<T> {
 filters.forEach((filter, index) => {
 const paramKey = `param_${index}`
 const fieldRef = `${alias}.${filter.field}`
 switch (filter.operator) {
 case 'eq':
 qb.andWhere(`${fieldRef} = :${paramKey}`, { [paramKey]: filter.value })
 break
 case 'neq':
 qb.andWhere(`${fieldRef} != :${paramKey}`, { [paramKey]: filter.value })
 break
 case 'gt':
 qb.andWhere(`${fieldRef} > :${paramKey}`, { [paramKey]: filter.value })
 break
 case 'gte':
 qb.andWhere(`${fieldRef} >= :${paramKey}`, { [paramKey]: filter.value })
 break
 case 'lt':
 qb.andWhere(`${fieldRef} < :${paramKey}`, { [paramKey]: filter.value })
 break
 case 'lte':
 qb.andWhere(`${fieldRef} <= :${paramKey}`, { [paramKey]: filter.value })
 break
 case 'like':
 qb.andWhere(`LOWER(${fieldRef}) LIKE :${paramKey}`, {
 [paramKey]: `%${String(filter.value).toLowerCase()}%`
 })
 break
 case 'in':
 qb.andWhere(`${fieldRef} IN (:...${paramKey})`, { [paramKey]: filter.value })
 break
 case 'isNull':
 qb.andWhere(`${fieldRef} IS NULL`)
 break
 }
 })
 return qb
}

Usage:

const filters: QueryFilter[] = [
 { field: 'status', operator: 'in', value: ['active', 'trial'] },
 { field: 'createdAt', operator: 'gte', value: thirtyDaysAgo },
 { field: 'deletedAt', operator: 'isNull' }
]
const users = await applyFilters(
 dataSource.getRepository(User).createQueryBuilder("user"),
 "user",
 filters
).getMany()

Ask Claude Code to extend this utility with additional operators or to generate it from a TypeScript interface:

Generate a TypeORM dynamic filter utility that accepts filters derived from a REST API query string and safely applies them to a query builder without SQL injection risk.

Dynamic Ordering and Pagination

Implement flexible sorting and pagination:

function applySortingAndPagination<T>(
 builder: SelectQueryBuilder<T>,
 options: QueryOptions
): SelectQueryBuilder<T> {
 const validSortFields = ["createdAt", "updatedAt", "name", "id"];
 const sortField = validSortFields.includes(options.sortBy) ? options.sortBy : "createdAt";
 const sortOrder = options.sortOrder === "ASC" ? "ASC" : "DESC";
 return builder
 .orderBy(sortField, sortOrder)
 .skip((options.page - 1) * options.limit)
 .take(options.limit);
}

For efficient pagination in large tables, consider cursor-based pagination instead of OFFSET-based. OFFSET pagination forces the database to scan and discard rows before returning results, an O(n) operation as page number grows:

// Offset-based. degrades at high page numbers
const page5 = await qb
 .skip(400) // Database must scan 400 rows to discard them
 .take(20)
 .getMany()
// Cursor-based. consistent performance at any depth
const afterId = 1234 // ID of the last item on the previous page
const nextPage = await qb
 .where("user.id > :cursor", { cursor: afterId })
 .orderBy("user.id", "ASC")
 .take(20)
 .getMany()

Cursor-based pagination is not always applicable (it works best with stable, monotonic sort columns like auto-increment IDs or UUIDs sorted by creation time), but when it is, it provides consistent query performance regardless of how deep into the dataset you are.

Transactional Query Building

For operations requiring multiple queries to succeed or fail together, TypeORM’s Query Builder integrates smoothly with transactions:

await dataSource.transaction(async (manager) => {
 // Update user balance
 await manager
 .createQueryBuilder()
 .update(User)
 .set({ balance: () => "balance - :amount" })
 .where("id = :userId", { userId })
 .execute();
 // Record transaction
 await manager
 .createQueryBuilder()
 .insert()
 .into(Transaction)
 .values({
 userId,
 amount,
 type: "debit",
 createdAt: new Date()
 })
 .execute();
});

Pessimistic Locking in Transactions

When two concurrent requests might update the same row (for example, two processes decrementing inventory), use pessimistic locking to prevent race conditions:

await dataSource.transaction(async (manager) => {
 // Lock the product row for the duration of the transaction
 const product = await manager
 .getRepository(Product)
 .createQueryBuilder("product")
 .setLock("pessimistic_write")
 .where("product.id = :id", { id: productId })
 .getOne()
 if (!product || product.stock < quantity) {
 throw new Error("Insufficient stock")
 }
 await manager
 .createQueryBuilder()
 .update(Product)
 .set({ stock: () => `stock - ${quantity}` })
 .where("id = :id", { id: productId })
 .execute()
 await manager
 .createQueryBuilder()
 .insert()
 .into(OrderLineItem)
 .values({ orderId, productId, quantity, price: product.price })
 .execute()
})

TypeORM translates setLock("pessimistic_write") to SELECT ... FOR UPDATE in PostgreSQL and MySQL. The lock is held until the transaction commits or rolls back, preventing other transactions from modifying the same row concurrently.

Lock mode	SQL generated	Use when
pessimistic_read	SELECT … FOR SHARE	Multiple readers, no writers
pessimistic_write	SELECT … FOR UPDATE	Read-then-update pattern
optimistic	Uses version column	Low contention, high read

Bulk Insert and Upsert

For importing large datasets or syncing external records, the Query Builder’s bulk insert is far more efficient than individual save() calls:

// Insert 1,000 records in a single query
await dataSource
 .createQueryBuilder()
 .insert()
 .into(Product)
 .values(productsArray)
 .execute()
// Upsert. insert or update on conflict (PostgreSQL/MySQL 8+)
await dataSource
 .createQueryBuilder()
 .insert()
 .into(Product)
 .values(productsArray)
 .orUpdate(
 ["price", "stock", "updatedAt"], // columns to update on conflict
 ["sku"] // conflict target (unique column)
 )
 .execute()

This is significantly faster than looping through records and calling save() individually. A batch of 1,000 records that takes 3–4 seconds with individual saves typically completes in under 100 ms with bulk insert.

Performance Optimization Patterns

Eager Loading vs Lazy Loading

Choose your loading strategy based on your use case. Eager loading is perfect for known relationships, while lazy loading suits unpredictable access patterns:

// Eager loading - always loads relations
const users = await userRepo.find({
 relations: ["orders", "profile"],
 where: { active: true }
});
// Lazy loading via Query Builder - more control
const users = await userRepo
 .createQueryBuilder("user")
 .leftJoinAndSelect("user.orders", "orders")
 .where("user.active = :active", { active: true })
 .andWhere("orders.createdAt > :date", { date: thirtyDaysAgo })
 .getMany();

Selecting Only the Columns You Need

By default, getMany() fetches all columns. For wide tables or API endpoints that only need a subset of fields, use .select() to reduce data transfer:

const userSummaries = await dataSource
 .getRepository(User)
 .createQueryBuilder("user")
 .select(["user.id", "user.name", "user.email", "user.createdAt"])
 // Omits large columns like profile.bio, user.avatarData, etc.
 .where("user.active = :active", { active: true })
 .getMany()

For endpoints that return hundreds of rows in a list, avoiding large text or blob columns can halve the response payload size.

Query Result Caching

For frequently accessed, rarely changing data, use TypeORM’s built-in caching:

const cachedResults = await dataSource
 .getRepository(Product)
 .createQueryBuilder("product")
 .where("product.category = :category", { category: "electronics" })
 .setCacheable(true)
 .setCacheId("electronics-products-list")
 .getMany();

Enable query caching in your DataSource configuration:

const dataSource = new DataSource({
 type: "postgres",
 // ... connection options
 cache: {
 type: "redis",
 options: {
 host: "localhost",
 port: 6379
 },
 duration: 60000 // Cache entries expire after 60 seconds
 }
})

Without a Redis cache configured, TypeORM falls back to in-memory caching (useful for development, but not suitable for multi-instance production deployments).

Diagnosing Slow Queries with Claude Code

When a query runs slowly in production, provide Claude with the query and ask it to identify optimization opportunities:

This TypeORM Query Builder query takes 800ms on a table with 500,000 rows:
[paste your query here]
What indexes should I add, and are there any structural changes to the query itself that would improve performance?

Claude can identify missing indexes on JOIN columns, suggest covering indexes for common WHERE + SELECT combinations, and point out patterns like LOWER() on a non-expression-indexed column that prevent index usage.

Putting It Together: A Repository Pattern with Query Builder

For production applications, encapsulate your Query Builder logic inside repository methods rather than scattering it across service files:

@Injectable()
export class UserRepository extends Repository<User> {
 constructor(private dataSource: DataSource) {
 super(User, dataSource.createEntityManager())
 }
 async findActiveUsersWithRecentOrders(daysSince: number): Promise<User[]> {
 const since = new Date()
 since.setDate(since.getDate() - daysSince)
 return this.createQueryBuilder("user")
 .innerJoinAndSelect("user.orders", "order", "order.createdAt > :since", { since })
 .where("user.active = :active", { active: true })
 .orderBy("order.createdAt", "DESC")
 .getMany()
 }
 async findHighValueCustomers(minSpend: number): Promise<User[]> {
 return this.createQueryBuilder("user")
 .where("user.id IN " +
 this.dataSource
 .getRepository(Order)
 .createQueryBuilder("order")
 .select("order.userId")
 .groupBy("order.userId")
 .having("SUM(order.amount) > :minSpend", { minSpend })
 .getQuery()
 )
 .getMany()
 }
 async searchUsers(filters: UserSearchFilters, page: number, limit: number) {
 const qb = this.createQueryBuilder("user")
 if (filters.name) {
 qb.andWhere("LOWER(user.name) LIKE :name", {
 name: `%${filters.name.toLowerCase()}%`
 })
 }
 if (filters.status) {
 qb.andWhere("user.status = :status", { status: filters.status })
 }
 const [items, total] = await qb
 .skip((page - 1) * limit)
 .take(limit)
 .getManyAndCount()
 return { items, total, page, limit, pages: Math.ceil(total / limit) }
 }
}

The getManyAndCount() call at the end returns both the paginated results and the total matching row count in a single database round-trip, exactly what a paginated API endpoint needs.

Conclusion

The TypeORM Query Builder transforms database operations from rigid SQL strings into flexible, type-safe, and maintainable code. By mastering these advanced patterns, subqueries (including computed SELECT subqueries), complex multi-alias joins, dynamic query construction with Brackets for correct precedence, bulk insert/upsert, transactional pessimistic locking, and performance-conscious patterns like column selection and cursor-based pagination, you’ll build data access layers that are both powerful and elegant.

Claude Code accelerates the process by generating correct Query Builder syntax for complex use cases, auditing existing queries for N+1 problems, suggesting missing indexes, and explaining trade-offs between different join and loading strategies. Start implementing these patterns in your projects today, and experience the difference enterprise-grade database code makes in application reliability and maintainability.

---

---

I'm a solo developer in Vietnam. 50K Chrome extension users. $500K+ on Upwork. 5 Claude Max subscriptions running agent fleets in parallel. These are my actual CLAUDE.md templates, orchestration configs, and prompts. Not a course. Not theory. The files I copy into every project before I write a line of code. **[See what's inside →](https://zovo.one/lifetime?utm_source=ccg&utm_medium=cta-default&utm_campaign=claude-code-typeorm-query-builder-advanced-patterns-guide)** $99 once. Free forever. 47/500 founding spots left.

Related Reading

• Claude Code for Apache Drill Workflow Tutorial
• Claude Code for Astro Actions Workflow Tutorial
• Claude Code for Automated PR Checks Workflow Tutorial

Built by theluckystrike. More at zovo.one

Find the right skill → Browse 155+ skills in our Skill Finder.

See Also

Try it: Paste your error into our Error Diagnostic for an instant fix.

• Claude Code for Kysely — Workflow Guide