How to connect your ExpressJS app with Postgres using Knex
Published: 2021-01-17 00:23:24

*Note: I've created a video for this tutorial if you'd like to check that out [here](https://www.youtube.com/watch?v=xED_AMH-eBI&feature=youtu.be). Also, the supporting code for the same can be found [here](https://github.com/mandeepm91/express-postgres-knex-app)*

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](http://knexjs.org/) and [Sequelize](https://sequelize.org/). 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](/content/images/2021/01/Screenshot-from-2021-01-17-02-32-23.png)

### 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