Database Column Naming for REST API

Question

tl/dr;

Should both the column names in my db and JSON-Keys be camelCase

or

Should both the column names in my db and JSON-Keys be uppercase SNAKE_CASE

Long version

I'm designing a REST API that communicates with a sqlite database. I have been following some of the google styling guides, particularly the one for JSON. As it mentions, camelCase is the preferred style for JSON. I've also seen a comment where snake_case is up ~20% easier to read.

Currently, I have implemented functions to execute the SQL query, receive the result as a table object, then use json-smart to convert that to an actual JSON string.

The column names I've been using inside of the database are really upper SNAKE_CASE, since the are only a few references to a standardized format for database column names. This upper SNAKE_CASE column names become part of key-value pairs in the returned JSON.

So, since I'm at the very early stages of the design, does it seem reasonable to use camelCase column labels in my database or stick to the upper CAMEL_CASE, thereby breaking the JSON standard? I don't want to convert the names after an sql query in the application as this would add un-needed complexity and potential performance hinderance.

Answer 1

You might be asking the wrong question.

Some preliminaries

Speaking as a DBA, naming database objects by following one interface guideline is far from Best Practice. Mine has to serve as a source for

• HTML,
• json,
• XML,
• text,
• pdf,
• SMS (text messages),
• email,
• direct mail, and
• others I can't think of of the top of my head.

Programs written in several languages access the database.

In SQL, identifiers may or may not be delimited (surrounded by quotes). SQL standards require undelimited identifiers to be folded to upper case. That means that select camelCase is processed as CAMELCASE. To preserve case, you have to use a delimited identifier, as select "camelCase".

PostgreSQL deviates by folding to lower case. (Columns foo, Foo, and FOO are the same.)

SQLite deviates by using double quotes only to identify an identifier, not to preserve case. (Columns "foo", "Foo", and "FOO" are the same, but the name returned as a column header retains the case from the CREATE TABLE statement, regardless of whether you use a delimited identifier.)

Column names as keys

Your question only makes sense if you intend to use column names as json keys.

PostgreSQL has native support for json 9.3+; it doesn't use column names as json keys by default. For example, here PostgreSQL uses f1, f2, f3, and f4 as json keys instead of the column names "cust_id", "addr_type", etc.

select row_to_json(row(cust_id, addr_type, street_addr, city_name))
from test;

row_to_json
--
{"f1":1,"f2":"bill","f3":"123 Main St","f4":"Anytown"}

You can get arbitrary key names by using a common table expression. There are other ways; none of them are especially pleasant.

WITH data("custId", "addrType", "streetAddr", "cityName") AS (
  select cust_id, addr_type, street_addr, city_name 
  from test
)
SELECT row_to_json(data) FROM data;

row_to_json
--
{"custId":1,"addrType":"bill","streetAddr":"123 Main St","cityName":"Anytown"}

SQLite doesn't have any native support for json. All the json functionality has to come from application code.

So you need to ask, "What does the first application language I use do to create json keys from a database query?" It probably won't be the last language you use. And the next one probably won't work the same way. Keep that in mind.

I don't think I've seen a programming language that, by default, reads database metadata in generating json.