javascriptpostgresqlpg-promise

Is there a way to Camel Case the column names returned from PostgreSQL using pg-promise?


When we execute queries to our PostgreSQL Database and receive responses, we then pass these responses to our clientside to display/work with.

Example:

const response = [
  {
    first_name: 'Bob',
    last_name: 'English',
    title: 'The Dude',
  },
  {
    first_name: 'Harry',
    last_name: 'Smith',
    title: 'Unknown',
  },
];

Our app then has to map over this and rename the keys in a rather inefficient and verbose manner. Is there a better way? Perhaps using pg-promise?

Worth noting we also have to convert back when we send the data as an UPDATE to the DB.

It's worth noting we are not looking to use a ORM like Objection or a query builder like Knex.


Solution

  • Event receive in the API offers a usable example of how this can be done:

    // Example below shows the fastest way to camelize all column names.
    // NOTE: The example does not do processing for nested JSON objects.
    
    const initOptions = {
    
        // pg-promise initialization options...
    
        receive(e) {
            camelizeColumns(e.data);
        }
    };
    
    function camelizeColumns(data) {
        const tmp = data[0];
        for (const prop in tmp) {
            const camel = pgp.utils.camelize(prop);
            if (!(camel in tmp)) {
                for (let i = 0; i < data.length; i++) {
                    const d = data[i];
                    d[camel] = d[prop];
                    delete d[prop];
                }
            }
        }
    }
    

    It also has been discussed in various issues on the project in the past, and documented by other developers, like in this article, which is a good way to get started with it. UPDATE: That article is obsolete for pg-promise v11.

    It is a universal approach that works for all types of queries, including streams.

    UPDATE

    The example above has been updated to use pg-promise v11 or later.