Sequelize 7 alpha 29: mapping of (?) placeholder has changed

[bot-nftmagicians]

Code that works on Sequelize 6 broke with Sequelize 7 (alpha 29). The database is Postgres (15 I believe).

I must admit I do not understand deeply how replacing questionmark placeholders work. But long time ago I got it to work via trial and error and it's quite problematic now that it doesn't any more, especially because I am forced to use the new version of Sequelize because only it has support for connecting to Google Cloud SQL.

This is the code that used to work (and still works with 6):

   const values = (?)
    const sql = `
        INSERT INTO mytable (created_at,num_block,num_found)
        VALUES ${values}
    `
    const data = // .... we prepare some data
    console.log('Data:', data)
    return db.query(sql, {
        replacements: data,
        type: db.QueryTypes.INSERT
    })
}

This is the console log of data (a timestamp and two strings):

 [ [ 2023-11-27T09:19:12.720Z, '12896912', '0' ] ]

This is the error with Sequelize 7:

Executing (default): INSERT INTO mytable
               (created_at,num_block,num_found)
        VALUES (ARRAY[
'2023-11-27 09:19:40.545 +00:00',
'1294-09-11 21:00:00.000 +00:00',
'1999-12-31 21:00:00.000 +00:00']::TIMESTAMP(3) WITH TIME ZONE[])

DatabaseError [SequelizeDatabaseError]: INSERT has more target columns than expressions

At the moment data is mapped onto the ? placeholder something is happening with types that didn't happen before.

Struggling to figure out what, any help appreciated.

[ephys]

Hi!
The serialization of values changed between v6 and v7, if you need an SQL list instead of an SQL array, you need to use this: https://sequelize.org/docs/v7/querying/raw-queries/#sqllist

I think you can do the following as well:

const query = `
  INSERT INTO mytable (created_at,num_block,num_found)
  VALUES ?
`;

const data = [
  sql.list([new Date(), '12896912', '0'])
];

return db.query(query, {
  replacements: data,
  type: db.QueryTypes.INSERT
})

[bot-nftmagicians]

Thanks @ephys for the quick reply!

Indeed it seems to work if I pass an array of lists and change VALUES (?) to VALUES ?.

I need to run more tests but this seems to be the solution, so I'm marking it as answered.

Thank you and have a great day 🙏