Sequelize: Is it possible to install a postgres extension inside a migration?

[davidleureka]

I am using sequelize with postgres 9.5 and am attempting to make use of the postgis extension. Ideally we would like the extension to be installed inside the first migration so the other migrations can make use of the GEOMETRY data type. The first migration does appear to install postgis correctly but further migrations fail reporting that the extension has not been installed. The migrations appear to be running in order. Are they asynchronous? Seems like migrations would have to run in order to work properly.

I have each migration in its own file. Inside the server code, I'm using umzug to run the migrations. I still get the error when I use sequelize-cli though. Here is the code for the migrations:

gis.js

module.exports = {
    up: function (queryInterface, Sequelize) {
        queryInterface.sequelize.query('CREATE EXTENSION postgis;')
    },

  down: function (queryInterface, Sequelize) {
        queryInterface.sequelize.query('DROP EXTENSION postgis;')
    }
};

user.js

module.exports = {
    up: function (queryInterface, Sequelize) {
        return queryInterface.createTable('user', {
            user_id: {
                primaryKey: true,
                autoIncrement: true,
                type: Sequelize.INTEGER
            },
            location: Sequelize.GEOMETRY
        });
    },
    down: function (queryInterface, Sequelize) {
        return queryInterface.dropTable('user');
    }
};

Here is the output when running the migrations. It's not super easy to read, but if you look you'll see that it's executing

[CREATE](url) EXTENSION postgis;

before executing

CREATE TABLE IF NOT EXISTS "user" ("user_id" SERIAL , "location" GEOMETRY, PRIMARY KEY ("user_id"));

and giving me

Unhandled rejection SequelizeDatabaseError: type "geometry" does not exist

Here's the full output:

Executing (default): CREATE TABLE IF NOT EXISTS "SequelizeMeta" ("name" VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY ("name"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'SequelizeMeta' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): SELECT "name" FROM "SequelizeMeta" AS "SequelizeMeta" ORDER BY "SequelizeMeta"."name" ASC;
Executing (default): CREATE TABLE IF NOT EXISTS "SequelizeMeta" ("name" VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY ("name"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'SequelizeMeta' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): SELECT "name" FROM "SequelizeMeta" AS "SequelizeMeta" ORDER BY "SequelizeMeta"."name" ASC;
Executing (default): CREATE EXTENSION postgis;
Executing (default): CREATE TABLE IF NOT EXISTS "SequelizeMeta" ("name" VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY ("name"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'SequelizeMeta' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "SequelizeMeta" ("name") VALUES ('20160205111045-gis.js') RETURNING *;
Executing (default): CREATE TABLE IF NOT EXISTS "SequelizeMeta" ("name" VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY ("name"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'SequelizeMeta' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): SELECT "name" FROM "SequelizeMeta" AS "SequelizeMeta" ORDER BY "SequelizeMeta"."name" ASC;
Executing (default): CREATE TABLE IF NOT EXISTS "user" ("user_id" SERIAL , "location" GEOMETRY, PRIMARY KEY ("user_id"));
Unhandled rejection SequelizeDatabaseError: type "geometry" does not exist
at Query.formatError (/api/data/node_modules/sequelize/lib/dialects/postgres/query.js:357:14)
at null. (/api/data/node_modules/sequelize/lib/dialects/postgres/query.js:88:19)
at emitOne (events.js:77:13)
at emit (events.js:169:7)
at Query.handleError (/api/data/node_modules/pg/lib/query.js:108:8)
at null. (/api/data/node_modules/pg/lib/client.js:171:26)
at emitOne (events.js:77:13)
at emit (events.js:169:7)
at Socket. (/api/data/node_modules/pg/lib/connection.js:109:12)
at emitOne (events.js:77:13)
at Socket.emit (events.js:169:7)
at readableAddChunk (_stream_readable.js:146:16)
at Socket.Readable.push (_stream_readable.js:110:10)
at TCP.onread (net.js:523:20)

[mickhansen]

You can simply run a raw query: https://github.com/sequelize/sequelize/blob/e6c42a4acc4af0caf20c9562c247784621982146/test/integration/support.js#L11

All methods in Sequelize (and generally in Node.js) are async:

up: function (queryInterface, Sequelize) {
  queryInterface.sequelize.query('CREATE EXTENSION postgis;')
}

should be

up: function (queryInterface, Sequelize) {
  return queryInterface.sequelize.query('CREATE EXTENSION postgis;')
}

[Jadex1]

I'm sorry to Necro this thread, but I was interested in this implementation. @davidleureka Did you end up creating a migration file to add the POSTGIS Ext? I've tried adding the file you have, but it seems my files are excited in order and not async. Would you have any suggestions for making sure the postgis migration fires first?

[ptboyer]

@Jadex1 Because all the methods are async and subsequently return Promises, you could chain them so that your CREATE EXTENSION postgis; always runs first in your up, and vice versa your DROP EXTENSION postgis; always runs last (in your down).

With Promise.then:

up: (queryInterface, Sequelize) => {
  return queryInterface.sequelize.query('CREATE EXTENSION postgis;')
    .then(() => Promise.all([
      queryInterface.createTable( ... ),
      ...
    ]));
}

Or with async/await:

up: async (queryInterface, Sequelize) => {
  await queryInterface.sequelize.query('CREATE EXTENSION postgis;');
  return Promise.all([
    queryInterface.createTable( ... ),
    ...
  ]);
}