King Somto
20 Jan 2022
•
4 min read
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.
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.
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
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();
}
}
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.
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
.
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).
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 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.
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;
}
}
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/
Ground Floor, Verse Building, 18 Brunswick Place, London, N1 6DZ
108 E 16th Street, New York, NY 10003
Join over 111,000 others and get access to exclusive content, job opportunities and more!