Tutorial: 019 - Schema - Customizing Names (Mapping)

In the last tutorial we examined a schema object that was generated by node-data-mapper's initialization routine. Here we'll look at how to customize that schema object at initialization time by providing custom table mappings (ad-hoc mappings were covered in a previous tutorial).

If you inspect the bike_shop database, you'll notice that the tables use snake case (e.g. bike_shop_bikes). This is a common naming convention for databases, but generally JavaScript uses camel case. Let's say we'd like to have node-data-mapper convert table names to camel case when select queries are executed. This can be accomplished by listening to the SchemaGenerator.ADD_TABLE event and defining a custom mapping for tables, as the following example demonstrates.

'use strict';

const MySQLDriver = require('node-data-mapper-mysql').MySQLDriver;
const driver      = new MySQLDriver(require('../bikeShopConOpts.json'));

// Listen for ADD_TABLE events during initialization, and customize table
// mappings.
driver.generator.on('ADD_TABLE', onAddTable);

driver
  .initialize()
  .then(runQuery)
  .then(printResult)
  .catch(console.error)
  .finally(() => driver.end());

function runQuery(dataContext) {
  // Retrieve the bike shop with ID 1, along with all the bikes sold
  // at the shop.
  return dataContext
    .from('bike_shops bs')
    .innerJoin('bs.bike_shop_bikes bsb')
    .innerJoin('bsb.bikes b')
    .where({$eq: {'bs.bikeShopID': ':id'}}, {id: 1})
    .select(
      'bs.bikeShopID', 'bs.name',
      'bsb.bikeShopBikeID',
      'b.bikeID', 'b.model')
    .execute();
}

function printResult(result) {
  // Note that the tables that were originally snake case (bike_shops and
  // bike_shop_bikes) are now camel case.
  console.log(JSON.stringify(result, null, 2));
}

/**
 * The table mapping (mapTo) removes any underscores and uppercases the
 * proceeding character.  Ex: bike_shop_bikes => bikeShopBikes
 * @param {Table} table - An ndm.Table instance with a name property.
 * @return {void}
 */
function onAddTable(table) {
  table.mapTo = table.name.replace(/_[a-z]/g, c => c.substr(1).toUpperCase());
}
  

Running this example ($ node example/schema/customTableMapping.js) displays the following output.

{
  "bikeShops": [
    {
      "bikeShopID": 1,
      "name": "Bob's Bikes",
      "bikeShopBikes": [
        {
          "bikeShopBikeID": 1,
          "bikes": {
            "bikeID": 1,
            "model": "F1"
          }
        },
        {
          "bikeShopBikeID": 2,
          "bikes": {
            "bikeID": 2,
            "model": "Z5"
          }
        },
        {
          "bikeShopBikeID": 3,
          "bikes": {
            "bikeID": 5,
            "model": "Sinner Complete"
          }
        },
        {
          "bikeShopBikeID": 4,
          "bikes": {
            "bikeID": 6,
            "model": "SDV2"
          }
        },
        {
          "bikeShopBikeID": 5,
          "bikes": {
            "bikeID": 7,
            "model": "Leucadia DLX"
          }
        }
      ]
    }
  ]
}
  

Note that the tables that are defined in the database using snake case now get mapped to camel case.

It's important to point out that when inserting, deleting, and updating models, the mapping of the table is used, not the name. In other words, with the above custom mappings in place, a bikeShops instance can be inserted, but not a bike_shops instance.