How to 'replace' table name in raw SQL query?-CodePudding

I have the following SQL query, which works:

await sequelize.query(
    "DELETE FROM `table_name` WHERE (?) IN (?)",
    {
        replacements: ["project_id", projectIds],
        type: QueryTypes.DELETE,
    }
);

But I also want to use a replacement for table_name like this:

await sequelize.query(
    "DELETE FROM (?) WHERE (?) IN (?)",
    {
        replacements: ["table_name", "project_id", projectIds],
        type: QueryTypes.DELETE,
    }
);

But this doesn't work and generates an error about SQL syntax. How can I make this work?

CodePudding user response：

You are mixing data value binding and quoting identifiers.

There is ancient issue in the repo: https://github.com/sequelize/sequelize/issues/4494, which sounds like the problem above.

I believe you can create a workaround that respects different sql dialects like this:

const queryInterface = sequelize.getQueryInterface();

const tableName = queryInterface.quoteIdentifier("projects");
const columnName = queryInterface.quoteIdentifier("project_id");

await sequelize.query(`DELETE FROM ${tableName} WHERE ${columnName} IN (?)`, {
  replacements: [project_ids],
  type: QueryTypes.DELETE,
});

Assuming you are using sequelize 6.x.