Knex.js and Postgres

Knex.js is a SQL query builder based on Laravel Query Builder. It supports a handful of different databases, most importantly Postgres. It has been my first choice for database interaction in JavaScript for years.

Connecting to Postgres

To start, we need to make a connection to our Postgres database.

const knex = require('knex');

const db = knex({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'postgres',
    password: 'hunter3',
    database: 'bigdata03',
  },
});

You can pass a connection string instead like this (passing an environment variable would be better).

const db = knex({
  client: 'pg',
  // connection: 'postgresql://postgres:hunter3@localhost/bigdata03',
  connection: process.env.DB_CONNECTION,
});

Queries

Here is a typical select query to retrieve a record from our meal table.

knex
  .select('*')
  .from('meal')
  .where('id', 1)
  .then(function (meals) {
    console.log(meals);
    // [ { id: 1, description: 'Burrito', ... } ]
  });

Here is a more complex query that returns a few specific fields from our meal table, joins with the user table, filters down the data, sorts it by date, and then limits the results to 5 rows.

knex
  .select(['id', 'description', 'queso', 'user.name'])
  .from('meal')
  .join('user', 'user.id', 'meal.user_id')
  .where({
    queso: true,
    'user.id': 5,
  })
  .orderBy('created_at', 'desc')
  .limit(5);

If you ever need to see what SQL will be generated from a Knex query, you can append .toString() to the Knex query and it will return the SQL query as a string. This is very useful for debugging.

Transactions

Transactions are a way of keeping your data consistent. A transaction groups your queries together into a unit that can either be committed or rolled back. Committing a transaction writes the data to the database. Rollback aborts the update and does not write anything to the database. Knex has two different ways to use transactions. I generally prefer the following way so I don't have to manually call trx.commit and trx.rollback manually.

const meals = [
  { description: 'Burrito', queso: true },
  { description: 'Churro', queso: false },
  { description: 'Chips', queso: true },
];

knex.transaction(function (trx) {
  return Promise.all(
    meals.map(function (meal) {
      return trx.insert(Object.assign(meal, { user_id: 4 })).into('meal');
    })
  );
});

In this example, we are bulk adding a list of meals and if one of them fails, we want to rollback so we can try again. If any of the promises throw, Knex will rollback the transaction for us.

Inserts and Updates

Knex handles inserts and updates with a nice JavaScript flavor on the SQL syntax. Insert a meal into our database and return the id.

knex.insert({ description: 'Burrito', queso: false, user_id: 4 }).into('meal').returning('id');

Update the meal we just created because we definitely had queso.

knex.table('meal').where({ id: 20 }).update({ queso: true });

Migrations

Migrations are a great way to version your database structure changes. You will be able to run a command that runs all the necessary migrations to get your database schema up to date.

To start, you can run knex init to generate a knexfile. The knexfile is a config file that contains various settings, like your database credentials.

Next, you can run knex migrate:make add_meal to generate a migrate file. The generated file will look similar to the file below. There are two functions exposed, up and down. The up function is called when a migrate is run. The down function is called when a migrate is rolled back.

exports.up = function () {};

exports.down = function () {};

We can add to our migrate file by calling Knex’s createTable function in the up function. We also add id, description, queso, created_at, and updated_at columns to our meal table. In the down function, we do the opposite and drop the burritos table.

exports.up = function () {
  return knex.schema.createTable('meal', function (table) {
    // add an autoincrementing id column (serial type in Postgres)
    table.increments();
    // add a string column called description
    table.string('description');
    // add a boolean column to indicate whether the meal had queso.
    table.boolean('queso');
    // add created_at and updated_at columns with appropriate default values.
    table.timestamps();
    // create a foreign key that references the id column of the user table
    table.integer('user_id').references('user.id');
  });
};

exports.down = function () {
  return knex.schema.dropTable('meal'’);
}

To run the migrations and make the schema changes on the database, you run knex migrate:latest. If you need to rollback a migration, you can use knex migrate:rollback.