Node-postgres: Option to convert query rows snake_case fields to camelCase

[rynz]

Is it possible for node-postgres to have a configuration option to convert snake_case database fields into camelCase automagically for the result.rows when a successful query is returned?

I understand I could write a function to loop and pass it through myself, but I feel like that would be a double-up / extra process work. As the query already builds the rows, why not have the option of camelCase conversion too rather than query building the rows, then having to re-build them myself.

[postsql]

This is something that I also would very much like to see.

+1

[hoegaarden]

I don't think such a thing should be in in node-postgres. I wouldn't want my client to change data in such a way.

This being said, I think you have (amongs others) following possibilities:

• change your sql queries: SELECT 1 AS nowThisIsSomeAwesomeCamelCase;
• do some patching, monkey style: https://gist.github.com/hoegaarden/8602233 (very little tested)

HTH

[rynz]

@hoegaarden That's why if it were an option it would cover us both. You wouldn't have to use it.

Changing the SQL queries means adding more load on the database server which I would like to avoid. Plus the SQL queries may come from an external file used by different applications.

The most convenient piece of this option is for rich JSON APIs where the SELECT statement can be converted to JSON with camelCase already applied.

Perhaps the option could be globally applied pg.defaults.camelCase = true or applied per query() call client.query('SELECT name FROM users WHERE email = $1', ['[email protected]'], callback, true) where true is to return fields as camelCase, false|undefined|null is to return fields as normal.

[rynz]

Monkey patching wise I would probably implement this in Result.prototype.addFields's for loop before the fieldDescriptions[i] is pushed into this.fields to avoid any extra unnecessary loops.

[hoegaarden]

Hi @2ix ,

Changing the SQL queries means adding more load on the database server

Really? I would have suspected (close to) zero performance impact. Do you have any benchmarks, insights ... on this topic? I'd be very interested ...

Plus the SQL queries may come from an external file ...

I can think of cases, where you don't want or can't change the queries but, in the end, want your result in a "special format", e.g. camelCased. But again, I am not convinced that such a thing should be put in node-postgres. I think some other "plugable" thing/module/whatever should be responsible for transforming the actual data coming from the server to whatever format you desire. But that's just my opinion.

Monkey patching wise I would probably implement this in Result.prototype.addFields's for loop ...

I just had a quick look and couldn't see how this could be done without changing too much code or tricking the require system too much. If you should implement or already have implemented this I'd be happy to see your approach!

Anyway, thinking more about your suggestion, it perhaps would be valuable to implement some kind of plugin/hooks infrastructure for node-postgres (no idea, how this should be implemented, I'm just thinking loud ...).

[rynz]

Hooks would be great, even if it were just a hook to call an array of fieldDescriptionTransform[] functions that a developer could push functions into accepting the raw fieldDescription object and returning the transformed version. Therefore it could be called within Result.prototype.addFields's for loop. It wouldn't have to be an array, just suggesting an option so multiple transforms could be applied by 3rd parties.

[brianc]

I'm in agreement with @hoegaarden. It doesn't seem like node-postgres should have this much cleverness built in. I think the best approach, which I have recommended before, is to monkey patch over the method you want changed: in this case Result.prototype.addFields. If requiring the Result constructor is a pain because it's not exported cleanly from the module, that's something we will happily change to make user-land modifications like this easier.

Also, I think worrying about the performance impact of one extra for loop on what is likely to be a very small number of items in the field descriptions array seems a bit like premature optimization, especially in comparison to the string manipulation function to convert those field names from underscore_case to camelCase. I've benchmarked node-postgres heavily over the years and doing things like manual loop unrolling or avoiding function calls _in most cases_ makes a surprisingly low impact on the performance. V8 is smart enough to do some of that for you when it can. The one exception to this would be in handling actual rows coming from the database: that code path is very hot & does respond well to weird tinkery optimizations, but I digress.

Another option which might be easier to monkey patch would be to have the Result.prototype.addFields function call Result.prototype.addField for each field in the array. Then you could just punch over the method dealing with individual fields, and we wouldn't have to add any complexity or more documentation & maintenance overhead to the code-base.

[rynz]

@brianc @hoegaarden If you are able to make user-land modifications easier to Result.prototype.addField that would be greatly appreciated.

[postsql]

On 01/25/2014 09:09 PM, Brian C wrote:

...

Also, I think worrying about the performance impact of one extra |for|
loop on what is likely to be a very small number of items in the field
descriptions array seems a bit like premature optimization, especially
in comparison to the string manipulation function to convert those
field names from |underscore_case| to |camelCase|.

For lots of applications you would likely want to do this string
manipulation:

|> "snake_case_string".replace(/_([a-z])/g, function(_, c) { return c.toUpperCase(); });
'snakeCaseString'

only once and and cache it so it will be just lookups after the first time.
I would expect a lookup to be faster though I have not benchmarked it.

for @hoegaarden 's sample "SOME_wired_SNAKE_____CaSe" (it needs to be in double quotes in query or else postgresql will lowercase it ) you need a bit longer regex:

"SOME_wired_SNAKE_____CaSe".toLowerCase().replace(/_+([a-z]|$)/g, function(_, c) { return c.toUpperCase(); });
'SomeWiredSnakeCase'

[hoegaarden]

If there is anybody interessted: https://www.npmjs.org/package/pg-camelcase

[brianc]

@hoegaarden that is awesome

[rynz]

@hoegaarden Thank you, this works a treat! I would suggest updating the example as it does not work unless you call the .inject() method - Unless I'm missing something?

[hoegaarden]

Oh sorry ... The example is indeed wrong. I'll fix this asap. Thank you for pointing this out!