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.