Interacting with Database in Node using Knex

migration, seeding, querying, and why I use it

Image for post
Image for post
Photo by Matthew Cabret on Unsplash

Recently I’m still focusing on drilling into database and everything related. I started with Flywaydb and database migration. But Flywaydb is only on migration. In order to have a thorough exploration, I decided to try using a query builder to do everything from migration, seeding, to queries and interaction with API — and I decided to go for Knex.js.

Per the documentation, Knex.js is a “batteries included” SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, and Oracle designed to be flexible, portable, and fun to use. It features :

Here we are using postgresql and thus using pg client. (Source code)

Install knex globally (so we can init the config file) and pg client:

npm install -g knex
npm install knex pg --save

Then init knex with knex init , this will create a config file for you which save you from writing some boilerplate code. Replace it with your own database config.

There are dev, staging, prod 3 environment, with the latter two has a bit more config than dev (pool/migration).

production: {
client: 'pg',
connection: { //I used a connection string instead
database: 'my_db',
user: 'username',
password: 'password'
},
migrations: {
directory: "./db/migrations",
}
seeds: {
directory: "./db/seeds/dev",
},

Create a db.js file in the data directory that would hold our knex configuration in the data directory. This would hold the configuration for our environments. You can then require it in other files like server.js

const environment = process.env.NODE_ENV || 'development';   
const configuration = require('../knexfile')[environment];
export const database = require('knex')(configuration);

Migrations is like version control for database(more illustrated in my prev blog). In Knex, migration files are timestamped files created through CLI.

knex migrate:make <migration identifier>

For example, knex migrate:make add-publisher will generate a file called: 20200709215731_add-publisher.js with templates like

exports.up = function(knex, Promise) {
let createQuery = `CREATE TABLE <examples>(
id SERIAL PRIMARY KEY NOT NULL,
message TEXT,
created_at TIMESTAMP
)`
return knex.raw(createQuery)
}

exports.down = function(knex, Promise) {
let dropQuery = `DROP TABLE <examples>`
return knex.raw(dropQuery)
}

Note that the up defines migration changes we want to make. The down is to roll back the up change.

To run the migration: knex migrate:latest .

To roll back: knex migrate:rollback .

It’s good to have some data you can run on the database for testing and other purpose. That’s why we have seed. To make a seed file:

knex seed:make <seed_file>

This creates a file with default content below:

exports.seed = function(knex) {
// Deletes ALL existing entries
return knex('table_name').del()
.then(function () {
// Inserts seed entries
return knex('table_name').insert([
{id: 1, colName: 'rowValue1'},
{id: 2, colName: 'rowValue2'},
{id: 3, colName: 'rowValue3'}
]);
});
};

Note that the seed file starts with deleting all the data in current table before perform any operation. This is to make sure that the table could have a clean state.

Note that Knex supports both Promise and async/await key words, and sometimes you might need to combine both like

 await Promise.all(
/*do something, maybe map through a data file and insert row by row*/
)

To run the seed, simply knex seed:run .

For Knex, the query syntax is easy to get it if you are familiar with raw sql syntax.

As an example, CRUD operation for a todos table can be done like:

 getAllTodo(db) {
return db.select("*").from("todos");
},
insertTodo(db, newTodo) {
return db
.insert(newTodo)
.into("todos")
.returning("*")
.then((rows) => {
return rows[0];
});
},
getById(db, id) {
return db.from("todos").select("*").where("id", id).first();
},
deleteTodo(db, id) {
return db("todos").where({ id }).delete();
},
updateTodo(db, id, todoFields) {
return db("todos").where({ id }).update(todoFields);
},
};

For more syntax, check here.

Before I explain why I use a query builder like Knex, let’s take a look at where Knex (tools like Knex) sits in the various database tools we use.

At the lowest level, we have the Database Driver, which is a software that implements a protocol (ODBC or JDBC)to connect a generic interface to a database vendor implementation. So here you need to write raw SQL syntax. Popular tools are mysql for MySQL, pg for PostgreSQL and sqlite3 for sqlite. These tool will initiate a database instance and use the credentials to connect to a database. Then when you want to query the database, you write raw SQL syntax like below:

const {Pool, Client} = require('pg');
const pool = new Pool({connectionString: db.url});
....
//to query from database todo with todo_id = 7
pool.query(
`select * from todo where todo_id = 7`,
);

The next level is the query builder — like Knex. It sits on top of database driver but not too abstract yet. Libraries like this can normally generate queries for different database. The advantage of this over the database driver is that you are able to generate dynamic queries while writing SQL like syntax, so you get both convenience and confidence over the queries you are writing.

The last kind is ORM — someone loves it someone hates it. I only used ActiveRecord in ruby on superficial level so I’m not in the position to comment. In Node, the popular ones are sequelize, bookshelf and objectionJS . This is the most abstract layer among the three kinds mentioned — as it maps a “row” of data to an object instance.

When we compare the above three categories of tools, remember at the end of the day it needs to be evaluated on per project base.

So for me, why I choose Knex?

To database driver:

  • Flexibility when switching database, since Knex will change the SQL queries under the hood for you;(but again, SQL is universal but query builder like Knex are normally language/platform specific)
  • Handle migration and seeding other than building queries
  • Allow dynamic queries (conditional perform operations)

To ORMs:

  • Easier to write complex queries or do complex operations like multi-join/index/locking, while with ORM you might encounter Object-relational impedance mismatch problem;
  • More scalable and maintainable. I’ve heard more than once that people find ORMs make life easier 90% of time, but when handling complex queries and especially debugging, it make it so much harder;

Obviously, both categories have their own strength, and I’m planning to explore more in-depth sometime soon.

Happy reading!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store