using sequelize-cli db:seed, schema is ignored when accessing Postgres

Question

i am building a web service using express.js and Sequilize with a Postgres DB.

Database holds a table 'country' under schema 'schema1'. Table 'country' has fields 'name', 'isoCode'.

Created a seed file to insert a list of countries inside table 'country'.

Seed file looks like :

'use strict';

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.bulkInsert(
        'country', 
        [
          {
            "name":"Afghanistan",
            "isoCode":"AF"
          },
          {
            "name":"Åland Islands",
            "isoCode":"AX"
          },
          {
            "name":"Albania",
            "isoCode":"AL"
          },
          {
            "name":"Algeria",
            "isoCode":"DZ"
          },
          {
            "name":"American Samoa",
            "isoCode":"AS"
          },
          {
            "name":"Andorra",
            "isoCode":"AD"
          }
        ], 
        {
            schema : 'schema1'
        }
    );
  },

  down: function (queryInterface, Sequelize) {

  }
};

While running seed i get this error :

    node_modules/sequelize-cli/bin/sequelize --url postgres://user:password@localhost:5432/database db:seed

    Sequelize [Node: 0.12.6, CLI: 2.0.0, ORM: 3.11.0, pg: ^4.4.2]

    Parsed url postgres://user:*****@localhost:5432/database        
    Starting 'db:seed'...
    Finished 'db:seed' after 165 ms
    == 20151029161319-Countries: migrating =======
    Unhandled rejection SequelizeDatabaseError: relation "country" does not exist
        at Query.formatError (node_modules/sequelize/lib/dialects/postgres/query.js:437:14)
        at null.<anonymous> (node_modules/sequelize/lib/dialects/postgres/query.js:112:19)
        at emit (events.js:107:17)
        at Query.handleError (node_modules/pg/lib/query.js:108:8)
        at null.<anonymous> (node_modules/pg/lib/client.js:171:26)
        at emit (events.js:107:17)
        at Socket.<anonymous> (node_modules/pg/lib/connection.js:109:12)
        at Socket.emit (events.js:107:17)
        at readableAddChunk (_stream_readable.js:163:16)
        at Socket.Readable.push (_stream_readable.js:126:10)
        at TCP.onread (net.js:538:20)

I think i am stuck on this. I would appreciate any provided help / guidance etc.

Thank you for your time.

What is the search_path for the DB user? Maybe it doesn't include schema1 — user330315
– user330315, Commented Oct 29, 2015 at 15:37
Thanks for replying. Executed : show search_path The result is : "$user", public — Alexandros Monastiriotis
– Alexandros Monastiriotis, Commented Oct 29, 2015 at 15:41
Then of course an unqualified table name will not be resolved to schema1. You either need to change the user's search path to include schema1, teach your library to qualify the tables with a schema or teach your library on how to change the search path dynamically after a connect — user330315
– user330315, Commented Oct 29, 2015 at 17:10

Community · Accepted Answer · 2017-05-23 12:00:53Z

I executed SQL query on Postgres :

     ALTER ROLE <username> SET search_path TO schema1,public;

as noted here : Permanently Set Postgresql Schema Path

Then, executed seeder again succesfully :

    node_modules/sequelize-cli/bin/sequelize --url postgres://user:password@localhost:5432/database db:seed

    Sequelize [Node: 0.12.6, CLI: 2.0.0, ORM: 3.11.0, pg: ^4.4.2]

    Parsed url postgres://user:*****@localhost:5432/database
    Using gulpfile node_modules/sequelize-cli/lib/gulpfile.js
    Starting 'db:seed'...
    Finished 'db:seed' after 558 ms
    == 20151029161319-Countries: migrating =======
    == 20151029161319-Countries: migrated (0.294s)

Thanks @a_horse_with_no_name for the information about search_path. I wish the sequelize library could handle this situation, or maybe i misuse it.

update : Opened a ticket on Github (https://github.com/sequelize/sequelize/issues/4778#issuecomment-152566806) and the solution is quite simple :

instead of setting only the table as the first argument, set

{tableName: 'country', schema : 'schema1'}

Sergio Flores · Accepted Answer · 2019-09-24 22:51:03Z

1

You can actually specify the schema and table name via object like is explained in this Github issue:

'use strict';

module.exports = {
    up: function (queryInterface, Sequelize) {
        return queryInterface.bulkInsert(
            { tableName: 'account', schema: 'crm' },
            {
                name: 'Michael'
            },
            {}
        );
    },

    down: function (queryInterface, Sequelize) {
        return queryInterface.bulkDelete({ tableName: 'account', schema: 'crm' }, null, {});
    }
};

answered Sep 24, 2019 at 22:51

Sergio Flores

5,4455 gold badges39 silver badges60 bronze badges

Collectives™ on Stack Overflow

using sequelize-cli db:seed, schema is ignored when accessing Postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related