DEV Community

Cover image for Building and Running SQL Queries with Knex.js
Itachi Uchiha
Itachi Uchiha

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'
  }
});
Enter fullscreen mode Exit fullscreen mode

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'
  }
});
Enter fullscreen mode Exit fullscreen mode

SQLite3 Example

const knex = require('knex')

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

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",
},
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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

npm run knex init
Enter fullscreen mode Exit fullscreen mode

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'
    }
  },
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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) {

};
Enter fullscreen mode Exit fullscreen mode

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();
    });
};
Enter fullscreen mode Exit fullscreen mode

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

npm run knex migrate:latest
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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());
}
Enter fullscreen mode Exit fullscreen mode

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);
})
Enter fullscreen mode Exit fullscreen mode

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());
}
Enter fullscreen mode Exit fullscreen mode

We can create different queries using the following examples;

Select all the todos

selectData('todos')
.then(todos => {
    console.log(todos)
})
Enter fullscreen mode Exit fullscreen mode

Select the todos using where

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

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());
}
Enter fullscreen mode Exit fullscreen mode

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)
})
Enter fullscreen mode Exit fullscreen mode

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)
})
Enter fullscreen mode Exit fullscreen mode

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());
}
Enter fullscreen mode Exit fullscreen mode

We can use this method like that;

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

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();
});
Enter fullscreen mode Exit fullscreen mode

We'll create our migrations like that;

db.migrate.make('todos');
Enter fullscreen mode Exit fullscreen mode

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

db.migrate.latest();
Enter fullscreen mode Exit fullscreen mode

That's all :)

Thank you for reading :)

Resources

I used these resources while I preparing this article;

Top comments (0)