Setting up a Node API with Postgres and Knex

In this article, I would be setting up a Node project with Postgres database and Knex query builder.

What is Knex

Knex.js is a "batteries included" SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use.

To get started, we have to initialize our project to create our package.json file.

npm init -y

Database Setup

I would be using an online database called Elephantsql to set up my database. You can register here.

After registering, create a database. An example is shown below.

Setup Knex

Install dependencies needed for the project

npm i knex -g
npm i pg express dotenv --save

Create a .env file in the root of your project and add the database URL. Example below.

DB_URL=URL (where URL is the database connection string)

On your the terminal, run the command below to initialize knex

knex init

The above command would generate a knexfile.js in the root of your project and would look like below.

// Update with your config settings.

module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    }
  },

  staging: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  production: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }

};

I am going to change the knexfile.js configuration file to use Postgres and also specify the directory where my migrations and seeds should be saved. Edit the knexfile.js file to the code below.

require('dotenv').config();

module.exports = {
  development: {
    client: 'pg',
    connection: process.env.DB_URL,
    migrations: {
      directory: './data/migrations',
    },
    seeds: { directory: './data/seeds' },
  },

  testing: {
    client: 'pg',
    connection: process.env.DB_URL,
    migrations: {
      directory: './data/migrations',
    },
    seeds: { directory: './data/seeds' },
  },

  production: {
    client: 'pg',
    connection: process.env.DB_URL,
    migrations: {
      directory: './data/migrations',
    },
    seeds: { directory: './data/seeds' },
  },
};

DB Config

Create a db.js file in the data directory that would hold our knex configuration in the data directory. This would hold the configuration for our environments.

Add the code below to the db.js file.

const knex = require('knex');

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


const env = process.env.NODE_ENV || 'development';
const configOptions = knexfile[env];

module.exports = knex(configOptions);

Migrations

Migrations allow for you to define sets of schema changes so upgrading a database is a breeze.

To create/generate a migration file, run the command below.

knex migrate:make todo 

Note: todo in the command above is the migration name.

The above command would create a migration file in the path specified for migrations. In our case /data/migrations

We can then edit the migration file to include the columns we would be needing in our todo table.

exports.up = knex =>
  knex.schema.createTable("todo", tbl => {
    tbl.increments();
    tbl.text("task", 128).notNullable();
  });

exports.down = knex => knex.schema.dropTableIfExists("todo");

To run the migrations, you can run the command below

knex migrate:latest

knex migrate:latest would run through all our migration files and run the up functions which would then create the tables in our database.

To rollback the migrations, you can run the command below

knex migrate:rollback

knex migrate:rollback would run through all our migration files and run the down functions which would then drop the tables in our database.

Seeds

Seed files allow you to populate your database with test or seed data independent of your migration files.

To generate seeds, run the command below on your terminal

knex seed:make todo

This would create a todo.js file in your seeds directory. specified for seeds in our knexfile.js, which can then be edited to include our test data.
Example of a seed file below.

exports.seed = function(knex) {
  // Deletes ALL existing entries
  return knex('todo').del()
    .then(function () {
      // Inserts seed entries
      return knex('todo').insert([
        {id: 1, task: 'Create API'},
        {id: 2, task: 'Watch Money Heist'},
        {id: 3, task: 'Do Dishex'}
      ]);
    });
};

To run the seeds files we can run the command below on your terminal

knex seed:run 

The run command then gets all seeds files and then seeds them to our database.

Scripts

You can add scripts to package.json file.

 "scripts": {
    "start": "node index",
    "migrate": "knex migrate:latest",
    "unmigrate": "knex migrate:rollback",
    "seed": "knex seed:run "
  }

start: This command is to start our application.
migrate: This command is to add our migrations to our database.
unmigrate: This command is to rollback/remove all existing migrations from the database.

In order to query the database, all you need to do is to import in the db.js file we created and make the query. An example is shown below.

const db = require("./data/db.js"); // importing the db config

app.get("/todo", async (req, res) => {
  const todos = await db("todo"); // making a query to get all todos
  res.json({ todos });
});

Conclusion

In this article, we have been able to look at setting up a Node API with a Postgres database and Knex query builder.

The source code used in demonstrating this article can be found here.

Find more information on knex here

If you have any questions or feedback, please leave a comment.

Thanks for reading.

This article was originally posted on my blog

Comments

[Ryland G]

Hey I saw this article and decided to read it on a whim. As someone who already knows how to use knex/pg this is a really really concise and useful article. Great work.

[Ekunola Ezekiel]

This really means a lot to me. Thank you Ryland.

[lumsrouge]

Hi! Thank you very much for writing this article.
I am trying to follow your instructions but I need to set up the DB locally.
I am failing with the part where I have set the URL in the ".env" file.

I keep having this error:
Knex:Error Pool2 - error: password authentication failed for user "username"

Tried different URL configuration but none is working. Do you have an Idea of what this should look like?

Thx

[Ekunola Ezekiel]

Which database are you using? The error says the password is not correct for the username provided, so maybe check that

[Samuel Cardoso]

Thanks! I was looking for this, had to use postgres and knex in a small project. Helped me a lot. Great article.

[Ekunola Ezekiel]

I'm glad it was helpful to you Samuel