This is the second part of the Node.js PostgreSQL tutorial, make sure to check out part 1 before proceeding.

Connecting the DB

In the previous part, we created a database using pgAdmin, initialized the server using Express, and installed all the dependencies. Now we will make the database connection and proceed with building the API. Make sure the server is up and running (npm start and pgAdmin).

1. Create a new file and name it queries.js. This is where we will make our DB connection and all the API methods or operations.

2. We are using the node-postgres module to create our connection pool. This is to make sure we do not have to reset the client every time we make a change. It is the same dependency that we installed using npm i pg.

3. In production, we use a separate file to store our credentials with security measures but we will not complicate things in this tutorial.

4. Set up the configuration and remove the comments.

   const { Pool } = require('pg');    // importing the module
   const pool = new Pool({
       user: yourUsername,    // the root user of your database
       host: yourHost,    // localhost in this case
       database: yourDBName,    // whatever you named the database
       password: yourPassword, 
       port: 8080,    // where your PostgreSQL server is running
   });

And just like that, our database is now connected with Node.js.

CRUD Operations

It's time to create the endpoints. Usually, we should use Routes and Controllers but in this case, the project is pretty small so we will use just two files, index.js, and queries.js.

Read more about Routes and Controllers

Let us create all methods first and export them so we can add them to the index.js file and create routes. Write the required functions with export.

const getAllUsers = (req, res) => {};

const addNewUser = (req, res) => {};

const checkBalance = (req, res) => {};

const transferCoins = (req, res) => {};

module.exports = {
    getAllUsers,
    addNewUser,
    checkBalance,
    transferCoins,
};

Now we should import them and create Routes in index.js.

const db = require('./queries'); // import all our methods from queries.js

app.get('/', db.getAllUsers);
app.post('/register', db.addNewUser);
app.get('/checkbalance/:id', db.checkBalance);
app.put('/transfer/:id', db.transferCoins);

Let us create all endpoints and then start testing them using VSCode itself. We do not need to install any tool like Postman.

GET all users

const getAllUsers = (req, res) => {
    const q = 'SELECT * FROM public.backend';
    pool.query(q, (err, data) => {
        if (err) {
            console.log(err);
            return res.json(err);
        }
        return res.json(data.rows).status(200);
    });
};

POST new user

Instead of directly passing the body values to the SQL query, we will use $1 as a placeholder and pass the values using const values.

Remember the previous tutorial where we added a default value of 100 for the coin item in the database. This will make sure that all new users get a balance of 100 coins initialized when they create the account.

const addNewUser = (req, res) => {
    const q = 'INSERT INTO public.backend(name) VALUES ($1)';
    const values = [req.body.name];
    pool.query(q, [...values], (err, data) => {
        if (err) return res.json(err);
        return res.send('User added').status(201);
    });
};

GET coin balance

We will make use of the parameter called id, which will return the coin balance for the user with the same ID.

const checkBalance = (req, res) => {
    const q = 'SELECT name, coins FROM public.backend WHERE id = $1';
    const values = [req.params.id];
    // consider the example:
    // http://localhost:3300/checkbalance/1
    // this will fetch coin balance for user with id 1
    pool.query(q, [...values], (err, data) => {
        if (err) return res.json(err);
        // Check if user exists or not
        if (data.rows.length === 0) return res.send('User does not exist.');
        return res.send(data.rows).status(200);
    });
};

PUT coins from one account to another

Now we can create a PUT method to transfer coins from one user to another using the required validations.

// Params ID is the receiver and body ID is the sender
// Example: http://localhost:3300/2
// body: id : 1
// coins: 100
// This will check if the receiver [id: 2] exists, confirm that the sender [id: 1] has enough coins to make the transfer, perform the transfer

const transferCoins = (req, res) => {
    const values = [req.params.id, req.body.id, req.body.coins];

    // Check if receiver exists
    const q0 = 'SELECT name FROM public.backend WHERE id = $1';
    pool.query(q0, [values[0]], (err, data) => {
        if (err) return res.json(err);
        if (data.rows.length === 0)
            return res.send('Receiver does not exist.').status(404);

        // Then make sure the sender has enough coins to make the transfer
        const q1 = 'SELECT coins FROM public.backend WHERE id = $1';
        pool.query(q1, [values[1]], (err, data) => {
            if (err) return res.json(err);
            if (data.rows[0].coins < values[2])
                return res
                    .send('You do not have enough coins to make the transfer.')
                    .status(400);

            // Now if the sender has enough coins we will proceed with the transfer
            // remove coins from sender
            const q2 =
                'UPDATE public.backend SET coins = coins - $1 WHERE id = $2';
            pool.query(q2, [values[2], values[1]], (err, data) => {
                if (err) return res.json(err);

                // add coins to receiver
                const q3 =
                    'UPDATE public.backend SET coins = coins + $1 WHERE id = $2';
                pool.query(q3, [values[2], values[0]], (err, data) => {
                    if (err) return res.json(err);
                    return res
                        .send('Coins transferred successfully.')
                        .status(200);
                });
            });
        });
    });
};

Read more about PUT vs PATCH

Executing all Queries

We are going to use Thunder Client for our API instead of Postman. Make sure to run npm start to start the express server.

POST new users

Before we move forward, we need to have a few accounts to perform transactions. Register new users with any name you like and they will get 100 coins by default in their accounts. Once you register about 4-5 users, we can move ahead with other methods.

GET all users

This will return a Response with all our database entries.

You can either write a new PUT method called addcoins to add coins to any user account using their id or manually edit the table in pgAdmin. This method is in the Repo if you need any help.

GET coin balance

We can find the coin balance of any account using their ID with this method. We are checking the balance for user with ID 4 here.

PUT coin transfer

Wrong receiver ID

If you enter an ID that does not exist then this error pops up. In this case that is 1444.

Sender with insufficient balance

This error occurs when we try to transfer more coins than the sender currently has. In this case, we are trying to transfer 100000 coins but the sender only has 300 coins.

Successful transfer of 100 coins from user ID 1 to user ID 4

Check the current balance first:

Make the transfer:

After passing the two validations, the transfer is successful and you can see the coin balance again:

You can see in the screenshots above that initially user with ID 1 had 300 coins and the user with ID 4 had 100. Now after the successful transfer, both have 200 coins.

What's next?

This completes the second part of this tutorial. We have learned:

✅ DB connection.

✅ REST API endpoints

✅ CRUD operations

I will be posting more updates on Twitter🐦, you can follow me there. Star the GitHub Repo. Have a good one!