Should I not use camelCase in my column names?

Question

I am creating a GraphQL API using Node.js, which foces me to return all the field names in camelCase.

In my PostgreSQL database, I currently have all my columns named following a camelCase convention, but I am thinking: is that the best idea?

Should I use snake_case in the database columns and convert them in the back-end?

Answer 1

Use lower case in Postgres. Postgres folds identifiers to lowercase, unless you double-quote your identifiers. To make Postgres operations easier, use lower_snake. If you need to bind to an API, etc, you can create a view with “CamelCase” aliases. For example, we do this with data stored in Postgres that is pulled by Domo.

Answer 2

Quick note on this, since you're using node. I've found that knex is a great tool for building queries imperatively in node. It's not an ORM (which many say is an anti-pattern and I tend to agree).

If you use knex, you have this option:

Step 1. Use wrapIdentifier to convert from camel to snake

Step 2. Use postProcessResponse to convert from snake to camel

lodash has snakeCase and camelCase functions that should come in handy.

The reason I recommend these methods is because if you don't do this, you will either have to write your code using snake case variables (which is very idiosyncratic for JS), or you'll have to name your db columns in camel case (and constantly be wrapping your SQL query column names in "), or you'll have to have a bunch of code with the sole purpose of converting between the two.

---

Footnote:

I've also found that Objection is an ORM that defies a lot of the typical arguments against ORMs and is quite useful. It sits on top of knex and provides a huge API for interacting with your database in ways that don't completely abstract away the fact that you're interacting with a database. Here's an article on this topic in relation to Objection.