QAVideos (2) – Add Custom Model and ORM to Node.js

2 Replies

(updated: 20170318)
This is part 2 in a series to build QAVideos. QAVideos is a Question and Answer application that uses videos instead of text. Think of QAVideos as StackOverflow meets Youtube. QAVideos will use LoopBack, API Connect, Open API Initiative (OAI formerly Swagger.io), OpenWhisk, Object Storage.

In part 1 ‘QAVideos (Part 1), Adding User Management to Node.js with API Connect‘, I showed how to add User Management to a Node.js app using API Connect.
In this part 2, I will add a custom data model, i.e. a Video model and use ORM to persist data to a PostGreSQL database.
Part 3 (to be updated) is found here, which adds model extensions and uses Open API Initiative (formerly Swagger) support.

Source code for QAVideos is released per part via https://github.com/remkohdev/qavideos/releases.

Requirements

  • Install Node.js and npm,
  • Install API Connect.
  • Check if the ‘apic’ tool is installed, by running ‘apic -v’ from the commandline. If not installed, follow the installation instructions, here.
  • Get the source code for part 1 of this tutorial and follow the installation instructions for QAVideos Release v1.0.0. First clone the repository, then make sure you fetch all the remote tags to your local repository, and then checkout the v1.0.0 tag.
    git clone https://github.com/remkohdev/qavideos.git
    git fetch --all --tags --prune
    git checkout tags/v1.0.0

Table of Contents

  1. Create Data Model
  2. Define Relation
  3. Adding ACL
  4. Add Video Functionality
  5. Add Data Source

1. Create Data Model

First, test if QAVideos (part 1) is running correctly. Open a commandline, change your directory to the root directory of your QAVideos application, type ‘apic start’, and browse to ‘http://0.0.0.0:4001‘ in your browser.

Now, I want to add a custom model ‘Video’ so that users can manage their list of videos. To do this, I create a model for the Video with the ‘apic’ tool, and define the relationship between Video and User (a User can own many videos), and specify the access level of Users to Video objects using an Access Control List (ACL).

To create models with ‘apic’ you can use the ‘apic create –type model’ command to run the model generator. I will create a Video model with the following properties:

  • title (string; required),
  • url (string; required),
  • username (string; not required),
  • date_published (date; not required),
  • likes (number; not required),
  • dislikes (number; not required),
  • votes (number; not required).
$ apic create --type model Video

Open your IDE.

This creates two files: ~/common/models/video.js and ~/common/models/video.json. The ‘video.js’ file exports the video.js module as a function that takes a Video model as a parameter. The ‘video.json’ file is the configuration file for the Video model.

video.js

use 'strict';

module.exports = function(Video) {

};

video.json

{
  "name": "Video",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "title": {
      "type": "string",
      "required": true
    },
    "url": {
      "type": "string",
      "required": true
    },
    "username": {
      "type": "string"
    },
    "date_published": {
      "type": "date"
    },
    "likes": {
      "type": "number"
    },
    "dislikes": {
      "type": "number"
    },
    "votes": {
      "type": "number"
    }
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": {}
}

Also take a look at the ‘~/server/model-config.json’ file. Scroll to the bottom and you will see the Video model has been added. Its dataSource propertie is set to “db” and the visibility is set to “public”:true.

2. Define Relation

In our case of the QAVideos app, I want to create a 1-to-many relation between the User model and the Video model. A User should be able to own many Videos, and a Video should be owned by a single User.

To add a relationship between the models User and Video, use the ‘apic loopback:relation’ command to define the one-to-many relation as follows.

$ apic loopback:relation

This results in the following ‘relations’ configuration in the ‘~/common/models/video.json’ file. You can also directly add the relation configuration to the ‘~/common/models/video.json’ file.

"relations": {
  "videoBelongsToUser": {
    "type": "belongsTo",
    "model": "User",
    "foreignKey": "videotouserid"
  }
},

3. Adding ACL

To define access control to the Video model, I will use the ACL command ‘apic loopback:acl’. I want to create the following access controls:

  • Deny everyone all endpoints, as the default behavior.
  • Allow everyone to view videos by adding a ‘READ’ permission.
  • Allow authenticated users to ‘EXECUTE.create’ videos.
  • Allow the video owner to edit and thus delete videos by adding a ‘WRITE’ permission.
$ apic loopback:acl

This results in the modification of the ‘~/common/models/video.json’ file, the acl generator will add the following lines.

"acls": [
{
  "accessType": "*",
  "principalType": "ROLE",
  "principalId": "$everyone",
  "permission": "DENY"
},
{
  "accessType": "READ",
  "principalType": "ROLE",
  "principalId": "$everyone",
  "permission": "ALLOW"
},
{
  "accessType": "EXECUTE",
  "principalType": "ROLE",
  "principalId": "$authenticated",
  "permission": "ALLOW",
  "property": "create"
},
{
  "accessType": "WRITE",
  "principalType": "ROLE",
  "principalId": "$owner",
  "permission": "ALLOW"
}],

Regenerate the Angular Services

With a new model added to our app, from the command-line re-run the ‘lb-ng’ command to add an Angular services SDK for the models in the QAVideos app.

$ lb-ng server/server.js client/js/lb-ng-services.js

$ lb-ng server/server.js client/js/lb-ng-services.js
Loading LoopBack app "/Users/remko/dev/src/github/remkohdev-qavideos/server/server.js"
Generating "lbServices" for the API endpoint "/api"
Warning: scope User.accessTokens targets class "AccessToken", which is not exposed
via remoting. The Angular code for this scope won't be generated.
Saving the generated services source to "/Users/remko/dev/src/github/remkohdev-qavideos/client/js/lb-ng-services.js"

4. Add Video Functionality

Now, we are ready to add the model back into the web functionality and list all videos, list videos by user, add videos, and edit videos.

Modify the ‘~/client/index.html’ template and add the following list items to the menu, under the logout list item.

The ‘Edit Video’ and ‘Delete Video’ functionality is added to each video listed in the ‘My Videos’ page.

Now I will add the matching HTML pages, the Router UI states and the Angular controllers for Video.

Modify the app.js Angular client and add the following states. 

.state('my-videos', {
  url: '/my-videos',
  templateUrl: 'views/my-videos.html',
  controller: 'MyVideosController',
  authenticate: true
})
.state('add-video', {
  url: '/add-video',
  templateUrl: 'views/video-form.html',
  controller: 'AddVideoController',
  authenticate: true
})
.state('edit-video', {
  url: '/edit-video/:id',
  templateUrl: 'views/video-form.html',
  controller: 'EditVideoController',
  authenticate: true
})
.state('delete-video', {
  url: '/delete-video/:id',
  controller: 'DeleteVideoController',
  authenticate: true
})

The ‘all-videos’ state was previously already defined, but we need to add a controller object called ‘AllVideosController’, but make sure to not create a duplicate definition of state. Also add the authenticate property.

.state('all-videos', {
  url: '/all-videos',
  templateUrl: 'views/all-videos.html',
  controller: 'AllVideosController',
  authenticate: true
})

Note that in the ‘edit-video’ and ‘delete-video’ states, we also define the video.id in the ‘url’ property that is passed as a parameter in the ‘edit-video’ and ‘delete-video’ calls as ‘:id’.

In the ‘~/client/views/’ directory add the following pages:

  • my-videos.html,
  • video-form.html, and
  • forbidden.html.

Edit the following views as follows:

all-videos.html

<section>
  <article ng-repeat="v in videos.slice().reverse()">
    <header>
      <h1>{{v.title}}</h1>
    </header>
    <p>id: {{v.id}}</p>
    <p>url: {{v.url}}</p>
    <p>by: {{v.username}}</p>
    <p>date published: {{v.date_published}}</p>
    <p>votes: {{v.votes}}, likes: {{v.likes}}, dislikes {{v.dislikes}}</p>
  </article>
</section>

my-videos.html

<section>
  <article ng-repeat="v in videos.slice().reverse()">
    <header>
      <h1>{{v.title}}</h1>
    </header>
    <p>id: {{v.id}}</p>
    <p>url: {{v.url}}</p>
    <p>by: {{v.username}}</p>
    <p>date: {{v.date_published}}</p>
    <p>likes: {{v.likes}}, dislikes {{v.dislikes}}</p>
    <div class="actions" ng-show="currentUser">
      <button ui-sref="edit-video({ id: v.id })">Edit</button>
      <button a ui-sref="delete-video{ id: v.id })">Delete</button>
    </div>
  </article>
</section>

Note that the video.id is passed as a parameter in the ‘edit-video’ and ‘delete-video’ function call.

video-form.html

<section>
  <form name="form" ng-submit="submitVideo()">
    <fieldset>
    
      <legend>{{action}} Video Form</legend>

      <div class="form-group">
        <label>Title</label>
        <input type="text" ng-model="video.title">
      </div>    
      <div class="form-group">
        <label>URL</label>
        <input type="text" ng-model="video.url">
      </div>     
      <div class="form-group">
        <label>Username</label>
        <input type="text" ng-model="video.username">
      </div>
      <div class="form-group">
        <label>Date Published <i>(yyyy-mm-dd)</i></label>
        <input type="text" ng-model="video.date_published">
      </div>    
      <div class="form-group">
        <label>Votes</label>
        <input type="text" ng-model="video.votes">
      </div>  
      <div class="form-group">
        <label>Likes</label>
        <input type="text" ng-model="video.likes">
      </div>     
      <div class="form-group">
        <label>Dislikes</label>
        <input type="text" ng-model="video.dislikes">
      </div>
      
      <div class="actions">
        <button>{{action}} video</button>
      </div>
      
    </fieldset>
  </form>
<section>

forbidden.html

<section>
  <article>
    <header>
      <h1>Forbidden</h1>
    </header>
    <p>An error occurred.</p>
  </article>
</section>

To add the video controller, create a new file ‘~/client/js/controllers/video.js’. Add the ‘AllVideosController’, ‘MyVideosController’, ‘AddVideoController’, ‘EditVideoController’ and the ‘DeleteVideoController’ to the ‘~/client/js/controllers/video.js’ file.

Then, link the new script in the ‘index.html’ file, right below the ‘js/controllers/auth.js’ script.

<script src="js/controllers/video.js"></script>

Test to see if your configuration is running correctly, by running your application from the commandline using ‘apic start’ and opening the ‘http://0.0.0.0:4001/‘.

  1. Register a new User,
  2. Login with the new User,
  3. Add a new Video,

    When successfully added,
  4. Go to My Videos,
  5. Edit the Video, change votes to 1,
  6. Log out,

Go back to the command-line and run ‘apic edit’. The API Connect Designer will open, login with your Bluemix account.

Click the Explore button. You will see the API Connect Explorer with the API documentation that is generated from the Open API Initiative (OAI) API definition file.

For instance, let’s create a new user, under the Paths menu, click the POST /Users.

Scroll a little bit down, until you see the ‘Call operation’ button. Under the ‘Model instance data’ enter the following json in the data line:
{
"username": "user2",
"email": "user2@qavideos.com",
"password":"pw"
}
and press the ‘Call operation’ button.


A second user was created. Note that if you stop the application from running, using the ‘apic stop’ command, the ‘db’ datasource will be removed from memory, and you will loose the two created users and the video. Therefor, in the next step I will replace the in-memory database with a persistent database like PostGreSQL.

5. Add Data Source

By default, API Connect used an in-memory database. Instead of using an in-memory database, I want to use a PostGreSQL database for persisted storage, though you can choose any other data storage supported by API Connect. Because we used the default in-memory database so far, use and video information was lost each time we restarted or stopped the application.

First, from the command line, install the correct database connector, in this case a PostGreSQL connector:
$ npm install --save loopback-connector-postgresql
Or for MySQL you need to use the MySQL connector:
npm install --save loopback-connector-mysql

Install PostGres, either on your local machine or use a remote PostGres installation, and create a new database instance.

If you want to use a database on Bluemix, go to http://bluemix.net and login to your Bluemix account. If you do not yet have a Bluemix account, signup for a free account now to create a free tier PostGreSQL database. In Bluemix, go to the Catalog, browse to the ‘Data & Analytics’ section, click the ElephantSQL service, select the default free Tiny Turtle version, and click the CREATE button.

Open the Elephant Dashboard, and in the Dashboard click the ‘Details’ button to see your database credentials.

Now, with the obtained database connection credentials, I generate the data source in the API Connect apic cli.
$ apic create --type datasource postgresdb
or
$ apic create --type datasource mysqldb

Don’t use a hyphen in your name. This process creates a new data source reference in the ‘~/server/datasources.json’ file, with the default memory database and the newly configured postgresdb connector.

{
  "db": {
    "name": "db",
    "connector": "memory"
  },
  "postgresdb": {
    "host": "db.elephantsql.com",
    "port": "5432",
    "url": "postgres://username:passw0rd@.db.elephantsql.com:5432/db",
    "database": "w",
    "password": "passw0rd",
    "name": "postgresdb",
    "user": "w",
    "connector": "postgresql"
  }
}

Now modify the ‘~/server/model-config.json’ file and replace the ‘db’ value for the ‘dataSource’ properties on the object models by the new ‘postgresdb’ dataSource.

"User": {
  "dataSource": "postgresdb"
},
"AccessToken": {
  "dataSource": "postgresdb",
  "public": false
},
"ACL": {
  "dataSource": "postgresdb",
  "public": false
},
"RoleMapping": {
  "dataSource": "postgresdb",
  "public": false
},
"Role": {
  "dataSource": "postgresdb",
  "public": false
},
"Note": {
  "dataSource": "postgresdb"
},
"Video": {
  "dataSource": "postgresdb"
  "public": true
}

The last thing that remains to do now, is to use the ‘automigrate’ tool in StrongLoop to generate the tables that map to our data model. Create a new directory ‘~/server/bin/’ and in it, add a new file ‘~/server/bin/automigrate.js’.

var app = require('../server');
var dataSource = app.dataSources.postgresdb;
dataSource.automigrate([
  'User', 
  'AccessToken',
  'ACL', 
  'RoleMapping',
  'Role', 
  'Video'
], function(err) {
  if (err) throw err;
});

To run the automigrate script, execute the following command from the project root.

node server/bin/automigrate.js

videoapp_automigrate
If you use the ElephantSQL service via Bluemix, you will get an error ‘too many connections for role’ because you are creating too many models at once. You will have to comment out the models and run the automigrate for each model at a time. If you use a local PostGreSQL, you will be good.

var app = require('../server');
var dataSource = app.dataSources.postgresdb;
dataSource.automigrate([
  'User', 
  'AccessToken'/**,
  'ACL', 
  'RoleMapping',
  'Role', 
  'Video'*/
], function(err) {
  if (err) throw err;
});

Check your PostGres installation to make sure the tables were created successfully. The ElephantSQL service via Bluemix has a database Browser that you can access via the ElephantSQL service icon.

Or when you use pgAdmin:

Now, start your application again with the ‘apic start’ command and in your browser go to http://0.0.0.0:4001.

Now when you register a new user, the user is persisted to the PostGres database. Login with the created used, and add a new Video. Check in the PostgreSQL database to see that the Video is now persisted to the Video table. Also note, the column ‘videotouserid’, which was added by API Connect as the Foreign Key relation from Video to User.

To get the source code for QAVideos (part 2) go https://github.com/remkohdev/qavideos/releases.

In the next part, Part 3, I will extend the built-in User model to a child model called Member, add extend the Video model to a Question and Answer model.

Leave a Reply

Your email address will not be published. Required fields are marked *