Intro to

Objectives

• Discuss the definition of a query builder
• Install and configure Knex in an Express project
• Write database queries using Knex

What is Knex?

• SQL query builder
• An abstraction of SQL
• JavaScript - you can write SQL queries with JS!

Write a Query!

    
    // SQL 

    SELECT * FROM users;


    // Knex.js

    knex('users')

A Few Ways to SELECT

    
    // SQL 

    SELECT * FROM users;


    // Knex.js

    knex('users')
    knex('users').select()

SELECT a subset

    
    // SQL 

    SELECT * FROM users WHERE name = 'Elana';


    // Knex.js

    knex('users').where('name', 'Elana')

INSERT

    
    // SQL 

    INSERT INTO cities (name) VALUES ('Denver');


    // Knex.js

    knex('cities').insert({name: 'Denver'})

UPDATE

    
    // SQL 

    UPDATE cities SET name = 'Chicago' where id = 1;

    // Knex.js

    knex('cities').where('id', 1).update({ name: 'Chicago'})

DELETE

    
    // SQL 

    DELETE FROM cities WHERE id = 1;

    // Knex.js

    knex('cities').where('id', 1).del()

What does a Knex query return ?

A promise!

    knex('cities')
        .where('id', 1)
        .first()
        .then(city => {
            console.log(city)
        })

How do figure out the specific syntax for using knex.js?

DOCS!

Make sure you choose the correct RDBMS (PostgreSQL) for code examples because there are some variations.

GETTING STARTED

       
    $ npm init                    // create package.json
    $ npm i -S express pg knex    // install pg, knex and express locally
    $ npm i knex -g               // install knex cli globally
    $ knex init                   // create knexfile.js

* pg is a client that connects Knex to PostgreSQL

knexfile.js

       
    module.exports = {

        development: {
            client: 'pg',
            connection: 'postgres://localhost/mydb'
        },

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

db/knex.js

    var environment = process.env.NODE_ENV || 'development'
    var config = require('../knexfile.js')[environment]
    module.exports = require('knex')(config)

Let's Build an Express Project with Knex!

Review Objectives

• Discuss the definition of a query builder
• Install and configure Knex in an Express project
• Write database queries using Knex