Sails Tutorial — Chapter 5

This is the fifth article in the series. You can read the forth article at this link.

In this article, we are going to work with a database. For the purpose of learning we are going to use an in-memory database. In future articles, we will use PostgreSQL. Further, we will not write the raw queries. Instead, we are going to use ORM — Waterline. Waterline ORM is developed by the same team that developed the Sails framework and Waterline has nice/tight integration with Sails. So, we are going to use the Waterline ORM with the Sails application.

In order to work with Waterline ORM, we need to install the appropriate adapter for the given database. For example, to work with in-memory database, we need to install sails-disk adapter or npm package. But, installing this sails-disk adapter and configuring it from scratch in the Sails application is complicated and no documentation is available for the same. Instead I’m going to install sails-hook-orm package that includes the sails-disk npm package and provides needed (but not all) configurations for us.

Go ahead and install thesails-hook-orm package using following the command.

npm i sails-hook-orm

We need to create a Contact model that contains the attributes or database columns information. The models in the sails application are usually lived within the api/models folder. We do not have this models folder within the api. So, let’s create a models folder first.

mkdir api/models

Now, within this models folder, we can create a model file for the Contact. The name of the file should be Contact.js. I will follow practice to give a singular name to the model.

touch api/models/Contact.js

Open this api/models/Contact.js file and write the following code.

module.exports = {};

Let’s first define the table name using tableName settings.

module.exports = {
  tableName: 'contacts',
};

Next, we need to define columns within this table. Columns or attributes is an object that contains the details about the columns including name, type, and other constraints. For example, to define a firstName column we can write the following.

module.exports = {
  tableName: 'contacts',

  attributes: {
    firstName: {
      type: 'string',
      required: true,
    },
  },
};

The type of firstName is string and to make it not null (and with validation), I have written required to true.

Refer attribute documentation at this link to know what are the other properties that you can write for the given column.

Similarly, we can define the lastName as well.

module.exports = {
  tableName: 'contacts',

  attributes: {
    firstName: {
      type: 'string',
      required: true,
    },
    lastName: {
      type: 'string',
      required: true,
    },
  },
};

Before we go further, I would like to mention one important point and that is primary key is not auto generated by Waterline ORM. We need to explicitly mention about it. We can mention here like we did for the firstName and lastName or we can define within config/models.js. The advantage of defining within the configuration file is, it’ll create a primary key for all the models (or tables). Even further, whatever columns we define within configuration file, it will be created for every models.

Let’s create this model configuration file as models.js within the config folder.

touch config/models.js

Open this config/models.js file and write the following code.

module.exports.models = {};

Let’s define the attributes object within this default object.

module.exports.models = {
  attributes: {},
};

Here, we can define the id attribute or column as follows.

module.exports.models = {
  attributes: {
    id: {
      type: 'number',
      autoIncrement: true,
    },
  },
};

By convention, id will be marked as the primary key by the Waterline. So, don’t worry about mentioning the primary key constraint!

While we are here, how about adding two more columns that track timestamp on when it is created and when it last updates. In other words, let’s add two more attributes createdAt and updatedAt as type number with autoCreatedAt and autoUpdatedAt settings to be true.

module.exports.models = {
  attributes: {
    id: {
      type: 'number',
      autoIncrement: true,
    },
  },
  createdAt: {
    type: 'number',
    autoCreatedAt: true,
  },
  updatedAt: {
    type: 'number',
    autoUpdatedAt: true,
  },
};

Don’t worry about the type as a number. Waterline doesn’t have date or datetime or timestamp type. number type is fine for timestamp and internally it’ll help it very well. autoCreatedAt and autoUpdatedAt insert and update the timestamp when data is inserted or updated automatically These three attributes are included in all our models by default.

Now, if you run the application, you’ll get a long console message and a prompt.

This prompt is about the migration of the model into the database. Although we have defined the model, we haven’t created the database and table yet! Sails is asking us what to do in this case with three options — alter (remove everything and re-insert the data), drop (remove the data but have all the tables), or safe (do nothing). In development, they even suggest to choose alter option. We can write 1 in prompt or we can define 1 or alter as default for migrate for the development in config/models.js file. Let’s open the models.js file and add this setting.

module.exports.models = {
  migrate: 'alter',

  attributes: {
    id: {
      type: 'number',
      autoIncrement: true,
    },
  },
  createdAt: {
    type: 'number',
    autoCreatedAt: true,
  },
  updatedAt: {
    type: 'number',
    autoUpdatedAt: true,
  },
};

Now, check the terminal again! Waterline has auto migrated the schema. You should also see a folder with the name .tmp/localDiskDb in the application folder. This is where our in-memory or disk database is saved.

One more configuration we need to do in order to use this model (or any other models) in our controllers. This is one of those odd and confusing things about Sails.

Go ahead and create a file with the name globals.js within the config folder.

touch config/globals.js

Open this globals.js file and write the following code.

module.exports.globals = {};

Next, let’s define models to true in this default globals object.

module.exports.globals = {
  models: true,
};

And three more settings as follows.

module.exports.globals = {
  models: true,
  sails: true,
  _: false,
  async: false,
};

These are a little bit confusing and few of these settings are there for legacy purposes. But, if you don’t define these three settings, Sails will throw an error in the console when you try to call/use the models.

Let’s use this model within the ContactsController.js file to fetch all the contacts in index action. To fetch all the contacts, we can use the .find() method as Contact.find(). One more thing — as we have done the above configurations in globals.js file, we do not have to import the Contact model from Contact.js file even if your text-editor/IDE complains about it. Sails internally imported all the models. We just need to use it.

The .find() method returns a promise so we need to update the index function to make it async/await and then we can save the result in the contacts variable and print it.

index: async (req, res) => {
  const contacts = await Contact.find();
  console.log(contacts);

  res.json({ message: 'All contacts' });
},

Now, let’s call/open http://localhost:3000/contacts, we should see an [] in the console as we don’t have any contacts in the database yet.

Note: Here, try to remove those three settings we added in globals.js file and you’ll get an error. These errors are sometimes confusing.

As fetching all contacts is working, let’s remove that sample JSON response and send the contacts as data in index action.

index: async (req, res) => {
  const contacts = await Contact.find();

  res.json({ data: contacts });
},

To fetch a single contact, .findOne() method is used. This .findOne() method accepts an object that should have the criteria. Again, this .findOne() method returns a promise so we need to update the show function to make it async/await and then we can save the result in the contact variable to return it.

show: async (req, res) => {
  const contact = await Contact.findOne({ id: req.params.id });

  res.json({ data: contact });
},

Open http://localhost:3000/contacts/1 and it returns an empty object {} as we do not have the contact with id 1.

Going forward, let’s use the async .create() method to create a new contact in create action. This .create() method accepts the object with the key mentioned in model attributes and the values that you’re interested to save.

await Contact.create({
  firstName: req.body.firstName,
  lastName: req.body.lastName,
});

This code will create a contact but not return the created contact details. To fetch the created contact details, we need to chain the .fetch() method on it and it’ll return the details of created contact that we can save in the contact variable and return it. create action should have the following code after these changes.

create: async (req, res) => {
  const contact = await Contact.create({
    firstName: req.body.firstName,
    lastName: req.body.lastName,
  }).fetch();

  res.status(201);
  res.json({ data: contact });
},

Go ahead and call POST http://localhost:3000/contact with the JSON body that must have firstName and lastName and .create() method will save it in the contacts table. You’ll get that contact as a return object as well. For example, this is what I get after calling an API.

{
  "data": {
    "id": 1,
    "firstName": "Jane",
    "lastName": "Doe"
  }
}

Before we go further and do changes in update action, try to call http://localhost:3000/contacts and http://localhost:3000/contacts/1 we should now get some data as response.

Let’s use async .update() or .updateOne() method to update an existing contact. I’m going to use the .updateOne() method as it does some extra validation for us. We need to chain this method with the .set() method. In the .updateOne() method we need to pass the criteria whereas in the .set() method we need to pass the values that we’re interested to update.

await Contact.updateOne({
  id: req.params.id,
}).set({
  firstName: req.params.firstName,
  lastName: req.params.lastName,
});

After successful update, it’ll return the updated contact. So, we don’t have to chain the .fetch() method on the .updateOne() method. update action should have the following code after these changes.

update: async (req, res) => {
  const contact = await Contact.updateOne({
    id: req.params.id,
  }).set({
    firstName: req.body.firstName,
    lastName: req.body.lastName,
  });

  res.json({ data: contact });
},

Call PUT http://localhost:3000/contacts/1 with sample JSON that should have different values for the firstName and lastName. After a successful call, it should return the updated contact.

Finally, to delete the contact, we can use the .destroy() or .destroyOne() method. I’m going to use the .destroyOne() method as it does some extra validation for us. This async method accepts an object with criteria to delete the contact.

Here is the code we need to write in destroy action.

destroy: async (req, res) => {
  const contact = await Contact.destroyOne({
    id: req.params.id,
  });

  res.json({ data: contact });
},

Go ahead and call DELETE http://localhost:3000/contact/1 in Insomnia, and it’ll return the deleted contact for the first time. If you call the same API again, it’ll return an empty object, as the contact is already deleted. This is because we haven’t added any validation. Leave it for future articles. But, for now, we have the working API+CRUD application with database support in Sails.

Take a break and read the sixth article in this series at this link.