Node + Express + Knex + PostgreSQL

We will use a PostgreSQL database to create a RESTful API while using Knex.js for migration and seeding and express.js as our backend server.

What is a RESTful API? CRUD? Node.js environment? Express.js? Knex.js? And how can you use these on your database?

Before we dive into how we even begin to run our app on a server, let’s discuss some key concepts we have been exposed to. If you have not been exposed to any of these terms, this tutorial will expose you to some of the common topics and terms regarding how to run your app on a server. By the end of this tutorial, you will have made the connection between these commonly discussed topics and terms:

• API (Application Programming Interface)
• CRUD
• Express.js
• Node.js
• PostgreSQL
• SQL
• Database
• DBMS (Database Management System)
• Migrations

These are some common technologies used for creating an application. An API is used to create functions and procedures for the application. This means that the functionality of an application depends on the purpose of the website that is being created. Each functionality for each application you create in your career as a developer will be unique to the purpose of your website. But what will remain the same are the HTTP verbs that are used to make requests to your API. Now, I am going to introduce the important connection between the meaning of the acronym ‘CRUD’ and having a ‘RESTful API’. These HTTP verbs that are known for Creating, Reading, Updating, and Deleting the resources of an application are:

• GET — an HTTP request to Read (Used to read an existing resource)
• POST — an HTTP request to Create (Used to create and add a resource)
• PUT —an HTTP request to Update(client sends data that updates the entire resource)(Used to update an entire resource)
• PATCH — HTTP request to Update (client sends partial data that is to be updated without modifying the entire data)(Used to partially update a resource)
• DELETE — HTTP request to Delete (Used to delete an entire resource)

Let’s build a full CRUD, RESTful API using Express.js and PostgreSQL for a Todo List.

Let’s start off by creating our database, a Todo table, and some starter tasks.

First, go to the TablePlus app and create a database called ‘todo’ and leave the database open. You will want to ‘Command + R’ and refresh your database once you have made your first migration.

TablePlus App — todo <database>

For the assignment, our goal is that for each endpoint below, we should respond with the appropriate JSON response and our API should support:

We should analyze the image above to interpret what will be needed to create our table. What could be the possible schema for our table? We know that there is a PUT/PATCH method that responds with an updated ‘description’. This indicates that ‘description’ can be a table attribute with a data type of ‘string’. We know that we will need to have the ability to mark a todo as ‘complete’, so ‘complete’ can be a table attribute with a data type of ‘boolean’. We narrowed down the possible attributes our table can have. And we know that we want our table to auto-increment the id’s of our resources- as best practice. Which leaves us with three attributes to account for:

• id
• description
• completed

For better search ability, I think we should add a table attribute named ‘title’ with a ‘string’ data type- so we can also use the title to search for a todo. So the names of our table attributes that we will use for our todo schema are:

• id
• title
• description
• completed

Now inside of the directory where you cloned down the assignment, and we know what is required for this assignment, lets begin by first initializing a node project and running this in the command line:

npm init -y

We now have a package.json file that stores the metadata/information regarding our project.

You can do this now, or I’ll instruct you to do this later- but create an index.js file in the same directory where your node project was initialized. This index.js file is where you will:

• run your app using express()
• indicate the port you will run your application on and create your server
• perform all of your applications CRUD operations

The next step is to install the pg and knex libraries to connect to a database and to make migration files. The pg library is what we will use to connect to the postgres database. There is a special class called ‘Pool’ that we will use to create our connection to the postgres database. The knex library allows us to create migration files- and these files are used to create, update, and delete from a database. The knex library also allows us to create seed files that are used to populate the tables in our database. Lets install these libraries by running this in the command line:

npm install pg knex

Now, create a ‘database.js’ file. This file will be used to create a connection between the server and the postgres database. Alter the code below to your correct information and then add this code to the ‘database.js: file:

database pool connection in database.js

The next dependency we want to install is express which will allow us to run our application on a server and allow our app to make RESTful API calls.

npm install express

To create our Todo table that we will migrate to our database, we have to configure a knex file that will handle the connection to our database. To set this up, we can initialize a knexfile.js by running:

npx knex init 

We should now see a knexfils.js in our project folder and will see some starter code in there. We can replace that code with the code below and set the property values to the correct configuration according to your database authentication. The properties that we will use in our project from the starter code are the ‘development’ and ‘production’ properties:

migration connection in knexfile.js

When that is completed, we should begin migrating. A migration is the process of creating tables and essentially just designing their schema. A schema of the tables refers to the columns that make up the table, the data types for each column, and the relationship a table may have with other tables in our database. Once we have our database configured, we should now create our migration file that will hold the schema of our Todo table:

npx knex migrate:make create_todo_table

We should now see a folder called ‘migrations’ and this will be the location of your Todo table’s first migration and all the future migrations for the table.

We can open the only migration file we currently see and take a look at the starter code. What you see is the structure for a migration, both exporting migrations up and down. You can insert this into both functions, this is the code to create the Todo table’s schema and to revert the table back to its original state.

attributes for our todo table

Once you have created your schema, you can now migrate your table to TablePlus by running:

npx knex migrate:latest

If you navigate to TablePlus and refresh your ‘todo’ database, you should now see three tables named ‘knex-migrations’, ‘knex-migrations-lock’ and ‘todo’. The Todo’s table should consist of the attributes (id, title, description, and completed).

We can now create our seed file and create some starter task to populate our Todo table with. Let us run this in the command line to create a seed file:

npx knex seed:make 01_seed

We should now see a folder called ‘seeds’ and this will be the location of your Todo table’s first seed file and all the future seeds for the table. We will see some starter code in the seed file and we can replace it with some starter task. You can give your task any title or description, but make sure your id’s are incrementing by 1, and start at 1; like this:

01_seed.js

Once you have about three or four starter tasks, run the seed files and add them to the TablePlus database by running:

npx knex seed:run

Refresh the TablePlus application and notice your starter tasks have been added to your Todo Table.

RESTful API

Now that we are connected to our database with knex.js and can migrate and populate tables in node.js, we are done with that- and we can close all of those previous files we worked in (NOT DELETE).

If you have not already, create an ‘index.js’ file. We will be working in the ‘index.js’ file for the rest of the project. We will structure our API in our index.js file. This is also where we connect to our database.js files’ ‘pool’ class, by requiring the file. Do you remember when we coded module.exports = pool in our database.js file?

This file is where we will require ‘express()’ for running our app, the port for running our app on a server, the required ‘database’ file to make queries to the database, and all of the necessary responses for this tasks’ required endpoints.

Add this to your ‘index.js’ file as the first and only block of code for now:

requirements for running our app on a server and connecting to the database in index.js

When we make a route that handles a POST request, we will need to parse the request into a JSON, and we will need to add some middleware to handle this. Underneath your ‘port’ variable, add this line of code:

app.use(express.json())

This will ensure that the appropriate body that is being sent with every request is parsed and readable for our API.

GET — ‘/todo’ — a route to see all todos

To create this route, lets add this line of code next:

route to READ all todos (in index.js)

This response waits for the GET request to the route’/todo’ and uses SQL(structured query language) to query the database for all records of todo’s in the todo table and orders them by their id. By default, the records will be displayed in ascending order/from least to greatest by their id numbers.

GET — ‘/todo/:id’ — A route to see details about an individual todo item

To create this route, lets add this line of code:

A route to see (READ) details about an individual todo item in index.js

This response waits for the GET request to the route’/todo/:id’ and uses SQL(structured query language) to query the database for all records of todo’s in the todo table with the matching ‘:id” parameter from the route. The response is a todo object that has the corresponding id. If there is no corresponding todo with an id that matches the request parameter id, then the API responds with a ‘404 Not Found’.

POST — ‘/todo’ — Create a todo

To create this route, lets add this line of code:

route to CREATE a todo in index.js

This response waits for the POST request to the route’/todo’ and uses SQL(structured query language) to query the database and inserts a new record to the Todo table. We will not need to account for the ‘id’ in our request body because the ‘id’ column auto-increments by 1- like we specified in our schema.

PUT— ‘/todo/:id’ —the ability to update the description of a todo

To create this route, lets add this line of code:

route to UPDATE the description of a todo (in index.js)

This response waits for the PUT request to the route’/todo/:id/’ and uses SQL(structured query language) to query the database and updates a record of the Todo table with the corresponding ‘id’ that matches the request paramaters’ id. When the object with the corresponding id is found, then the completed property is set to be ‘true’.

PUT — ‘/todo/:id/complete— The ability to mark a todo complete

To create this route, lets add this line of code:

route to UPDATE the ‘complete’ property of todo (in index.js)

This response waits for the PUT request to the route’/todo/:id/complete’ and uses SQL(structured query language) to query the database and updates a record of the Todo table with the corresponding ‘id’ that matches the request paramaters’ id. When the object with the corresponding id is found, then the completed property is set to be ‘true’.

DELETE — ‘/todo/:id —Delete a todo

To create this route, lets add this line of code:

route to DELETE a todo (in index.js)

This response waits for the DELETE request to the route’/todo/:id/’ and uses SQL(structured query language) to query the database and delete a record of the Todo table with the corresponding ‘id’ that matches the request paramaters’ id. When the object with the corresponding id is found, then the todo object is deleted.