How to use postgres pgcrypto with Nodejs and Sequelize?

I will walk through from starting what steps you need to follow :)

Steps to follow to get pgcrypto plugin into the Schema you are using

1. Login to Postgres and goto the Schema used. Or If you have pgadmin running... goto Schema... right-click... click Query Tool.

2. Run this query there to check available plugins in your Postgres:

   select * from pg_available_extensions
   

   Scroll and check there if pgcrypto is available. If yes, move on to (3), else download pgcrypto plugin first.

3. Run another query, this will show what plugins are there with the Schema you have selected in (1):

   select * from pg_extension
   

   Check if pgcrypto is there. If yes, skip to (5); if no, continue to (4)

4. Run this query to bring pgcrypto plugin from extensions to current Schema supported extension:

   create extension pgcrypto
   

   You can re-verify by running the query from (3) to check if pgcrypto got pulled successfully to current Schema supported pluings.

Now we are ready to use pgcrypto in our Nodejs application

1. For the query which you want to encrypt, make use of sequelize to encrypt it. Use below code to modify the text value of that column to encrypted value:

   query: sequelize.fn("PGP_SYM_ENCRYPT", "data_to_encrypt", "secret_key")
   

2. When you save the data to the DB using create, data will be encrypted using PGP_SYM_ENCRYPT which is a method provided by the pgcrypto plugin.

3. To query or decrypt the values now, you can run this query in Postgres:

   select PGP_SYM_DECRYPT(colum_name::bytea, 'secret_key') FROM table where PGP_SYM_DECRYPT(column_name::bytea, 'secret_key' LIKE '%search_string%';
   

4. To decrypt the value in your Node application, use:

   sequelize.findAll({
     attributes: [
       [
         sequelize.fn(
           'PGP_SYM_DECRYPT',
           sequelize.cast(sequelize.col('column_name'), 'bytea'), 
           'secret_key'
         ), 
         "column_name"
       ]
     ]
   }).then(data => console.log(data))
   

NOTE: To automate the 1st part (getting extension into Schema), you can use a sequelize raw query, so that you don't have to do it manually each time required.

CREATE EXTENSION IF NOT EXISTS pgcrypto;