How should we name or map SQL tables from Postgres to Node?

Question

We're switching over for new projects from .NET to Node and PostgreSQL.

Since Postgres best practices seem to be not relying on capitalization, we're naming DB columns with_underscores_to_separate_words instead of UsingPascalCase as we were with MSSQL and Linq.

Would best practice be:

1. To map all the columns to camelCase in the queries? (Tedious - that's what we're doing now, with multiple lines like "member_id as memberID" or "obj.memberID = dbObj.member_id".)

2. To automatically map somehow camelCase variables in the code with underline-separated SQL columns?

3. To just give in to Postgres naming and have my objects returned from DB queries have underscore separation in my code? (Seems undesirable - then we have non-DB objects with camel case and DB objects with underscore separation... messy.)

Would really like to use SQL queries instead of an ORM, but so far this is a sticking point.

Answer 1

We decided to go with Knex which automatically quotes all column names, so there's no casing issue. We're using camelCase and PascalCase for naming in Postgres so the DB is consistent with the code.

The disadvantage is that when running raw queries against Postgres, we need to quote the column names, which we can live with.

Edit: We're now using Objection's knexSnakeCaseMappers which handles this automatically in Postgres - camelCase in the code, snake_case in the DB. Very convenient.

Answer 2

Which one would you prefer to support? Mapping columns in the queries as in #1 is a lot of work now and in the future; automating it by passing source and result objects through humps or the like subtracts much of that constant effort, but it's another step and another place things could go wrong. The only strike against #3 is that it's a bit ugly. You can live with ugly -- turn off any camelCase lint rules and it'll barely register after a while.

You do have some options if you're dead set on casing; I know Sequelize supports switching between camelCase and snake_case if you configure your models appropriately, and for a much lower-level take there's an old driver plugin. I would, however, recommend just getting used to it.