Automagically join sql tables loaded into level

level-magic.js

/*
Each table is put into it's own sublevel.
It's assumed that every table's primary key is ID.
and a foreign key is always {table}ID
If there isn't A table with that name, assume it's a self link.
(like ManagerID points back to the Employee table)
*/

//Automagically join two tables that have conventionally named fields.
// magic (nameOfLeftTable, nameOfRightTable)

function magic(left, right) {
  return pull(
    pull.map(function (data) {
      return data.key && data.value ? data.value : data
    }),
    pull.asyncMap(function (data, cb) {
      //check whether this property is just missing...
      if(Object.hasOwnProperty(data[right+'ID']) && data[right+'ID'] == null)
        return cb(null, data)
      //decide whether we want a left or a right join.
      else if(data[right+'ID'])
        //guess whether this is a join to another table, or back to this table.
        db.sublevel(db.sublevels[right] ? db.sublevel(right) : db.sublevel(left))
          .get(data[right+'ID'], function (err, _data) {
            if(err) return cb(null, data)
            data[right] = _data
            cb(null, data)
          })
      else if (db.sublevels[right])
        pull(
          db.sublevel(right)
            .sublevel('index')
            .search([left+'ID', data.ID], {keys: false}),
          pull.collect(function (err, ary) {
            if(err) return cb(null, data)
            if(ary.length)
              data[right + 's'] = ary
            cb(null, data)
          })
        )
      //last resort: just do nothing.
      else
        return cb(null, data)
    })
  )
}

to use:

pull(
  pl.read(db.sublevel('Customer')), //read the Customer table
  magic('Customer', 'Product'),      //magically join it to the Product table
  pull.collect(console.error)           //output all results...
)