Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 37 ยท Querying

Query Builders and Raw SQL

Build complex queries with the TypeORM QueryBuilder, run Sequelize raw queries with replacements and bind parameters, and avoid SQL injection, with a runnable fluent builder stand-in.

Find options cover most queries. When you need joins on conditions, subqueries, grouping or database-specific features, you step down a level: a query builder (SQL as chained method calls) or raw SQL (the string itself). Both are normal tools, not failures of the ORM.

Library code is read-only; the stand-ins show how fluent builders and parameter binding work.

TypeORM QueryBuilder

TypeScript
const rows = await AppDataSource
  .getRepository(Product)
  .createQueryBuilder("p")
  .select(["p.id", "p.title", "p.priceCents"])
  .where("p.priceCents > :min", { min: 1000 })
  .andWhere("p.title ILIKE :q", { q: `%${search}%` })
  .orderBy("p.priceCents", "DESC")
  .addOrderBy("p.id", "ASC")
  .limit(20)
  .getMany();

Key points:

  • The string "p" is an alias for the table; refer to properties as p.priceCents (property names, not column names).
  • :min is a named parameter; the object supplies the value. Never interpolate values into the string.
  • Terminators: getMany() (entities), getOne(), getRawMany() (plain objects, for custom selects), getCount(), execute() (for UPDATE and DELETE builders).
  • Builders also exist for writes:
TypeScript
await AppDataSource.createQueryBuilder()
  .update(Product)
  .set({ priceCents: () => "priceCents + 100" })
  .where("id IN (:...ids)", { ids: [1, 2, 3] })     // :...ids expands an array
  .execute();

Sequelize raw queries

Sequelize has no full fluent builder. For anything complex you write SQL and bind parameters.

TypeScript
import { QueryTypes } from "sequelize";

const rows = await sequelize.query(
  "SELECT id, title FROM products WHERE price_cents > :min AND title ILIKE :q ORDER BY id LIMIT :limit",
  {
    replacements: { min: 1000, q: `%${search}%`, limit: 20 },
    type: QueryTypes.SELECT,
  }
);

// Bind parameters ($1, $2) are sent to the database separately
await sequelize.query("SELECT * FROM products WHERE id = $1", { bind: [42], type: QueryTypes.SELECT });

replacements are escaped by Sequelize and put into the SQL text; bind sends values separately (true prepared parameters). Both are safe when used as shown. Add model: Product, mapToModel: true to get model instances back.

For smaller escape hatches inside a normal finder, Sequelize provides fn, col and literal:

TypeScript
import { fn, col, literal } from "sequelize";

await Product.findAll({
  attributes: ["id", [fn("upper", col("title")), "shout"]],
  order: [[literal("price_cents % 100"), "ASC"]],     // literal is raw text: never put user input here
});

SQL injection: the one rule

Never concatenate user input into SQL text. Always pass it as a parameter (:name, $1, replacements, bind). Dynamic identifiers (column or table names from user input) cannot be parameters; check them against a hard-coded allow-list instead.

Node.js Injection vs binding

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

A fluent builder, from scratch

A query builder is a small object that collects parts and assembles SQL at the end. Reading this stand-in explains why .where() can be called in any order.

Node.js Mini QueryBuilder

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

When to use what

Need Use
Simple filters, sorting, paging find options / finders
Conditional filters built at runtime, joins with conditions, subqueries TypeORM QueryBuilder
Reports, window functions, database-specific features Raw SQL with bound parameters

Keep raw SQL in repository files, covered by tests, and typed at the boundary (map rows to an interface).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In the TypeORM QueryBuilder, what is :min in .where("p.price > :min", { min: 10 })?

    Named parameters keep values out of the SQL text, which prevents injection.
  2. 2. Which Sequelize options send parameters safely with raw SQL?

    replacements are escaped and bind values are sent as real prepared parameters.
  3. 3. How should you handle a sort column chosen by the user?

    Identifiers cannot be parameters, so only known names may reach the SQL.
  4. 4. What does getRawMany() return in TypeORM?

    getRawMany returns raw rows, useful for custom selects and aggregates.

Summary

  • Use find options for simple queries; reach for the QueryBuilder or raw SQL when needed.
  • TypeORM createQueryBuilder uses aliases and named parameters; Sequelize uses sequelize.query with replacements or bind.
  • Never concatenate user input into SQL; allow-list dynamic identifiers.
  • fn, col and literal are Sequelize's small escape hatches.
  • Keep raw SQL in repositories and map results to types.

Next: Aggregates and Grouping