Objection.js as an ORM - Building Relationships

Introduction

ORMs are simple libraries that help us interface with our database and are not agnostic to different database languages, an example is objection.js which is an ORM built on Knex to connect to databases like MySQL, NoSQL, etc.

Recap 🧢 of part 1

In our last article we discussed what ORMs are and how they help us in building backend systems that connect to structured DBs eg MYSQL, we were able to understand their advantages and perform simple CRUD queries using Objection.js. Objection.js is a much powerful tool for performing database manipulation and reading data, we would be looking at some more uses.

Model definition

Objection.js helps us define a model for our table that connects to the DB we can define some constant variables in the DB and also describe other things like

• JSON schema for data saving
• columns of our DB
• before saving function
• before update function
• DB relationship with other tables

Let us look at an example

import { Model } from 'objection';
import { UserModel } from './users.model';

export class Card extends Model {
  id!: number;
  user_id: number;
  is_active: number;
  card_name: string;
  last4: string;
  cvv2: string;
  created_at: Date;
  updated_at: Date;
  deleted_at: Date;

  static tableName = 'service_cards'; // database table name
  static idColumn = 'id'; // id column name

  static relationMappings = () => ({
    user: {
      relation: Model.HasOneRelation,
      modelClass: UserModel,
      join: {
        from: 'service_cards.user_id',
        to: 'users.id',
      },
    },
  });


  $beforeInsert() {
    this.created_at = new Date();
    this.updated_at =new Date();
  }

  $beforeUpdate() {
    this.updated_at = new Date();
  }
}

What is a DB relationship?

A relationship is created between two database tables when one table uses a foreign key that references the primary key of another table. This is the concept behind DB relationships, we can use that concept to get related data across different tables, in MYSQL this is done with a join query.

How a relationship is established between private and public keys

A primary key is a unique identifier in the row, it is used to identify the row and does not share this value with any other row in the table, the foreign key is a value in a column that is used to reference another table usually the primary key in that table. For example, consider these two tables that identify who owns a car. Here, the Cars table's primary key is Cars_ID. Its foreign key is Owner_ID.

Types of DB relationships#### One-to-One Relationship

This kind of relationship happens when one row in a table is connected to a single row in another table, for example, if there is a row in the User(id, name, country) table and we have another table called Passport(id,user_id, expiration), we can create a relationship by adding a user_id column to the passport table which is mapped to the id column in the user table. This is the least popular mode of relationship but is used when we have data that's unique eg passports, where people usually don't have more than one active passport per country(if you do though reach out I wanna know how).

One to many

This kind of relationship occurs when we have a row that has a relationship to one or many items in another table, this is the most used type of relationship for databases I personally use, an example would be two tables User(id, name, country) table and a Cars(id,uuser_id,plate_number) table where we can have multiple car entries for just one user.

Many to many

Many to Many relationships involve when multiple rows in one table match multiple rows in another table an example can be seen in a user and seen post table here multiple users have seen multiple posts and multiple posts have been seen by users.

Building DB Relationships with Objection.js

Relations in the database involves joining two databases together based on them having common values across the individually specified columns, let's say I have a card table and user table, and let say I want to get all user data with their cars, we need to create a relationship between user and table, in objection.js this is defined in the model class.

 static relationMappings = () => ({
    user: {
      relation: Model.HasOneRelation,
      modelClass: UserModel,
      join: {
        to: 'cars.user_id',
        from: 'users.id',
      },
    },
  });

the join param defines our relationship, from: 'cars.user_id' our current table and to: 'users.id' defining our target table.

Making use of this relationship we can make a query to our Cars table and add the user that owns the car Let's look at an example.

car.model.ts

import { Model } from 'objection';
import { OwnerModel } from './owner.model';

export class CarModel extends Model {
  id: number;
  user_id: number;
  car_name:  string;
  owner:OwnerModel

  static tableName = 'cars'; // database table name
  static idColumn = 'id'; // id column name

  static relationMappings = () => ({
    carOwners: {
      relation: Model.HasOneRelation,
      modelClass: ownerModel,
      join: {
        from: 'cars.user_id',
        to: 'car_owners.id',
      },
    },
  });

  $beforeInsert() {
    
  }

  $beforeUpdate() {
    
  }
}

Now let's look at our owner model.

Owner.model.ts

import { Model } from 'objection';
import { CarModel } from './car.model';

export class OwnerModel extends Model {
  id: number;
 car : CarModel

  static tableName = 'car_owners'; // database table name
  static idColumn = 'id'; 

  static relationMappings = () => ({
    cars: {
      relation: Model.HasOneRelation,
      modelClass: CarModel,
      join: {
       to: 'cars.user_id',
        from: 'car_owners.id',
      },
    },
  });

  $beforeInsert() {
    
  }

  $beforeUpdate() {
  
  }
}

Now we have our two models, let's see how we can take advantage of this and return a list of cars with its users.

import {CarModel} from '.car.model'

const getAllCars = async () =>{
	const cars = await CarModel.query().withGraphFetched({
        owner: true
      })
}

This can use the relationship model to query the DB and return cars with the owners

{
	id:1,
	user_id:2,
	car_name:'kia',
	owner:{
		  id: 2;
	},
id:1,
	user_id:3,
	car_name:'toyota',
	owner:{
		  id: 3;
	},
id:1,
	user_id:4,
	car_name:'nissan',
	owner:{
		  id: 4;
	},
id:1,
	user_id:5,
	car_name:'bmw',
	owner:{
		  id: 5;
	}
}

Conclusion

We learned about relationships in databases and the types of relationships and their application with objection.js, objection.js also has more features that can be checked out at https://vincit.github.io/objection.js/