Create a People Table
To start, let’s generate a new migration script.
formn m create create_table_people
That will create a new migrations
directory with a single script in it. All
we have to do is fill out the SQL in the up
and down
methods.
'use strict';
module.exports = {
/**
* Run the migration.
*/
up(dataContext) {
const sql = `
CREATE TABLE people (
personID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
firstName VARCHAR(255),
lastName VARCHAR(255),
createdOn TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)`;
const params = {};
console.log(sql);
return dataContext
.getExecuter()
.query(sql, params);
},
/**
* Bring down a migration.
*/
down(dataContext) {
const sql = `DROP TABLE people`;
const params = {};
console.log(sql);
return dataContext
.getExecuter()
.query(sql, params);
}
};
up
creates the table, and down
drops it. Simple.
Populate the People Table
Now let’s make a migration to populate the people
table with some dummy data.
formn m create insert_people
This migration is less trivial than the last. We’ll loop over an array of people, inserting one at a time and keeping track of the results. To keep things simple, we’ve made the methods async.
'use strict';
const people = [
{firstName: 'Joe', lastName: 'Shmo'},
{firstName: 'Rand', lastName: 'al\'Thor'},
{firstName: 'Holly', lastName: 'Davis'},
{firstName: 'Jenny', lastName: 'Mather'},
];
module.exports = {
/**
* Run the migration.
*/
async up(dataContext) {
const results = [];
for (let i = 0; i < people.length; ++i) {
const sql = 'INSERT INTO people (firstName, lastName) VALUES (:firstName, :lastName)';
const params = people[i];
console.log(sql);
console.log(params);
const result = await dataContext
.getExecuter()
.query(sql, params);
results.push(result);
}
return results;
},
/**
* Bring down a migration.
*/
async down(dataContext) {
const results = [];
for (let i = 0; i < people.length; ++i) {
const sql = 'DELETE FROM people WHERE firstName = :firstName AND lastName = :lastName';
const params = people[i];
console.log(sql);
console.log(params);
const result = await dataContext
.getExecuter()
.query(sql, params);
results.push(result);
}
return results;
}
};
The down
method is pretty much the same as the up
, but it deletes records
rather than inserting.
Run the Migrations
Run the two migrations by using the up
sub-command.
formn m up
Formn will run the two scripts sequentially, one after the other, in order.
To bring down the latest migration, use the down
sub-command. This command
is especially useful during development when the database schema is subject to
change.
formn m down
The migrations presented in this section are both available in the formn-example repository in the migrations folder. There are a couple more migrations covered in the following section, and they’re a bit more advanced.