Objectives

• Define what a query builder is and what some of the benefits are to using one

• Understand how to set up a project with knex and the knex CLI

• Write migrations to create tables

• Perform CRUD on a resource using Knex methods

What is a Query Builder?

A software library that allows the use of objects and methods to write SQL queries in an database agnostic way.

Benefits of Query Builder?

• Avoid long concatenated strings in code.

• Build complex SQL statements programmatically

• Automatically quotes table names and columns to prevent conflict with SQL reserved words and special characters.

• Automatically escapes parameters to reduce risk of SQL injection attacks

• Provides DB abstraction, simplifying migration to different DB platforms

Whiteboards

From SQL to Knex

Requirements

• knex module
• knexfile.js

knex

knexfile.js

Recipe

• List of ingredients
• Directions
• Duration

Migrations

exports.up = function(knex, Promise) {
  return knex.schema.createTable('albums', function(table){
    table.increments();
    table.string('artist');
    table.string('name');
    table.string('genre');
    table.integer('stars');
    table.boolean('explicit');
  })
};

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