convert column name format during migration

[jamesdixon]

Hi,

In Bookshelf, I see that it's possible to override parse() and format() if you want to convert from snake_case to camelCase and vice-versa when fetching or saving. Is there a similar method to do so in Knex? I'm running migrations where my table columns are snake_case, but I'm passing in an object where the properties are camelCase. I do realize that I can just change the property names to snake_case by wrapping it in quotes, but was hoping for something that I may override as can be done in Bookshelf.

Thank you!
James

[timruffles]

👍

[elhigu]

There is currently no support for this in knex. Since there are already wrapping methods for column identifiers it might even be quite easy to implement in a way that it would work everywhere, in queries etc.

Though I'm not sure if this would be out of scope... To me I don't mind having that feature. Actually it would be pretty useful also for our project, which is based on knex.

[prashaantt]

Is there a better solution to this yet, other than manually transforming the object properties between camel and snake case every time? I'm using ES6 classes to model my tables and everything would work beautifully but for this.

[elhigu]

@prashaantt no there is currently no other way and it gets even more nasty than just converting them before inserting and after fetching rows. You would also need to convert all the column names passed to query's where clause.

We ended up just using camelCase column names in our DB.

[prashaantt]

We ended up just using camelCase column names in our DB.

So I suppose now you quote all your column names when writing raw SQL queries, right? Have you found it to be a fair compromise for most practical purposes?

[elhigu]

Knex does quoting itself e.g. knex.raw('?? > ?', ['MyTable.myCol', 1]). It hasn't been a problem for us... Though we are not using knex directly. Only place where it has been a bit inconvenient is when writing SQL queries directly to psql commandline, but I can live with that.

[prashaantt]

Yes, raw SQL in the psql CLI is what I meant. Thanks for your insight.

[elhigu]

Closing in favor of #2084 which has some suggestion for initial implementation.

[joelmukuthu]

Hi all, sorry to plug but I'm working on an ORM for knex which resolves this issue, PTAL https://github.com/joelmukuthu/knorm

[elhigu]

@joelmukuthu looks like it wraps pretty big part of knex API and limits it to use only .where({ field: value }) style of queries. Does it allow using raw() at all?

[joelmukuthu]

@elhigu yeah, it wasn't fun to do that. it could also support raw() but i'd need to wrap it as well. i might have missed it, but is there a low-level method in knex's query builder that i can call or override to transform field-names to column-names (and vice-versa when data comes back from the database)?

[elhigu]

I just implemented hook for postProsessingResults for the data read from DB. Docs are found here knex/documentation#58

So now with #2261 and #2217 this should be possible to do completely in knex side.

[joelmukuthu]

@elhigu awesome, thank you! I'll try out the new hooks and provide some feedback, hopefully before 0.14 is out