Building and Running SQL Queries with Knex.js
Tags: sql, javascript, node, database
Published: 2020-02-18 18:04:37

*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](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.

```bash
npm install knex --save
```

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

```bash
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

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

### PostgreSQL Example

```js
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

```js
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;

```json
"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.

```bash
npm run knex
```

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

```text
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;

```bash
npm run knex init
```

The knex file will be like that;

```js
// 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;

```bash
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;

```js
exports.up = function(knex) {
  
};

exports.down = function(knex) {
  
};
```

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

```js
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.


```bash
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.

```js
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;

```js
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.

```js
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;

```js
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**

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

**Select the todos using where**

```js
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.

```js
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.

```js
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".

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

#### Delete Method

We'll create a basic delete method.


```js
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;

```js
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;

```js
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;

```js
db.migrate.make('todos');
```

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

```js
db.migrate.latest();
```

That's all :)

Thank you for reading :)

## Resources

I used these resources while I preparing this article;

- [https://knexjs.org/](https://knexjs.org/)
- [Setting up an Knex SQLite3 Project from scratch](https://gist.github.com/guy-stapleton/baee8845c9378204c1176bdb8699969a)