A closer look at Knex.js

A closer look at Knex.js
A closer look at Knex.js

Before going further, let’s first ask a few questions—

What is Knex, how can this be used in Node.js applications and what are the pros of using it?

>> So, what is Knex.js?

Knex is a technique that is used to build queries. It supports various databases like Postgres, MySQL, SQLite, Oracle, and some others as well. It provides both callbacks and promise interface. It provides connection pooling and standardized responses.

It is an efficient tool for node.js application because it supports raw SQL queries.

Let’s have a look at a very simple query into a table(‘Student’) for a certain value in a specific column.

knex(‘Student’).where(id>10).limit(10).offset(5).orderby(name,’desc’);

It will be converted to the following SQL query by knex.

Select * from Student where id>0 limit=10 offset=5 order by name ‘desc’;

In this blog, I’ll be showing step by step how to work with knex.js in Node Apps. But make sure your node version is 8 or 8+.

>> Installing Knex

You can simply install it from npm.

npm install knex –save

I will be using Postgres DB so I have already installed “pg” but you can use any of the database i.e.(SQLite, MySQL, Oracle dB or MsSQL).

npm install pg;

>> Initializing knex module

The Knex module is itself a function. The client and connection parameters are required to determine which client and database configuration will be used with the library.

And configuration function can be passed in two ways whether you can write directly or create an object and then pass it there.

I’m going to create an object with the required parameters.

const connector ={client : ‘pg’,
connection : “postgres://abcdef:******@host:4000/Mypg”,
searchPath: [‘knex’, ‘public’]
}
const pg = require(‘knex’)(connector);

The database version can be added in knex configuration when you use the PostgreSQL database to connect with any other database.

const connector = {client: `pg`, version: ‘8.3’,
connection: {host : ‘127.0.0.1’,user : ‘abcdef’,
password : ‘********’,
database : ‘Mypgdb’}
}
const pg = require(‘knex’)(connector);

Applications that use Knex

I have given some common applications which we mostly perform but apart from this, you can write some other complex queries very easily by using Knex.

> Create Table

1. Create With Raw Query

const Query = `CREATE TABLE Student(
id SERIAL,
name VARCHAR(20),
email VARCHAR(50),password VARCHAR(20));
`knex.raw(Query).then(() =>{
console.log(“Table created successfully!!”)
}).catch(()=.{
console.log(“Error occurred in table creation!!”)
});

2. Create Without Raw Query

knex.schema.createTable(‘Student’, (column) => {
column.increments(‘id’)
column.string(‘name’)
column.string(‘email’)
column.string(‘password’)
}).then(() =>{
console.log(“Table created successfully!!”)
}).catch(()=>{
console.log(“Error occurred in table creation!!”)});

> Inserting data

1. Insert with Raw Query

const Query = `INSERT INTO Student(name,email,password)VALUES
(‘abc’,’abc@gmail.com’,’****#***’),
(‘def’,’def@gmail.com’,’****@***’));
`knex.raw(Query).then(() =>{
console.log(“Inserted data into table successfully!!”)
}).catch(()=>{
console.log(“Error in insertion data into table!!”)
});

2. Insert Without Raw Query

const dataToBeInserted = [{
name : ‘abc’,
email : ‘abc@gmail.com’,
password : ‘****#***’
},
{name : ‘def’,email : ‘def@gmail.com’,
password : ‘****@***’
}
];
knex(‘Student’).insert(dataToBeInserted) ).then(() =>{console.log(“Inserted data into table successfully!!”)
}).catch(()=>{
console.log(“Error in insertion data into table!!”)});

> Select Data

1. Select Data With Raw Query

const Query=`SELECT * FROM Student WHERE email=’abc@gmail.com’;`knex.raw(Query).then((data) =>{console.log(data)
}).catch(()=>{console.log(“No such data exists!!”)});

2. Select Data Without Raw Query

const whereClause = {email:’abc@gmail.com’};
knex(‘Student’).where(whereClause).then((data) =>{
console.log(data)
}).catch(()=>{
console.log(“No such data exists!!”)
});

> Update Table

1. Update With Raw Query

const updateQuery = `UPDATE Student SET name=’xyz’ WHERE email=’abc@gmail.com’;`knex.raw(updateQuery).then(() =>{console.log(“Updated successfully!!”)}).catch(()=>{console.log(“Error occurred in Updation!!”)});

2. Update Without Raw Query

const whereClause = {email : ‘abc@gmail.com’}
const updateData = {name : ‘xyz’
}
knex(‘Student’).where(whereClause).update(updateData).then(() =>{console.log(“Updated successfully!!”)
}).catch(()=>{
console.log(“Error occurred in Updation!!”)});

> Delete Row

1. Delete With Raw Query

const Query = `DELETE FROM Student WHERE email=’xyz@gmail.com’;`
knex.raw(Query).then(() =>{
console.log(“Data deleted successfully!!”)}).catch(()=>{
console.log(“Error occurred in Deletion!!”)});

2. Delete Without Raw Query

const whereClause = {email:’xyz@gmail.com’};
knex(‘Student’).where(whereClause).del().then(() =>{
console.log(“Data deleted successfully!!”)
}).catch(()=>{
console.log(“Error occurred in Deletion!!”)});

Some of the key benefits of using knex.js:

  1. It supports Multiuser
  2. Allows partial queries
  3. Flexible to use
  4. Write once and connect any engine
  5. Fast
  6. Supports Migrations and seeds
  7. Free of cost
  8. Allows migrating between different SQL dialects

Conclusion

Knex.js query builder can be used for the queries that need to be generated dynamically while raw SQL can be used to deal with the larger and complex queries.

We can divide a large query into number of chunks and each chunk can be managed separately as explained in the example above, that is, by creating a Query variable separately and then passing it into raw query.

Author — Ajeet Kumar Maurya, DLT Labs

About Ajeet Kumar Maurya: Ajeet is working as a software engineer at DLT Labs. He is a part of the DL Certify team and is skilled in Node.js, PSQL, JS, MongoDB, Postgres.

Written by

DLT Labs is a global leader in Distributed Ledger Technology and Enterprise Products. To know more, head over to: https://www.dltlabs.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store