Countries & Cities

migration.js

'use strict';

exports.up = function(knex, Promise) {
  var table = knex.schema.table.bind(knex.schema);
  var createTable = knex.schema.createTable.bind(knex.schema);
  var sequence = [
    createTable.bind(null, 'countries', function(table) {
      table.increments('id').primary();
      table.string('name');
      table.timestamps();
    }),
    createTable.bind(null, 'cities', function(table) {
      table.increments('id').primary();
      table.string('name');
      table.integer('country_id').notNullable().references('id').inTable('countries').onDelete('cascade');
      table.timestamps();
    }),
    table.bind(null, 'countries', function(table) {
      table.integer('capital_id').unique().references('id').inTable('cities').onDelete('cascade');
    })
  ];
  return Promise.reduce(sequence, function(aggregate, fn) { return fn(); }, null);
};

exports.down = function(knex, Promise) {
  var table = knex.schema.table.bind(knex.schema);
  var dropTable = knex.schema.dropTable.bind(knex.schema);
  var sequence = [
    table.bind(null, 'countries', function(table) {
      table.dropColumn('capital_id');
    }),
    dropTable.bind(null, 'cities'),
    dropTable.bind(null, 'countries')
  ];
  return Promise.reduce(sequence, function(aggregate, fn) { return fn(); }, null);
};

models.js

var City;
var Country;

Country = exports.Country = DB.Model.extend({
  tableName: 'countries',
  hasTimestamps: true,
  cities: function() {
    return this.hasMany(City);
  },
  capital: function() {
    return this.belongsTo(City, 'capital_id');
  }
});

City = exports.City = DB.Model.extend({
  tableName: 'cities',
  hasTimestamps: true,
  isCapital: function() {
    return this.related('countryItGoverns').fetch()
      .then(function(country) { return !!country; });
  },
  countryItGoverns: function() {
    return this.hasOne(Country, 'capital_id');
  },
  country: function() {
    return this.belongsTo(Country);
  }
});