A CRUD API manages data through four basic database operations: create, read, update, and delete. You can create a simple CRUD API with just Express and a PostgreSQL database.

Start by creating an Express server to which you’ll connect PostgreSQL. Then, create the CRUD functions and hook them up to API endpoints. Once you’ve done so, you’ll be able to connect Node to PostgreSQL and perform database queries on each route.

Prerequisites for Building the API

To follow along with this tutorial, you should:

  • Install Node on your machine.
  • Install the PostgreSQL server on your machine.
  • Have a basic knowledge of Express.js.

Create an Express Server

To create an Express server, begin by creating a new directory and entering it:

        mkdir notes
cd notes

Then initialize npm:

        npm init -y

This command will generate a package.json file in the notes folder. Finally, install Express.

        npm install express

Create a new file called index.js and add the following code.

        const express = require("express");
const app = express();
 
app.use(express.urlencoded({
  extended: true
}));
 
app.use(express.json())
app.listen(3000, () => console.log("Listening on port 3000"));

This will create a new server listening at port 3000.

Create a PostgreSQL Database

Execute the following command in the psql command prompt to create a PostgreSQL database called notedb.

        postgres=# CREATE DATABASE notedb;

Run this command to list all the Postgres databases and check that you created the notedb database:

        postgres=# \l
    

Connect to the Database

First things first, connect your Node application to the PostgreSQL server. You can use the node-Postgres module.

Run the following to install it via npm:

        npm install pg

As good practice, connect to the database in a separate file.

Create a new file called db.js and add the following.

        const { Client } = require("pg");
const { user, host, database, password, port } = require("./dbConfig");
 
const client = new Client({
  user,
  host,
  database,
  password,
  port,
});
 
client.connect();
module.exports = client;

Here, you are exporting the connection string which you'll use to communicate to the database. Note you are reading the database connection settings from a config file called dbConfig.js. Therefore, create dbConfig.js and add the following data to it.

        module.exports = {
  user: "{dbUser}",
  host: "{dbHost}",
  database: "notedb",
  password: "{dbPassword}",
  port: 5432,
};

Remember to replace the database details with your own local values.

Create a PostgreSQL Table

In the psql command prompt, create a table called notes. To start with, connect to the notedb database using the \c command.

        postgres=# \c notedb

Next, create a table in the connected database using the CREATE TABLE command.

        notedb=# CREATE TABLE notes (
  ID SERIAL PRIMARY KEY,
  note VARCHAR(255)
);

This table is quite simple. It only has an ID which is an autogenerated primary key and a text field called note.

Create a Note

Instead of performing the CRUD operations in the routes, create functions that will interact with the database in a separate file.

Create a helper file called helper.js and import the connection object from db.js.

        const client = require("./db");

Use the following code to create the createNote() function.

        const createNote = (req, res) => {
  try {
    const { note } = req.body;
 
    if (!note) {
      throw Error("Send note in request body");
    }
 
    client.query(
      "INSERT INTO notes (note) VALUES ($1)",
      [note],
      (err, data) => {
        res.status(201).json({
          error: null,
          message: "Created new note",
        });
      }
    );
  } catch (error) {
    res.status(500).json({
      error: error.message,
      message: "Failed to create new note",
    });
  }
};

This function first checks if the request body included a note. If the note is absent, it throws an error.

To create a note, the function uses the INSERT clause. It returns a JSON object containing a null error message and a success message if successful.

Get All Notes

To get all the notes from the table, use the SELECT * clause.

        const getNotes = (req, res) => {
  try {
    client.query("SELECT * FROM notes", (err, data) => {
      if (err) throw err;
 
      res.status(200).json({
        err: null,
        notes: data.rows,
      });
    });
  } catch (error) {
    res.status(500).json({
      err: error.message,
      notes: null,
    });
  }
};

getNotes() sends the notes array in the response object if the query is successful.

Get Note by ID

The API will also have an endpoint that returns a note by ID. In helper.js, add a getNoteById() function.

        const getNoteById = (req, res) => {
  try {
    const { id } = req.params;
    client.query("SELECT * FROM notes WHERE id=$1", [id], (err, data) => {
      if (err) throw err;

      res.status(200).json({
        err: null,
        note: data.rows[0],
      });
    });
  } catch (error) {
    res.status(500).json({
      err: err.message,
      note: null,
    });
  }
};

This function will return a JSON object containing the note and an error object.

Update Note by ID

To update a note, you need a note and the ID of that note. You will get the note from the request body and the ID from the URL.

The updateNoteById() function uses the UPDATE clause to update an existing note with a new note.

        const updateNoteById = (req, res) => {
  try {
    const { id } = req.params;
    const { note } = req.body;
    client.query(
      "UPDATE notes SET note = $1 WHERE id = $2",
      [note, id],
      (err, data) => {
        if (err) throw err;
 
        res.status(201).json({
          err: null,
          message: "Updated note",
        });
      }
    );
  } catch (error) {
    res.status(500).json({
      err: error.message,
      message: "Failed to update note",
    });
  }
};

This function returns a success message if the table is updated and an error message if it’s not.

Delete Note by ID

To delete a note by ID from the table use the following code.

        const deleteNote = (req, res) => {
  try {
    const { id } = req.params;
    client.query("DELETE FROM notes WHERE id=$1", [id], (err, data) => {
      if (err) throw err;
 
      res.status(200).json({
        error: null,
        message: "Note deleted",
      });
    });
  } catch (error) {
    res.status(500).json({
      error: error.message,
      message: "Failed to delete note",
    });
  }
};

Now that you have created all the CRUD functions, export them.

In helper.js, add the following.

        module.exports = { createNote, getNotes, getNoteById, updateNoteById, deleteNote };

You will import them in index.js when creating the API endpoints.

Create API Routes

The final step is to create API endpoints in index.js for each of the CRUD operations.

Begin by importing the helper.js file.

        const db = require("./helper")

Next, create each endpoint.

        app.get("/notes", db.getNotes);
app.get("/note/:id", db.getNoteById);
app.put("/note/:id", db.updateNoteById);
app.post("/note", db.createNote);
app.delete("/note/:id", db.deleteNote);

A REST API to Update Your Database

Once you’re done, you can check that your API works by using a REST client like Postman, or by writing unit tests. You should be able to ensure that the five endpoints all work as you expect.

You can also inspect and manage the data you added to the database using the pgAdmin tool. It’s a GUI application that makes it easy to perform database administration and interact with PostgreSQL servers.