CGPA Calculator with AdonisJS: CRUD and Relationships
Tags: node, javascript, database
Published: 2020-12-05 20:31:27

Hey there, welcome to the third tutorial in the series. In the [previous tutorial](https://osi.codes/build-a-cgpa-calculator-with-adonisjs-authentication), we added authentication to our APIs. If you will like to skip the previous steps, clone the [repo](https://github.com/vicradon/cgpa-api) and checkout to the **authentication** branch, then code along.

## The course model
![course-model.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1606412284324/X6D2gYShG.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 
```bash
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
```js
      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](http://knexjs.org/) docs for more info on datatypes. The last column, user_id is a foreign key.
```js
      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.
```bash
adonis migration:run
```

## Course CRUD routes 
If we were to create individual routes for CRUD, we will end up with >4 routes. 
```js
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](https://adonisjs.com/docs/4.1/routing#_route_resources)! Add this line to routes.js

```js
  Route.resource("courses", "CourseController").apiOnly().middleware(["auth"]);
```
Refer to the [docs](https://adonisjs.com/docs/4.1/routing#_route_resources) 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.
```js
const Course = use("App/Models/Course");
const { validateAll } = use("Validator");
```

### The `store` method
Add the store method below to CourseController.js. 
```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
```js
  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
```json
{
    "title": "Metals and Non metals",
    "grade": "A",
    "code": "MNM 304",
    "credit_load": "4",
    "semester": "2",
    "level": "100"
}
```

 You should get a response similar to
```json
{
    "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.
```js
  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.
```js
  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.

```js
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](https://cdn.hashnode.com/res/hashnode/image/upload/v1606435351406/NkfPPYMan.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 
```js
      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](https://adonisjs.com/docs/4.1/lucid#_query_builder).

### 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.
```js
  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.
```js
  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
```js
      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.
```js
  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 ✌🏾🧡.