DEV Community

Cover image for Setting up a Node API with Postgres and Knex
Ekunola Ezekiel
Ekunola Ezekiel

Posted on • Updated on • Originally published at blog.ezekielekunola.com

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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

knex init
Enter fullscreen mode Exit fullscreen mode

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

};

Enter fullscreen mode Exit fullscreen mode

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' },
  },
};


Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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 
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

To run the migrations, you can run the command below

knex migrate:latest
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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

knex seed:run 
Enter fullscreen mode Exit fullscreen mode

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 "
  }
Enter fullscreen mode Exit fullscreen mode

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 });
});
Enter fullscreen mode Exit fullscreen mode

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

Top comments (6)

Collapse
 
lumsrouge profile image
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

Collapse
 
easybuoy profile image
Ekunola Ezekiel

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

Collapse
 
taillogs profile image
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.

Collapse
 
easybuoy profile image
Ekunola Ezekiel

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

Collapse
 
kardoso profile image
Samuel Cardoso

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

Collapse
 
easybuoy profile image
Ekunola Ezekiel

I'm glad it was helpful to you Samuel