Multi table example - nodify/nodify-persist GitHub Wiki

If you looked at the Simple Example, you probably have a good idea of the nodify-persist basics. But that example was a little unrealistic; most web applications have more than one database table / collection. This example shows how to use multiple tables, each with their own logic in different javascript modules. Again, this is a MySQL-specific example. If you're more of a Mongo person, have a look at the Mongo Example.

This example is split into three files:

  • multi-example.js - the main application that references collection logic in other files.
  • multi-user.js - the logic and schema for the "user" collection
  • multi-persona.js - ditto, but for the "persona" collection.

The flow of the example will be something like this:

  1. Setup - we create a new persistence instance and reference the collections in multi-user.js and multi-persona.js.
  2. We read a previously defined record out of the database and print it, modify it and then delete it.
  3. We then start exercising the "view" methods that use simple relational joins to access data without issuing too many queries.

At this point it may be useful to SQL users for me to describe the schema of our example. In it, we have two tables: user and persona. Each user may have multiple personas, but there is only one user per persona. Here are the table definitions our middleware creates for us when we import the user and persona modules:

CREATE TABLE user (
  user INT NOT NULL AUTO_INCREMENT KEY,
  uname VARCHAR(80) NOT NULL DEFAULT '',
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
};

CREATE TABLE persona (
  pid INT NOT NULL AUTO_INCREMENT KEY,
  user INT NOT NULL DEFAULT 1,
  pname VARCHAR(80) NOT NULL DEFAULT '',
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

These two tables are created with the following components of the schema objects found in each of the collection modules:

user.schema = {
  name: "user",
  table: {
    uid: "INT NOT NULL AUTO_INCREMENT",
    uname: "VARCHAR(80) NOT NULL DEFAULT ''"
  },
  key: "uid",
  created: true
};

persona.schema = {
  table: {
    pid: "INT NOT NULL AUTO_INCREMENT",
    user: "INT NOT NULL DEFAULT 1",
    pname: "VARCHAR(80) NOT NULL DEFAULT ''"
  },
  key: 'pid',
  created: true
};

And if you look carefully in the code, you can find where we specify data to be inserted into the database after it is created:

insert: [
  { uid: 1, uname: "Anonymous User" },
  { uid: 2, uname: "Meadhbh Hamrick" },
  { uid: 3, uname: "William Shatner" }
]

and

insert: [
  { pid: 1, user: 1, pname: "Anonymous Persona" },
  { user: 2, pname: "Meadhbh Hamrick" },
  { user: 2, pname: "Meadhbh Octopodidae" },
  { user: 3, pname: "William Shatner" },
  { user: 3, pname: "Captain Kirk" },
  { user: 3, pname: "T.J. Hooker" },
  { user: 3, pname: "Guy on airplane who sees gremlin" }
]

Which cause nodify-persist to insert the following data:

INSERT INTO user (uid, uname) VALUES (1, 'Anonymous User');
INSERT INTO user (uid, uname) VALUES (2, 'Meadhbh Hamrick');
INSERT INTO user (uid, uname) VALUES (3, 'William Shatner');

INSERT INTO persona (pid, user, pname) VALUES (1, 1, 'Anonymous Persona');
INSERT INTO persona (user, pname) VALUES (2, 'Meadhbh Hamrick');
INSERT INTO persona (user, pname) VALUES (2, 'Meadhbh Octopodidae');
INSERT INTO persona (user, pname) VALUES (3, 'William Shatner');
INSERT INTO persona (user, pname) VALUES (3, 'Captain Kirk');
INSERT INTO persona (user, pname) VALUES (3, 'T.J. Hooker');
INSERT INTO persona (user, pname) VALUES (3, 'Guy on airplane who sees gremlin');

Here are the complete multi-user.js and multi-persona.js files so you can see the collection logic we've defined for them:

multi-user.js

( function ( ) {

  var persona = require( './multi-persona' );
  var _ = require( 'underscore' );

  function user ( options ) {
    this.uid = options.uid;
    this.uname = options.uname;
  }

  if( module && module.exports ) { 
    module.exports = user;
  }

  user.schema = {
    name: "user",
    table: {
      uid: "INT NOT NULL AUTO_INCREMENT",
      uname: "VARCHAR(80) NOT NULL DEFAULT ''"
    },
    relations: {
      "persona": {
        "local": {
          "members": [ "uid", "uname" ]
        },
        "foreign": {
          "table": "persona",
          "members": [ "pid", "pname" ],
          "key": { "uid": "user" }
        }
      }
    },
    key: 'uid',
    created: true,
    insert: [
      { uid: 1, uname: "Anonymous User" },
      { uid: 2, uname: "Meadhbh Hamrick" },
      { uid: 3, uname: "William Shatner" }
    ]
  };

  user.prototype.loadPersonas = function( _complete ) {
    var that = this;
    user.dao.userViewPersona( { uid: this.uid }, function( err, data ) {
      if( err ) { return _complete( err, null ); }
      that.personas = [];
      _.each( data, function ( item ) {
        item.user = item.uid;
        that.personas.push( new persona( item ) );
      } );
      _complete( null, that );
    } );
  };

  user.prototype.toString = function ( ) {
    var output = "User(" + this.uid + ") " + this.uname;
    if( this.personas ) {
      _.each( this.personas, function( item ) {
        output += "\n " + item.toString(); 
      } );
    }
    return( output );
  }

} ) ( );

multi-persona.js

( function ( ) {

  function persona ( options ) {
    this.pid = options.pid;
    this.pname = options.pname;
    this.user = options.user;
  }

  if( module && module.exports ) { 
    module.exports = persona;
  }

  persona.name = "persona";
  persona.schema = {
    name: "persona",
    table: {
      pid: "INT NOT NULL AUTO_INCREMENT",
      user: "INT NOT NULL DEFAULT 1",
      pname: "VARCHAR(80) NOT NULL DEFAULT ''"
    },
    relations: {
      "user": {
        "local": {
          "members": [ "pid", "pname" ]
        },
        "foreign": {
          "table": "user",
          "members": [ "uid", "uname" ],
          "key": { "user": "uid" }
        }
      }
    },
    key: 'pid',
    created: true,
    insert: [
      { pid: 1, user: 1, pname: "Anonymous Persona" },
      { user: 2, pname: "Meadhbh Hamrick" },
      { user: 2, pname: "Meadhbh Octopodidae" },
      { user: 3, pname: "William Shatner" },
      { user: 3, pname: "Captain Kirk" },
      { user: 3, pname: "T.J. Hooker" },
      { user: 3, pname: "Guy on airplane who sees gremlin" }
    ]
  };

  persona.prototype.toString = function ( ) {
    var output = "Persona(" + this.pid + ") " + this.pname;
    if( this._user ) {
      output += " - " + this._user.toString();
    } else {
      output += " - User(" + this.user + ")";
    }
    return( output );
  };

  persona.prototype.loadUser = function ( complete ){
    var that = this;

    if( persona.dao ) {
      persona.dao.userRead( { uid: this.user }, function( err, data ) {
        if( err ) { return complete( err ); }
        if( data && data.length > 0 ) {
          that._user = data[0];
          complete( null, that );
        }
      } );
    }
  };

} ) ( );

Note that we define the loadPersonas() in multi-user.js and loadUser() in the multi-persona.js files. These functions use the "view" created from the "relations" section of the schema. They execute a query with an implicit join and create a new object (or objects) to hold the results.

Now let's look at the main file:

multi-example.js

#!/usr/bin/env node

var persist = require( '../nodify-persist.js' );

var persist_options = {
  providers: {
    mysql: {
      host: "127.0.0.1",
      database: "example",
      user: "insert db username here",
      password: "insert password here"
    }
  },
  collections: {
    user: "multi-user.js",
    persona: "multi-persona.js"
  },
  drop: true,
  loglevel: 1
};

var dao;
var userid;
var user;
var persona;

function _error ( f ) {
  return function( err ) {
    if( err ) {
      throw err;
    }
    
    f.apply( this, Array.prototype.slice.call( arguments, 1 ) );
  };
}

var instance = new persist( persist_options );
instance.init( _error( _post_persist ) );

function _post_persist( _target ) {
  dao = _target;

  // read anonymous user
  console.log( "\nReading Anonymous User record" );
  dao.userRead( { uid: 1 }, _error( function( data ) {
    console.log( "  " + data[0].toString() );
    _create_user();
  } ) );
}

function _create_user() {
  console.log( "\nCreating New User" );
  dao.userCreate( { uname: "Random User" }, _error( function( data ) {
    console.log( "  " + data.toString() );
    user = data[0];
    _read_user();
  } ) );
}

function _read_user() {
  console.log( "\nRereading user from database" );
  dao.userRead( { uid: user.uid }, _error( function( data ) {
    console.log( "  " + data.toString() );
    _write_user();
  } ) );
}

function _write_user() {
  console.log( "\nChanging the name of this user and committing changes" );
  dao.userUpdate( { uid: user.uid, uname: "Someone Else" }, _error( function( data ) {
    console.log( "  " + data.toString() );
    _delete_user();
  } ) );
}

function _delete_user() {
  console.log( "\nDeleting that user" );
  dao.userDelete( { uid: user.uid }, _error( function( ) {
    console.log( '  User ' + user.uid + ' deleted' );
    _read_user_again();
  } ) );
}

function _read_user_again() {
  console.log( "\nTrying to read user #" + user.uid + " again" );
  dao.userRead( { uid: user.uid }, _error( function( data ) {
    if( data.length > 0 ) {
      console.log( "  Uh oh. Looks like we didn't delete it." );
    } else {
      console.log( "  Yup. Looks like we really deleted it." );
    }
    _read_another_user();
  } ) );
}

function _read_another_user() {
  console.log( "\nReading user number 3" );
  dao.userRead( { uid: 3 }, _error( function( data ) {
    user = data[0];
    console.log( "  " + user.toString() );
    _read_persona();
  } ) );
}

function _read_persona() {
  console.log( "\nReading persona(s) for this user" );
  user.loadPersonas( function( err, data ) {
    console.log( "  " + user.toString() );
    _read_user_from_persona();
  } );
}

function _read_user_from_persona () {
  console.log( "\nReading user from first persona" );
  user.personas[0].loadUser( function( err, data ) {
    console.log( "  " + user.toString() );
    _close_connection();
  } );
}

function _close_connection () {
  console.log( '\nClosing DB Connection' );
  instance.close( function () {
    console.log( 'DB Connection Closed' );
  } );
}

Hopefully you could spot some of the patterns. If any of the features are unclear (especially the bits about the schema descriptor), check out the Developers' Guide for more info.

⚠️ **GitHub.com Fallback** ⚠️