ExpressJs and MySQL – Better alternatives for Knex Interceptor for Encrypting Decrypting database table fields

I have a list of sensitive fields that needs to encrypted before inserting into my MySQL tables, and decrypt them post retrieving it. I thought of the following ways to implement this.

1. Implement a DATABASE Trigger that will encrypt/decrypt the values based on Insert/Update or Select. The problem with this approach is that, I have my own encryption and decryption function. So, storing the encryption password and salt in a secure place and accessing it from the Trigger doesn’t seem to work. I am currently using AWS Secret Manager to store the keys.

2. In the code, whenever I want to insert a sensitive filed, encrypt it using the function and then call the query. Likewise, once I got the encrypted value, decrypt it. But the problem with this approach is that, currently I have 10 such fields, this may increase or decrease. So, the code change needed will be too much. Wherever I am using this sensitive fields (for DB operations), I will have to call this encrypt/decrypt function.

3. The third approach which I have implemented today is that the knex interceptor:

   dbConnection.client.on('start', (builder) => {
                sensitiveFieldsInterceptor(builder);
   });
   

   In this centralised approach, sensitiveFieldsInterceptor is the interceptor attached to the knex object. This has Event Listeners and I can modify the query and query response. In the interceptor, I am checking if the sensitive fields are involved in the query or query response and accordingly, I am encrypting/decrypting the values. This is much simpler because I just need to keep an array of sensitive fields to compare against.

   const sensitiveFieldsInterceptor = (builder) => {
       builder.on('query', (queryData) => {
          // if query has sensitive field present
              encrypt(columnValue, encryptPassword, encryptSalt);
       )};
       builder.on('query-response', (response, response_obj, context) => {
          // if response_obj has sensitive field present
               decrypt(columnValue, encryptPassword, encryptSalt);
       )}
   }
   

The problem I am facing today is that, if the result has more records, the decryption is taking too much and leading to timeout exception. So, I am confused that is this the right approach or should I do something else. If this is the right approach, how do I optimise this further?