Foreign Keys + Indexes

exports.up = function(knex, Promise) {
  return knex.schema.table('sprites', function(table){
    table.integer('pokemon_id').references('pokemon.id').notNullable();
  });
};

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

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');
};

Objectives

1. Add the necessary code to create foreign keys and indexes.
2. Explain how joins are used in Knex

Discuss:

What does .references() do?

What does .index() do?

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

Joins in Knex

knex('pokemon')
.join('sprites', 'sprites.pokemon_id', 'pokemon.id')
.then(function(pokemon_and_sprites) {
    console.log(pokemon_and_sprites)
}); 

SELECT * FROM pokemon
JOIN sprites ON sprites.pokemon_id = pokemon.id;

one to many

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

Objectives

Time for a CFU!

1. Add the necessary code to create foreign keys and indexes.
2. Explain how joins are used in Knex

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';