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

Question

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?

madflow · Accepted Answer · 2023-01-02 17:29:31Z

2

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.

edited Jan 2, 2023 at 17:29

answered Jan 2, 2023 at 17:13

madflow

8,6933 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related