PDO + Angular: how to reconcile DB underscore_case with Angular's camelCase?

Question

I have an AngularJs app which connects to a database using php PDO APIs.

This is the schematic data flow:

DB:
  CREATE TABLE person (
    ...
    first_name,
    ...
  );

php API:
  $stmt = $this->db->prepare("SELECT * FROM person");
  $stmt->execute();
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  var_dump($result);
  ...
  'first_name' => 'Alice',
  ...

angular service:
  getPerson: function (id) {
    return $http({
      method: 'GET',
      url: apiUri + 'get' + '/' + id,
    }).then(handleSuccess, handleError);
  },

angular controller:
  Person.getPerson(id).then(function(person) {
    var name = person.first_name; // this throws a jslint warning
  });

The problem is SQL recommended naming standard is underscore_case, while Angular's is camelCase...

I would not like to disable jslint, which is very useful, in other respects...

I know I could avoid warnings even with

{
  "camelcase": false
}

in .jshintrc, but I'd prefer not to disable camelcase check globally...

Currently I avoid jslint warnings with "jslint comments", this way:

/* jshint camelcase: false */
var name = person.first_name;
/* jshint camelcase: true */

but my code is going to contain more jslint comments than code... Quite unreadable...

How do you (or would you) solve this issue?

score 1 · Answer 1 · answered Apr 26 '15 at 08:24

1

Use the object like you'd use an array: person['first_name']

answered Apr 26 '15 at 08:24

Tom Arad

251
2
7

*line 121 col 19 ['first_name'] is better written in dot notation.* – MarcoS Apr 26 '15 at 08:29
Hmm. the best way to do it would be to create a variable that holds the field name. `var firstName = 'first_name'; person[firstName]` Not the best, but better than disabling jshint I guess. – Tom Arad Apr 26 '15 at 08:35
I agree, not the best... :-) – MarcoS Apr 26 '15 at 09:00

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

As underscore_case is not mandatory with SQL and camelCase is mandatory with Angular it is sesible to use names suitable for both environments. In this case camelCase is acceptable with both.

In 40+ years programming I never thought underscore_case was useful. I have always preferred camelCase. Rename first_name to firstName it not only looks better but it will solve the problem.

EDIT

Column names in Standard SQL can consist of letters, underscores, and digits starting with a letter. SQL allows you to use spaces, reserved words, and special characters in a name if you enclose them in double quotation marks.

Identifiers not only have to be meaningful they have to be unique. So in choosing a naming convention the environment in which the operate has to be taken into consideration

Windows

SQL is case insesitive ie, NAME == name == Name == NaMe

Unix

SQL is case sesitive ie, NAME != name != Name != NaMe

Michael Lato recomends PascalCase for MS Server, Oracle uses UPPER with underscores. MySQL recomends lower with underscores BUT this is not mandatory See

NOTE

On Windows, InnoDB always stores database and table names internally in lowercase

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 26 '15 at 10:57

david strachan

7,174
2
23
33

Are you sure naming SQL fields does not infringe any SQL "standard" ? I have (almost) always seen SQL tables/field names underscore_case ... – MarcoS Apr 26 '15 at 16:43
From "http://stackoverflow.com/questions/14317784/why-underline-is-usually-used-in-the-sql-table-names-rather-than-camel-case: "The (ANSI) SQL standard requires that non-quoted identifiers are stored in all uppercase in the system catalogs and that non-quoted identifiers are **case-insensitive**."!!! I couldn't find a more authoritative source, though... – MarcoS Apr 27 '15 at 14:14
O.k. So your proposed answer (*using camelCase for SQL identifiers*) is **not** portable, since - for example - on Windows "aColumn" should be confused with "acoLumn", a very dangerous situation... Right? – MarcoS Apr 28 '15 at 07:25
In SQL identifiers should be meaningful. What would "acoLumn" mean? Would you have "aColumn" and "acoLumn" in the same table? – david strachan Apr 28 '15 at 08:09
The goal is to map 1-to-1 names used in Angular with DB names. Since Angular is case-sensitive, yes, "aColumn" and "acoLumn" are **two** distinct variables, an so should be on DB, to grant 1-to-1 mapping. And it's not the case. Now, my example is not very clear... You could instead think about "bithday" and "birthDay"... These names could legitimately refer to distinct javascript objects, but should be confused on DB side... – MarcoS Apr 28 '15 at 08:49
In your earlier comment SQL non-quoted identifiers are case-insensitive. It is not recommended to quoted identifiers. If you are required to use an SQL database you need to abide by its limitations. Identifiers not only have to be meaningful they have to be unique ,SQL along with Windows see "birthday" and "birthDay" as the same(Error 1060 - Duplicate column name 'birthday'). As Angular requires cameCase you are stuck with the dilemma of how to comply with both constraints. If you want 2 entities to represent "birthday" you need to use different words. ie "dateOfBirth" date "birthday" varchar – david strachan Apr 28 '15 at 09:42
So, the answer to original question sould be: "It's not possible." – MarcoS Apr 28 '15 at 10:28
YES as underscore_case is not mandatory with SQL and not applicable with Angular. Whereas camelCase is mandatory with Angular and acceptable with SQL. – david strachan Apr 28 '15 at 11:50
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/76508/discussion-between-marcos-and-david-strachan). – MarcoS Apr 29 '15 at 07:06

PDO + Angular: how to reconcile DB underscore_case with Angular's camelCase?

2 Answers2