CODEWITHSUNDEEP
node.jssequelize.js
Fossmo
Fossmo

Reputation: 2892

Add data in Sequelize migration script?

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()
}

}

Upvotes: 22

Views: 22844

Answers (5)

Duhan Canbakan
Duhan Canbakan

Reputation: 1

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

Upvotes: 0

xab
xab

Reputation: 1236

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

Upvotes: 6

Diogo Neves
Diogo Neves

Reputation: 261

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

Upvotes: 11

Fossmo
Fossmo

Reputation: 2892

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();
    })
}

Upvotes: 19

Paul Keeble
Paul Keeble

Reputation: 1222

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();
});

Upvotes: 12

Related Questions