Connecting ExpressJS to PostgreSQL

Creating environment variables

First things first, you don’t want to be committing your own details about the server – the username, password, host, port etc. should all be safe (generally seen as a bad idea to commit secrets to source control) and configurable. This makes it so if you want to deploy on AWS today, then DigitalOcean tomorrow, you can (kind of) just change a couple variables and you’re good to go. There are numerous ways to accomplish this, but dotenv seems to be the go-to for Node projects.

# Add dotenv
cd min-auth/min-auth-server
yarn add dotenv

# Then a file to hold your environment variables
touch .env
echo /.env >> .gitignore

Then configure your server to use dotenv, loading in the contents of the .env file. This should be done right at the beginning, so all subsequent commands have the environment variables visible.

// app.js
require('dotenv').config()

var express = require('express');
...

Now your server has visibility into any environment variables added into your .env file, and your .env file is explicitly excluded from Git.

NodeJS to PostgreSQL

Pretty much everything builds on top of one pretty standard library – node-postgres (or simply pg). From there, there are all sorts of tools that lie on a continuum of abstraction.

  • pg-promise is a popular library which trades in callbacks for promises, and provides ES6 generator support.
  • Knex is marginally more abstract, but still largely just syntactic sugar over SQL queries
  • Bookshelf is an ORM built on top of Knex – climbing up the ladder of “do you really want this, or should you just learn SQL already?
  • Sequelize is a promise based ORM that seems to avoid building directly on the other libraries, I have no personal experience with it

Once again, what to use, what to use? I’ve been somewhat put off by ActiveRecord in Ruby on Rails, so I’d like to try avoiding an ORM and see how it pans out. That means that both Sequelize and Bookshelf are out. Knex is appealing because of the ORM layer that can be tacked on to it, if I should ever find that desirable, and it also has some built in opinions about how to implement and organize migrations. pg-promise is generally more popular, and a bit lower level. I used it a bit when trying to create a module to automate the provisioning of the PostgreSQL server for web app development, and while it was okay I wasn’t blown away by it by any means. I found myself writing a lot of what I imagine Knex provides for free.

As I don’t think the database work is one of my stronger skills, I’ll opt for the more feature rich Knex.

Setting environment variables

When it comes to actually using Knex, the library has the concept of a knexfile for configuration. I don’t really know too much about it or what the intentions are, but it comes across as more of an interface between the the actual environment variables and the Knex configuration – it doesn’t quite fit in either. As I don’t really need it right now, I’ll put off using it until I get to dealing with seed files and migrations.

So first things first, set up all the environment variables. These fields should be pretty self explanatory, the names are arbitrary – they can be named whatever. To start with, we’ll connect to our development database, as we’re in development. Note that this is building off the convention used previously, i.e. the databases are named {database_name}_{environment} where environment is one of production, development or test. This is another arbitrary decision I made, this could be handled many other ways, the important aspect is that its consistent.

// .env

DB_USER=min_auth_user
DB_PASSWORD=user_password
DB_NAME=min_auth
DB_HOST=192.168.99.100
DB_PORT=5432
DB_NUM_MAX_CLIENTS=10
DB_IDLE_TIMEOUT_MILLIS=3000

NODE_ENV=development

These variables are specific to a particular machine. This should *not* be added to version control. It’s generally a bad idea to post your passwords on the internet, even if you’re just messing around. The idea here is that these values will be set specifically for each deployment environment.

ExpressJS to PostgreSQL

The next step is straight out of the Knex documentation. We’ll add a separate file, just so our app.js doesn’t get too crowded, perhaps min-auth-server/config/db.js?

// config/db.js

module.exports = require('knex')({
  client: 'pg',
  connection: {
    user: process.env.DB_USER,
    database: process.env.DB_NAME + "_" + process.env.NODE_ENV,
    password: process.env.DB_PASSWORD,
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    max: process.env.DB_NUM_MAX_CLIENTS,
    idleTimeoutMillis: process.env.DB_IDLE_TIMEOUT_MILLIS,
  }
});

… and the corresponding require to app.js

// app.js
...
const knex = require.main.require("../config/db")
...

At this stage, if everything is as intended and the environment variables are set appropriately, Express should be able to talk to the the database! It’s nice to say that, but if there’s a typo somewhere it’s hard to tell for sure. Let’s verify the connection actually works! You can do this a million different ways, but I’ll just print out a log on the server start up. Again, in the interest of not cluttering up app.js, I’ll put it elsewhere – utils/knexUtils.js

// utils/knexUtils.js

module.exports = function (knex) {
  return {
    logVersion: function () {
      knex.raw('SELECT version()').then(function (resp) {
        dbServer = resp.rows[0].version
        return dbServer;
      }).catch(function (error) {
        console.log("Connection to database failed");
        console.error(error);
        return error;
      });
    }
  }
}

And the corresponding entry in app.js:

// app.js

...
const knex = require.main.require("../config/db")
const knexUtils = require.main.require("../utils/knexUtils")(knex);
knexUtils.logVersion();
...

Now we can give it a shot! Back in the root of the project (the parent directory of min-auth-server and min-auth-client) run the start-server task created previously:

$ yarn start-server
yarn start-server v0.19.1
$ cd min-auth-server && nodemon
[nodemon] 1.11.0
[nodemon] to restart at any time, enter `rs`
[nodemon] watching: *.*
[nodemon] starting `node ./bin/www`
Successfully connected to PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Alpine 6.2.1) 6.2.1 20160822, 64-bit

Awesome! Our ExpressJS app is now connected to the PostgreSQL server, and they’re communicating successfully. The Angular front end development workflow is working out, it can be packaged up and then delivered from the server – all of the sudden we find ourselves with a full stack!

Advertisements

2 thoughts on “Connecting ExpressJS to PostgreSQL

    1. tobymurray Post author

      I’ll take a look at your demo – thanks for the link. I don’t relish rebuilding the migration tooling that Knex provides for determining whether a schema is out of date or not, and applying the appropriate migrations if it is. What do you think of the CLI that Knex provides (http://knexjs.org/#Migrations)? Taking a quick look at your demo project it looks like you’ve addressed a piece of that, but I don’t see a means to determine what to do if a schema is only halfway there.

      I see you address that somewhat in the issue here. That comes across as unsatisfying though – noted that ORM-ness is out of scope for pg-promise, but what’s the alternative? I don’t want to roll my own…

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s