How to Add Knex to ExpressJS Apps and Connect to a Postgres Database

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)
  })

• 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?

If someone clones my repository to their local system, they should have a command to create all the database tables on their local setup.

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

knex migrate:rollback

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

• 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

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

   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

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

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

Step 3: Creating a 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

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

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

npx knex migrate:make initial_setup

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' }
      ]);
    });
};

npx knex migrate:latest

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 6: 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']);

SELECT id, name FROM users;

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>

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

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

Conclusion

Also published on: //codingfundas.com/how-to-connect-your-expressjs-app-with-postgres-using-knex