CGPA Calculator with AdonisJS: CRUD and Relationships

CGPA Calculator with AdonisJS: CRUD and Relationships

Hey there, welcome to the third tutorial in the series. In the previous tutorial, we added authentication to our APIs. If you will like to skip the previous steps, clone the repo and checkout to the authentication branch, then code along.

The course model

course-model.png The course model has 8 fields with 2 timestamps. To add the course table to our database, we

  1. Create the Course model with migrations
  2. Modify the migration file to include necessary fields
  3. Run the migration

Creating the Course model with migrations

adonis make:model Course -m

# Output
# ✔ create  app/Models/Course.js
# ✔ create  database/migrations/1606416525019_course_schema.js

The -m flag creates the migration file.

Setting the required fields

Open the newly created course_schema and add this code

      table.string("title").nullable();
      table.string("code").notNullable();
      table.enu("grade", ["A", "B", "C", "D", "F"]).notNullable();
      table.integer("credit_load").unsigned().notNullable();
      table.enu("semester", [1, 2, 3, 4]).notNullable();
      table
        .enu("level", [100, 200, 300, 400, 500, 600, 700, 800, 900])
        .notNullable();
      table
        .integer("user_id")
        .unsigned()
        .nullable()
        .references("id")
        .inTable("users")
        .onUpdate("CASCADE")
        .onDelete("CASCADE");

Notice that grade and level are implemented with table.enu. An enum only allows defined values. Refer to knex.js docs for more info on datatypes. The last column, user_id is a foreign key.

      table
        .integer("user_id")
        .unsigned()
        .nullable()
        .references("id")
        .inTable("users")
        .onUpdate("CASCADE")
        .onDelete("CASCADE");

Notice the onUpdate("CASCADE") and onDelete("CASCADE") attached? This means that changes in the user will reflect here. If the user is deleted, the courses with their id are also deleted.

Go ahead and run the migration.

adonis migration:run

Course CRUD routes

If we were to create individual routes for CRUD, we will end up with >4 routes.

Route.get('course', 'CourseController.index')
Route.post('course', 'CourseController.store')
Route.get('course/:id', 'CourseController.show')
Route.put('course/:id', 'CourseController.update')
Route.delete('course/:id', 'CourseController.destroy')

Adonis gives us a cleaner way to handle such routing: Route Resources! Add this line to routes.js

  Route.resource("courses", "CourseController").apiOnly().middleware(["auth"]);

Refer to the docs for more info on the apiOnly and route resources in general.

Course Controller

We will add logic for the five methods above: index, store, show, update and destroy. But first, let import the necessary.

const Course = use("App/Models/Course");
const { validateAll } = use("Validator");

The store method

Add the store method below to CourseController.js.

  async store({ auth, request, response }) {
    try {
      const {
        title,
        grade,
        code,
        credit_load,
        semester,
        level,
      } = request.all();

      const rules = {
        title: "required",
        grade: "required|in:A,B,C,D,F",
        code: "required",
        credit_load: "required|integer",
        semester: "required|in:1,2",
        level: "required|in:100,200,300,400,500,600,700,800,900",
      };

      const validation = await validateAll(request.all(), rules);

      if (validation.fails()) {
        return response.status(400).send(validation.messages());
      }

      const course = await Course.create({
        title,
        grade,
        code,
        credit_load,
        semester,
        level
      });

      const user = await auth.getUser();
      await user.courses().save(course);

      return response.status(201).send(course);
    } catch (error) {
      console.log(error);
      return response.status(500).send(error);
    }
  }

If you compare the rules and the datatypes defined in the migration, you'll discover the similarity between them. If we weren't using a validator, the database will throw errors when we try inserts or updates with wrong data. But it's best to validate data at the application layer.

A very interesting part of the store method is how relationships are handled. Since user and courses share a 1:N relationship, we need to tell the database to set a foreign key for each course created. We do that using

await user.courses().save(course);

In our User Lucid model, we will define a courses method like so

  courses() {
    return this.hasMany("App/Models/Course");
  }

This method returns a relationship binder linked to Course. So when the save method is called on this method, the user's id will be inserted in the user_id field of the course in the database. This may seem confusing at first, but to better understand it, we need to create courses.

Creating courses

Prop open your favourite API testing app and call this route POST /api/v1/courses. Ensure you have the right JWT set. You can use the JSON below for your request body

{
    "title": "Metals and Non metals",
    "grade": "A",
    "code": "MNM 304",
    "credit_load": "4",
    "semester": "2",
    "level": "100"
}

You should get a response similar to

{
    "title": "Metals and Non metals",
    "grade": "A",
    "code": "MNM 304",
    "credit_load": "4",
    "semester": "2",
    "level": "100",
    "created_at": "2020-11-27 00:46:00",
    "updated_at": "2020-11-27 00:46:00",
    "id": 4,
    "user_id": 1
}

You can go ahead and prevent the created_at, updated_at and user_id fields from showing up by adding this to the Course model.

  static get hidden() {
    return ["created_at", "updated_at", "user_id"];
  }

Returning created courses

Single course

To return a single course, we will utilize a request parameter for course id.

  async show({ auth, params, response }) {
    try {
      const course = await Course.find(params.id);

      if (!course) {
        return response.status(404).send("Course not found");
      }

      const courseOwner = await course.user().fetch();
      const requester = await auth.user;
      if (requester.id !== courseOwner.id) {
        return response
          .status(403)
          .send("You cannot view another user's course");
      }
      return response.status(200).send(course);
    } catch (error) {
      return response.status(500).send(error);
    }
  }

First, we find a course that matches the given id. If no such course exists, we return a 404. We obtain the course owner by calling the user() method on the course Lucid model. Remember how we used hasMany when we were defining the relationship between user and course? Now, we will use belongsTo to define the inverse relationship. Add the user method below to your Course model.

user() {
    return this.belongsTo("App/Models/User");
}

Back to the show method. We check that the course owner's id matches the user calling the route. If it doesn't, we return a 403 else we return the course. Below is an attempt to return a single course.

get-single-course.png

Multiple courses

To return multiple courses, we use the index method.

  async index({ auth, request, response }) {
    try {
      const { semester, level } = request.all();

      const rules = {
        semester: "in:1,2",
        level: "in:100,200,300,400,500,600,700,800,900",
      };

      const validation = await validateAll(request.all(), rules);

      if (validation.fails()) {
        return response.status(400).send(validation.messages());
      }

      const user_id = await auth.user.id;

      let courses;

      if (level && !semester) {
        courses = await Course.query()
          .where({ user_id: user_id, level: level })
          .fetch();
      } else if (level && semester) {
        courses = await Course.query()
          .where({ user_id: user_id, level: level, semester: semester })
          .fetch();
      } else {
        courses = await Course.query().where("user_id", user_id).fetch();
      }

      return response.status(200).send(courses);
    } catch (error) {
      console.log(error);
      return response.status(500).send(error);
    }
  }

A user calling this API has three options

  1. /courses returns all of a user's courses
  2. /courses?level=300 returns all of a user's 300 level courses
  3. /courses?level=100&semester=1 returns all of a user's 300 level, 1st semester courses. The logic to determine the right query to perform is

       let courses;
    
       if (level && !semester) {
         courses = await Course.query()
           .where({ user_id: user_id, level: level })
           .fetch();
       } else if (level && semester) {
         courses = await Course.query()
           .where({ user_id: user_id, level: level, semester: semester })
           .fetch();
       } else {
         courses = await Course.query().where("user_id", user_id).fetch();
       }
    

    Notice that we used fetch when using the where clause abstraction. You can learn more about queries from the docs.

Course updates using the update method

Updating a course is very similar to creating one. We make every field required and find the course from the params. We then check if the course belongs to the user requesting it. If all goes well, we update the course fields and save it. Below is an incomplete update method. Your job is to complete it. Feel free to modify it if you think about a better implementation.

  async update({ auth, params, request, response }) {
    try {

      // your code above

      const courseOwner = await course.user().fetch();
      const requester = await auth.user;
      if (requester.id !== courseOwner.id) {
        return response
          .status(403)
          .send("You cannot view another user's course");
      }

      course.title = title;
      course.grade = grade;
      course.code = code;
      course.credit_load = credit_load;
      course.semester = semester;
      course.level = level;

      await course.save();
      return response.status(200).send(course);
    } catch (error) {
      console.log(error);
      return response.status(500).send(error);
    }
  }

Notice that we used the same logic to verify that the user making the request is a course's owner. We can refactor that logic to this method.

  async isOwner({ auth, course, response }) {
    const courseOwner = await course.user().fetch();
    const requester = await auth.user;
    if (requester.id !== courseOwner.id) {
      throw new Error("You cannot view another user's course");
    }
  }

Now, to call it, we do

      try {
        await this.isOwner({ auth, course, response });
      } catch (error) {
        return response
          .status(403)
          .send("You cannot <action> another user's course");
      }

You may find it ironic that the refactor added more code to the codebase. The good part of it is that we can easily change our logic in one place.

Course deletion

Course deletion is probably the simplest here. It only involves verification and deletion.

  async destroy({ auth, params, response }) {
    try {
      const course = await Course.find(params.id);
      if (!course) {
        return response.status(404).send("Course not found");
      }
      try {
        await this.isOwner({ auth, course, response });
      } catch (error) {
        return response
          .status(403)
          .send("You cannot delete another user's course");
      }
      await course.delete();
      return response.send("course deleted successfully");
    } catch (error) {
      return response.status(500).send("An error occured");
    }
  }

If you notice, Lucid models simplify DB operations. Deletion is simply course.delete() instead of delete from courses where id =<id>.

What you've learned so far

  1. Generating models and migrations
  2. Cascade update and delete
  3. Route resources
  4. A simple way to handle authorization

Next steps

In the next tutorial, we will look into 1:1 relationship and how we can seed our database. Thank you for following along. Adios ✌🏾🧡.