How To Hash Password with PostgreSQL Function in Nodejs

Introduction

Storing user passwords in plain text is a major security vulnerability. In order to protect user data, it is important to hash passwords before storing them in a database. In this tutorial, we will be discussing how to hash passwords with PostgreSQL in a Node.js application by creating a PostgreSQL function and adding it to a migration file.

Creating the Users Table and Hashing Function in Migration

To create the table and the function in a migration file, you need to create a new migration file with a name like XXXXXXXXXXXXXX_create_users_table.js where XXXXXXXXXXXXXX is a timestamp, then add the following code to the file:

exports.up = async function(knex) {
  await knex.schema.createTable("users", table => {
    table.string("username").notNullable();
    table.string("first_name").notNullable();
    table.string("last_name").notNullable();
    table.string("password").notNullable();
    table.string("email").notNullable();
  });
  await knex.raw(`
  CREATE OR REPLACE FUNCTION hash_password(password VARCHAR(255))
  RETURNS VARCHAR(255) AS $$
  BEGIN
    RETURN crypt(password, gen_salt('bf'));
  END;
  $$ LANGUAGE plpgsql;
  `);
};

exports.down = async function(knex) {
  await knex.schema.dropTable("users");
  await knex.raw(`DROP FUNCTION IF EXISTS hash_password(VARCHAR)`);
};

This migration file exports two functions, up and down. The up function is used to create the table and the function when you run your migrations. It creates the "users" table with the fields you specified and creates the hash_password function that uses the Blowfish algorithm to hash the password. The down function is used to rollback the changes made by the up function. it drops the table and the function.

To run this migration file, you can use a package like knex and run the following command:

knex.migrate.latest()

Creating the Signup API

Now, we can create a Node.js API called "Signup" that will be used to insert values into the "users" table. This API will use the PostgreSQL function we created to hash the password before it is stored in the table. Here is the code for the Signup API:

const express = require('express');
const { Client } = require('pg');

const app = express();

app.use(express.json());

const client = new Client();
await client.connect();

app.post('/signup', async (req, res) => {
  const { username, first_name, last_name, password, email } = req.body;
  const query = 'INSERT INTO users (username, first_name, last_name, password, email) VALUES ($1, $2, $3, hash_password($4), $5)';
  const values = [username, first_name, last_name, password, email];

  await client.query(query, values);
  res.json({ message: 'User created successfully' });
});

app.listen(3000, () => {
  console.log('Server started on port 3000');
});

In this example, the password is passed as a parameter to the hash_password function and the hashed password is returned and stored in the password field of the user table.

Conclusion

In this tutorial, we have discussed how to hash passwords with PostgreSQL in a Node.js application by creating a PostgreSQL function. By using this function to hash the password before storing it in the "users" table, we can ensure that user data is protected in the event