node: mysql column name collisions on select, join

I’ve been playing around with the MySQL Node module. I came to a place where the async javascript callback result for selects on multi-table joins started having key name collisions.

In comes nestTables:true. Wrapping the select query in an object with the nestTables property set to true will cause the result object to use nested objects for each table.

//Return Obj has name collisions
  db.query('SELECT parents.name,\
                   jobs.type, \
                   locations.name \       
            FROM parents JOIN jobs \
              ON jobs.id=parents.jobId \
                         JOIN locations \
              ON locations.id=parents.locationId',
            function(err, result){
              console.log("LOG:",result);
              //LOG: [{type:'Teacher',
              //       name:'Home'}]
          });
//Return Obj is now broken out by table with sub properties
  db.query({sql: \
           'SELECT parents.name,\
                   jobs.type,\
                   locations.name\
            FROM parents JOIN jobs \
              ON jobs.id=parents.jobId \
                         JOIN locations \
              ON locations.id=parents.locationId',
            nestTables:true},
            function(err, result){
              console.log("LOG:",result);
              //LOG: [{parents:{name: "Joe"},
              //       jobs: {type:'Teacher'},
              //       locations:{name:'Home'}}]
          });

Note: you can also use nestedTable: "_" to force the result to use TableName_ColumnName as the object keys.

mysql: early testing & foreign key syntax

Early on in a local project, I kept finding out of date, or strange references to setting up Foreign Keys in various complicated situations in MySQL, this resulted in not only me being confused, but in constantly needing to drop tables and rebuild them. Which was annoying.

Using Node as the backend framework, it dawned on me, that I could just have three terminals open, one running the Node server, one using the mysql> prompt, and the third running terminal commands to rebuild the database using the schema.sql file. Failing quickly is always better than failing slowly.

So the order went:
1. Test something, screw up database.
2. Terminal 1: mysql> drop database people;
3. Terminal 2: $: mysql -u root < schema.sql
4. Terminal 3: execute stuff on server
5. Go back to 1.

Here's an example schema file, in case you came here just looking for Foreign Key syntax:

CREATE DATABASE people;
USE people;

CREATE TABLE parents(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE locations(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE children(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(140) NULL DEFAULT NULL,
  parentId INT,
  locationId INT,
  PRIMARY KEY(id),
  INDEX(parentId, locationId),
  FOREIGN KEY (parentId) REFERENCES parents(id) ON DELETE CASCADE,
  FOREIGN KEY (locationId) REFERENCES locations(id) ON DELETE CASCADE
);