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

Question

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.

Well you can always `SELECT first_name AS firstName, last_name AS lastName, title FROM …` - I don't think there's anything else that's more efficient than remapping them in the application. (Btw, it shouldn't be verbose, can you share your current code?) — Bergi, Sep 16 '19 at 19:17
But it will work I guess if I wrap the rename in quotes like `first_name AS "firstName"` — Matt Weber, Sep 16 '19 at 19:41

vitaly-t · Accepted Answer · 2022-12-30T01:22:17.073

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.

@MattWeber I'm not trying to boss around anybody, just trying to help :) — vitaly-t, Sep 17 '19 at 14:50

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

1 Answers1