Pg-promise and case sensitivity in column names

So you were innocently using pg-promise and everything was good, but one day added eg userId column to your db, and: db.any('select * from products') returned [{ userid: 1 }] instead of [{ userId: 1 }].

The reason for this is PostgreSQL downcasing column names in all of our queries. We could avoid this behavoiur by putting column names in quotes (select "userId" from products), but that would make our code a bit too heavy.

So let's:

1. rename all our db's columns from userId (camelCase) to user_id (underscore)

2. add receive property to pg options:

   const pgOptions = {
     receive: (data, result, e) => {
       camelizeColumns(data);
     }
   };
   
   const camelizeColumns = (data) => {
     const template = data[0];
     for (let prop in template) {
       const camel = pgPromise.utils.camelize(prop);
       if (!(camel in template)) {
         for (let i = 0; i < data.length; i++) {
           let d = data[i];
           d[camel] = d[prop];
           delete d[prop];
         }
       }
     }
   }
   
   const pgPackage = pgPromise.default(pgOptions);

   Here are the docs for recieve option. It takes what query methods would return and tranforms it before returning.

3. change all

   db.any('select * from products where userId = ${userId}', { userId })

   for

   db.any('select * from products where user_id = ${userId}', { userId })

---

That's it! Now you'll be getting { userId: 1 } from queries like db.any('select user_id from products').