ORM: Relationships

See how to create relations between models in Athenna Framework.

Defining relationships

Database tables are often related to one another. For example, a blog post may have many comments or an order could be related to the user who placed it. Athenna makes managing and working with these relationships easy, and supports a variety of common relationships that are defined as properties on your model classes using annotations:

import {
  Column,
  HasOne,
  HasMany,
  BaseModel,
  BelongsTo,
  BelongsToMany,
  type Relation,
} from '@athenna/database'

import { Course } from '#app/models/Course'
import { Profile } from '#app/models/Profile'
import { Comment } from '#app/models/Comment'
import { Customer } from '#app/models/Customer'
import { UsersCourses } from '#app/models/UsersCourses'

export class User extends BaseModel {
  @Column()
  public id: number 

  @HasOne(() => Profile)
  public profile: Relation<Profile> 

  @HasMany(() => Comment)
  public comments: Relation<Comment[]> 

  @BelongsTo(() => Customer)
  public customer: Relation<Customer> 

  @BelongsToMany(() => Course, () => UsersCourses) 
  public courses: Relation<Course[]> 
}

tip

Check the full documentation of relationships annotations:

On this documentation you will see how to setup the following relationships using the above annotations:

One To One

A one-to-one relationship is a very basic type of database relationship. For example, a User model might be associated with one Phone model. To define this relationship, we will place a phone property on the User model and annotate it with the @HasOne() annotation:

import { Phone } from '#app/models/Phone'
import type { Relation } from '@athenna/database'
import { Column, BaseModel, HasOne, type Relation } from '@athenna/database'

export class User extends BaseModel {
  @Column()
  public id: number

  @HasOne(() => Phone)
  public phone: Relation<Phone> 
}

The first argument passed to the @HasOne() annotation is closure that return the related model class. Once the relationship is defined, we may retrieve the related record using the with() method of the model query builder:

const { phone } = await User.query()
  .with('phone')
  .find()

Since all relationships are extending BaseModel, you may add further constraints to the relationship query by adding a closure as second argument of the with() method:

const { phone } = await User.query()
  .with('phone')
  .find()

// Or

const user = await User.find()

await user.load('phone', query => query.where('ddd', '+55'))

console.log(user.phone)

Athenna determines the foreign key of the relationship based on the parent model name. In this case, the Phone model is automatically assumed to have a userId foreign key. If you wish to override this convention, you may pass a second argument to the @HasOne() annotation:

@HasOne(() => Phone, { foreignKey: 'foreignKey' })
public phone: Relation<Phone> 

Additionally, Athenna assumes that the foreign key should have a value matching the primary key column of the parent. In other words, Athenna will look for the value of the user's id column in the userId column of the Phone record. If you would like the relationship to use a primary key value other than id, you may pass a primaryKey property to the options in the second argument:

@HasOne(() => Phone, {
  primaryKey: 'primaryKey',
  foreignKey: 'foreignKey'
})
public phone: Relation<Phone> 

Defining the inverse of the relationship

So, we can access the Phone model from our User model. Next, let's define a relationship on the Phone model that will let us access the user that owns the phone. We can define the inverse of a @HasOne() relationship using the @BelongsTo() annotation:

import { User } from '#app/models/User'
import type { Relation } from '@athenna/database'
import { Column, BelongsTo, BaseModel } from '@athenna/database'

export class Phone extends BaseModel {
  @Column()
  public id: number

  @Column()
  public userId: number

  @BelongsTo(() => User)
  public user: Relation<User> 
}

When invoking the with('user') method, Athenna will attempt to find a User model that has an id which matches the userId column on the Phone model.

Athenna determines the foreign key name by examining the name of the relationship model and suffixing it with Id. So, in this case, Athenna assume that the Phone model has a userId column. However, if the foreign key on the Phone model is not userId, you may pass a custom key name as the second argument to the @BelongsTo() annotation:

@BelongsTo(() => User, { foreignKey: 'foreignKey' })
public user: Relation<User> 

If the parent model does not use id as its primary key, or you wish to find the associated model using a different column, you may pass a third argument to the @BelongsTo() method specifying the parent table's custom key:

@BelongsTo(() => User, { 
  primaryKey: 'primaryKey',
  foreignKey: 'foreignKey' 
})
public user: Relation<User> 

One To Many

A one-to-many relationship is used to define relationships where a single model is the parent to one or more child models. For example, a blog post may have an infinite number of comments. Like all other Athenna relationships, one-to-many relationships are defined by defining a @HasMany() annotation in your model class property:

import { Comment } from '#app/models/Comment'
import type { Relation } from '@athenna/database'
import { Column, BaseModel, HasMany } from '@athenna/database'

export class Post extends BaseModel {
  @Column()
  public id: number

  @HasMany(() => Comment)
  public comments: Relation<Comment[]> 
}

Remember, Athenna will automatically determine the proper foreign key column for the Comment model. By convention, Athenna will take the "camelCase" name of the parent model and suffix it with Id. So, in this example, Athenna will assume the foreign key column on the Comment model is postId.

Once the relationship method has been defined, we can access the data of related comments by using the with() method:

const { comments } = await Post.query()
  .with('comments')
  .find()

Since all relationships are extending BaseModel, you may add further constraints to the relationship query by adding a closure as second argument of the with() method:

const { comments } = await Post.query()
  .with('comments', query => query.where('title', 'foo'))
  .find()

// Or

const post = await Post.find()

await post.load('comments', query => query.where('title', 'foo'))

console.log(post.comments)

Like the @HasOne() annotation, you may also override the foreign and primary keys by passing the additional options second argument to the @HasOne() annotation:

@HasMany(() => Comment, { 
  foreignKey: 'foreignKey',
  primaryKey: 'primaryKey'
})
public comments: Relation<Comment[]>

Defining the inverse of the relationship (Many To One)

Now that we can access all of a post's comments, let's define a relationship to allow a comment to access its parent post. To define the inverse of a @HasMany() relationship, define a relationship property on the child model annotated with the @BelongsTo() annotation:

import { Post } from '#app/models/Post'
import type { Relation } from '@athenna/database'
import { Column, BaseModel, BelongsTo } from '@athenna/database'

export class Comment extends BaseModel {
  @Column()
  public id: number

  @Column()
  public postId: number

  @BelongsTo(() => Post)
  public post: Relation<Post> 
}

And just like the other relationships, once we have defined it in our class, we can retrieve a comment's parent post by using the with() and load() methods:

const { post } = await Comment.query()
  .with('post', query => query.where('title', 'foo'))
  .find()

// Or

const comment = await Comment.find()

await comment.load('post', query => query.where('title', 'foo'))

console.log(comment.post)

In the example above, Athenna will attempt to find a Post model that has an id which matches the postId column on the Comment model.

Athenna determines the foreign key name by examining the name of the relationship model and suffixing it with Id. So, in this case, Athenna assume that the Comment model has a postId column. However, if the foreign key on the Comment model is not postId, you may pass a custom key name as the second argument to the @BelongsTo() annotation:

@BelongsTo(() => Post, { foreignKey: 'foreignKey' })
public post: Relation<Post[]>

If your parent model does not use id as its primary key, or you wish to find the associated model using a different column, you may pass a primaryKey option specifying your parent table's custom key:

@BelongsTo(() => Post, {
  foreignKey: 'foreignKey',
  primaryKey: 'primaryKey'
})
public post: Relation<Post[]>

Many To Many

Many-to-many relations are slightly more complicated than one-to-one and one-to-many relationships. An example of a many-to-many relationship is a user that has many roles and those roles are also shared by other users in the application. For example, a user may be assigned the role of "Author" and "Editor"; however, those roles may also be assigned to other users as well. So, a user has many roles and a role has many users.

Table structure

To define this relationship, three database tables are needed: users, roles, and roles_users. The roles_users table is derived from the alphabetical order of the related model names and contains userId and roleId columns. This table is used as an intermediate table linking the users and roles.

Remember, since a role can belong to many users, we cannot simply place a userId column on the roles table. This would mean that a role could only belong to a single user. In order to provide support for roles being assigned to multiple users, the roles_users table is needed. We can summarize the relationship's table structure like so:

users
    id - integer
    name - string
 
roles
    id - integer
    name - string
 
roles_users
    userId - integer
    roleId - integer

Model structure

Many-to-many relationships are defined by defining a property annotated with the @BelongsToMany() annotation. But before we check how to define this annotation, we need to first create our RolesUsers pivot model:

import { Role } from '#app/models/Role'
import { User } from '#app/models/User'
import type { Relation } from '@athenna/database'
import { Column, BaseModel, BelongsTo } from '@athenna/database'

export class RolesUsers extends BaseModel {
  @Column()
  public id: number

  @Column()
  public roleId: number

  @Column()
  public userId: number

  @BelongsTo(() => Role)
  public role: Relation<Role> 

  @BelongsTo(() => User)
  public user: Relation<User> 
}

And now let's define the roles relation on our User model. The first argument passed to the annotations is a closure that returns the related model class and the second argument is a closure that returns the pivot model:

import { Role } from '#app/models/Role'
import type { Relation } from '@athenna/database'
import { RolesUsers } from '#app/models/RolesUsers'
import { Column, BaseModel, BelongsToMany } from '@athenna/database'

export class User extends BaseModel {
  @Column()
  public id: number

  @Column()
  public name: string

  @BelongsToMany(() => Role, () => RolesUsers)
  public roles: Relation<Role[]> 
}

Once the relationship is defined, you may access the user's roles using the with() or load() methods:

const user = await User.query()
  .with('roles', query => query.select('id'))
  .find()

// Or

const user = await User.find()

await user.load('roles', query => query.select('id'))

console.log(user.roles)

To determine the table name of the relationship's pivot table, Athenna will call the table() method of the pivot model. However, you are free to override this convention. You may do so by passing a third argument to the @BelongsToMany() annotation:

@BelongsToMany(() => Role, () => RolesUsers, {
  pivotTable: 'roles_users'
})
public roles: Relation<Roles[]>

In addition to customizing the name of the pivot table, you may also customize the column names of the keys on the table by passing additional arguments to the options:

@BelongsToMany(() => Role, () => RolesUsers, {
  pivotTable: 'roles_users',
  primaryKey: 'id',
  foreignKey: 'userId',
  relationPrimaryKey: 'id',
  relationForeignKey: 'roleId'
})
public roles: Relation<Roles[]>

Defining the inverse of the relationship

To define the "inverse" of a many-to-many relationship, is basically the same process of defining on the parent model. To complete our user / role example, let's define the users property on the Role model:

import { User } from '#app/models/User'
import type { Relation } from '@athenna/database'
import { RolesUsers } from '#app/models/RolesUsers'
import { Column, BaseModel, BelongsToMany } from '@athenna/database'

export class Role extends BaseModel {
  @Column()
  public id: number

  @Column()
  public name: string

  @BelongsToMany(() => User, () => RolesUsers)
  public users: Relation<User[]> 
}

As you can see, the relationship is defined exactly the same as its User model counterpart with the exception of referencing the User model. Since we're reusing the @BelongsToMany() annotation, all of the usual table and key customization options are available when defining the "inverse" of many-to-many relationships.

Retrieving pivot table data

As you have already learned, working with many-to-many relations requires the presence of a pivot table and it pivot model, meaning that if you need to retrieve the data from the pivot table, you can simply use the pivot model:

const user = await User.find()
const role = await Role.find()

const rolesUsers = await RolesUsers.query()
  .where('userId', user.id)
  .where('roleId', role.id)
  .findMany()

Eager loading

Eager loading alleviates the "N + 1" query problem. To illustrate the N + 1 query problem, consider a Book model that "belongs to" to an Author model:

import { Author } from '#app/models/Author'
import type { Relation } from '@athenna/database'
import { Column, BelongsTo, BaseModel } from '@athenna/database'

export class Book extends BaseModel {
  @Column()
  public id: number

  @BelongsTo(() => Author)
  public author: Relation<Author>
}

Now, let's retrieve all books and their authors:

const books = await Book.findMany()

for (const book of books) {
  await book.load('author')

  console.log(book.author.name)
}

This loop will execute one query to retrieve all of the books within the database table, then another query for each book in order to retrieve the book's author. So, if we have 25 books, the code above would run 26 queries: one for the original book, and 25 additional queries to retrieve the author of each book.

Thankfully, we can use eager loading to reduce this operation to just two queries. When building a query, you may specify which relationships should be eager loaded using the with() method:

const books = await Book.query()
  .with('author') 👈
  .findMany()

for (const book of books) {
  console.log(book.author.name)
}

For this operation, only two queries will be executed - one query to retrieve all of the books and one query to retrieve all of the authors for all of the books:

select * from books
 
select * from authors where id in (1, 2, 3, 4, 5, ...)

Eager loading multiple relationships

Sometimes you may need to eager load several different relationships. To do so, just concat multiple with() calls:

const books = await Book.query()
  .with('author') 👈
  .with('publisher') 👈
  .findMany()

Nested eager loading

To eager load a relationship's relationships, you may use "dot" syntax. For example, let's eager load all of the book's authors and all of the author's personal contacts:

const books = await Book.query()
  .with('author.contacts') 👈
  .findMany()

warning

Closures set as second argument of the with() method will be ignored when using "dot" syntax to eager load relationship's relationships.

Check the relationship's relationships constraints documentation section to see how you could have more power under the constraints added to each of your relationships.

Constraining eager loads

Sometimes you may wish to eager load a relationship but also specify additional query conditions for the eager loading query. You can accomplish this by passing a closure as second argument to the with() function that adds additional constraints to the eager loading query:

const users = await User.query()
  .with('posts', query => query.whereLike('title', '%code%')) 👈
  .findMany()

In this example, Athenna will only eager load posts where the post's title column contains the word code. You may call other query builder methods to further customize the eager loading operation:

const users = await User.query()
  .with('posts', query => query.orderBy('createdAt', 'desc')) 👈
  .findMany()

Relationship's relationships constraints

When using the "dot" syntax to eager load relationship's relationships the closure set as second argument will always be ignored:

const books = await Book.query()
  // ❌ Load `author` and `contacts` of author, 
  // but doesn't query only cellphones contacts.
  .with('author.contacts', query => query.where('isCellphone', true))
  .findMany()

To have full power under the constraints that you want to add to your queries you can call with() method inside of your closure:

const books = await Book.query()
  .with('author', query => {
    query
      .where('name', 'Lenon')
      .with('contacts', query => {
        query.where('isCellphone', true)
      })
  })
  .findMany()

Since all of your relationships extends the BaseModel, you can use methods like save() to create your relationships:

const author = await Author.find()

author.book = new Book()

author.book.authorId = author.id

await author.book.save()

Or even to update it:

const book = await Book.query()
  .with('author')
  .find()

book.author.name = 'Lenon'

await book.author.save()

Creating many-to-many relationships

When working with many-to-many relationships you need to use the pivot model to create the relation between the other two models:

import { User } from '#app/models/User'
import { Role } from '#app/models/Role'
import { RolesUsers } from '#app/models/RolesUsers'

const user = await User.find()
const role = await Role.find()

const rolesUsers = new RolesUsers()

rolesUsers.userId = user.id
rolesUsers.roleId = role.id

await rolesUsers.save()

// Or simply

await RolesUsers.create({ userId: user.id, roleId: role.id })

ORM: Relationships

Defining relationships​

One To One​

Defining the inverse of the relationship​

One To Many​

Defining the inverse of the relationship (Many To One)​

Many To Many​

Table structure​

Model structure​

Defining the inverse of the relationship​

Retrieving pivot table data​

Eager loading​

Eager loading multiple relationships​

Nested eager loading​

Constraining eager loads​

Relationship's relationships constraints​

Inserting & updating related models​

Creating many-to-many relationships​

Defining relationships

One To One

Defining the inverse of the relationship

One To Many

Defining the inverse of the relationship (Many To One)

Many To Many

Table structure

Model structure

Defining the inverse of the relationship

Retrieving pivot table data

Eager loading

Eager loading multiple relationships

Nested eager loading

Constraining eager loads

Relationship's relationships constraints

Inserting & updating related models

Creating many-to-many relationships