Priyanka Phillips
Priyanka Phillips in Tutorials
Fri Jul 19 2019 · 22 min read

Create a REST API [Part 2]: PostgreSQL with KnexJS Setup

In this tutorial, we will continue with our simple-api project by creating a Postgres database and get ready to connect to it with KnexJS. This is part 2 of a series of tutorials on building a REST API in Node with ExpressJS, KnexJS, and PostgreSQL.

More From This Series

• Part 2 - PostgreSQL with KnexJS Setup
Create a REST API [Part 2]: PostgreSQL with KnexJS Setup

Download the code for this series

Although document databases (Like MongoDB) are popular these days, SQL based databases are still the ultimate way to store and manipulate data with Postgres being the best of them. So that's what we use here. To continue with this tutorial you are going to need to be a little comfortable with Postgres and have it installed and running. You should also be comfortable using the psql command line tool or pgAdmin. Let's continue!

1. More packages to install

For this tutorial we are going to need a few more packages:

bcryptjs - A JavaScript module created for password hashing based on the bcrypt function. bcrypt website.

validator - Checks strings for a list of criteria (called validators) and removes unauthorized characters from strings. validator website.

knex.js - Knex.js is used here to enable queries to a PostgreSQL database from NodeJS. KnexJS website.

pg - This module is required by knex.js for PostgreSQL. pg website.

Use the following command to install them:

npm i knex pg bcryptjs validator

2. Create database schema file

We are going to create a schema file for our database called 'simple_api'. In Postgres there are a couple of ways to setup a database but by using a file, you type out your schema once and run the file every time you need to rebuild your database. And you are likely to need to do this often when in development.

Create a file in your project's root directory called 'simple_api.sql' and enter the following:

DROP DATABASE IF EXISTS simple_api;
CREATE DATABASE simple_api;

\c simple_api;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id uuid UNIQUE DEFAULT uuid_generate_v4 (),
  email VARCHAR(128) NOT NULL UNIQUE,
  password VARCHAR(128) NOT NULL,
  registered BIGINT,
  token VARCHAR(128) UNIQUE,
  createdtime BIGINT,
  emailVerified BOOLEAN,
  tokenusedbefore BOOLEAN,
  PRIMARY KEY (email)
);

3. Database setup with our schema file

Let's run our new schema file and watch our database get magically setup for us. In your command line, enter the psql shell:

psql

and enter in the following command to run your simple_api.sql file.

postgres=# \i C:/Users/Priyanka/tutorials/simple-api/simple_api.sql

Replace 'C:/Users/Priyanka/tutorials/simple-api/simple_api.sql' with the path to the file on your hard drive.

You should see the following if it worked:

postgres=# \i C:/Users/Priyanka/tutorials/simple-api/simple_api.sql
DROP DATABASE
CREATE DATABASE
You are now connected to database "simple_api" as user "postgres".
CREATE EXTENSION
CREATE TABLE

4. Visualize the database with pgAdmin

Just to confirm that the database was created properly, use pgAdmin to see the database visually

pgAdmin users tablepgAdmin users table

5. Database connection file with KnexJS

The last step in this part of the series is to create a database connection file that we can reuse throughout or project to connect to our database. Create a file called 'database.js' in your project's root directory and add the following code:

const knex = require("knex");

const database = knex({
  client: "pg", // pg is the database library for postgreSQL on knexjs
  connection: {
    host: "127.0.0.1", // Your local host IP
    user: "postgres", // Your postgres user name
    password: "your_password", // Your postgres user password
    database: "simple_api" // Your database name
  }
});

module.exports = database;

It's as simple as that. Every time you need a connection to your database you can import this file

Conclusion

Congratulations! You now have your database setup and your project is ready to make connections to it. In the next part of this series we will start registering users in the database through our API using the database we just set up and our 'database.js' file.

Leave a comment below and share this tutorial to help someone else out!