knex migrations: associating records

1_README.md

Knex seeds: associating records

It's easiest if you're associating records between tables if you do it in one seed file. There are other ways but for now this is probably easiest.

PostgreSQL will generate your id fields for you, so don't put them in your seeds! Instead, take advantage of the returning chainable method from knex. See the below seed file and also http://knexjs.org docs.

migrations__1_create_users.js

// migrations/20170928131039_create_users.js

exports.up = function(knex, Promise) {
  return knex.schema.createTable('users', t => {
    t.increments();
    t.string('name');
  });
};

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('users');
};

migrations__2_create_pets.js

// migrations/20170928131212_create_pets.js

exports.up = function(knex, Promise) {
  return knex.schema.createTable('pets', t => {
    t.increments();
    t.string('name');
    t.integer('owner_id').references('users.id');
  });
};

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('pets');
};

seeds__1_pets_and_users.js

// seeds/1_pets_and_users.js

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  // Pets has a foreign key into users, so delete them first
  // (Usually it's the opposite order they were created in migration)
  return knex('pets').del()
    .then(() => knex('users').del())
    .then(() => {
      // Inserts seed entries
      // NOTE: not including `id`, let postgresql generate it.
      // The returning('id') chained method causes the promise
      // to resolve to an array of ids (matching the array of
      // objects passed to insert).
      return knex('users').returning('id').insert([
        {name: 'Alice'},
        {name: 'Bob'},
        {name: 'Charlie'},
      ]).then(ids => {
        const [idAlice, idBob, idCharlie] = ids;

        // Now that we have users, insert some pets.
        return knex('pets').insert([
          {name: 'Fuzzy', owner_id: idAlice},
          {name: 'Wuzzy', owner_id: idAlice},
          {name: 'Frank', owner_id: idCharlie},
        ])
      });
    });
};

test_code.js

const NODE_ENV = process.env['NODE_ENV'] || 'development';
// knexfile not included (DIY)
const knex = require('knex')(require('./knexfile')[NODE_ENV]);


// Query: select "pets"."name" as "pet", "users"."name" as "owner"
//   from "users" left join "pets"
//   on "users"."id" = "pets"."owner_id"
knex('users').leftJoin('pets', 'users.id', 'pets.owner_id')
  .select('pets.name AS pet', 'users.name AS owner')
  .map(({owner, pet}) => {
    if (pet) {
      console.log(`${owner} has a pet ${pet}`);
    } else {
      console.log(`${owner} has no pet.`);
    }
  })
  .catch(console.error)
  .then(() => process.exit(0));

// I did a funny thing above. `map` instead of `then`. This isn't
// normally something you can do with a standard Promise, but
// knex uses a different implementation called bluebird.js and
// exposes a few extra methods. `map` on a bluebird promise,
// if it resolves to an array (or other iterable thing), is like
// Promise's `then` combined with Array's `map`. You could even implement
// it yourself something like this:
Promise.prototype.map = function(cb) {
  this.then(items => Promise.all(items.map(cb)))
}

test_output

Alice has a pet fuzzy
Alice has a pet wuzzy
Charlie has a pet frank
Bob has no pet.