Embedded Files
There are situations when new changes appear for database. To manage these changes to the database we use migrations. Existing database state can be updated to new state by migration files and reverted back.
Preconditions:
PostgreSQL
pgAdmin with my local database
Sequelize CLI
Visual Studio Code
Create first model and migration. For this run the following command:
$ npx sequelize-cli model:generate --name user –attributes firstName:string,lastName:string,email:string
where model:generate - command to create model; --name user - name of model; firstName, lastName - required attributes.
After completing this command we get:
created new model ‘user’ in folder ‘models’
created new mirgation ‘XXXXXXXXXXXXXX-create-user.js’ in folder ‘migrations’
Look at these new files: we have model of future table that will be named ‘user’ and will have columns with types string: firstName,lastName and email.
// I replaced table name ‘users’ with ‘user’ and saved changes
We can run our first migration. For this use the following command:
$ npx sequelize-cli db:migrate
Login to our local database
See "SequelizeMeta" table: our migration appears here)
Also I created new table "user" with correct columns:
Now we can populate created table "user" with some data. For this we will use Seeders.
Firstly run command:
$ npx sequelize-cli seed:generate --name test-user
As we see created new file ‘20200406114244-test-user.js’ in folder 'seeders':
Edit this file to enter test data, save:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('user', [{
firstName: '1user',
lastName: '1test',
email: '1user@1test.com',
createdAt: new Date(),
updatedAt: new Date()
}], {});
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('user', null, {});
}
};
Run seed to commit data to database using the following command:
$ npx sequelize-cli db:seed:all
Return to our database and see data of table "user": test user added as first item:
To cancel all seeders we can use command:
$ npx sequelize-cli db:seed:undo:all
DB: refresh data to see that table "user" cleared:
For using more expanded types in models we can use migration-skeleton
Firstly create typical migration file, run command:
$ npx sequelize-cli migration:generate --name migration-skeleton
that will create xxx-migration-skeleton.js in your migration folder:
Edit this file, we want our new table to have columns of two types: string and integer:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('person', {
name: Sequalixe.STRING,
isBetaMember: {
type:Sequelize.BOOLEAN,
defaultValue: false,
allowNull: false
},
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('person');
}
};
Run migration:
$ npx sequelize-cli db:migrate
Refresh database, running of migration-skeleton is tracked on "SequelizeMeta" table:
Created new table ‘Person’ with columns of two data types such as STRING or INTEGER:
Using the following as an example we do a migration that performs two changes in the database
// previously generate another migration-skeleton file
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.sequelize.transaction((t) => {
return Promise.all([
queryInterface.addColumn('person', 'petName', {
type: Sequelize.STRING
}, { transaction: t }),
queryInterface.addColumn('person', 'favoriteColor', {
type: Sequelize.STRING,
}, { transaction: t })
])
})
},
down: (queryInterface, Sequelize) => {
return queryInterface.sequelize.transaction((t) => {
return Promise.all([
queryInterface.removeColumn('person', 'petName', { transaction: t }),
queryInterface.removeColumn('person', 'favoriteColor', { transaction: t })
])
})
}
};
Our table ‘person’ updated with two new columns: petName and favoriteColor
The following example we can use if we want to combine two tables by a foreign key:
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('person', {
name: Sequelize.STRING,
isBetaMember: {
type: Sequelize.BOOLEAN,
defaultValue: false,
allowNull: false
},
userId: {
type: Sequelize.INTEGER,
references: {
model: {
tableName: 'user'
}
key: 'id'
},
allowNull: false
},
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('person');
}
}
Create new seed (use incorrect userId):
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('person', [{
name: '1test',
isBetaMember: true,
userId: 2
}], {});
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('person', null, {});
}
};
Run this seed
$ npx sequelize-cli db:seed --seed 20200406150356-test-person.js
We get an error:
If we update seed file with correct userId, migration will run successfully and ‘’person’ table will be populated with correct item:
Page updated
Google Sites
Report abuse