Relational data loading

[iberflow]

Not sure if others use Dexie (or IndexedDB) for pulling and managing relational data, but for my use case this is essential. Since I couldn't find anything in the API docs I wrote a small plugin myself which I think could potentially be included in the core of Dexie.

It is fairly simple and because I really struggled to figure out the right way to do it via available built-in methods I'm sure this could be extended and improved.

Improvements that I can think of:

• add support for multiple related tables
• add support for single row relationships via withOne (many-to-one, one-to-one)
• add built-in eager loading for performance

I'd be happy to add these with some guidance on how to properly use the API, because at the moment I'm not quite sure how using this method in a method chain would work with where clauses and other options.

Plugin API:

db.projects.with('project_settings', 'project_id', 'settings', 'id')

Result

[  
  {  
    "name":"project_1",
    "id":1,
    "settings":[  
      {  
        "project_id":1,
        "name":"setting1",
        "id":1
      },
      {  
        "project_id":1,
        "name":"setting2",
        "id":2
      }
    ]
  },
  {  
    "name":"project_2",
    "id":2,
    "settings":[  
      {  
        "project_id":2,
        "name":"setting3",
        "id":3
      }
    ]
  },
  {  
    "name":"project_3",
    "id":3,
    "settings":[]
  }
]

Plugin code

import Dexie from 'dexie'

/**
 * Usage: db.table_a.with('table_a_children', 'parent_id', 'children', 'id')
 *
 * This will return all items from table 'table_a'
 * where each item will have a 'children' array with linked records from 'table_a_children' table
 */
Dexie.addons.push((db) => {
  /**
   * Iterate through all items and collect related records
   *
   * @param table
   * @param column
   * @param asProperty
   * @param searchProperty
   *
   * @returns {Dexie.Promise}
   */
  db.Table.prototype.with = function (table, column, asProperty, searchProperty) {
    return this.toCollection().with(table, column, asProperty, searchProperty)
  }

  /**
   * Iterate through all items and collect related records
   *
   * @param table
   * @param column
   * @param asProperty
   * @param searchProperty
   *
   * @returns {Dexie.Promise}
   */
  db.Collection.prototype.with = function (table, column, asProperty, searchProperty) {
    var self = this

    searchProperty = searchProperty || 'id'

    return new Dexie.Promise((resolve, reject) => {
      self.toArray().then(rows => {
        var length = rows.length

        // loop through all rows and collect all data from the related table
        rows.forEach((row, index) => {
          let load = db._allTables[table]
            .where(column)
            .equals(row[searchProperty])
            .toArray()

          load.then(children => {
            rows[index][asProperty] = children
            // once the collection of related items is done, resolve the promise
            if (index + 1 === length) {
              resolve(rows)
            }
          })
        })
      })
    })
  }
})

export default Dexie

[dfahlander]

Thanks for sharing this small but relevant piece of code. Let's keep it as your dexie addon but let's advertise it on the wiki and from samples code where applicable, at least on https://github.com/dfahlander/Dexie.js/wiki/DerivedWork but we could refer to it from other relevant pages, such as some of the samples that does this kind of stuff "manually". Could also add a jsfiddle where the addon is used.

I'll probably help out with pull requests to improve the addon.

I've been thinking a lot of how to incorporate relational awareness in a Dexie addon somehow but never came to a conclusion about how to do it the best way. This could be a good start.

Extended Schema

Let me share an idea that I have (and that could be done in your addon). The relational model could be expressed already as a minimalistic foreign keys syntax in the schema, which would let the with() method have less arguments.

Example:

db.version(x).stores({
    projects: 'id',
    project_settings: 'id, project_id -> projects.id',
    project_members: 'id, project_id -> projects.id'
});

By the schema, you could see the foreign key relation between the tables. The with() method could then be expressed:

// Include related items with projects
db.projects.with({
    settings: 'project_settings',
    members: 'project_members'
});

// Include project with project_settings
db.project_settings.with({ project: 'project_id' })

An addon can override the interpretation of the schema syntax:

db.Version.prototype._parseStoresSpec = Dexie.override (
    db.Version.prototype._parseStoresSpec,
    parseStoresSpec => function (storesSpec, outDbSchema) {
        // Parse foreign keys syntax
        let foreignKeys = {},
            cleanedStoresSpec = {};
        Object.keys(storesSpec).forEach (table => {
            let indexes = storesSpec[table].split(',');
            foreignKeys[table] = indexes
                .filter(idx => idx.indexOf('->') !== -1)
                .map(idx => {
                    let fk = idx.split('->').map(x => x.trim());
                    return ({
                        index: fk[0],
                        targetTable: fk[1].split('.')[0],
                        targetIndex: fk[1].split('.')[1]
                   });
               });
            // Remove foreign keys syntax before calling original
            cleanedStoresSpec[table] = indexes.map(idx => idx.split('->')[0]).join(',');
        });

        // Call original method:
        let rv = parseStoresSpec.call (this, cleanedStoresSpec, outDbSchema);

        // Update outDbSchema (to use later in 'with' method)
        Object.keys(outDbSchema).forEach(table => {
            if (foreignKeys.hasOwnProperty(table)) {
                outDbSchema[table].foreignKeys = foreignKeys[table];
            }
        });

        return rv;
    });

The code above is written directly in wiki so it might have errors, but it should work in concept. Would need some error checks though. The idea is that you would store the foreign key information into dbSchema, which would be available in table.schema and accessible from the with method to know how the tables relate so that fewer arguments is required to the method.

EDIT: Comments appreciated!

[iberflow]

Awesome! I think your example will be a great start. Though one thing I kind of dislike is the index definition via strings. I think it gets a bit messy without a structure. My personal choice would be something like (I think this approach would add more flexibility, though it's a completely different syntax):

db.version(x).stores({
  projects: [
    {
      index: 'id'
    }
  ],
  project_settings: [
    {
      index: 'id'
    },
    {
      index: 'project_id',
      foreign: {
        belongsTo: false,
        reference: 'projects.id',
        cascade: true
      }
    }
  ],
  project_members: [
    {
      index: 'id',
    },
    {
      index: 'project_id',
      foreign: {
        belongsTo: true,
        reference: 'projects.id',
        cascade: false
      }
    },
    {
      index: 'member_id',
      foreign: {
        belongsTo: true,
        reference: 'members.id',
        cascade: false
      }
    },
  ]
});

What do you think? :)

[dfahlander]

If we would offer a structured (not string-based) syntax definition, as an alternative to the more minimalistic string-based, it would rather be something like the following:

db.version(x).stores({
    project_settings: {
        primaryKey: {
            name: 'id',
            keyPath: 'id',
            autoIncrement: false
        }
        indexes: [{
            name: 'project_id',
            keyPath: 'project_id',
            unique: false,
            multiEntry: false
        }]
    }
});

Compare that with this:

db.version(x).stores({
    project_settings: 'id, project_id'
});

I think the latter is both more ergonomic and easier to review. If you'd need more properties to the foreignKey definition, one could try finding intuitive describing syntax,for that. An example would be:

db.version(x).stores({
    projects: `id`,

    project_settings: `
        id,
        project_id -> projects.id: cascade`,

    project_members: `
        id,
        project_id -> projects.id: cascade,
        manager_id -> project_members.id`
});

How would you differentiate cascade from belongsTo?

[iberflow]

Yeah, point taken. Not sure about the differentiation though. Not even sure it's that necessary at this point. I think maybe I'll start with the basics and we'll see where it goes from there :)