Query Builder

Repository.find() covers most cases. When you need GROUP BY, HAVING, manual JOINs, aggregations, or arbitrary SQL expressions, use QueryBuilder.

const queryBuilder = conn.getRepository(Post).createQueryBuilder();

---

Available Methods

Method	Return	Description
select(keys)	this	Columns to select
where(condition)	this	WHERE condition (overwrites previous calls)
andWhere(sql, params?)	this	Add WHERE condition with raw SQL
leftJoin(relation, alias)	this	LEFT JOIN by relation key
groupBy(columns)	this	GROUP BY
having(condition)	this	HAVING
orderBy(options)	this	ORDER BY
limit(n)	this	LIMIT
offset(n)	this	OFFSET
getMany()	Promise<T[]>	Executes and returns hydrated entities
getRaw()	Promise<Record[]>	Executes and returns simple objects
getCount()	Promise<number>	Runs COUNT(*)
build()	{ sql, params }	Generates SQL without executing
explain()	Promise<string>	Returns the EXPLAIN ANALYZE (PostgreSQL)

---

Basic Query

const posts = await conn.getRepository(Post)
  .createQueryBuilder()
  .where({ status: 'published' })
  .orderBy({ createdAt: 'DESC' })
  .limit(10)
  .getMany();

The keys in where and orderBy are entity properties — QueryBuilder automatically maps them to the column names in the database:

.where({ viewCount: MoreThan(100) })
// WHERE "view_count" > $1

---

getMany() vs getRaw()

• getMany() — returns instances of the entity, with type casting and all mappings applied
• getRaw() — returns simple JavaScript objects with the database column names, without hydration

// getMany — entidades tipadas
const posts = await queryBuilder.getMany();
// posts[0] instanceof Post === true
// posts[0].viewCount === 42  (number)

// getRaw — objetos simples
const rows = await queryBuilder.getRaw();
// rows[0] = { id: 1, title: 'Olá', view_count: '42' }
//                                              ^^^^ string crua do banco

Use getRaw() when you select calculated columns or aggregations that do not exist in the entity.

---

Aggregations and GROUP BY

For COUNT, SUM, AVG and similar, pass the SQL expressions directly into select() and use getRaw():

const stats = await conn.getRepository(Post)
  .createQueryBuilder()
  .select(['authorId', 'COUNT(*) AS total', 'SUM("view_count") AS views'])
  .groupBy('"author_id"')
  .having('COUNT(*) > 5')
  .orderBy({ 'COUNT(*)': 'DESC' })
  .getRaw();

// stats = [{ author_id: 1, total: '12', views: '3400' }, ...]

To just count records with a filter, use getCount():

const total = await conn.getRepository(Post)
  .createQueryBuilder()
  .where({ status: 'published' })
  .getCount();
// SELECT COUNT(*) FROM "posts" WHERE "status" = $1

---

Conditions WHERE

Operators

All find() operators work in QueryBuilder:

import { MoreThan, Like, In, IsNull } from 'mirror-orm';

await conn.getRepository(Post)
  .createQueryBuilder()
  .where({
    viewCount: MoreThan(100),
    title:     Like('%mirror%'),
    status:    In(['published', 'featured']),
    deletedAt: IsNull(),
  })
  .getMany();

OR logic — array of objects

await conn.getRepository(Post)
  .createQueryBuilder()
  .where([
    { status: 'published' },
    { status: 'featured', viewCount: MoreThan(1000) },
  ])
  .getMany();
// WHERE (status = $1) OR (status = $2 AND view_count > $3)

andWhere() — additional raw SQL

For conditions that operators do not cover, add arbitrary SQL. Parameters are automatically repositioned:

await conn.getRepository(Post)
  .createQueryBuilder()
  .where({ status: 'published' })
  .andWhere('"view_count" > (SELECT AVG("view_count") FROM "posts")')
  .getMany();
// WHERE "status" = $1
//   AND "view_count" > (SELECT AVG("view_count") FROM "posts")

With parameters:

await conn.getRepository(Post)
  .createQueryBuilder()
  .where({ authorId: 1 })
  .andWhere('"score" BETWEEN $1 AND $2', [50, 100])
  .getMany();
// WHERE "author_id" = $1 AND "score" BETWEEN $2 AND $3
//                                             ^^^ reposicionado automaticamente

Calling where() more than once overwrites the previous condition. To accumulate conditions, use andWhere() or pass everything into a single object.

---

LEFT JOIN

leftJoin() receives the relationship key on the entity (not the table name) and an alias:

const books = await conn.getRepository(Book)
  .createQueryBuilder()
  .leftJoin('author', 'author')
  .where({ 'author.name': Like('%Knuth%') })
  .getMany();
// LEFT JOIN "authors" "author" ON "books"."author_id" = "author"."id"
// WHERE "author"."name" LIKE $1

Use the alias with dot notation in where to filter by the joined table:

.where({ 'author.country': 'BR', status: 'published' })
// WHERE "author"."country" = $1 AND "status" = $2

Only leftJoin() is available. For INNER JOIN, use andWhere() with raw SQL.

---

Pagination

const page    = 2;
const perPage = 10;

const posts = await conn.getRepository(Post)
  .createQueryBuilder()
  .where({ status: 'published' })
  .orderBy({ createdAt: 'DESC' })
  .limit(perPage)
  .offset((page - 1) * perPage)
  .getMany();

---

Soft Delete

If the entity has @DeletedAt, QueryBuilder always adds WHERE deleted_at IS NULL automatically — including in getCount(). This filter cannot be disabled in QueryBuilder; to include deleted ones use repo.find({ withDeleted: true }).

---

Inspect SQL — build() and explain()

build() — generates SQL without executing

Useful for debugging, logging or testing:

const { sql, params } = conn.getRepository(Post)
  .createQueryBuilder()
  .select(['id', 'title'])
  .where({ status: 'published' })
  .orderBy({ createdAt: 'DESC' })
  .limit(5)
  .build();

console.log(sql);
// SELECT "id", "title" FROM "posts"
// WHERE "status" = $1 AND "deleted_at" IS NULL
// ORDER BY "created_at" DESC
// LIMIT 5

console.log(params);
// ['published']

explain() — execution plan (PostgreSQL)

const plan = await conn.getRepository(Post)
  .createQueryBuilder()
  .where({ authorId: 1 })
  .explain();

console.log(plan);
// Index Scan using posts_author_id_idx on posts
//   (cost=0.28..8.30 rows=1 width=32)
//   (actual time=0.021..0.022 rows=1 loops=1)

---

Transactions

QueryBuilder inherits the transaction context via AsyncLocalStorage automatically — the same mechanism as Repository:

await conn.transaction(async (transaction) => {
  const drafts = await transaction.getRepository(Post)
    .createQueryBuilder()
    .where({ status: 'draft', authorId: userId })
    .getMany();

  for (const draft of drafts) {
    draft.status = 'published';
    await transaction.getRepository(Post).save(draft);
  }
});

---

Behavior Reference| Situation | Behavior |

|---|---| | Call where() twice | Second call overwrites the first | | Unknown key in where() | Silently ignored | | Unknown key in select() | Passed as raw SQL (allows expressions) | | Unknown key in orderBy() | Passed as raw SQL (allows expressions) | | Entity with @DeletedAt | WHERE deleted_at IS NULL added automatically | | getCount() with soft delete | COUNT respects the soft delete filter | | Parameters in andWhere() | Automatically repositioned after those of where() |