Cli: Migrations - Add Multiple Columns Example?
I am somewhat of a newbie, and I have a single add column working, but a couple of questions.
how can I add multiple columns in one migration file...
also, when I rerun my app I am seeing ER_DUP_FIELDNAME: Duplicate column name 'test' when I run the node server.js the next time...which is correct because it has already been created, should I worry abot catching and ignore the error or just let it run as it is?
Also, if there are multiple migration files in the directory what order are they ran?
countrdd
All 52 comments
I was just looking for the answer to this and couldn't find it anywhere. I managed to figure it out though.
'use strict';
module.exports = {
up: function (queryInterface, Sequelize) {
return [
queryInterface.addColumn(
'users',
'email',
{
type: Sequelize.STRING,
allowNull: false
}
),
queryInterface.addColumn(
'users',
'encryptedPassword',
{
type: Sequelize.STRING,
allowNull: false
}
)
];
},
down: function (queryInterface, Sequelize) {
return [
queryInterface.removeColumn('users', 'email'),
queryInterface.removeColumn('users', 'encryptedPassword')
];
}
};
Regarding the error, I'd need to see your migrations to have a better idea about what is going on.
Sequelize will run migrations sorted by the filename. You can run
sequelize migration:create --name add-email-and-password-to-user
from the command line to create a new migration prefixed with a timestamp that will order by when they were created.
abams
on 19 May 2015
Hey guys. The migration functions are supposed to return promises. So if you want to do multiple things in a migration, just return a chained promised like this:
return queryInterface.removeColumn('users', 'email').then(function () {
return queryInterface.removeColumn('users', 'encryptedPassword');
});
sdepold
on 6 Jun 2015
as said, we would need to see your migration to answer the question about the error
sdepold
on 6 Jun 2015
@sdepold That was helpful. I saw no mention of these migration functions returning promises. Was running into some weird race conditions due to returning an array of functions that are asynchronous in nature.
connected-rcheung
on 27 Aug 2015
I'd love to add multiple columns in a single migration as well. But chaining the promises actually isn't a good idea.
There currently isn't any type of rollback for column changes. So if you try and add 2 columns in the same migration and the 2nd fails. Then the migration will fail but column no. 1 will still exist. And when you re-run the migrations they will eternally fail because the migration will try to create column no. 1 a second time and conflict.
Thanks to that, as annoying as it is, you actually need a separate migration for each column you want to add, each index you want to add, etc. For your migrations to be reliable.
dantman
on 28 Aug 2015
Though I do wonder if there is a nice alternative to unreliable migrations or being forced to use a separate migration file for each individual change in a large migration.
Something like a multi-migration, ie: a single migration file that actually contains multiple migrations.
"use strict";
module.exports = [
{
up: function(migration, Sequelize) {
return migration
.addColumn(
'FooBar',
'B',
{
type: Sequelize.STRING,
allowNull: true
});
},
down: function(migration) {
return migration
.removeColumn('FooBar', 'B');
}
},
{
up: function(migration, Sequelize) {
return migration
.addColumn(
'FooBar',
'C',
{
type: Sequelize.STRING,
allowNull: true
});
},
down: function(migration) {
return migration
.removeColumn('FooBar', 'C');
}
}
];
For these SequelizeMeta would contain either multiple 20150801013124-mig-migration.js#1 entries (with the #1 identifying the individual migrations in the file) or SequelizeMeta would have a second column, a nullable integer indicating which migration within the file the row corresponds to.
dantman
on 28 Aug 2015
I also find this annoying. +1 for @dantman's suggestion. As a work around I have found that adding the migration name manually to the SequelizeMeta table allows me to run undo, which, if you structure the rollback symmetrically, will then error out at the same point. This will have the effect of removing the same columns up to the point of error, which is effectively a rollback of a multi column update.
This work around could be made easier by having the sequelize db:migrate:undo be able to specify a specific migration that needs to be undone, or run it on the one that is not in SequelizeMeta. IE sequelize should anticipate that people may want to rollback their migrations when they fail.
renewooller
on 3 Feb 2016
Hey guys, just thought I would share a solution in case you are still looking for one. I don't like super-chaining my commands because it gets hard to read after a while, and I get lost in the indentation. Especially if you're adding a ton of column changes or other alterations. I generally don't like to chain column changes anyway because if the migration fails then you have a big issue on your hands in terms of figuring out what worked and what didn't. So I basically only use chaining for initial table creation + indexes (or else I would need a separate migration for each index, which is painful). I do this because the indexes are erased when calling down() when the table is dropped. So my migrations would look like this:
var promise = require('bluebird');
module.exports = {
up: function (queryInterface, Sequelize) {
var actions = [];
// Create the main table
actions.push({
exec: function() {
return queryInterface.createTable(...);
}
});
// Append the indexes and whatnot
actions.push({ exec: function() { return queryInterface.addIndex('users', ['...']); }});
// Go through one by one (with concurrency!) and process each function
return promise.map(actions, function(item) {
return item.exec();
}, { concurrency: 1 });
},
down: function (queryInterface, Sequelize) {
queryInterface.dropTable('users');
}
};
panbanda
on 4 May 2016
With node 6 the following appears to work...
module.exports = {
up: function(queryInterface, Sequelize) {
let migrations = [];
migrations.push(queryInterface.addColumn(
'Character',
'user_id',
{
type: Sequelize.STRING,
allowNull: false,
}
));
return Promise.all(migrations);
},
down: function(queryInterface, Sequelize) {
let migrations = [];
migrations.push(queryInterface.removeColumn('Character', 'user_id', Sequelize.STRING));
return Promise.all(migrations);
}
};
parris
on 31 Jul 2016
@parris If you add another add/removeColumn to that migration it will suffer from the same issue as chaining using .then.
If even one of your addColumn commands succeeds but any others fail, then your migrations will be stuck in a state they cannot migrate or escape from. Any attempt to re-run the migration will fail, because it will try to create a column that already exists. Any attempt to undo the migration will also fail, because it will try to remove a column that doesn't exist.
dantman
on 31 Jul 2016
@dantman I see what do you mean, how can we protect against that?
diosney
on 3 Aug 2016
@dantman Maybe just like @jylinman proposed, though in the inverse operation triggers an error in the middle, maybe can get the migrations in a recoverable state if the operations are inversed too?
I mean (in the same migration):
up:
- create model A
- create model B
down:
- remove model B
- remove model A
I'm asking this because I have a migration which change a description column type in all of my models, and it can get in that hole too.
diosney
on 3 Aug 2016
Aren't transactions the solution to the bad state issue described by dantman? https://github.com/sequelize/cli/issues/133#issuecomment-135576255
I believe that's how Rails deal with multiple DB changes in a single migration. Each migration runs within a transaction. If any individual change fails, the transaction rolls back.
createthis
on 20 Oct 2016
@createthis Agree. If transactions could be used in the migrations, that will be the solution for this.
diosney
on 20 Oct 2016
They... can't?
createthis
on 20 Oct 2016
@createthis @diosney MySQL (at least) does not support transactions for ALTER TABLE. Any transactions are implicitly committed before table structure changes start.
To be honest, I don't blame them. Transactions are primarily meant for rolling back data changes. It sounds absurd to try and attempt to apply them to changes that alter the very structure of that data聽which can implicitly alter values within every single row of a table of undefined size.
dantman
on 20 Oct 2016
Thanks. That's interesting. I never noticed that. Worked with Rails and MySQL for years. I guess the recent trend toward Postgres spoiled me. So, in sqlite and postgres, would something like this work?
up: function (queryInterface, Sequelize) {
var sequelize = queryInterface.sequelize;
return sequelize.transaction(function (t) {
var migrations = [];
migrations.push(queryInterface.addColumn(
'products',
'some_count',
Sequelize.INTEGER.UNSIGNED,
{transaction: t}
));
migrations.push(queryInterface.addColumn(
'products',
'some_other_property',
Sequelize.INTEGER.UNSIGNED,
{transaction: t}
));
return Promise.all(migrations);
});
},
down: function (queryInterface, Sequelize) {
var sequelize = queryInterface.sequelize;
return sequelize.transaction(function (t) {
var migrations = [];
migrations.push(queryInterface.removeColumn('products', 'some_count', {transaction: t}));
migrations.push(queryInterface.removeColumn('products', 'some_other_property', {transaction: t}));
return Promise.all(migrations);
});
}
Seems to work on my end, but I haven't tried to make it fail yet.
createthis
on 20 Oct 2016
@createthis Transaction wise it should work in PG/SQlite. However I'd advise against an array+Promise.all based method. You're doing 2 ALTER table operations in parallel. You're protected from the worst by transactions, but you could still end up with weird race conditions; like the order of columns in your table being different between different runs and confusing changes in error messages if both your alterations fail but which one errors out first differs between migration attempts.
dantman
on 20 Oct 2016
@dantman actually, in the previous example I gave, I'm getting this:
Sequelize [Node: 6.8.0, CLI: 2.4.0, ORM: 3.24.4, sqlite3: ^3.1.6]
Loaded configuration file "config/config.json".
Using environment "development".
== 20161020154112-AddStatisticsToProducts: reverting =======
Unhandled rejection SequelizeDatabaseError: SQLITE_ERROR: no such table: products
I can delete the sqlite db and run the migrations forward, but any time I try to undo the last migration I get the above error. It's like it's grabbing the wrong table or something because I manually verified the table exists using the sqlite3 cli tool.
createthis
on 20 Oct 2016
Spent some time debugging this. Turns out the error message in the previous comment was a direct result of the queries happening async as @dantman warned. I'd forgotten that SQLite has no drop column. Sequelize simulates drop column by dropping the table and recreating it. In this case it was dropping the table twice, thus the error on the second drop table. I figured it out by enabling sequelize console logging and examining the SQL queries logged.
Here's my revised strategy (for posterity):
up: function (queryInterface, Sequelize) {
var sequelize = queryInterface.sequelize;
return sequelize.transaction(function (t) {
return queryInterface.addColumn('products', 'some_count', Sequelize.INTEGER.UNSIGNED, {transaction: t})
.then(function () {
return queryInterface.addColumn('products', 'some_other_property', Sequelize.INTEGER.UNSIGNED, {transaction: t});
});
});
},
down: function (queryInterface, Sequelize) {
var sequelize = queryInterface.sequelize;
return sequelize.transaction(function (t) {
return queryInterface.removeColumn('products', 'some_count', {transaction: t})
.then(function () {
return queryInterface.removeColumn('products', 'some_other_property', {transaction: t});
});
});
}
I've tested this and it works as expected in multiple migrate/undo cycles. The indentation is a little deeper than I'd like, but at least it never goes deeper no matter how many .then clauses are appended.
createthis
on 20 Oct 2016
@createthis Guess you discovered another race condition I didn't think about that makes running db migration steps in parallel a bad idea.
Btw, if you're willing to use Babel for now, then async/await could flatten that a little more.
dantman
on 20 Oct 2016
Migrations must be sync not async as they must run in order.
Also having multiple migrations per file is _not_ recommended, even if the down is going in reverse order, here is why:
UP
- Migration A
- Migration B
- Migration C
DOWN
- Migration C
- Migration B
- Migration A
If on the UP Migration B fails, then on the down Migration C will also fail as it never occurred in the UP. Now migrations will be in an unrecoverable state.
eddiejaoude
on 8 Nov 2016
@eddiejaoude probably that's why createthis used transactions, if the B will fail then all the migration will rolleback.
iszlailorand
on 13 Jan 2017
@createthis you might get better indentation by putting the first query into Promise.resolve
return Promise.resolve(
queryInterface(...)
).then(() => {
queryInterface(...)
})
chuan137
on 21 Apr 2017
This what worked:
sequelize migration:create --name add-password-to-user
'use strict';
module.exports = {
up(queryInterface, Sequelize) {
return queryInterface.sequelize.transaction(function handleTransaction (t) {
return Promise.all([
queryInterface.addColumn('Users', 'password', Sequelize.STRING, {transaction: t}),
queryInterface.addColumn('Users', 'resetPasswordToken', Sequelize.STRING, {transaction: t}),
queryInterface.addColumn('Users', 'resetPasswordExpires', Sequelize.DATE, {transaction: t})
]);
});
},
down(queryInterface, Sequelize) {
return queryInterface.sequelize.transaction(function handleTransaction (t) {
return Promise.all([
queryInterface.removeColumn('Users', 'password', {transaction: t}),
queryInterface.removeColumn('Users', 'resetPasswordToken', {transaction: t}),
queryInterface.removeColumn('Users', 'resetPasswordExpires', {transaction: t})
]);
});
}
}
And then
sequelize db:migrate
If not happy
sequelize db:migrate:undo
Respecting the bellow comment from scottbeacher, the above worked for me on a PostgreSQL database.
iamfree-com
on 28 May 2017
@iamfree-com Alter table causes implicit commits, can't use transactions with those. Has already been mentioned.
https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html
scottbeacher
on 31 May 2017
Guys, I'm posting here only to document the solution that I have used to have a more clean code on scenario where we have multiple removeColumn calls.
Async/await were a good solution for my case:
down: async (queryInterface, Sequelize) => {
return [
await queryInterface.removeColumn('users', 'facebook_id'),
await queryInterface.removeColumn('users', 'twitter_id'),
await queryInterface.removeColumn('users', 'gender')
];
}
godfather
on 8 Dec 2017
@godfather ah man I have been searching for about 2 hours for a manageable solution to chaining migrations. This is the best solution I have seen so far. Thank you!
AlexanderHolman146
on 1 Feb 2018
Note, there appears to be a difference in a couple of the suggestions proposed here. Returning an array of promises might be invoking a race condition. Depending on what you're doing, this may not matter. I didn't get an error but I had an issue where a column rename wasn't working after running changeColumn on the same column. Switching to a then chain which forces the calls to run sequentially seemed to fix my issue.
mattcodez
on 18 Apr 2018
Just a thought, but if you use await, my understanding is that the function will effectively be suspended until the promise resolves whereby it will continue to the next line of code. So there should never be a race condition as I understand it?
pldilley
on 3 Jun 2018
I'm trying to create tables having dependency in this order in my single migration file but on migration I'm facing the error
cannot find property startYear of undefined
`"use strict";
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface
.createTable("Users", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
googleId: {
allowNull: true,
type: Sequelize.STRING
},
facebookId: {
allowNull: true,
type: Sequelize.STRING
},
fullName: {
type: Sequelize.STRING,
allowNull: false
},
email: {
type: Sequelize.STRING,
allowNull: false,
unique: true,
validate: {
isEmail: true
}
},
password: {
type: Sequelize.STRING,
allowNull: false,
validate: {
isAlphanumeric: true,
notEmpty: true
}
},
isVerified: {
type: Sequelize.BOOLEAN,
allowNull: false,
defaultValue: false
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
.then(function() {
return queryInterface
.createTable("DesignationMasters", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
designationName: {
type: Sequelize.STRING,
allowNull: false,
unique: true
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
.then(function() {
return queryInterface
.createTable("companyDetails", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
companyName: {
type: Sequelize.STRING,
allowNull: true,
defaultValue: null
},
userId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "Users",
key: "id"
}
},
designationId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "DesignationMasters",
key: "id"
}
},
startyear: {
type: Sequelize.INTEGER,
allowNull: true,
validate: {
isNumeric: true,
len: [4, 4]
},
defaultValue: null
},
endYear: {
type: Sequelize.INTEGER,
validate: {
isNumeric: true,
len: [4, 4],
min: this.startYear
},
defaultValue: null
},
isCurrentWorkplace: {
type: Sequelize.BOOLEAN,
defaultValue: false
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
.then(function() {
return queryInterface
.createTable("InterestMasters", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
interestValue: {
allowNull: false,
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
.then(function() {
return queryInterface.createTable("UserInterests", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
userId: {
type: Sequelize.INTEGER,
references: {
model: "Users",
key: "id"
},
allowNull: false
},
interestId: {
type: Sequelize.INTEGER,
references: {
key: "InterestMasters",
value: "id"
},
allowNull: false
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
});
});
});
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable("InterestMasters").then(function() {
return queryInterface.dropTable("Interests").then(function() {
return queryInterface.dropTable("companyDetails").then(function() {
return queryInterface
.dropTable("DesignationMasters")
.then(function() {
return queryInterface.dropTable("Users");
});
});
});
});
}
};
orton009
on 16 Jun 2018
Hey orton009,
I would use multiple migrations for this and not try to create/drop multiple tables in one migration. Single migrations with multiple methods is more like if you are adding a whole bunch of columns to a table.
If you really want to do it in one method, you can only have ONE return method per javascript function, because return causes a function to terminate and output the returned result: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/return
Finally, just a Github tip (and tech websites like this one), you can use three backticks in a row around your code to so that it is readable. Your code above is indecipherable otherwise! Here is how it works:
Similarly for single line, you can surround with single backticks:
P.S. As far as I know, using one column as the minimum value of another column is not possible. This sort of thing is kept in the code as business logic and beyond the responsibilities of the database.
endYear: {
type: Sequelize.INTEGER,
validate: {
isNumeric: true,
len: [4, 4],
min: this.startYear <--- REMOVE THIS, JUST DO IT IN THE CODE!
},
defaultValue: null
},
If you really must do it, it may be possible by using triggers: https://www.siteground.com/kb/mysql-triggers-use/
In any case, this is beyond the scope of this thread - so if you have any more questions regarding how to set minimum values in this way, I would start a new issue thread: https://github.com/sequelize/cli/issues/new
pldilley
on 16 Jun 2018
Can you provide me any resource on how to create multiple migrations in same file and run those migrations in an order in order to maintain table dependencies
orton009
on 16 Jun 2018
Dude, it's in this very thread :P Scroll up!
pldilley
on 16 Jun 2018
This is giving me the error "replace " not defined
I tried removing the last table ("UserInterests") from migrations array then it worked fine but I want to create all of my tables with a single migration file.
"use strict";
module.exports = {
up: (queryInterface, Sequelize) => {
let migrations = [];
migrations.push(
queryInterface.createTable("Users", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
googleId: {
allowNull: true,
type: Sequelize.STRING
},
facebookId: {
allowNull: true,
type: Sequelize.STRING
},
fullName: {
type: Sequelize.STRING,
allowNull: false
},
email: {
type: Sequelize.STRING,
allowNull: false,
unique: true,
validate: {
isEmail: true
}
},
password: {
type: Sequelize.STRING,
allowNull: false,
validate: {
isAlphanumeric: true,
notEmpty: true
}
},
isVerified: {
type: Sequelize.BOOLEAN,
allowNull: false,
defaultValue: false
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
);
migrations.push(
queryInterface.createTable("DesignationMasters", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
designationName: {
type: Sequelize.STRING,
allowNull: false,
unique: true
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
);
migrations.push(
queryInterface.createTable("companyDetails", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
companyName: {
type: Sequelize.STRING,
allowNull: true,
defaultValue: null
},
userId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "Users",
key: "id"
}
},
designationId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "DesignationMasters",
key: "id"
}
},
startyear: {
type: Sequelize.INTEGER,
allowNull: true,
validate: {
isNumeric: true,
len: [4, 4]
},
defaultValue: null
},
endYear: {
type: Sequelize.INTEGER,
validate: {
isNumeric: true,
len: [4, 4],
min: this.startYear
},
defaultValue: null
},
isCurrentWorkplace: {
type: Sequelize.BOOLEAN,
defaultValue: false
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
);
migrations.push(
queryInterface.createTable("InterestMasters", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
interestValue: {
allowNull: false,
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
);
migrations.push(
queryInterface.createTable("UserInterests", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
userId: {
type: Sequelize.INTEGER,
references: {
model: "Users",
key: "id"
},
allowNull: false
},
interestId: {
type: Sequelize.INTEGER,
references: {
key: "InterestMasters",
value: "id"
},
allowNull: false
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
);
return Promise.all(migrations);
},
down: (queryInterface, Sequelize) => {
let migrations = [];
migrations.push(queryInterface.dropTable("InterestMasters"));
migrations.push(queryInterface.dropTable("Interests"));
migrations.push(queryInterface.dropTable("companyDetails"));
migrations.push(queryInterface.dropTable("DesignationMasters"));
migrations.push(queryInterface.dropTable("Users"));
}
};
I'm gonna guess that there's a race condition because there are references to other tables.
references: {
model: "Users",
key: "id"
}
I still highly recommend using multiple migrations, or at least two. However if it must be done in one you can probably put the UserInterests into a then:
return Promise.all(migrations)
.then(() => queryInterface.createTable("UserInterests", {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
userId: {
type: Sequelize.INTEGER,
references: {
model: "Users",
key: "id"
},
allowNull: false
},
interestId: {
type: Sequelize.INTEGER,
references: {
key: "InterestMasters",
value: "id"
},
allowNull: false
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
}));
Its not adding the foreign key error
orton009
on 17 Jun 2018
If I am using multiple migrations files for multiple tables then how can I control table creation as tables are using foreign key so former tables must be created before them, how can I specify which migrations to take place first and executing dependent migrations later
orton009
on 17 Jun 2018
pldilley
on 17 Jun 2018
I like @iamfree-com's suggestion most, but the next code fails:
down: (queryInterface, Sequelize) => {
return queryInterface.sequelize.transaction(function handleTransaction (t) {
return Promise.all([
queryInterface.renameColumn(
'Users',
'uid',
'email',
{transaction: t}
),
queryInterface.removeColumn(
'Users',
'active',
{transaction: t}
),
]);
});
}
shepelevstas
on 1 Sep 2018
@shepelevstas those should be separate migration steps, if one fails, you won't know which one
eddiejaoude
on 2 Sep 2018
Is this OK? Works for me:
down: (queryInterface, Sequelize) => {
return queryInterface.sequelize.transaction(async transaction => {
return [
await queryInterface.renameColumn(
'Users',
'uid',
'email',
{transaction}
),
await queryInterface.removeColumn(
'Users',
'active',
{transaction}
)
]
})
}
those should be separate migration steps, if one fails, you won't know which one
@shepelevstas ^^
then when you try to rollback or forwards, one of those might have already run successfully and it won't know but when it tries to run it again it will always now fail
eddiejaoude
on 9 Sep 2018
@eddiejaoude No. Transaction makes the whole migration to fail and rollback if any of modifications fail.
shepelevstas
on 9 Sep 2018
@shepelevstas where did you read that? Its not in the docs. In fact, there is an open issue for that feature, so I dont think it exists yet! But its your product, if you would rather save a few seconds when running the migrations at the cost of it being more fragile, then thats your decision 馃 . Good luck to you 馃槈 https://github.com/sequelize/sequelize/issues/7902
eddiejaoude
on 9 Sep 2018
@eddiejaoude it's one of the main tabs in the docs: http://docs.sequelizejs.com/manual/tutorial/transactions.html
I tried it and it works as described.
shepelevstas
on 11 Sep 2018
I didn't see you were using .transaction all on the same line, on the phone it was small. Fair enough, however, I still dont know what the benefit is, the performance gain will be minimal, and if this migration fails, you still won't know which one it is. Plus both are running async and changing the same table, depending on the db engine being used, one will probably block the other with a table lock. I think you are trying to solve a problem that doesn't exist and creating potentially a new issue.
I would like to understand the problem you are trying to solve?
eddiejaoude
on 11 Sep 2018
@shepelevstas Alter table (which is what removeColumn and renameColumn will use) causes implicit commits, transactions don't affect those. The change is committed immediately so there is no way to rollback if a later step in the transaction fails. Has already been mentioned several times in this ticket.
https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html
scottbeacher
on 11 Sep 2018
I tested it in SQLite, and it properly rolls back the whole transaction if one of the later alterations fails. I checked the SQL query, it creates a backup table during transaction. But, yes, it is better to make alterations in a chain, as some of them interfear with each other. Still transaction helps a lot.
shepelevstas
on 13 Sep 2018
I am still struggling to see the problem you are trying to solve by running multiple migrations in a single migration file?
eddiejaoude
on 13 Sep 2018
Hello .... I encountered the same scenario where I need to add multiple columns in one migration and this works for me.
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return Promise.all([
queryInterface.addColumn('table_1','column_5', {
type: Sequelize.ENUM,
defaultValue: 'unverified',
values: [
'unverified', 'updated'
]
}),
queryInterface.addColumn('table_1','column_6', {
type: Sequelize.BOOLEAN,
defaultValue: 0,
}),
queryInterface.addColumn('table_1', 'column_7', {
type: Sequelize.BOOLEAN,
defaultValue: 0,
})
])
},
down: (queryInterface, Sequelize) => {
return Promise.all([
queryInterface.removeColumn('table_1', 'column_5'),
queryInterface.removeColumn('table_2', 'column_6'),
queryInterface.removeColumn('table_3', 'column_7')
])
}
};
sathiz1993
on 26 Nov 2018
Hello .... I encountered the same scenario where I need to add multiple columns in one migration and this works for me.
'use strict'; module.exports = { up: (queryInterface, Sequelize) => { return Promise.all([ queryInterface.addColumn('table_1','column_5', { type: Sequelize.ENUM, defaultValue: 'unverified', values: [ 'unverified', 'updated' ] }), queryInterface.addColumn('table_1','column_6', { type: Sequelize.BOOLEAN, defaultValue: 0, }), queryInterface.addColumn('table_1', 'column_7', { type: Sequelize.BOOLEAN, defaultValue: 0, }) ]) }, down: (queryInterface, Sequelize) => { return Promise.all([ queryInterface.removeColumn('table_1', 'column_5'), queryInterface.removeColumn('table_2', 'column_6'), queryInterface.removeColumn('table_3', 'column_7') ]) } };
this works for me.
tolerious
on 29 Dec 2018
Related issues
OsoianMarcel
路
4Comments
khaledbnmohamed
路
3Comments
papb
路
5Comments
przbadu
路
3Comments
LoneWolfPR
路
5Comments
Most helpful comment
Hey guys. The migration functions are supposed to return promises. So if you want to do multiple things in a migration, just return a chained promised like this: