camelCase API, snake_case Database: How to Convert Without Bugs

SQL treats unquoted identifiers as case insensitive. PostgreSQL implements that by folding them to lowercase, so a column you create as userId is stored as userid. Any query that spells it userId works only because it is folded the same way. The moment someone quotes the name, as in double quotes around userId, the query is looking for a different, case sensitive identifier and fails if the stored name is lowercase.

Teams that insist on camelCase columns end up quoting every identifier in every query and every migration. One forgotten pair of quotes becomes a runtime error that tests may not catch. snake_case survives case folding unchanged, which is why it became the convention for SQL, while JavaScript, JSON APIs and many mobile clients standardized on camelCase.

The rule that works is to convert once, at one boundary, in code that you can test. Never let both conventions mix inside the same layer.

• ▸Database layer: snake_case columns and tables, always unquoted.
• ▸Application layer inside the backend: whatever your language prefers, such as snake_case in Python and Ruby or camelCase in Java and JavaScript.
• ▸API layer: pick one style for the public contract, usually camelCase for JSON, and document it.
• ▸Conversion point: the data access layer or the serializer. Most ORMs and query builders have a setting for a naming strategy that maps property names to column names automatically, which is better than hand written mappings.
• ▸Frontend: consume the API as it is. Do not convert keys again in the browser unless you have to, because every extra conversion is another place for a mismatch.

Case conversion is not always reversible. A converter has to guess where words begin, and those guesses can disagree between tools and between directions.

• ▸Acronyms: userID can become user_id or user_i_d depending on the algorithm. A good converter treats a run of capitals as one word, so parseHTTPResponse becomes parse_http_response. Going back gives parseHttpResponse, which is a different string from the original.
• ▸Digits: address2 may become address2 or address_2. Pick a rule and test it, because a mismatch means the field silently never maps.
• ▸Reserved words: a property named order or user cannot be used unquoted as a column name in many databases. Prefix or rename it at the schema level.
• ▸Collisions: two different keys such as userId and user_id become the same name after conversion, and one overwrites the other.
• ▸Non-ASCII letters: older converters split words at every accented character. Check that yours keeps them, so café_menu does not become caf_menu.

Collect every field name your API exposes into a list. Convert the list to snake_case, convert the result back to camelCase and compare with the original. Any line that differs is a field that will break. Fix those names or add explicit mappings for them. Doing this with a list takes minutes, and the JSON Key Case Converter on this site can convert whole nested sample responses so you can see the effect on real data, including objects inside arrays.

If you inherited quoted camelCase columns, do not rename everything in one risky step. A staged plan keeps production safe.

• ▸Inventory every quoted identifier. Search the migrations and the queries for double quoted names.
• ▸Rename columns with ALTER TABLE ... RENAME COLUMN inside a migration. In PostgreSQL this is a quick catalog change, not a table rewrite, but it takes a lock briefly, so schedule it.
• ▸Keep old names working during the transition with a view that exposes the previous names as aliases, or by deploying code that reads both.
• ▸Update ORM mappings and raw SQL in the same release as the rename, and remove the compatibility view after clients have moved.
• ▸Add a lint rule or a CI check that rejects new quoted identifiers so the problem does not return.

Write down three things in your contributing guide: the case style per layer, the rule for acronyms and digits, and where the single conversion happens. Teams that settle these questions once spend far less time in code review arguing about names and far less time debugging fields that arrive as undefined.