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 '#src/models/Course'
import { Profile } from '#src/models/Profile'
import { Comment } from '#src/models/Comment'
import { Customer } from '#src/models/Customer'
import { UsersCourses } from '#src/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 '#src/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 '#src/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 '#src/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 '#src/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 '#src/models/Role'
import { User } from '#src/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 '#src/models/Role'
import type { Relation } from '@athenna/database'
import { RolesUsers } from '#src/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 '#src/models/User'
import type { Relation } from '@athenna/database'
import { RolesUsers } from '#src/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()

Has Many Through

The "has many through" relationship is a handy way to reach distant relations through an intermediate model. Imagine an application where a Region has many Member models, and each Member writes many Article models. With a has many through relationship, you can get all the articles of a region directly, without loading the members first. Let's look at the tables needed to define this relationship:

regions
    id - integer
    name - string

members
    id - integer
    regionId - integer
    name - string

articles
    id - integer
    memberId - integer
    title - string

Now let's define the relationship in the Region model using the @HasManyThrough() annotation. The first argument is the final model we want to access, while the second argument is the intermediate model:

import { Member } from '#src/models/Member'
import { Article } from '#src/models/Article'
import type { Relation } from '@athenna/database'
import { Column, BaseModel, HasManyThrough } from '@athenna/database'

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

  @Column()
  public name: string

  @HasManyThrough(() => Article, () => Member)
  public articles: Relation<Article[]>
}

And that's it! You can load it like any other relationship:

const region = await Region.query()
  .with('articles')
  .find()

region.articles.forEach(article => console.log(article.title))

When the key lives in the intermediate model

In the example above, the final model (Article) has the key that points to the intermediate model (memberId). But sometimes it's the other way around: the intermediate model has the key that points to the final model. A good example is a SaleItem that connects an Appointment to a Sale:

appointments
    id - integer

sale_items
    id - integer
    appointmentId - integer
    saleId - integer

sales
    id - integer
    total - integer

For this shape, set the inverse option to true:

import { Sale } from '#src/models/Sale'
import { SaleItem } from '#src/models/SaleItem'
import type { Relation } from '@athenna/database'
import { Column, BaseModel, HasManyThrough } from '@athenna/database'

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

  @HasManyThrough(() => Sale, () => SaleItem, { inverse: true })
  public sales: Relation<Sale[]>
}

tip

Athenna guesses the keys of your relationship by following the conventions above. If your tables use different column names, you can set them using the localKey, firstKey, secondLocalKey and secondKey options. Check the @HasManyThrough() annotation documentation for more details.

Has One Through

The "has one through" relationship works exactly like the "has many through", but returns a single model instead of an array. Using the same tables of the previous examples, let's define one using the @HasOneThrough() annotation:

import { Member } from '#src/models/Member'
import { Article } from '#src/models/Article'
import type { Relation } from '@athenna/database'
import { Column, BaseModel, HasOneThrough } from '@athenna/database'

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

  @HasOneThrough(() => Article, () => Member)
  public latestArticle: Relation<Article>
}

const region = await Region.query()
  .with('latestArticle', query => query.latest())
  .find()

console.log(region.latestArticle.title)

The inverse option is also available for @HasOneThrough():

@HasOneThrough(() => Sale, () => SaleItem, { inverse: true })
public sale: Relation<Sale>

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 '#src/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()

Lazy loading

When you already have a model instance and need one of its relationships, use the load() method. It loads the relationship, sets it in your model and also returns it:

const book = await Book.find({ id: 1 })

const author = await book.load('author')

console.log(book.author.name) // The relationship is now set in the model

You can also pass a closure to add constraints, or use the "dot" syntax to load nested relationships:

await book.load('author', query => query.select('id', 'name'))
await book.load('author.contacts')

Loading without changing the model

Sometimes you just want to read a relationship without changing your model instance, for example to avoid returning it inside the JSON of your API response. For that, use the loadOnly() method. It works like load(), but your model stays untouched:

const book = await Book.find({ id: 1 })

const author = await book.loadOnly('author')

console.log(author.name) // 'João Lenon'
console.log(book.author) // undefined

Querying relationship existence

Sometimes you want to retrieve models based on their relationships. For example, imagine you want to retrieve only the posts that have at least one comment. You can do this using the whereHas() method:

const posts = await Post.query()
  .whereHas('comments')
  .findMany()

You can also pass a closure to add constraints to the relationship. In the example below, we are retrieving only the posts that have at least one comment written by João Lenon:

const posts = await Post.query()
  .whereHas('comments', query => query.where('author', 'João Lenon'))
  .findMany()

To go deeper in the relationships, just call whereHas() again inside your closure:

// Books that have an author with at least one cellphone contact
const books = await Book.query()
  .whereHas('author', query => {
    query.whereHas('contacts', query => query.where('isCellphone', true))
  })
  .findMany()

The whereHas() method works with all the relationship types: has one, has many, belongs to, belongs to many, has one through and has many through.

Or where has

The orWhereHas() method joins the relationship clause using the or operator. It's very useful inside a grouped where() to check a column of your model or a column of a relationship:

const users = await User.query()
  .where(query => {
    query
      .whereILike('name', '%lenon%')
      .orWhereHas('profile', query => query.whereILike('bio', '%lenon%'))
  })
  .findMany()

tip

If what you are trying to build is a search box, take a look at the model search() method. It does exactly what the example above does, but in a single line.

Filtering and loading at the same time

The whereHas() method only filters your models, it doesn't load the relationship. If you also want the relationship data in your results, combine it with with():

const posts = await Post.query()
  .whereHas('comments', query => query.where('isApproved', true))
  .with('comments')
  .findMany()

The closures of whereHas() and with() are independent. In the example above, the posts are filtered by approved comments, but all of their comments are loaded. If you only want to load the approved ones, add the same constraint to with() as well:

const posts = await Post.query()
  .whereHas('comments', query => query.where('isApproved', true))
  .with('comments', query => query.where('isApproved', 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 '#src/models/User'
import { Role } from '#src/models/Role'
import { RolesUsers } from '#src/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 })

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​

Has Many Through​

When the key lives in the intermediate model​

Has One Through​

Eager loading​

Eager loading multiple relationships​

Nested eager loading​

Constraining eager loads​

Relationship's relationships constraints​

Lazy loading​

Loading without changing the model​

Querying relationship existence​

Or where has​

Filtering and loading at the same time​

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

Has Many Through

When the key lives in the intermediate model

Has One Through

Eager loading

Eager loading multiple relationships

Nested eager loading

Constraining eager loads

Relationship's relationships constraints

Lazy loading

Loading without changing the model

Querying relationship existence

Or where has

Filtering and loading at the same time

Inserting & updating related models

Creating many-to-many relationships