Support specifying the type of a bind param / replacement

[ephys]

Issue Creation Checklist

• I have read the contribution guidelines

Feature Description

Is your feature request related to a problem? Please describe.

Right now, Sequelize automatically determines the type of bind params & replacements based on their JS type. But a JS type can be mapped to more than one SQL type.

It guesses these types in https://github.com/sequelize/sequelize/blob/main/src/sql-string.js

This leads to things like:

// "permissions" and ":permissions" are both SQL arrays.
sequelize.query('SELECT * FROM users WHERE permissions @> :permissions', {
  replacements: {
    permissions: ['read', 'write']
  },
});

generating

SELECT * FROM users WHERE permissions @> 'read', 'write';

instead of

SELECT * FROM users WHERE permissions @> ARRAY['read', 'write'];

Describe the solution you'd like

It should be possible to specify the DataType of a given replacement or bind parameter.

// "permissions" and ":permissions" are both SQL arrays.
sequelize.query('SELECT * FROM users WHERE permissions @> :permissions', {
  parameterTypes: {
    permissions: DataTypes.ARRAY(DataTypes.STRING),
  },
  replacements: {
    permissions: ['read', 'write']
  },
});

Why should this be in Sequelize

To continue making raw queries first class citizen.

Describe alternatives/workarounds you've considered

Use replacementTypes and bindTypes instead of parameterTypes.

Other ideas to solve this feature welcome.

Is this feature dialect-specific?

• No. This feature is relevant to Sequelize as a whole.

Would you be willing to implement this feature by submitting a Pull Request?

• Eventually, at some point.

[nilabja-bhattacharya]

The Oracle dialect does support bind in case of raw query. It needs to be done this way:

plsql = `BEGIN no_proc(:i, :io, :o); END;`,
await sequelize.query(plsql, {bind: {
        i:  'Chris',
        io: { val: 'Jones', dir: oracledb.BIND_INOUT },
        o:  { type: oracledb.NUMBER, dir: oracledb.BIND_OUT }
      },type: QueryTypes.RAW}

Replacements don't need a type and would be executed like a query with no binds after replacing the replacements with the value.

So if this feature request adds a key parameterType, then in the query.js the bind can be formed using the mentioned type and it needs to be passed to the node-oracledb layer.

[ephys]

That would be the idea :) Instead of providing the oracle-specific data type, we'd provide a Sequelize DataType. That DataType instance could expose a getBindDataType method, like you we in the oracle implem, which returns the dialect-specific data type.

I also prefer the solution you have in place for passing types.

We should also explore a standard way of specifying io & o bind parameters.

plsql = `BEGIN no_proc($i, $io, $o); END;`,
await sequelize.query(plsql, {
  bind: {
    // No DataType specified. Direction = input, Bind Data Type is guessed based on JS type
    i:  'Chris',
    // DataType specified. Direction = input, Bind Data Type is retrieved from Sequelize DataType
    ityped: { val: 'Chris', type: DataTypes.STRING },
    // Research needed for a standard way of specifying IO & O bind parameters that would be compatible with all dialects that support IO & O params.
    // Maybe just an enum that dialects can map to their own values in their Query class
    io: { val: 'Jones', dir: BindDir.IN_OUT },
    o:  { dir: BindDir.OUT, type: DataTypes.NUMBER }
  }, 
  type: QueryTypes.RAW
});

I also need to research how the type of a bind param or replacement could be specified when using this feature:

User.findAll({
  // the type of "name" is guessed based on the JS type, but the user should be able to specify it, maybe
  where: sql`first_name = ${name}`,
});

[nilabja-bhattacharya]

Yes, this sounds good.