Knex.js tutorial

last modified July 7, 2020

Knex.js tutorial shows how to program databases in JavaScript with Knex.js.

Knex.js

Knex.js is a JavaScript query builder for relational databases including PostgreSQL, MySQL, SQLite3, and Oracle. It can be used with callbacks and promises. It supports transactions and connection pooling.

In this tutorial we work with MySQL.

Setting up Knex.js

First, we need to install Knex.js.

$ nodejs -v
v9.11.2

We use Node version 9.11.2.

$ npm init

We initiate a new Node application.

$ npm i knex mysql2

We install Knex.js and the MySQL driver. There are two drivers available: mysql and mysql2; we have chosen the latter.

Database version with Knex.js

In the first example, we figure out the version of MySQL.

const options = {
    client: 'mysql2',
    connection: {
        host: '127.0.0.1',
        user: 'user12',
        password: 's$cret',
        database: 'mydb'
    }
}

const knex = require('knex')(options);

knex.raw("SELECT VERSION()").then(
    (version) => console.log((version[0][0]))
).catch((err) => { console.log( err); throw err })
    .finally(() => {
        knex.destroy();
    });

The example returns the version of MySQL.

const options = {
    client: 'mysql2',
    connection: {
        host: '127.0.0.1',
        user: 'user12',
        password: 's$cret',
        database: 'mydb'
    }
}

These are the connection options for MySQL.

const knex = require('knex')(options);

We load Knex.js and provide the connection options.

knex.raw("SELECT VERSION()").then(
    (version) => console.log((version[0][0]))
).catch((err) => { console.log( err); throw err })
    .finally(() => {
        knex.destroy();
    });

With the raw() function, we execute the SQL statement. If the statement runs OK, we print the output. Otherwise, we log the error. In the end, we close the database connection with destroy().

$ node version.js 
TextRow { 'VERSION()': '5.7.22-0ubuntu0.16.04.1' }

This is the output.

Knex.js creating table

In the second example, we create a new database table.

const options = {
    client: 'mysql2',
    connection: {
        host: '127.0.0.1',
        user: 'user12',
        password: 's$cret',
        database: 'mydb'
    }
}

const knex = require('knex')(options);

knex.schema.createTable('cars', (table) => {
    table.increments('id')
    table.string('name')
    table.integer('price')
}).then(() => console.log("table created"))
    .catch((err) => { console.log(err); throw err })
    .finally(() => {
        knex.destroy();
    });

A new table is created with the Knex.js schema createTable() function. We define the schema to contain three columns: id, name, and price.

Knex.js inserting data

Next, we are going to insert some data into the created table.

const options = {
    client: 'mysql2',
    connection: {
        host: '127.0.0.1',
        user: 'user12',
        password: 's$cret',
        database: 'mydb'
    }
}

const knex = require('knex')(options);

const cars = [
    { name: 'Audi', price: 52642 },
    { name: 'Mercedes', price: 57127 },
    { name: 'Skoda', price: 9000 },
    { name: 'Volvo', price: 29000 },
    { name: 'Bentley', price: 350000 },
    { name: 'Citroen', price: 21000 },
    { name: 'Hummer', price: 41400 },
    { name: 'Volkswagen', price: 21600 },
]

knex('cars').insert(cars).then(() => console.log("data inserted"))
    .catch((err) => { console.log(err); throw err })
    .finally(() => {
        knex.destroy();
    });

We select the cars table with knex('cars) and insert eight rows with insert() method.

Knex.js selecting all rows

In the following example, we select all rows from the cars table.

const options = {
    client: 'mysql2',
    connection: {
        host: '127.0.0.1',
        user: 'user12',
        password: 's$cret',
        database: 'mydb'
    }
}

const knex = require('knex')(options);

knex.from('cars').select("*")
    .then((rows) => {
        for (row of rows) {
            console.log(`${row['id']} ${row['name']} ${row['price']}`);
        }
    }).catch((err) => { console.log( err); throw err })
    .finally(() => {
        knex.destroy();
    });

We select all rows with the select() function. This time we have chosen the table with the from() function. Then we go through the returned array of rows and print the three fields.

$ node select_cars.js 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

This is the output.

Knex.js restricting output with WHERE

The SQL WHERE clause is used to define the condition to be met for the rows to be returned.

const options = {
    client: 'mysql2',
    connection: "mysql://root:andrea@localhost:3306/mydb"
}

const knex = require('knex')(options);

knex.from('cars').select("name", "price").where('price', '>', '50000')
    .then((rows) => {
        for (row of rows) {
            console.log(`${row['name']} ${row['price']}`);
        }
    })
    .catch((err) => { console.log( err); throw err })
    .finally(() => {
        knex.destroy();
    });

The example returns cars whose price is higher than 50000.

const options = {
    client: 'mysql2',
    connection: "mysql://user12:s$cret@localhost:3306/mydb"
}

This time we have provided a connection URL.

knex.from('cars').select("name", "price").where('price', '>', '50000')

We have selected two columns with select() and added a WHERE clause with the where() function.

$ node select_where.js 
Audi 52642
Mercedes 57127
Bentley 350000

Three cars are more expensive than 50000.

Knex.js ordering rows

We can order data with orderBy() function.

const options = {
    client: 'mysql2',
    connection: {
        host: '127.0.0.1',
        user: 'user12',
        password: 's$cret',
        database: 'mydb'
    }
}

const knex = require('knex')(options);

knex.from('cars').select('name', 'price').orderBy('price', 'desc')
    .then((rows) => {
        for (row of rows) {
            console.log(`${row['name']} ${row['price']}`);
        }
    }).catch((err) => { console.log( err); throw err })
    .finally(() => {
        knex.destroy();
    });

The example selects all cars and orders them by price in descending order.

$ node order_cars.js 
Bentley 350000
Mercedes 57127
Audi 52642
Hummer 41400
Volvo 29000
Volkswagen 21600
Citroen 21000
Skoda 9000

This is the output.

In this tutorial, we have worked with the Knex.js library. We have created a few command line programs that interacted with MySQL.

List all JavaScript tutorials.