Build your own REST API with Node, Express, Knex and PostgreSQL

23 Sep 2019 - John

While Rails is a perfectly viable solution to spin out a quick API server, I must confess that one of the things about programming that I like the most is the constant learning. So recently I decided to build an API using tools I normally don’t use and documented the process so you can learn too.

The app

We are going to build a simple blog API using Node, Express, PostgreSQL and Knex with authentication. There will be two models: User and Post.We’ll be using a one to many relationship from User to Post. Let’s get started!

Prerequisites:

Latest version of Nodejs.
PostgreSQL.
createdb - CLI command that usually ships with the default PostgreSQL installation.
Express-generator: npm i -g express-generator .
Knex: npm i -g knex.

Create file structure and database

Create an empty folder, cd into it and run express . This will create the base file structure needed for the project.
Then create a database by running createdb databaseName . I called mine tutorial.
Now we need to install knex and pg by typing npm i -S knex pg . Then check your package.json file to confirm that everything went well.

Initialize knex

Now type knex init to create a knexfile.js in your project root. Edit this file and delete all entries except for the Development one, then change the client from "sqlite3" to "postgresql" and change connection value from an object to a string and set its value to postgres://localhost/yourDatabaseName. I named mine tutorial, so here’s what mine looks like:

module.exports = {
  development: {
    client: 'postgresql',
    connection: 'postgres://localhost/tutorial'
  }
};

Create migrations

Now it’s time to define what our database is going to look like by creating our first table. To do so, type knex migrate:make create-users, check the newly created migrations folder and look for a file called timestamp_create-users.js. Inside, you’ll find two functions called up and down. This is where we define our table structure:

exports.up = function (knex) {
     return knex.schema.createTable('users', (table) => {
         table.increments();
         table.string('username');
         table.string('email');
     }); };
  exports.down = function (knex) {
     return knex.schema.dropTable('users');
 };

Super simple. We’re defining three fields: The first one will set up an id field with autoincrement set to true and an unique constraint. Then we have two more fields: one for the username and one for the email. We will come back later to add a password.

Run the migration and check the database

Run knex migrate:latest to load our newly created schema into our database. Then fire up a terminal and run psql yourDatabaseName. You’ll land at the Postgres prompt where you can type \dt and if everything went well, you should see something like this:

List of relations
Schema |         Name         | Type  | Owner
--------+----------------------+-------+-------
public | knex_migrations      | table | john
public | knex_migrations_lock | table | john
public | users                | table | john
(3 rows)

The first two are used by knex to keep track of our migrations, but notice the third one? Great, that’s where our users will be stored, let’s check it out!
Still in the Postgres prompt, type \d users to see what’s inside:

Table "public.users"
Column  |          Type          | Collation | Nullable |              Default
----------+------------------------+-----------+----------+-----------------------------------
id       | integer                |           | not null | nextval('users_id_seq'::regclass)
username | character varying(255) |           |          |
email    | character varying(255) |           |          |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)

Great! We have an id column, an email and a username, just as we planned! Now exit the Postgres prompt by typing exit. If you’re having a hard time reading the above table, feel free to paste it on an empty txt file.

Create a seed file

No practice project is complete without some dummy data, so still in your terminal, run knex seed:make create_dummy_users. This will create a new file called create_dummy_users in a new /migrations folder. In this file, we have a nice template to fill with dummy data for our database. Just follow the format given to add a few users:

exports.seed = function(knex) {
   // Deletes ALL existing entries
   return knex('users').del()
     .then(function () {
       // Inserts seed entries
       return knex('users').insert([
         {username: 'John Doe', email: 'johndoe@example.com'},
         {username: 'Jane Doe', email: 'janedoe@example.com'}
       ]);
     });
 };

One thing to notice though is that the original templates suggest specifying the ID number. This might work on other databases, but Postgres takes care of them automatically so we should omit them to prevent conflicts. Now run the migration by typing knex seed:run and your database should get populated in a couple of seconds. To check if it worked, go back to the Postgres prompt and type SELECT * FROM users;:

id | username |        email
----+----------+---------------------
1 | John Doe | johndoe@example.com
2 | Jane Doe | janedoe@example.com
(2 rows)

In the next part we’re going to add an articles table, user authentication and table relationships.

John Fajardo