“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:

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

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):

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:

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!