a blog for those who code

Monday 4 April 2016

Access Data using Knex in Node.js

In this post we will be discussing about accessing data using Knex in Node.js. Knex is a data access library which has great features like Connection Pooling, Database Migrations, Data Seeding, Protection against SQL Injection, Supports Transactions etc.

According to Knex.js, it is a "batteries included" SQL query builder for Postgres, MySQL, MariaDB, SQLite3 and Oracle designed to be flexible, portable and fun to use. Knex can be also be used as a query and schema builders, and it also standardize's the code you write, that means you will write your code once and Knex will handle it for different databases.

Install Knex - You can install Knex using the command npm install knex

Install Database - 

PostgreSQL : npm install pg
SQLite : npm install sqlite3
MySQL : npm install mysql
MariaDB : npm install mariasql
Oracle : npm install strong-oracle

We need a database to work with, so we will create a database using SQLite. You can learn more about sqlite in Creating SQLite Database in Node.js.

Next we have to add configuration object which will hold the settings to connect to the database. First we will see what all the Config Options we have :

client - The client parameter is required and determines which client adapter will be used with the library.
connection - This can be a connection string or connection object.
debug - Its a boolean flag to initialize configuration with debugging.
pool - It initializes a connection pool using the Pool2 library with min and max value (Default min is 2 and max is 10).
acquireConnectionTimeout - It is used to determine how long knex should wait before throwing a timeout error when acquiring a connection is not possible (Deafult 60000ms).

Since we will be using sqlite database our configuration client will be sqlite3. The code for Knex configuration object is shown below. One thing to note that in sqlite client only one configuration pool is allowed.

var knexConfig = 
{
  client: "sqlite3",
  connection: 
  {
    filename: "./text.db"
  }
}
var knex = require("knex")(knexConfig);
knex.destroy();

The query to get all the results form text.db database using Knex is shown below :

var knex = require("knex")(knexConfig);
knex.select("*").from("test").asCallback(function(err, values) {
  if(err) {
    console.log(err);
  } else {
    console.log(values);
  }
  knex.destroy();
});

The above code will give output as an array of results and inside the array there is an object of each result.


When running the above code you might get a warning saying "Knex:warning - sqlite does not support inserting default values. Set the 'useNullAsDefault' flag to hide this warning". To fix this you need to change your configuration object and add a Boolean flag useNullAsDeafult to true.

var knexConfig = {
  client: "sqlite3",
  connection: {
    filename: "./text.db"
  },
  useNullAsDefault: true
}

In our next post we will be exploring more about accessing data using Knex in Node.js. Please Like and Share the CodingDefined.com Blog, if you find it interesting and helpful.

No comments:

Post a Comment