Skip to content

Instantly share code, notes, and snippets.

@vaz
Last active September 29, 2017 01:23
Show Gist options
  • Save vaz/3a0248b81af1e27a684bf0c1f990b91e to your computer and use it in GitHub Desktop.
Save vaz/3a0248b81af1e27a684bf0c1f990b91e to your computer and use it in GitHub Desktop.
knex migrations: associating records

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/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/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
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},
])
});
});
};
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)))
}
Alice has a pet fuzzy
Alice has a pet wuzzy
Charlie has a pet frank
Bob has no pet.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment