Node.js use PostgreSQL

 PostgreSQL is a free, open-source relational database management system(RDBMS). It become more popular. Let's learn how to use it. 

Download and postgreSQL from the official website. 

If Visual Studio Code is used for development, we can install extension to have a GUI control panel for setting postgreSQL.

        After setup, run the code in file database.sql inside folder usePostgreSQL of the source code stored in github to create table and insert test data.

         We copy the code from post Node.js check authorization for protected api service/webpage. And modify file repository.js to extract data from postgreSQL database instead of from the array of object.

node-postgres

         We will use node-postgres module. We can go to this website to see the document. Type below command in terminal to install the module

        npm install pg 

         Then we import and setup the module with below code. Since our application here use single query each time, we can use Pool class. The Pool class contains multiple clients used to connect to the database. It assign clients to each query request to the database and keep the client idle after it finished the query. Therefore, it allows multiple users to access the database at the same time as long as there is idle client in the pool.

import { Pool } from 'pg'

const pool = new Pool({

host: 'localhost',

user: 'postgres',

password: '123456',

port: 5432,

database: 'nodetest'

});

        We pass the path and port number connected to the postgreSQL server in the config (postgreSQL run at the local computer in this example). We also provide the database used for this example and the corresponding postgreSQL account (username, password) for the database.

         And we modify the function getUserInfoByName(). The function pool.query() is asynchronous so we add keyword async/await here to return the result after we get that from the query. We catch the exception and return null as result here for simplicity. For better user experience, we should inform the client the reason of failure access.

        The first argument of pool.query() is the postgreSQL command. If we need to insert javascript variable inside the command. It is recommended to put the variable inside an array and pass the array as the second argument of pool.query(). Then we put the word $X (where X is a positive integer) at the position where the variable should be in the command. The sequence of the variable in array must follow the sequence of X. This method can avoid SQL injection security issue.

async function getUserInfoByName(username){

try{

const result = await pool.query("SELECT * FROM users WHERE name=$1", [username]);

if(result.rows.length == 0)

return null;

return result.rows[0];

}catch(err){

console.error(err);

return null;

}

}

         In the file index.js, we need to add keyword async/await too.

passport.use(

"local",

new Strategy(

{usernameField: "username", passwordField:"password"},

async function verify(username, password, cb){

const userCredentials = await repository.getUserInfoByName(username);

if(!userCredentials)

return cb(null, false, { message:"User does not exist"});

if(password === userCredentials.password){

const userInfo = {};

userInfo.name = userCredentials.name;

userInfo.id = userCredentials.id;

//only provide the necessary info to callback function

return cb(null, userInfo);

}

return cb(null, false, {message: "incorrect password"});

}

)

);

         We use the command in file database.sql to create users table and input the account information. Now the users table should have 3 users.

         Now we can test POST /login function. The postman receive the token.  

        We modify every function in repository.js so that we retrieve the user, service and permission data from the database. We need to apply the keyword async/await to the functions involved of those function in service.js and index.js too.

       In addition, we change the name of variable user.roleId to user.role_id if the user object is returned by the database. It is because we use role_id as the column name in the users table of the database. The affect function is checkPermission(), isServicePermit() and permissionCheck().

const permitServices = await repository.getPermitServiceByRole(user.role_id); 

         After that, we test the server with Postman.

 The result of put checkPermission() as middleware of the protected route

 The result of check isServicePermit() inside each protected route function

The result of wrapping service inside permissonCheck() function

         3 kind of authorization methods work properly when use PostgreSQL database as storage. 

The completed code is stored in github.