DEV Community

Cover image for KNEX.JS SQL Query builder for Nodejs
Raqeeb Taiwo
Raqeeb Taiwo

Posted on • Updated on

KNEX.JS SQL Query builder for Nodejs

Getting Started with KnexJs for SQLs database, firstly install all the important dependencies like express e.t.c for your project.
For the installation of knex.js:

INSTALLATION

$ npm install knex --save

Enter fullscreen mode Exit fullscreen mode

Then add one of the following types of SQL you chose to use:

$ npm install pg  #For PostgreSQL

$ npm install pg-native  #PostgreSQL with native C++ libpq

$ npm install @vscode/sqlite3 # required for sqlite

$ npm install better-sqlite3

$ npm install mysql #For MySQL

$ npm install mysql2 #For MySQL2

$ npm install oracledb #For oracledb

$ npm install tedious
Enter fullscreen mode Exit fullscreen mode

Then the next thing you want to do is generate a knexfile in your config folder for configuring your SQL database by using:

$ npx knex init
Enter fullscreen mode Exit fullscreen mode

This will generate a file called knexfile.js and it contains:

knexfile.js

// Update with your config settings.

/**
 * @type { Object.<string, import("knex").Knex.Config> }
 */
module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    }
  },

  staging: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  production: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }

};

Enter fullscreen mode Exit fullscreen mode

Here you can edit this to suit your configuration, this is my own settings below:

// Update with your config settings.

/**
 * @type { Object.<string, import("knex").Knex.Config> }
 */


module.exports = {
  development: {
    client: 'mysql',
      connection: {
        user: 'Abdulraqeeb',
        password: null,
        database: 'test'
    },
    pool: {
        min: 2,
        max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }

};

Enter fullscreen mode Exit fullscreen mode

After setting your configuration, you need to link your configuration with knex by creating another file in your config folder called db.js or config.js, inside here you import knex and connect it with your configuration as shown below:

config.js or db.js

const knex = require('knex');
const knexfile = require('./knexfile');

const db = knex(knexfile.development);

module.exports = db; 
Enter fullscreen mode Exit fullscreen mode

Then you need to create a folder called migrations inside your config folder and then generate/write migrations for API or web applications. To generate the migration file(s), use the command below:

$ npx knex migrate:make init --migrations-directory config/migrations 
#you can change "init" to "user" or whatever table name you want to create in your database

Enter fullscreen mode Exit fullscreen mode

This will generate a file like this:

20220319104333_users.js

Inside it you will find:

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */

exports.up = function(knex) {

  };

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */

exports.down = function(knex) {

};

Enter fullscreen mode Exit fullscreen mode

"exports.up" literally means add to the database this "table's schema"
"export.down" means drop or delete this "table's schema"

Then you can write your table schema like this:

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */

exports.up = function(knex) {
    return knex.schema
        .createTable('users', table => {
            table.string('id', 10).notNullable().unique().primary();
            table.string('email').notNullable().unique();
            table.string('fullname').notNullable();
            table.string('username').notNullable().unique();
            table.string('password').notNullable();
            table.bigInteger('money').notNullable().defaultTo(0);
            table.timestamp('createdAt').defaultTo(knex.raw('CURRENT_TIMESTAMP'));
            table.timestamp('updatedAt').defaultTo(knex.raw('CURRENT_TIMESTAMP'))
        });


};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.down = function(knex) {
    return knex.schema.dropTable('users');
};

Enter fullscreen mode Exit fullscreen mode

You can find how to use the datatypes on knexjs here.

After this, it's time to migrate it to the SQL you are using, in my case I am using MySQL,
so to make it easier to migrate, you can include the "migrate" script in your

package.json file

 "scripts": {
    "test": "jest",
    "test:watch": "jest --watch",
    "dev": "nodemon app.js",
    "start": "node app,js",
    "migrate": "npx knex migrate:latest --knexfile config/knexfile.js"
  }

Enter fullscreen mode Exit fullscreen mode

And using the terminal to run

$ npm run migrate
#or
$ npx knex migrate:latest --knexfile config/knexfile.js
Enter fullscreen mode Exit fullscreen mode

This will create the tables and also knex_migration and knex_migration_lock

image.png

The knex_migration shows a table that contains each schema you migrated and the knex_migration_lock shows the schema's access locked or not locked.
You can find more about knex migrations here.

Now it's time to use our knex in our controllers, to fetch and insert data into our database. Here compare to other ORMs like sequelize or TypeORM etc, we won't use the same syntax like findByPk or any of such, But will be using the select, insert, where, del, update and etc. to perform different functions. Find more here.
I will explain some of this with examples;

exports.getUsers = async(req, res) => {
    try {
        await db('users')
            .select({
                id: 'id',
                fullname: "fullname",
                username: "username",
                email: "email",
                money: "money"
            })
            .then((user) => {
                if(user) {
                    res.status(200).json(user)
                } else{
                    res.status(404).json("No user found")
                }
            })
    } catch (error) {
        console.error(error)
        return res.status(500).json({
            status: false,
            msg: "an error occured"
        });
    }
}
Enter fullscreen mode Exit fullscreen mode

In the example above the** SELECT** method is used to define the parameter we need to send to the client from the database, in this case, we want to send the information of all users in the database, note that this is an asynchronous function meaning it has a callback function (".then()") to give the results/response after performing the function, it is very important to add a callback function with it, without that the function won't work.
This function will list the users in an array of objects:

[
    {
        "id": "_bnKpvCKaS",
        "fullname": "admin  test",
        "username": "admin",
        "email": "admin@gmail.com",
        "money": 295000
    },
    {
        "id": "pO5bMfU1yV",
        "fullname": "admin2  test",
        "username": "admin2",
        "email": "admin2@gmail.com",
        "money": 20000
    }
]
Enter fullscreen mode Exit fullscreen mode

For a case where one of the user's information is needed, ".where" should be added to the function as in:

exports.getUser = async(req, res) => {
 const userinfo = req.user // for cases where you use authorization for logging in
    try {
        await db('users')
            .select({
                id: 'id',
                fullname: "fullname",
                username: "username",
                email: "email",
                money: "money"
            })
            .where({
                username: userinfo.username
           })
            .then((user) => {
                if(user[0]) {
                    res.status(200).json(user[0])
                } else{
                    res.status(404).json("No user found")
                }
            })
    } catch (error) {
        console.error(error)
        return res.status(500).json({
            status: false,
            msg: "an error occured"
        });
    }
}
Enter fullscreen mode Exit fullscreen mode

Here you notice the addition of ".where" and also using "user[0]", this is because the response comes in arrays. This way you will get:

    {
        "id": "_bnKpvCKaS",
        "fullname": "admin test",
        "username": "admin",
        "email": "admin@gmail.com",
        "money": 295000
    },

Enter fullscreen mode Exit fullscreen mode

For INSERT, insert is used to get data into the database

       await db('users')
            .insert({
                id: nanoid(10),
                fullname: fullname,
                username: username,
                email: email,
                money: 0,
                password: hashedPass,
                pin: hashedPin,
                 })
                 .then((user)=>{
                  return res.status(201).json({
                  status: true,
                  mesaage: "user Account created successfully"
                    })
               });
Enter fullscreen mode Exit fullscreen mode

For UPDATE

      await db('users')
        .where({
                username: userinfo[0].username
            })
         .update(
                req.body
            )
         .catch((err) => console.log(err));
Enter fullscreen mode Exit fullscreen mode

For DELETE

   await db('users')
       .where({
             username: username
         })
       .del()
       .catch((err) => console.log(err));
Enter fullscreen mode Exit fullscreen mode

Note: Without callbacks, these functions won't work.

Thank you for reading my article, I hope I was able to help someone with it. If there is anything you want me to add, feel free to reach out.

Top comments (0)