Building and Running SQL Queries with Knex.js

The cover image took from the official Knex.js site

First of all, I'm so sorry for my bad English

What is Knex?

In short, Knex can be defined as a query builder for PostgreSQL, MSSQL, MySQL, MariaDB, SQLite3, Oracle and Amazon Redshift. Developer who uses ORM already knows what this query builder is.

The Knex' query builder inspired by the Laravel query builder.

Before we start, you should know Knex' GitHub repository.

Repository: https://github.com/knex/knex

Knex Works on Server and Browser

Knex works most efficiently with Node.JS on the server-side. However, if you want, it can work on the browser within the limits of WebSQL.

You shouldn't run browser-based SQL queries on the server. If you want to try to use it for your browser applications, a todo list application could be good.

What About TypeScript?

Knex has strong TypeScript support. TypeScript bindings will come when you install Knex' npm packages.

If you're using VSCode or similar editors, it works well.

Installation

There are two steps to install Knex. The first step you need to install Knex' itself.

npm install knex --save

In the next step, you have to choose a database engine. In this post, we will use SQLite3.

npm install pg --save
npm install sqlite3 --save
npm install mysql --save
npm install mysql2 --save
npm install oracledb --save
npm install mssql --save

In this step, you will not face any problem. Let's start coding by create a JavaScript file named index.js :)

First Configurations

There are different configs.

MySQL Example

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    user : 'DATABASE_USERNAME',
    password : 'DATABASE_PASSWORD',
    database : 'DATABASE_NAME'
  }
});

PostgreSQL Example

var knex = require('knex')({
  client: 'pg',
  version: '7.2',
  connection: {
    host : '127.0.0.1',
    user : 'DATABASE_USERNAME',
    password : 'DATABASE_PASSWORD',
    database : 'DATABASE_NAME'
  }
});

SQLite3 Example

const knex = require('knex')

const db = knex({
  client: 'sqlite3',
  connection: {
    filename: "./DATABASE_NAME.sqlite"
  }
});

We import knex to our project. However, we didn't create any migration or generate any schema. There are two types of methods to generate migrations. The first one, you create migrations from a JavaScript file. The second one is the Knex' CLI tool.

Edit package.json file

The scripts section will be like that;

"scripts": {
    "dev": "node index.js",
    "knex": "knex",
},

We will use dev to run the index.js file. We will use knex to run the Knex' CLI tool. Let's start with the CLI tool.

npm run knex

Using this command, we can see all the CLI commands. These are the CLI commands;

Commands:
  init [options]                          Create a fresh knexfile.
  migrate:make [options] <name>           Create a named migration file.
  migrate:latest [options]                Run all migrations that have not yet been run.
  migrate:up [<name>]                     Run the next or the specified migration that has not yet been run.
  migrate:rollback [options]              Rollback the last batch of migrations performed.
  migrate:down [<name>]                   Undo the last or the specified migration that was already run.
  migrate:currentVersion                  View the current version for the migration.
  migrate:list|migrate:status             List all migrations files with status.
  seed:make [options] <name>              Create a named seed file.
  seed:run [options]                      Run seed files.

First of all, we will create a knex file named knexfile.js using the following command;

npm run knex init

The knex file will be like that;

// Update with your config settings.

module.exports = {

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

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

Now, we have to create a migration. I mean, we will create a skeleton. We will use the below command;

npm run knex migrate:make todos

So, there is a task waiting to run. All the migrations will be in the migrations folder.

There will be methods named up and down in the migration file we've generated. For example;

exports.up = function(knex) {

};

exports.down = function(knex) {

};

Let's fill the blanks :P (We will not use the down)

exports.up = function(knex) {
    return knex.schema.createTableIfNotExists('todos', function(table) {
        table.increments();
        table.string('title');
        table.text('description');
        table.dateTime('start_date');
        table.dateTime('due_date');
        table.timestamps();
    });
};

Now, we will run the migrations using the below command.

npm run knex migrate:latest

Yay! We've generated a database file using the development environment. You can check the dev.sqlite3 file.

Creating CRUD Methods

We'll edit the index.js file using the knex file.

const knex = require('knex');

const knexFile = require('./knexfile').development;

const db = knex(knexFile);

Our configurations are ready for development.

Insert Method

We'll create a basic insert method;

const insertData = (tableName, data) => {

    return db(tableName)
            .insert(data)
            .then(resp => resp)
            .finally(() => db.destroy());
}

We will insert data into the database using the database instance.

The insertData method is waiting table name and data as a parameter. We can pass bulk data as an array or single data as an object.

insertData('todos', [
    {
        title: 'Write an article about Knex :)',
        description: 'This will be description',
        start_date: '2020-01-01 12:00',
        due_date: '2020-02-15 16:56',
    }
])
.then(insertedId => {
    console.log(insertedId);
})

If you pass bulk data, insertId will be an array. If you pass a single object, it will be integer value.

Select Method

We'll create a basic select method;

const selectData = (tableName, options = { fields: [], filteringConditions: [] }) => {

    const { fields, filteringConditions } = options

    return db(tableName)
            .select(fields)
            .where(builder => {
                filteringConditions.forEach(condition => {
                    builder.where(...condition)
                });

            })
            .then(data => data)
            .finally(() => db.destroy());
}

We can create different queries using the following examples;

Select all the todos

selectData('todos')
.then(todos => {
    console.log(todos)
})

Select the todos using where

selectData('todos', {
    filteringConditions: [
        ['id', '!=', 37],
        ['description', 'LIKE', '%123%']
    ]
})
.then(todos => {
    console.log(todos)
})

Update Method

We'll create a basic update method. For example, we have an ID like 38. We want to update this record's details. First of all, we'll create an update method.

const updateData = (tableName, options = { fields: {}, filteringConditions: [] }) => {

    const { fields, filteringConditions } = options

    return db(tableName)
            .where(builder => {
                filteringConditions.forEach(condition => {
                    builder.where(...condition)
                });

            })
            .update(fields)
            .then(data => data)
            .finally(() => db.destroy());
}

So, we have different cases.

Scenario 1:

In this example, we'll use where.

updateData('todos', {
    fields: {
        title: 'Updated',
    },
    filteringConditions: [
        ['id', '=', 38]
    ]
})
.then(updateData => {
    console.log(updateData)
})

Scenario 2:

In this example, we'll not use where. So all the todo titles will be "Updated".

updateData('todos', {
    fields: {
        title: 'Updated',
    }
})
.then(updateData => {
    console.log(updateData)
})

Delete Method

We'll create a basic delete method.

const deleteData = (tableName, options = { filteringConditions: [] }) => {

    const { filteringConditions } = options

    return db(tableName)
            .where(builder => {
                filteringConditions.forEach(condition => {
                    builder.where(...condition)
                });

            })
            .del()
            .then(data => data)
            .finally(() => db.destroy());
}

We can use this method like that;

deleteData('todos', {
    filteringConditions: [
        ['id', '=', 38]
    ]
})
.then(deleteData => {
    console.log(deleteData)
})

If you want to delete all the todos, you shouldn't use the filteringConditions

We created a simple CRUD architecture :P

Creating File-Based Migrations

I think you don't need to use file-based migrations. But we can create schemas like that;

db.schema.createTable('todos', (table) => {
    table.increments();
    table.string('title');
    table.text('description');
    table.dateTime('start_date');
    table.dateTime('due_date');
    table.timestamps();
});

We'll create our migrations like that;

db.migrate.make('todos');

And we need to run our migrations using the following code piece.

db.migrate.latest();

That's all :)

Thank you for reading :)

Resources

I used these resources while I preparing this article;

Blog