# Query Builders, Aggregates and Raw SQL — Prisma / TypeORM / Sequelize

Source: https://www.skillbyai.com/en/orms/or-query-builder-raw

> Use TypeORM's QueryBuilder, Prisma aggregates and parameterised raw SQL when the high-level API cannot express a query.

## Three levels of control

Every ORM gives you an escape ladder. The **high-level API** (`findMany`, `find`, `findAll`) covers most screens. **Aggregates and builders** sit in the middle: Prisma has `count`, `aggregate` and `groupBy`; TypeORM has the `QueryBuilder`, a fluent API (`createQueryBuilder('u').leftJoin(...).where(...).groupBy(...)`) that can express joins, subqueries and computed columns; Sequelize lets you use `sequelize.fn` and `sequelize.col` inside `attributes` and `group`. At the top is **raw SQL**: Prisma `$queryRaw` and `$executeRaw`, TypeORM `dataSource.query`, Sequelize `sequelize.query`. Raw SQL is not a failure; window functions, recursive CTEs and database-specific features are often clearer in SQL. The rule is that values must always travel as **parameters**, never by string concatenation. Raw results also lose much of the ORM's type safety, so validate or type them yourself.

## Posts per author, three ways

Prisma's tagged template turns `${minPosts}` into a bound parameter, not text pasted into the SQL.

```ts
// Prisma aggregate
const counts = await prisma.post.groupBy({ by: ['authorId'], _count: { _all: true } });

// TypeORM QueryBuilder with a named parameter
const rows = await dataSource
  .getRepository(User)
  .createQueryBuilder('u')
  .leftJoin('u.posts', 'p')
  .select('u.name', 'name')
  .addSelect('COUNT(p.id)', 'postCount')
  .groupBy('u.id')
  .having('COUNT(p.id) >= :minPosts', { minPosts: 2 })
  .getRawMany();

// Prisma raw SQL, safely parameterised
const minPosts = 2;
const prolific = await prisma.$queryRaw`
  SELECT "authorId", COUNT(*) AS n FROM "Post"
  GROUP BY "authorId" HAVING COUNT(*) >= ${minPosts}`;
```

## Sequelize raw query with replacements

Named replacements are escaped by Sequelize. `QueryTypes.SELECT` makes it return just the rows instead of a `[results, metadata]` pair.

```js
const { QueryTypes } = require('sequelize');

const rows = await sequelize.query(
  'SELECT id, name FROM "Users" WHERE email LIKE :domain',
  { replacements: { domain: '%@iitd.ac.in' }, type: QueryTypes.SELECT },
);
```

## Know which Prisma raw methods are unsafe

`$queryRaw` and `$executeRaw` used as tagged templates parameterise interpolated values. `$queryRawUnsafe` and `$executeRawUnsafe` take a plain string, so building that string with user input opens you to SQL injection. Treat the word *Unsafe* literally.

**Quiz:** Which call is vulnerable to SQL injection if name comes from a request?

- [ ] prisma.$queryRaw`SELECT * FROM users WHERE name = ${name}`
- [ ] repo.createQueryBuilder('u').where('u.name = :name', { name })
- [ ] sequelize.query('SELECT * FROM users WHERE name = :name', { replacements: { name } })
- [x] prisma.$queryRawUnsafe(`SELECT * FROM users WHERE name = '${name}'`)

*Answer:* prisma.$queryRawUnsafe(`SELECT * FROM users WHERE name = '${name}'`). The Unsafe variant receives a fully built string, so the quote in a crafted name ends the literal. The other three send name as a bound or escaped parameter.
