/ Programming

How to connect your ExpressJS app with Postgres using Knex

Note: I've created a video for this tutorial if you'd like to check that out here. Also, the supporting code for the same can be found here

Express is one of the most popular JavaScript framework for building backend APIs and Postgres is a really popular relational database. How do we connect the two?

If you look at the official documentation for Express, you'll see the section like this:

var pgp = require('pg-promise')(/* options */)
var db = pgp('postgres://username:password@host:port/database')

db.one('SELECT $1 AS value', 123)
  .then(function (data) {
    console.log('DATA:', data.value)
  })
  .catch(function (error) {
    console.log('ERROR:', error)
  })

It works for sure but it's not the way you would write it in a full fledged production application. Some of the questions that come to mind are:

  • How do you create the tables in the database?
  • How do you track changes to the database? For example, when you alter a table or create a new table. Or create/drop an index on a field. How to keep track of all these changes in your git/cvs/svn repository?
  • What if you switch from Postgres to some other database in future, say MariaDB for example? Do all your queries still work?

There might be a lot more questions but to me, the most important one feels like keeping track of changes to database in your application codebase. If someone clones my repository to their local system, they should have a command to create all the database tables on their local setup. Also, as we make changes to the database like adding/dropping tables or indices or altering any of the tables, one should be able to run a single command to sync their local copy of the database structure with the same on production DB. I am talking about structure, not the data. All the tables on the local database should have the same structure as that in the production database to make the testing of your application easy on local machine. And if you don't have this sync mechanism automated, you're likely to run into a lot of issues that you'll be troubleshooting in production.

To solve for these problems, we have libraries like Knex and Sequelize. These libraries provide a very neat API for writing SQL queries which are database agnostic and prevent issues like SQL injection attacks. They also provide transaction support to handle complex DB operations and streaming API to handle large volumes of data in a script. Also, to keep track of structural changes to your database in your code repo, these libraries use the concept of migrations. Migrations are files where you write structural changes you want to make to your database. For example, let's say you have a users table and want to alter the table to add a new column gender. You can write a Knex migration file like this:

exports.up = knex => knex.schema
  .alterTable('users', (table) => {
    table.string('gender')
  });

exports.down = knex => knex.schema
  .alterTable('user', (table) => {
    table.dropColumn('gender');
  });

The up function defines what to do when we run the migration and down function defines what to do when we rollback the migration. You can run the migration like this:

knex migrate:latest

And you can roll it back like this:

knex migrate:rollback

Once you commit this file to your code repository, your other team members can pull the changes from the repo and run these commands at their end to sync up the database structure on their machines.

In order to keep track of the database changes (migrations), Knex creates a few extra tables which contain information about what all migrations have been applied. So, for example if one of your team members hasn't synced their database in a long time and there are say 10 new migration scripts added since the last time they synced, when the pull the latest changes from the repo and run the migration command, all those 10 migrations will be applied in the sequence they were added to the repository.

Anyway, coming back to the main topic of this post. How do we add knex to our ExpressJS app and how do we use it to connect to our postgres database? Before we dive into this, there are some pre-requisites that should be met

Pre-Requisites

  • Node JS version 8 or higher intalled
  • Postgres installed on our localhost:5432

Steps

We will divide this article into following steps:

  • Creating the Express app
  • Creating the API endpoint with some hard coded data
  • Creating a database for our app
  • Installing and configuring knex
  • Populating seed data with knex
  • Updating the API endpoint created in step 2 to fetch the data from database instead of returning hard coded data

For this tutorial, we will be using Ubuntu Linux but these instructions should work fine other operating systems as well.

So, without further ado, let's get started with creating our Express app.

Step 1: Creating the Express app

Open the terminal (command prompt or Powershell on Windows), navigate to the directory where you want to create this project and create the project directory. We will be calling our project express-postgres-knex-app (not very innovative I know :-))

mkdir express-postgres-knex-app

Go to the project directory and run the following command to generate some boilerplate code using express generator

npx express-generator

The output should look like this:


   create : public/
   create : public/javascripts/
   create : public/images/
   create : public/stylesheets/
   create : public/stylesheets/style.css
   create : routes/
   create : routes/index.js
   create : routes/users.js
   create : views/
   create : views/error.ejs
   create : views/index.ejs
   create : app.js
   create : package.json
   create : bin/
   create : bin/www

   install dependencies:
     $ npm install

   run the app:
     $ DEBUG=express-postgres-knex-app:* npm start

This will create the some files and directories needed for a very basic Express application. We can customize it as per our requirements. Among other things, it will create an app.js file and a routes directory with index.js and users.js files inside. In order to run our application, we need to follow the instructions in the output shown above. First, install the dependencies:

npm install

Then run the app using the following command:

DEBUG=express-postgres-knex-app:* npm start

This should start our server on port 3000. If you go to your browser, you should be able to see the express application on http://localhost:3000

Screenshot-from-2021-01-17-02-32-23

Step 2: Creating the API endpoint with some hard coded data

The express generator automatically created a users router for us. If you open the file routes/users.js, you should see the code like this:

var express = require('express');
var router = express.Router();
const DB = require('../services/DB');

/* GET users listing. */
router.get('/', async function (req, res, next) {
  return res.send('respond with a resource');
});

module.exports = router;

Here, we need to return the users array instead of a string respond with a resource. And we need to fetch those users from our database. So, for step 2, we don't need to do anything as we already have a route created for us by express generator. In the later steps, we will modify this code to actually fetch the users from our database

Step 3: Creating a database for our app

In this tutorial, we have a pre-requisite that postgres is installed on your machine. So, we need to connect to the postgres server and once you're inside, run the following command to create the database for our app:

create database express-app;

Step 4: Installing and configuring knex

Install knex and pg modules (since we are using postgres) by running the following command:

npm install knex pg

Once installed, initialize knex with a sample config file:

knex init

This should create a knexfile.js file in your project's root directory. This file contains the configuration to connect to the database. By default, the knexfile will be using sqlite for development. We need to change this since we are using postgres

Modify your knexfile.js so it looks like this:

// Update with your config settings.
const PGDB_PASSWORD = process.env.PGDB_PASSWORD;

module.exports = {
  development: {
    client: 'postgresql',
    connection: {
      host: 'localhost',
      database: 'express-app',
      user: 'postgres',
      password: PGDB_PASSWORD
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations',
      directory: `${__dirname}/db/migrations`
    },
    seeds: {
      directory: `${__dirname}/db/seeds`
    }
  }
};

Now, we need to create a service called DB where we initialize knex in our application with the config from knexfile.js. In the project's root directory, create a directory services and inside the services directory, create a file DB.js

In that file, add the following code:

const config = require('../knexfile');

const knex = require('knex')(config[process.env.NODE_ENV]);

module.exports = knex;

Here, we are importing the config from knexfile and initializing the knex object using the same. Since, we will be running our app in development mode, the value of NODE_ENV will be development and the config for the same will be picked from the knexfile.js. If you run the app in production, you'll need to add the production config in the knexfile.js.

Now, wherever in our app we need to pull data from the database, we need to import this DB.js

Step 5: Populating seed data with knex

So we have our express app up and running with knex integrated. And we have our postgres database created. But we don't have any tables and data in our database. In this step, we will use knex migrations and seed files to do the same.

From the project's root directory, run the following commands:

npx knex migrate:make initial_setup

This will create a new file in the db/migrations directory.

npx knex seed:make initial_data

This will create a sample seed file under the db/seeds directory. First, we need to modify our migration file to create the users table. Open the newly created file under db/migrations directory and modify it so it looks like this:

exports.up = function (knex) {
  return knex.schema.createTable('users', function (table) {
    table.increments('id');
    table.string('name', 255).notNullable();
  });
};

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

Here, in the up function, we are creating a users table with two fields: id and name. So, when we apply this migration, a new table will be created. And in the down function, we are dropping the users table. So, when we rollback our migration, the users table will be deleted.

Also, open the newly created file under db/seeds directory and modify it so it looks like this:

exports.seed = function (knex) {
  // Deletes ALL existing entries
  return knex('users')
    .del()
    .then(function () {
      // Inserts seed entries
      return knex('users').insert([
        { id: 1, name: 'Alice' },
        { id: 2, name: 'Robert' },
        { id: 3, name: 'Eve' }
      ]);
    });
};

This will first remove any existing entries from our users table and then populate the same with 3 users.

Now, that we have our migration and seed files ready, we need to apply them. Run the following command to apply the migration:

npx knex migrate:latest

And then run the following command to populate the seed data:

npx knex seed:run

Now, if you connect to your postgres database, you should be able to see the users table with 3 entries. Now that we have our users table ready with data, we need to update the users.js file to fetch the entries from this table.

Step 5: Updating the API endpoint created in step 2 to fetch the data from database instead of returning hard coded data

Open the file routes/users.js and modify the API endpoint to look like this:

var express = require('express');
var router = express.Router();
const DB = require('../services/DB');

/* GET users listing. */
router.get('/', async function (req, res, next) {
  const users = await DB('users').select(['id', 'name']);
  return res.json(users);
});

module.exports = router;

Here, in the 3rd line we are importing the DB service. Then inside our route handler, we are fetching the users using the Knex's query builder

const users = await DB('users').select(['id', 'name']);

Knex does the job of translating this to an SQL query:

SELECT id, name FROM users;

And then we return the users (array of JSON objects) to the response.

Now, go to the terminal where you started the application earlier. Stop the server. If you remember in the knexfile we created earlier, we were using an environment variable PGDB_PASSWORD for passing the postgres password to our config. So we will need to export this variable with the password of our postgres server

export PGDB_PASSWORD=<enter your postgres password here>

Then run the Express server again

DEBUG=express-postgres-knex-app:* npm start

Now if you go to the http://localhost:3000/users , you should see the JSON array of user objects fetched from your postgres database.

Conclusion

So, in this article we created an Express JS app and connected it with a postgres database using Knex. We also touched upon the benefits of using a robust library like Knex for handling database operations in our application and learned about the concept of migrations. Hope you found this article helpful

—
How to connect your ExpressJS app with Postgres using Knex
Share this