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` — , Oct 29 '15 at 15:37
Thanks for replying. Executed : show search_path The result is : "$user", public — Alexandros Monastiriotis, Oct 29 '15 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 — , Oct 29 '15 at 17:10

score 4 · Accepted Answer · edited May 23 '17 at 12:00

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'}

score 1 · Answer 2 · answered Sep 24 '19 at 22:51

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, {});
    }
};

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

2 Answers2