Create a Phone Numbers Table

In this section we’ll make a phone_numbers table and populate it with some data. This new table will be related to the people table that we made previously, so the migration will be somewhate more involved.

We’ll start by generating a new migration script.

formn m create create_table_phone_numbers

Then fill in the SQL, which creates a new phone_numbers table and relates it to people using a personID column and constraint.

'use strict';

module.exports = {
  /**
   * Run the migration.
   */
  up(dataContext) {
    const sql = `
      CREATE TABLE phone_numbers (
        phoneNumberID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        phoneNumber VARCHAR(255) NOT NULL,
        type VARCHAR(255),
        personID INT NOT NULL,

        CONSTRAINT fk__phone_numbers__personID__people
          FOREIGN KEY (personID) REFERENCES people(personID)
          ON DELETE CASCADE)`;
    const params = {};

    console.log(sql);

    return dataContext
      .getExecuter()
      .query(sql, params); 
  },

  /**
   * Bring down a migration.
   */
  down(dataContext) {
    const sql    = `DROP TABLE phone_numbers`;
    const params = {};

    console.log(sql);

    return dataContext
      .getExecuter()
      .query(sql, params); 
  }
};

Populate the Phone Numbers Table

As usual, start by making a new migration script.

formn m create insert_phone_numbers

Now let’s add some dummy phone numbers for people. We’ll add three phone numbers for “Joe Shmo” and one for “Rand al’Thor.” It may be tempting to hard code the personID for each new record, but that’s a bad idea. Migrations should never depend on generated identifiers because identifiers may be different between environments. For example, if you bring the insert_people migration up and down a few times the identifiers will obviously continue to increment. That in mind, we’ll find each person by name and then add associated phone numbers.

'use strict';

const people = [
  {
    firstName: 'Joe',
    lastName: 'Shmo',
    phoneNumbers: [
      {phoneNumber: '530-307-8810', type: 'mobile'},
      {phoneNumber: '916-200-1440', type: 'home'},
      {phoneNumber: '916-293-4667', type: 'office'}
    ]
  },
  {
    firstName: 'Rand',
    lastName: 'al\'Thor',
    phoneNumbers: [
      {phoneNumber: '666-451-4412', type: 'mobile'}
    ]
  },
];

module.exports = {
  /**
   * Run the migration.
   */
  async up(dataContext) {
    const results = [];

    for (let i = 0; i < people.length; ++i) {
      // Find the personID by firstName and lastName.
      const selPerson = `
        SELECT  p.personID
        FROM    people p
        WHERE   p.firstName = :firstName
          AND   p.lastName = :lastName`;
      const person = people[i];

      console.log(selPerson);
      console.log(person);

      const [rows] = await dataContext
        .getExecuter()
        .query(selPerson, person);
      const personID = rows[0].personID;

      // Insert each phone number.
      for (let j = 0; j < person.phoneNumbers.length; ++j) {
        const insPhone = `
          INSERT INTO phone_numbers (personID, phoneNumber, type)
          VALUES (:personID, :phoneNumber, :type)`;
        const phone = person.phoneNumbers[j];

        phone.personID = personID;

        console.log(insPhone);
        console.log(phone);

        const result = await dataContext
          .getExecuter()
          .query(insPhone, phone);

        results.push(result);
      }
    }

    return results;
  },

  /**
   * Bring down a migration.
   */
  async down(dataContext) {
    const results = [];

    // All phone numbers in a flattened array.
    const phones = people
      .reduce((phones, person) => phones
        .concat(person.phoneNumbers), []);

    for (let i = 0; i < phones.length; ++i) {
      const sql = `
        DELETE
        FROM   phone_numbers
        WHERE  phoneNumber = :phoneNumber
          AND  type = :type`;
      const params = phones[i];

      console.log(sql);
      console.log(params);

      const result = await dataContext
        .getExecuter()
        .query(sql, params);

      results.push(result);
    }

    return results;
  }
};

Run the Migrations

Use the up sub-command to run the two new migrations.

formn m up

You should experiment with the up and down sub-commands a bit to ensure you understand how they work. All of the example migrations created in this section are available in the formn-example repository in the migrations directory.

In the next section we’ll use the Formn CLI to generate entities for the people and phone_numbers tables.