Knex - What is it good for?

(absolutely everything)

• Abstraction
• Conditional Filters
• Security
• Database Engine Interoperability
• Migrations
• Seeds

Knex - What does it look like?

knex('pokemon').select().then(function(data) {
  console.log(data);
  process.exit(1);
});

pg.query("SELECT * FROM pokemon;", function(err, data) {
    console.log(data);
    process.exit(1);
});

pg

knex

Knex - What does it look like?

knex('pokemon').select().then(function(data) {
  console.log(data);
});

Table Name

Operation

Promise

Returned rows

Knex - Abstraction

const goldAndSilverStart = 152;
const goldAndSilverEnd = 251;

knex('pokemon').select()
.whereBetween('pokedex_number', [goldAndSilverStart, goldAndSilverEnd])
.then(function(data) {
  console.log(data);
  process.exit(1);
});

const goldAndSilverStart = 152;
const goldAndSilverEnd = 251;

pg.query("SELECT * FROM pokemon WHERE pokedex_number BETWEEN $1 AND $2", 
[goldAndSilverStart, goldAndSilverEnd], 
function(err, data) {
    console.log(data);
    process.exit(1);
});

pg

knex

PG - Conditional Filters

var games = {
    gold : {
        start: 152,
        end: 251
    },
    red : {
        start: 1,
        end: 151
    }
};

router.get("/api/pokemon", function(req, res, next) {

    var query = "SELECT * FROM pokemon ";
    var params = [];
    
    if (req.query) {
        if (req.query.game) {
            query += "WHERE pokedex_number BETWEEN ? AND ? ";
            params.push(games[req.query.game.start]);
            params.push(games[req.query.game.start]);
        }
        if (req.query.type) {
           if (req.query.game) {
               query += "AND ";
           } else {
               query += "WHERE ";
           }
           query += " (type1 = ? OR type2 = ?)";
           params.push(req.query.type);
           params.push(req.query.type);
        }
    
    }
    
    query += ";"    

    pg.query(query, params, function(err, data){
      req.send(data);  
    })

});

Knex - Conditional Filters

var games = {
    gold : {
        start: 152,
        end: 251
    },
    red : {
        start: 1,
        end: 151
    }
};

router.get("/api/pokemon", function(req, res, next) {

    var query = knex('pokemon').select()
    
    if (req.query) {
        if (req.query.game) {
            query.whereBetween('pokedex_number', [games[req.query.game.start], games[req.query.game.start]])   
        }
        if (req.query.type) {
            query.where({type1: req.query.type}).orWhere({type2: req.query.type})
        }
    
    }
    
    query.then(function(data) {
      res.json(data)
    });

});

Objectives

1. Define what a Querybuilder is and articulate the benefits
2. Set up a new project with Knex
3. Perform CRUD on a resource using Knex methods

Discuss:

What is a Querybuilder?

Where would we use querybuilders?

Where can we find out how to use knex?

Knex - Setup

$ npm install knex -g

$ express pokemon && cd pokemon

$ npm install

$ npm install --save pg knex

$ knex init

Install Globally (for cli tools)

Install locally too (for deploy)

Initialize and Configure

Init new app, enter directory

Install Express

Knex - Setup

module.exports = {
  development: {
    client: 'pg',
    connection: 'postgres://localhost:5432/pokemon',
    debug: true
  },

  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL
  }
};

Knex - Migrations

$ knex migrate:make add_sprites

Make a migration file

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

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

$ knex migrate:latest

Run the migration

Knex - Migrations - Update

$ knex migrate:make add_base_experience_to_pokemon

exports.up = function(knex, Promise) {
  return knex.schema.table('pokemon', function(table){
    table.string('base_experience');
  });
};

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

$ knex migrate:latest

Run the migration

Knex - Seeds

$ knex seed:make insert_sprites

Create a seed file

exports.seed = function(knex, Promise) {
  return Promise.join(
    // Deletes ALL existing entries
    knex('moves').del(),

    // Inserts seed entries
    knex('moves').insert({name: 'cut', type: "normal"}),
    knex('moves').insert({name: 'fly', type: "flying"}),
    knex('moves').insert({name: 'sand attack', type: "normal"}),
    knex('moves').insert({name: 'splash', type: "water"})

  );
};

$ knex seed:run

Run the migration

Objectives

Time for a CFU!

1. Define what a Querybuilder is and articulate the benefits
2. Set up a new project with Knex
3. Perform CRUD on a resource using Knex methods

Reflect

Look at the pokemon_knex repo - how are moves being inserted?

Look at how sprites are seeded - can you modify the moves to insert from the API?

Why would we use migrations instead of raw SQL?

Do This - 25 mins:

In the pokemon_knex example:

1. Create a migration to make a Pokemon table
2. Create a migration for a Berries table
3. Insert at least 5 berries

Objectives

1. Define what a Querybuilder is and articulate the benefits
2. Set up a new project with Knex
3. Perform CRUD on a resource using Knex methods

Check Your Understanding

Add a route to display all moves, called /moves, and get data from Knex

Add a /pokemon/search route

• ?name={a pokemon name}
• ?type={a type}
• ?name={a pokemon name}&type={a pokemon type}

30 mins