Foreign Keys + Indexes

exports.up = function(knex, Promise) {
  return knex.schema.createTable('pokemon_moves', function(table){
    table.increments();
    table.string('name');
    table.integer('pokemon_id').references('pokemon.id').index().notNullable();
    table.integer('move_id').references('moves.id').notNullable();
  });
};

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

Discuss:

What does .references() do?

What does .index() do?

Can we add a reference before the other table is created?

Joins in Knex

knex('users u')
.join('users_pokemon up', 'u.id', 'up.user_id')
.join('pokemon p', 'p.id', 'up.pokemon_id')
.then(function(my_pokemon) {
    console.log(my_pokemon)
}); 

SELECT * FROM users u
JOIN users_pokemon up ON up.user_id = u.id
JOIN pokemon p ON p.id = up.pokemon_id

many to many

Write:

Write the following queries in KNEX:

SELECT name, type1, type2 FROM pokemon 
WHERE name = 'bulbasaur';

SELECT p.name, m.name FROM pokemon p
JOIN pokemon_moves pm ON pm.pokemon_id = p.id
JOIN moves m ON m.id = pm.move_id
WHERE p.name = 'bulbasaur';