How to use postgres pgcrypto with Nodejs and Sequelize?

1. How to use sequelize with pgcrypto plugin in postgres.
2. How to encrypt and decrypt the values of a column using sequelize
3. How to use PGP_SYM_ENCRYPT and PGP_SYM_DECRYPT using 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

Above command will help you know what all plugins you already have in Postgres. Scroll and check there if pgcrypto is available. If yes... move on to 3rd point else download pgcrypto plugin first.

3. Run another query, this will help you to know what plugins are there with the Schema you have selected in point (1) -

select * from pg_extension

Above command will help you know what all pluings are supported by the Schema you have selected. Check if pgcrypto is there, if yes- skip to point (5), if no -continue to point (4)

4. Run this command to bring pgcrypto plugin from extensions to current Schema supported extension -

create extension pgcrypto

You can verify running cmd at point(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 will save the data to the db using create- data will be encrypted using PGP_SYM_ENCRYPT which is a method provided by 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 Node application, use:

sequelize.findAll({
  attribute: [
    [
      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 sequelize raw query, so that you don't have to do it manually each time required.

CREATE EXTENSION IF NOT EXISTS pgcrypto;