< Back

Owen PhillipsDecember 05, 2018

Updated: April 23, 2023

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.

Simple-api on Github

More from this series

1. Project Setup with Express
2. PostgreSQL with KnexJS Setup
3. User Registration and Validation
4. Send Emails with Amazon SES
5. Verify Users with Tokens
6. User Login with JWT Authentication
7. Forgot / Reset Password Routes

Prerequisites

To continue with this series, you are going to need to be a little comfortable with Postgres and already have it installed. You should also be comfortable using the psql command line tool or a database manager like Beekeeper. Let's continue!

Step 1 - Install some more packages

For this part of the 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.

Install them now using the following command:

npm i knex pg bcryptjs validator

Step 2 - Create a 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)
);

Step 3 - Setup the database using the 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 <PATH_TO_SCHEMA_FILE>/simple_api.sql

You should see the following if it worked:

postgres=# \i <PATH_TO_SCHEMA_FILE>/simple_api.sql
DROP DATABASE
CREATE DATABASE
You are now connected to database "simple_api" as user "postgres".
CREATE EXTENSION
CREATE TABLE

Step 4 - Create a database connection file in JavaScript

The last step is to create a database connection file that can be called throughout the project to connect to your PostgreSQL database.

Back in the simple-api directory, create a file now called 'database.js' in the 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 'require' this file.

Conclusion

Congratulations! You now have your database setup and your project is ready to make connections to it. In the next tutorial in 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.

Use one of the links below to share this series on social media if it helped you out!