Add data in Sequelize migration script?

Question

How can I add data to a table in a Sequelize migration script? This is what I got:

module.exports = {
up: function(migration, DataTypes, done) {
    migration.createTable(
        'person',
        {
            name: DataTypes.STRING,
            age: DataTypes.INTEGER
        },
        {
            charset: 'latin1' // default: null
        }
    );
    // I want to insert person and age.
    migration.insert(???);
    done()
},
down: function(migration, DataTypes, done) {
    migration.dropTable('queue');
    done()
}

}

Answer 1

I figured it out. Sequelize is available from migration.migrator.sequelize. It is possible to do something like this:

up: function (migration, DataTypes, done) {
    migration.createTable(
        'Person',
        {
            name: DataTypes.STRING,
            age: DataTypes.INTEGER,
        }
    ).success(function () {
        migration.migrator.sequelize.query("insert into person (name, age) values ('Donald Duck', 60)");
        done();
    });
},
down: function (migration, DataTypes, done) {
    migration.dropTable(
      'Person'
    ).then(function() {
      done();
    })
}

Answer 2

In the latest version of sequelize this has changed and it should now be

migration.createTable(
    'Person',
    {
        name: DataTypes.STRING,
        age: DataTypes.INTEGER,
    }
).then(function () {
    migration.sequelize.query("insert into person (name, age) values ('Donald Duck', 60)");
    done();
});

Answer 3

Actually it is not a good idea to just run a query. queryInterface has create() and bulkCreate() now.

'use strict';
module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.bulkInsert('roles', [{
      label: 'user',
      createdAt: new Date(),
      updatedAt: new Date()
    }, {
      label: 'admin',
      createdAt: new Date(),
      updatedAt: new Date()
    }]);
  },
  down: function(queryInterface, Sequelize) {
    return queryInterface.bulkDelete('roles', null, {});
  }
};

This returns a promise as expected by sequelize-cli.

Source (adapted): https://github.com/sequelize/sequelize/issues/3210

Answer 4

In sequelize version 4.41.2, when you use ES6+ and you want to do more complex insertions within migration you could make the up an async function which creates your table and then inserts the necessary data into the table. To ensure that the migration either succeeds OR fails without making any changes a transaction is used for every interaction with sequelize. Another important thing to note is that up must return a Promise.

Example of creating a table -> fetching data from other table -> modifying fetched data -> inserting modified data to new table:

module.exports = {
    up: (queryInterface, Sequelize) => queryInterface.sequelize.transaction(async (transaction) => {
        await queryInterface.createTable('person', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER,
            },
            name: {
                type: Sequelize.STRING,
            },
            age: {
                type: Sequelize.STRING,
            },
        }, { transaction });
        // Returns array [[results], { /** result obj */ }]
        const [dogs] = await queryInterface.sequelize.query('SELECT * FROM public."Dogs";', { transaction });
        // prepare data
        const metamorphed = dogs.map(({ name, age }) => ({
            name,
            age: parseInt((age * 7), 10),
        }));
        return queryInterface.bulkInsert('person', metamorphed, { transaction });
    }),
    down: queryInterface => queryInterface.dropTable('person'),
};

Answer 5

I'm Solution. this is an example:

    "use strict";

    module.exports = {
        up: (queryInterface, Sequelize) => {
            return queryInterface
                .createTable("roles", {
                    id: { type: Sequelize.INTEGER, autoIncrement: true, primaryKey: true },
                    name: { type: Sequelize.STRING },
                    description: { type: Sequelize.STRING },
                })
                .then(() => {
                    queryInterface.bulkInsert("roles", [{
                            name: "admin",
                            description: "Admins",
                        },
                        {
                            name: "company",
                            description: "Companies",
                        },
                        {
                            name: "user",
                            description: "Users",
                        },
                    ]);
                });
        },

    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable("roles");
    },
};