I'm trying to use the PGP_SYM_ENCRYPT function to save encrypted data in a PostgreSQL database using Fastify.
The pgcrypto extension has been added using this command
CREATE EXTENSION IF NOT EXISTS pgcrypto;
I then checked if the extension was successfully added in my DB.
SELECT * FROM pg_extension WHERE extname = 'pgcrypto' AND extversion IS NOT NULL;
In my application.ts, I connect to the postgres db with this connection options:
try {
const connectionObject = {
database: config.db_name,
host: config.db_url,
user: config.db_user,
password: config.db_pass,
port: 5432,
pg: pg
}
await this.server.register(require('@fastify/postgres'), connectionObject)
await this.server.pg.query('SELECT NOW()');
console.log('DATABASE CONNECTED')
} catch(error){
console.error("CONNECTION ERROR", error);
}
await this.server.ready()
The database is connecting without any problem. Then, in my route.ts, I have a POST request that takes some data from a form, and I'm trying to insert this data with a column encrypted, just like that:
server.post(
"/api/v1/api-name",
async function(request: any, reply: any) {
try {
...
// GET THE DATA
// EXECUTING SOME CHECK ON DATA VALIDITY
...
await server.pg.query(`
INSERT INTO schema_name.table_name (data1, data2, data3, data3, data5, data6)
VALUES ($1, $2, $3, $4, $5, PGP_SYM_ENCRYPT($6 ::text, $7 ::text))
`, [data1, data2, data3, data4, data5, data6, config.secretKey]);
return reply.code(201).send({
statusCode: 202
})
} catch(error: any) {
return reply.code(500).send({
statusCode: 500,
message: error.message
})
}
}
);
But I receive this error when I execute the POST request function pgp_sym_encrypt(text, text) does not exist
Executing the query directly from dbeaver, the data is inserted and with the column encrypted


INSERT INTO schema_name.table_name (name, lastname, birthdate, birthplace, residence, fiscal_code) VALUES ('name', 'lastname', NOW(), 'birthplace', 'residence', PGP_SYM_ENCRYPT('XXXXXX00A00A000A', 'secret_key'))