Custom query on sequelize seeder

Question

Any one know how to custom select query on sequelize seeder

I have tried two ways, but no one work

First attempt

  up: function(queryInterface, Sequelize) {
    return queryInterface.sequelize.query(
      'SELECT * FROM "Users" WHERE username = "admin"',
      { type: queryInterface.sequelize.QueryTypes.SELECT }
    ).then(function(users) {});
    },

and then got error

SequelizeDatabaseError: column "admin" does not exist

I do not understand why admin is column here ???

Second attempt

return queryInterface.sequelize.query('SELECT * FROM "Users" WHERE username = :admin', {
  replacement: {
    admin: 'admin'
  },
  type: queryInterface.sequelize.QueryTypes.SELECT
}).then(function(users) {
});

Below error occurred

SequelizeDatabaseError: syntax error at or near ":"

Third attempt

return queryInterface.sequelize.query(
  'SELECT * FROM "Users" WHERE username = ' admin '',
  {type: queryInterface.sequelize.QueryTypes.SELECT})
.then(function(users) { })

Error:

SyntaxError: missing ) after argument list

UPDATED

Fourth attempt

    return queryInterface.sequelize.query(
      'SELECT * FROM Users WHERE username = "admin"',
      { type: queryInterface.sequelize.QueryTypes.SELECT }
    ).then(function(users) {});

Another error appear:

    SequelizeDatabaseError: relation "Users" does not exist

queryInterface.sequelize.query('SELECT * FROM "Users"') works without any error. I think the problem here is WHERE querying

It's driving me to crazy :)

Thank you for any help in advance!

Whorton

In the first attempt, you should remove the quote around users – Batik

It does not work. See my updated Shivam :) – Whorton

Don't use double quotes in a query unless you want to create/use an identifier. Your query should be written: "SELECT * FROM Users WHERE username = 'admin'" – Handset

@JorgeCampos Sorry. but an error as same as third attempt. – Whorton

@ToanTran Regarding your fourth attempt. Have you tried putting Users in quotes in the SQL statement? – Millimicron

Yes, I have tried it but SequelizeDatabaseError: column "admin" does not exist. Anyway, I have founded the solution when I read docs carefully. Let's me update the answer :) – Whorton

Answer 1

I have found solution for this issue after reading Sequelize document carefully. Sequelize raw queries replacements. If you face the same issue, please try following solution

return queryInterface.sequelize.query(
  'SELECT * FROM "Users" WHERE username = ? ', {
    replacements: ['admin'],
    type: queryInterface.sequelize.QueryTypes.SELECT
  }).then(users => {

Whorton

sequelize.org/docs/v6/core-concepts/raw-queries/#replacements updated link – Etiolate

Answer 2

In relation to @Toan Tran answer, to update your migration:

await queryInterface.sequelize.query(`
      UPDATE public."Table"
      SET "column_to_be_updated" = :column::uuid
      WHERE public."Permissions"."column_to_check" = :column_to_check
    `, {
        replacements: { column_to_be_updated: r.uuid, column_to_check: r.name },
        type: Sequelize.QueryTypes.UPDATE
      });

This is for Sequelize, using PostgresSQL. Note that this update also is for the column with type Sequelize.UUID.

Kamerun