What is the difference between bind and replacement? | NestJS | Page 1

winter acorn · 2025-01-21T20:19:39.305Z

I saw the description in the documentation but I didn't understand it very well, I wasn't convinced of the differences, they both apparently do the same thing, which is safer? All in the context of sequelize.

Hi,
The difference is slim, but it is there.

The replacement will let Sequelize replace the '?' or the ':key' with the given parameter before executing the query. This means that the query executed on the DB will already have the replaced value, like if you do this:

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

await sequelize.query('SELECT * FROM projects WHERE status = ?', {
  replacements: ['active'],
  type: QueryTypes.SELECT,
});

await sequelize.query('SELECT * FROM projects WHERE status = :status', {
  replacements: { status: 'active' },
  type: QueryTypes.SELECT,
});

The following query will be executed:

SELECT * FROM projects WHERE status = 'active'

But in the case of the Bind parameter, squelizer will send a parameterized query, so in the case of this code :

await sequelize.query(
  'SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $1',
  {
    bind: ['active'],
    type: QueryTypes.SELECT,
  },
);

This sql will be executed:

SELECT *, 'text with literal $1 and literal $status' as t FROM projects WHERE status = 'active';

But bind will link the parameters and send them outside of the query text.
It will serve the same purpose, but that bind is safer than replacement since the parameters are not directly inserted into the SQL query. This mitigates SQL injection attacks because Sequelize does not accept SQL keywords with the bind method.

Hope this help ! MavisCheerfulKomaCafe

#What is the difference between bind and replacement?