Setup PostGres with Node.js & Knex

Node.js and PostGres have grown in popularity for several companies to have in their tech stack. Let's do a quick walkthrough on how to setup and write our first query.

We will be using:

Prerequisites:

  • Node and npm already installed
  • Postgres database setup (Tutorial Here)

Assuming you have the prerequisites. If you haven't you can follow the instructions here. Let's stat by creating a new project. Open Terminal or your CLI tool of choice. I recommend iTerm. Let's create a new project.

mkdir new-project
cd new-project
npm init

You will then be prompted to name the project, license, etc. You can just click "enter" through these.

Next create our server.js node file and past the following code below.

server.js
require('dotenv');
var knex = require('knex')({
    client: 'pg',
    connection: {
        user: process.env.USER,
        password: process.env.PASSWORD,
        database: process.env.DATABASE,
        port: 5432,
        host: process.env.HOST,
        ssl: true
    }
});

We are adding the 'dotenv' package in order for the our .env file to be read locally. In our .env file we want to add our database connection values. We use the .env file vs having our variables directly in the server.js as it is not committed to our repo and is provides more security.

Create Table & Data

Next let's run a migration to add some simple data to our PostGres Database. If you don't have Postgres installed, follow the Steps to install PostGres on Mac tutorial

Now let's write some simple Knex commands into our server.js file that we added the connection into already. We will add a table and insert a few sample rows of data.

server.js
knex.schema.createTableIfNotExists("users", (table) => {
    table.increments();
    table.string('name');
    table.string('job');
}).then(() => {
    return knex("users").insert([
        {name: "John Smith", job: "Actor"},
        {name: "James Owen", job: "BB"},
        {name: "Chris Stalkin", job: "Writer"},
        {name: "Danny Glover", job: "Actor"}
    ]);
));

Now let's run the file by typing node server.js in our terminal, and then verify that the rows did get created.

Query Data

Now let's put together a couple queries with knex and see the data output.

server.js

Get all of the users that are actors.

knex("users").where('job', 'Actor').then((response) => {
    console.log(response.data);
}).catch((error) => {
    console.log(error);
    process.exit(); // Kills the node application
});

server.js
Get user with name John Smith and return just the description.

knex("users").where({"name":"John Smith"}).select("description").first().then((response) => {
    console.log(response.data);
}).catch((error) => {
    console.log(error);
    process.exit(); // Kills the node application
});

The final server.js file will look like this:

require('dotenv');
var knex = require('knex')({
    client: 'pg',
    connection: {
        user: process.env.USER,
        password: process.env.PASSWORD,
        database: process.env.DATABASE,
        port: 5432,
        host: process.env.HOST,
        ssl: true
    }
});

knex.schema.createTableIfNotExists("users", (table) => {
    table.increments();
    table.string('name');
    table.string('job');
}).then(() => {
    return knex("users").insert([
        {name: "John Smith", job: "Actor"},
        {name: "James Owen", job: "BB"},
        {name: "Chris Stalkin", job: "Writer"},
        {name: "Danny Glover", job: "Actor"}
    ]);
));

knex("users").where('job', 'Actor').then((response) => {
    console.log(response.data);
}).catch((error) => {
    console.log(error);
    process.exit(); // Kills the node application
});

knex("users").where({"name":"John Smith"}).select("description").first().then((response) => {
    console.log(response.data);
}).catch((error) => {
    console.log(error);
    process.exit(); // Kills the node application
});

In Summary

In summary, Node.js, Postgres, and Knex make it very easy to get started. In this simple application, we just ran everything in one file.

This would not be ideal when creating usable and scalable applications.

This post serves as a good proof of concept if you're wanting to learn how to connect Node.js and Postgres and create queries to bring the data into your application.

I recommend using knex migrations and creating migrations for your tables or for any data you need to populate into the tables. Here is a great cheat sheet for using knex. Cheatsheet. You can find many different Knex commands there to help you continue to learn Knex.

I hope you enjoyed this short 1 file guide on using Node.js, Knex, and PostGres. Checkout our other posts on building more complex applications that will cover application architecture.

Eric Cavazos

Eric Cavazos

https://codelabs.io

I love to build software, workout, and snowboard...

View Comments