“camelCase” < = > “snake_case” conversion with PostgreSQL and Knex.js

If you’re building a GraphQL data API, most likely you want to use lowerCamelCase names for all the fields in your GraphQL schema as recommended by the GraphQL specification. But, it’s likely that your database is using a different convention. For example, in case with PostgreSQL, the practical naming convention to use is snake_case (feel free to ask why in the comments bellow). Let’s see a couple of solutions to this problem.

Solution 1

You can use an ORM tool for Node.js such as Sequelize, Bookshelf, Objection.js, or another. Most of them are capable of converting field names to another case. But, that solution sounds boring, let’s just skip it :)

Solution 2

Convert snake_case fields to camelCase at a GraphQL level inside types’ resolve() methods. Here is an example:

This approach is quite verbose, but at least there is no “magic”, everything looks clear and straightforward. The down side is that you will end-up using mixed case in your data access code.

Solution 3

Let’s see how to handle that sort of conversion automatically with Knex.js, which is a very popular universal database client for Node.js.

Typically, you would start by creating db.js file exporting a singleton instance of the Knex client:

https://gist.github.com/koistya/10a09698e823a66e48463d4016b47850

So, you could use it in your data models like so (models/User.js):

https://gist.github.com/koistya/dd53966e233a8199f91ad3e185130bdd

Executing User.find() method would generate the following SQL query:

SELECT "id", "email", "is_admin" FROM "users"

…and, would resolve to an array of objects with a bunch of snake_case fields (ouch!). This is very easy to fix by tweaking the list of field (column) names in your data model class(es) (see line #3):

https://gist.github.com/koistya/72bb734a0ad617f692b259d72d8ad011

Now the SQL query generated by the .find() method would change into:

SELECT "id", "email", "is_admin" as "isAdmin" FROM "users"

Executing that method would return an array of perfectly shaped JavaScript objects. What’s more interesting, is how to make sure that you can use camelCase properties with .where() and .update() inputs? Luckily, it should also be easy to tweak by overriding Knex’s wrapdIdentifier(value) and wrapsAsIdentifier(value) methods:

https://gist.github.com/koistya/28c9da83c0eeeb5be5795d583459c162

That’s it! Now you can do things like User.find({ isAdmin: true }), User.update({ firstName: 'Bill' }).where({ id: 123 }), etc.

BTW, if you want to play with GraphQL, PostgreSQL and Knex, the easiest way to start is by installing Docker, the latest version of Yarn, and then running yarn create data-api— this will create the initial project structure including all the core building blocks and dev tools in place. Happy coding!

More by Konstantin Tarkus

Topics of interest

More Related Stories