Cli: how to create relationships during the migration

Created on 16 Dec 2015 · 6Comments · Source: sequelize/cli

Hi,

If I describe my models and relations and then do sequelize.sync() I receive my table scheme with constraints during declaration.

But when I do migration, like this:

queryInterface.createTable(
                'history',
                {
                    id: {type: Sequelize.BIGINT, primaryKey: true, autoIncrement: true},
                    user_id: Sequelize.BIGINT,
                    location_id: Sequelize.BIGINT,
                    action: Sequelize.ENUM('VISITED'),
                    data: Sequelize.JSONB,
                    created_at: Sequelize.DATE,
                    updated_at: Sequelize.DATE
                }
            )

I don't see any option to add correct relations between the tables. http://docs.sequelizejs.com/en/latest/docs/migrations/#createtabletablename-attributes-options

Any thoughts if it's possible to do using migrations?

Regards,

Source

PavelPolyakov

Most helpful comment

You can use the references property to reference other tables by name.

EDIT: you probably need to wait for the first table to be created before referencing it, I changed the code to not create the tables in parallell.

module.exports = {
    up: function (queryInterface, Sequelize) {
        return queryInterface.createTable(
            'users',
            {
                id: {type: Sequelize.BIGINT, primaryKey: true, autoIncrement: true},
                email: Sequelize.STRING
            }
        ).then(function () {
            return queryInterface.createTable(
                'history',
                {
                    id: {type: Sequelize.BIGINT, primaryKey: true, autoIncrement: true},
                    user_id: {
                        type: Sequelize.BIGINT,
                        references: {
                            model: "users",
                            key: "id"
                        }
                    },
                    record: Sequelize.STRING
                }
            )
        });
    }
};

fredrikekelund on 22 Dec 2015

👍7

All 6 comments

For now I came up with the next option:

module.exports = {
    up: function (queryInterface, Sequelize) {
        let Promise = queryInterface.sequelize.Promise;

        return Promise.coroutine(function *(){
            // create tables
            yield Promise.all([
                queryInterface.createTable(
                    'users',
                    {
                        id: {type: Sequelize.BIGINT, primaryKey: true, autoIncrement: true},
                        email: Sequelize.STRING
                    }
                ),
                queryInterface.createTable(
                    'history',
                    {
                        id: {type: Sequelize.BIGINT, primaryKey: true, autoIncrement: true},
                        user_id: Sequelize.BIGINT,
                        record: Sequelize.STRING
                    }
                )
            ]);

            // foreign keys
            yield Promise.all([
                queryInterface.sequelize.query('ALTER TABLE history ADD CONSTRAINT history_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id);')
            ]);

            return true;
        })();
    }
}

PavelPolyakov on 16 Dec 2015

👍1

You can use the references property to reference other tables by name.

EDIT: you probably need to wait for the first table to be created before referencing it, I changed the code to not create the tables in parallell.

module.exports = {
    up: function (queryInterface, Sequelize) {
        return queryInterface.createTable(
            'users',
            {
                id: {type: Sequelize.BIGINT, primaryKey: true, autoIncrement: true},
                email: Sequelize.STRING
            }
        ).then(function () {
            return queryInterface.createTable(
                'history',
                {
                    id: {type: Sequelize.BIGINT, primaryKey: true, autoIncrement: true},
                    user_id: {
                        type: Sequelize.BIGINT,
                        references: {
                            model: "users",
                            key: "id"
                        }
                    },
                    record: Sequelize.STRING
                }
            )
        });
    }
};

fredrikekelund on 22 Dec 2015

👍7

@fredrikekelund
Nice, wrapping this all to coroutine would make it looks even better.

PavelPolyakov on 22 Dec 2015

you could also separate both table into different migrations, just make sure that users runs before history