knex

James & Vaz has provided an incredible content with their notes from a cohorts. knex-notes knex-demo

ORMs/ODMs/Query builders

• They make your life easier and reduce copy-paste work
• They provide an abstraction layer to use from your code
• They may have limitations when it comes to complex application models

Why not just use string queries?

• Implementation lock in.
• No migrations.
• Poor security.

Implementation lock in

There are a lot of different SQL implementations.

• Postgres
• MSSQL
• MySQL
• MariaDB
• SQLite
• Oracle

It is the stated goal of knex to be a SQL query builder that supports multiple database implementations. In practice it is rare that you can ignore the implementation you are using. A lot of the complexity of creating connections is hidden.

Poor security

SQL Injections can be the result of unsantized user input making it's way into SQL queries. With SQL injection attacks someone could drop tables from your database, they could list out the contents of tables that contain private user information. knex helps us by handling the sanitization of user input at the library level.

No migrations

Migrations are version control for database schema.

We can use this to share the structure of the database between developers. When working on a project with multiple instances of the same db this will help you manage the rapid creation of these instances to suppor the current state of your app.

Say we wanted to create the urls table for TinyApp.

exports.up = function (knex) {
  return knex.schema.createTable("urls", (table) => {
    table.increments();
    table.string(‘short’);
    table.string(‘long’);
  });
};
exports.down = function (knex) {
  return knex.schema.dropTable('urls');
};

With that migration available any developer could run knex migrate:latest to create the table with those columns.

Knex Demo

Database Connection

There are a few ways that knex describes configuration. Similar to pg you can use a config object with the information.

var knex = require('knex')({
  client: 'pg',
  connection: {
    host : 'localhost',
    user : 'kjensen',
    password : '',
    database : 'w4d2'
  }
});

You can also use a connection string. This is a string that provides the same information but in a different format.

require('dotenv').config();

const knex = require('knex')({
  client: 'pg',
  connection: process.env.PG_CONNECTION_STRING,
  searchPath: 'knex,public'
});

.env

PG_CONNECTION_STRING=postgres://kjensen:@localhost:5432/w4d2

If we were to convert all of the TinyApp routes to use SQL queries instead then they would look something like this:

GET /login

select * from users;
knex.select().from("users");

GET /urls

select short, long from urls user_id = 1;
knex.select().from("urls").where({ user_id: 1});

POST /urls

insert into urls ("short", "long", "user_id") values ("abc", "http://www.lighthouselabs.ca/", 1);
knex.insert({ short: "abc", long: "http://www.lighthouselabs.ca/", user_id: 1 }).into("urls");

GET /urls/:short

select short, long from urls where short = "abc";
knex.select("short", "long").from("urls").where({ short: "abc" });

POST /urls/:short/edit

update urls set long="http://www.lighthouselabs.ca/" where short = "abc";
knex("urls").where({ short: "abc" }).update({ long: "http://www.lighthouselabs.ca/" });

POST /urls/:short/delete

delete from urls where short = "abc";
knex("urls").where({ short: "abc" }).del();

Getting the results

Above were all calls that could generate queries, but none of them actually made the queries we defined. For that we need to use a callback or Promise. You will be more familiar with callbacks at this point, but if you feel like you want a challenge, get used to using Promises.

As a callback

knex.select().from("users").asCallback((error, results) => {
  // handle error
  results.forEach((result) => {
    console.log(result.email);
  });
});

As a Promise

knex.select().from("users").then((results) => {
  results.forEach((result) => {
    console.log(result.email);
  });
}).catch((error) => {
  // handle error
});

Migrations

Before we create any migrations we need to initialize our knexfile.js configuration file. We can do this by running the knex command line tool knex init. You can configure it for the different environments you may have. The default knexfile.js has a development, staging and production configuration. In this exercise we will only use a development config.

module.exports = {
  development: {
    client: 'postgresql',
    connection: {
      database: 'w4d2',
      user:     'kjensen',
      password: ''
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }
};

In order to setup migrations we need to need to run the knex migrate:make <name> command.

We can call knex --env production migrate:latest if we want to specify which environment (from our knexfile) to use when applying the latest migrtion.

There were about four stages in TinyApp where the database schema had to change. Here are the four migrations that correspond.

Create urls table with id, short and long url.

20161120201923_create_urls_table.js

exports.up = function(knex) {
  return knex.schema.createTable("urls", (table) => {
    table.increments();
    table.string("short");
    table.string("long");
  });
};
exports.down = function(knex) {
  return knex.schema.dropTable("urls");
}

Create users table with id and username.

20161120202940_create_users_table.js

exports.up = function(knex) {
  return knex.schema.createTable("users", (table) => {
    table.increments();
    table.string("username");
  })
};
exports.down = function(knex) {
  return knex.schema.dropTable("users");
};

Add email and password, but remove the username for users.

20161120203540_add_email_password_to_users.js

exports.up = function(knex) {
  return knex.schema.table("users", (table) => {
    table.dropColumn("username");
    table.string("email");
    table.string("password");
  });
};
exports.down = function(knex) {
  return knex.schema.table("users", (table) => {
    table.dropColumns("email", "password");
    table.string("username");
  });
};

Add user_id to urls table so that users can have many urls.

20161120204226_add_user_id_to_urls.js

exports.up = function(knex) {
  return knex.schema.table("urls", (table) => {
    table.integer("user_id").unsigned();
  });
};
exports.down = function(knex) {
  return knex.schema.table("urls", (table) => {
    table.dropColumn("user_id");
  });
};

Rules for migrations

1. Do not modify a migration once it has been made available to another developer.
2. We can and should create a new migration for every change to the db.

Seeds

When you run knex seed:make <name> you will create a template file in the seeds directory.

users.js

exports.seed = function(knex, Promise) {
  return knex('users').del()
    .then(function () {
      return Promise.all([
        knex('users').insert({id: 1, email: 'first@user.com', password: '123456'}),
        knex('users').insert({id: 2, email: 'second@user.com', password: '123456'})
      ]);
    });
};

urls.js

exports.seed = function(knex, Promise) {
  return knex('urls').del()
    .then(function () {
      return Promise.all([
        knex('urls').insert({id: 1, short: 'abc', long: 'http://www.google.com/', user_id: 1}),
        knex('urls').insert({id: 2, short: 'def', long: 'http://www.duckduckgo.com/', user_id: 1}),
        knex('urls').insert({id: 3, short: 'ghi', long: 'http://www.bing.com/', user_id: 2}),
        knex('urls').insert({id: 4, short: 'jkl', long: 'http://www.yahoo.com/', user_id: 2}),
        knex('urls').insert({id: 5, short: 'mno', long: 'http://www.ask.com/', user_id: 2})
      ]);
    });
}

In order to seed the database you would run knex seed:run.