पाठ 17 / 35

Query Builders, Aggregates and Raw SQL

यह पाठ अभी हिंदी में अनुवादित नहीं है, इसलिए अंग्रेज़ी में दिखाया जा रहा है।

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

लेखक: · 15 साल से वेब डेवलपर

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.

// 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.

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.

त्वरित जाँच: 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 } })
  • 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.