In order to be able to follow along you will need:

• Initial Project Source

• VS Code

• Node.js

• Postman or cURL

• First pull down this github repo.

• Then install it by running npm init in a terminal within the project root.

• Then create these new files ./config/db.js, ./database.js and ./setup.js in the root folder.

• This means that your project root should now look like so:

Follow this tutorial in order to setup the PostgreSQL environment on your computer. You will be ready in no time!

When you're finished with that short tutorial, keep in mind that you'll need to remember the username, password and database name later in this tutorial.

We'll use pgAdmin in order to create a message table in which we will store our message data.

• So click and expand your database

• Find Schemas and expand it

• find public and expand it

• Right click on Tables and in the popup menu select Query Tool...

• Now the Query Editor should open up

• We'll run a query which creates a table called messages with two columns in it called id and text.

• Copy/Paste the following query there

DROP TABLE IF EXISTS messages;

CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    text VARCHAR(255)
  ); 

• Click the little play icon or press f5 to run the query.

• Next right click on Tables in the menu to the left and click Refresh...

• You should now see the table and the two columns id and text that we just created.

Well done! Now we have successfully added the messages table in which we will store and read messages through our API.

Now that you've created and setup the database it's time that we make use of it!

• Well start of by modifying adding some code to the file ./config/db.js.

• Replace yourusername with your database user.

• Replace youruserpassword with your database user password.

• Replace yourdatabasename with your database name.

module.exports = {
  host: "localhost",
  user: "yourusername",
  password: "youruserpassword",
  database: "yourdatabasename",
};

Then we'll modify the file database.js in our project root. It should then look like this:

const CONFIG = require("./config/db");
const Pool = require("pg").Pool;

const pool = new Pool({
  ...CONFIG,
  port: 5432,
});

module.exports = pool;

Now we are done with database.js. Good job so far! Next - this is what I want you to do:

• Save the changes and head over to index.js

• Import the connection from database.js and store it in a variable called db.

• We can also remove the defined MESSAGES variable now, since we're going to replace the simulated database from the previous tutorial with a real database.

• This means that we can also get rid of the file messagesTable.js so go ahead and delete that file.

• With these new changes made, the first part of index.js (before the actual routes/endpoints are defined) is finished.

const express = require("express");
const db = require("./database");
const app = express();
const port = 3000;

app.use(express.json());

Now the time has come to migrate our request handlers that we created in the last section. Yay!

There's going to be quite a bit of changes for each handler so my advice is that you copy/paste the upcoming code snippets and replace them with the old ones in your project and then after each snippet, I'll explain in detail what we're doing. Note that since I've already explained how the app request methods and their callbacks work, I'll focus on the code within the callback functions. Incase you need a recap, have a look here. Sound good? Ok. Let's go!

So let's begin with our post request handler.

app.post("/", function (req, res) {
  const query = "INSERT INTO messages (text) VALUES ($1)";
  const values = [req.body.text];

  db.query(query, values, function (err, result) {
    if (err) throw err;
    const message = { id: result.insertId, text: values[0] };
    res.status(200).send(message);
  });
});

Time to migrate our GET request handlers. As you probably remember from the last tutorial of these series, we have two types of GET endpoints. One for getting all the messages and the second for getting a message with a specific id.

So let's begin with the one for getting all the messages.

app.get("/", function (req, res) {
  const query = "SELECT * FROM messages";

  db.query(query, function (err, result) {
    if (err) throw err;
    const messages = result.rows;
    res.status(200).send(messages);
  });
});

Now it's time to migrate our second GET request endpoint.

app.get("/:id", function (req, res) {
  const id = parseInt(req.params.id);
  const query = "SELECT * FROM messages WHERE id = $1";
  const values = [id];

  db.query(query, values, function (err, result) {
    if (err) throw err;
    const found = result.rows;

    if (found.length > 0) {
      res.status(200).send(found);
    } else {
      res.status(404).send({ msg: `No message with id ${id} found!` });
    }
  });
});

Time to migrate the PUT request endpoint.

app.put("/:id", function (req, res) {
  const id = parseInt(req.params.id);
  const query = "UPDATE messages SET text = $1 WHERE id = $2";
  const values = [req.body.text, id];

  db.query(query, values, function (err, result) {
    if (err) throw err;
    const updated = [{ id: values[1], message: values[0] }];

    if (result.rowCount === 0)
      return res
        .status(404)
        .send({ msg: `No message with id ${values[1]} found!` });

    res.status(200).send(updated);
  });
});

Almost done now! Time for migration of the final endpoint where we handle DELETE requests.

app.delete("/:id", function (req, res) {
  const id = parseInt(req.params.id);
  const query = "DELETE FROM messages WHERE id = $1";
  const values = [id];

  db.query(query, values, function (err, result) {
    if (err) throw err;
    const deleted = [{ id: values[0] }];

    if (result.rowCount === 0)
      return res
        .status(404)
        .send({ msg: `No message with id ${values[0]} found!` });

    res.status(200).send(deleted);
  });
});

If you have curl installed on your computer you can follow along in this section. Note that all Mac computers, with operating system version equal to or greater than OSX, comes with cURL pre-installed.

Obviously your Express server needs to be running before you try to make any requests to it. So if it's not already running, start it.

npm run start

Executing a POST request

Next we'll make a POST request from a terminal. We'll send the request to http://127.0.0.1:3000 which is equal to localhost, at port 3000 which is the port that we use for our application. We pass a JSON request body with the message text. We also pass a request header that defines that the data that we're sending is JSON data.

curl -X POST http://127.0.0.1:3000/ -d '{"text":"New message"}' -H "Content-Type: application/json"

When sending this request from your terminal, you should get a response back that looks like this:

{"id":1,"text":"New message"}

Cool! So now we've successfully created a new message with a POST request!

Executing GET requests

Now let's try to make a GET request, fetching all the messages that our server has stored so far. We should be able to fetch the 1 message that we added with the POST request. And of course if you were to add more messages, these messages should also be fetched with this request.

So let's test our GET endpoint which will return all the messages stored in the database so far.

curl -X GET http://127.0.0.1:3000/

Boom! Here's all of our messages (currently just one if you haven't sent multiple POST requests).

[{"id":1,"text":"New message"}]

Next, let's try the GET endpoint which will return a specific message based on the passed request parameter id.

So, this time, let's pass a request parameter 1 when making the GET request.

curl -X GET http://127.0.0.1:3000/1

Voila! We get the message with the requested id.

[{"id":1,"text":"New message"}]

But what if we request an id that doesn't exist in our database? Let's see.

curl -X GET http://127.0.0.1:3000/1337

Ah! We get back the error object that we previously defined - telling us that the message with id 1337 was not found. Perfect!

{"msg":"No message with id 1337 found!"}

Executing a PUT request

Now let's try updating a message. Let's update message with id 1.

curl -X PUT http://127.0.0.1:3000/1 -d '{"text":"Updated message"}' -H "Content-Type: application/json"

We get back our updated message as a response. Awesome!

[{"id":1,"text":"Updated message"}]

Executing a DELETE request

Finally, let's try to delete a message. Let's delete message with id 1.

curl -X DELETE http://127.0.0.1:3000/1

Works like a charm!

[{"id":1}]

So if you were unable to test your endpoints with cURL, or just didn't feel like doing so, the time has come to test our endpoints using Postman.

Let's start off by making sure that our Express server is running. As you already know, we run it by executing the following command from our project root.

npm run start

Executing a POST request

So, open up Postman and from the top menu, select File > New Tab.

Then select POST from the dropdown menu and input http://127.0.0.1:3000/ as the request url. Note that this is the same as inputting http://localhost:3000/ so choose whatever you prefer.

Next click on Body just below the request url bar.

Select raw in the radio button selection and in the dropdown to the right of the radio buttons, select JSON. Then press the Send button.

Now in the bottom section of Postman, you should see a response consisting of the message that we just created, as well as a Status of "200 OK".

Awesome! We've just created our first message by sending our first POST request.

Executing GET requests

Now let's try to make a GET request, fetching all the messages that our database has stored so far. Since we've only made one POST request so far, we should be able to get that message. How ever if you've made more than one POST request to create more messages, you should be able to fetch them all from this GET request.

So let's test our GET endpoint which will return all the messages stored in the database so far. We can do this by simply just changing the request type to GET from the top left drop-down menu. Note that we can leave the request body data as is - it will not be sent with the GET request anyways. Press Send when you've applied that change to the request type.

Now we should get some data back which we can find in the response body section of Postman.

Cool! So we were able to get all the messages that we had stored in our database (only one in my case, since I've only made 1 POST request so far). Now let's test our other GET endpoint which will return a specific message based on the request parameter passed with the request.

Simply add an id to the request URL. Let's get message with id 1. Then press Send again.

This time the response body should look like so (Yes I know it looks exactly the same as the previous response if you haven't made any extra POST requests):

Doesn't it just feel good when you get whatever you request? Love it.

Ok, so what happens if we try to request a message with an id that doesn't exist? Let's try it out. Let's use 1337 as an id.

Press Send and see what happens.

Wonderful! We're getting our error message that we defined earlier, with the statuscode of 404 which means that the item that we requested was not found on the server.

Executing a PUT request

Now let's try updating a message. Let's update message with id 1.

So first, change the request type to PUT and make sure that the request parameter is 1 in the request url.

Then before sending this, let's modify the request body.

Now press Send.

And the response body should now return our updated message.

Executing a DELETE request

Finally, let's try to delete a message. Let's delete message with id 1. So change the request type to DELETE and make sure that you pass the id 1 as a request parameter.

We get the deleted message back as a response within the response body.

Wooha! No message with id 1 in our database, which means that our DELETE request worked!

You're now one step closer to being table to construct a fully functional REST api using Node.js, Express & PostgreSQL. In the next tutorial of these series, we will take the API that we just built to the next level and deploy it to a cloud service.

You can find the final source code for this tutorial here.