Sails API development (1/2): Datalayer -models, connections, waterline, API -blueprint, and related

Introduction

This Article

• Tries to give a headstart into sails data layer modelling and connecting to basic databases like MySQL / Mongo
• Tries to explain the enormous amount of work that the sails framework does for your application

Pre-requisites

• Already know nodejs with the sails framework at the experimentation level or more
• Used tools from git projects, basics of data modelling that is required in a web app project

Background

This article can be considered a supplementary document for sails.org documentation in using sails. Assuming we are good with sails and nodejs, let's jump into datalayer and creation of an API in sails

Database Connection

We will create the database and then model it on sails. It is a personal preference to define the database and then model the entities in code. While the reverse is also interesting, I am not sure sails has tools to update /create database objects based on model changes like a PHP LaRavel or a Microsoft Entity Framework -> code-First".. kind of approach.

I will start with MongoDB.

MongoDB Database and Collections

Create a database (no collection for now).

[Remember: If you installed MongoDB and didn't explicitly setup any users... probably you may have to start here with mongo shell or even better if you forgot the password, here.]

Connections.js

Under \config\connections.js, add mongo db connection information.

model.js

Under \config\model.js, add the mongo connection name as your default.

connection: 'myMongo',

Sails Generate API

Everywhere you will hear / read .. To generate REST API, run:

sails generate api user

so let's do that and immediately, you will hear/read that you can go to something like http://localhost:1337/user

In the default use of sails framework, that will work because the default data source is sails-disk, and it will work with Mongo also.

But if you used MySQL, and didn't have a table by the name user, it won't work.

(For some of us, an error on the default sails-disk can happen, if the sails framework version downloaded didn't happen to have sails-disk properly accessible... then you have to npm install sails-disk in your project directory... or even sometimes npm install sails in your project directory - it happened often to me on windows.my bad. This I had on windows, because I was doing 'sails new <project>' without coming through an administrator cmd window.. sails could not symbolic links and suggested doing npm install under the project instead.)

Internals of the 'Generate API' Command is Like This

1. Generate a blueprint API model and routes for an api with service name or base route path as user:
   1. ​Unless anything is defined under api\controllers\UserController.js and config\routes.js
2. Connect the API to data sources, using waterline ORM, with the default connection under model.js, or use sails-disk as default:
   1. Unless the connection is over-ridden under api\models\User.js (yes, if we specify, say a MySQL connection there, it will load from different source. And each of our models can be pointed to different data connections if needed without pain)
3. If there is no Structure defined at api\models\User.js, use a default/dynamic model based on what database it is... If NoSQL, dynamic as no-sql collections can have any number of keys... if MySQL, 3 default columns are expected (explained below).

It is important to absorb the enormity (/enormousness) of the above:

Point 1: Sails uses blueprint API: blueprint is an extensive framework in itself... something that apiary.io uses (apiary is where you would go if you want your api to use on production today without having to understand all this and just use an api).

Point 2: Sails uses waterline ORM: that sails creators created, and it is an ORM which can link to both No-SQL and SQL data sources with your entities, and each model is treated separate. This is something that gives sails awesome flexibility. It also changes the expectation on the model based on the database in the underlying connection.

Even with the interesting features like above, you can edit and customize and control your app without having to touch the implementations of blueprint or waterline.

Finally, all this makes sails a framework, that has a lot to grow, and there are some structure adjustments being done in different versions, which could be breaking changes. And importantly, some patience until things gets working... especially if you are only beginning to use/consider sails.

Post Generation of Sails API

Now, let's wire this thing.

• Create your database.
• Setup connection in connections.js, and make this a default connection in config\model.js
• Let's for now leave api\models\User.js at the default
• Call the below URL and you should see an array of users (or empty array if there are no users).

http://localhost:1337/user

Setup Database structure for the API

Call below URL to add users:

http://localhost:1337/user/create?name=nodejssailstest

If you did not see a 500 server error, verify the user is added to the database.

Now, we use mongo which adds a new collection if not already present, so there may not be problems.. but if you use something like MySQL, you will see errors like table user not present.

If You Use NoSQL Like mongodb

you won't have much trouble because it doesn't have structures that your SQL should adhere to. For example, the below will work without any changes to model or the database. Only you have to do sails generate api monster to create the api routes for blueprint framework (as we use monster below):

http://localhost:1337/monster/create?name=nodejssailstest3&age=34&
gender=m&address=akjsdflakjdsf 

If You Use MySQL

Create a table with the same name as the name you used when generating your service using sails generate api (in the above examples, it will be user / monster).

The below fields are minimum requirements for an API to work on your table design:

• id column - integer / varchar values (* could be made customized to point to a different primary key field name)
• createdAt column - date / datetime / timestamp
• updatedAt column - date / datetime / timestamp

Hint: If you don't want to define a model explicitly, you can go about calling the listing url like http://localhost:1337/monster or create url and when you get a 500, see the error on the sails window and update table structure. The error would show the mysql error on the query that sails pushed to it. But, if you are using a relational database like MySQL which has definite structure to its data, then you should create a model definition like below.

api/model/User.js:

(if you are still choosing to exclude the createdAt and updatedAt columns 
 make sure they are there on the table structure)

module.exports = {

  connection: 'myMySql',
  tableName: 'user',
  attributes: {
    id: {
      type: 'integer',
      unique: true,
      primaryKey: true,
      columnName: 'id'
    },
    name: {
      type: 'string',
      columnName: 'name'
    }
  }  
};

'id' Column is Optional... If a Primary Key Column is Defined in Model

If you define an attribute in the model explicitly like below, with primary key constraint defined, then sails will map its primary keys to this column and not expect 'id' column to be there in the database:

module.exports = {

  attributes: {
      name: {
      type: 'string',
      columnName: 'name'
    },
    guid: {                   //*** suppose your design has a guid primary key column, 
                              //and no 'id' column.
      type: 'string',
      unique: true,
      primaryKey: true,
      columnName: 'guid'
    }
  }
};

Important Usage Differences between NoSQL and Relational-SQL

1. Other than default fields (id, createdAt, updatedAt), if you want any other field, like 'name' or 'age', etc., you have to explicitly add it in the model above, otherwise sails doesn't know to map it to your database column and fields not explicitly mapped will be ignored when values are passed for them on 'create' calls.
2. If your id column is not auto-increment on your database, sails will fail to insert records after the first record. You should pass the value for 'id' in your create API calls.

To Switch Between Database Connections

Again, I use the below for playing around on the default connection:

under  \config\model.js

//connection: 'myMySql',
   connection: 'myMongo',

If you want to do that only on a model, meaning you do intend to use multiple databases with different models:

 under \api\models\your-service-name.js

module.exports = {

  //connection: 'myMySql',
  //tableName: 'user',

   connection: 'myMongo',

...

Data Validation

Sails by default doesn't do any validation.

All database errors are converted into API response and sent back. So if you used NoSQL, you may not even see data errors until you use the data.

The second part of this article explains more on customizing the API to do this and more... call it Advanced stuff (if you may).

Next Part of this Article

• Sails API development (2/2): Custom methods, overriding default actions, and related