How to escape colon char in sequelize when using raw query with parameters

Question

I'm new to the world of sequelize. Let's say I need to run a query (on postgres) like this one:

select to_char(column1, 'HH:MM:SS') from table1 where param1 = :param

Using sequelize I have to setup the raw query like this:

db.sequelize.query(query, { replacements: { param: 'whatever' }, type: db.sequelize.QueryTypes.SELECT }).then((list) => { ... });

I'm having trouble because the colon character appears in the query string; sequelize seems to interpret those colons for replacements.

Is there any special syntax I have to use, in order for sequelize to parse correctly the string to_char(column1, 'HH:MM:SS')?

Answer 1

Hope I'm not too late but I experienced the same issue and what I did was to put the string format in the replacement. Using your example, it'll be like:

select to_char(column1, :format) from table1 where param1 = :param

And the Sequelize part:

db.sequelize.query(query, { replacements: { param: 'whatever', format: 'HH:MM:SS' }, type: db.sequelize.QueryTypes.SELECT }).then((list) => { ... });

I couldn't find the 'official' way to do it so this is like a tiny roundabout method.

Answer 2

Maybe it's late, but can be useful for others who find this question.

The best approach I found is using "Bind Parameter" instead of "Replacements". For details check "Bind Parameter" section in Sequelize -> Raw queries.

For your example:

const query = `select to_char(column1, 'HH:MM:SS') from table1 where param1 = $param`

db.sequelize.query(query, {
  bind: {
    param: 'whatever'
  },
  type: db.sequelize.QueryTypes.SELECT
}).then((list) => { ... })

The idea is to replace : by $ and use bind instead of replacements in query options.