Scoping with TypeORM

Photo by Chris Ried on Unsplash

TypeORM offers two great tools you can use to query your database:

• Repository API: Provides a convenient tool to perform basic SQL queries from relations established in your entities.
• Query Builder: uses a loosely coupled builder pattern to allow you to assemble complex SQL queries from relations established in your entities.

The Repository API is convenient for simple query operations, but can fall short for more complex ones. Finding yourself fighting the Repository API is usually indicative that it’s time to move to the QueryBuilder.

Consider the following example:

User Entity:

class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number
  @Column({ name: "first_name" })
  firstName: string
  @Column({ name: "last_name" })
  lastName: string
  @Column({ name: "email" })
  email: string
  
  @Column({ name: "status" })
  status: string
  @CreateDateColumn({ name: "created_at" })
  createdAt: Date
  @UpdateDateColumn({ name: "updated_at" })
  updatedAt: Date
}

Let’s say we wanted to try to find all active users. We may consider querying the user entity like so:

const user = await getManager()
  .getRepository(User)
  .createQueryBuilder("user")
  .andWhere("user.status = :userStatus", { userStatus: "active" })

This is ok, but it’s generally safe to assume that we are going to be checking for active users in several places across our application. Let’s take a look at how we might apply scoping patterns to make this a little more succinct.

Scoping:

If you are coming from another web development framework like Ruby on Rails, you’re probably familiar with scoping patterns that allow you to define filters for queries without having to repeatedly implement them across your codebase. While TypeORM doesn’t strictly have support for scoping, we can create something relatively similar with little effort. Let’s create a query object to employ the scoping pattern:

class UsersQuery extends SelectQueryBuilder<User> {
  constructor(builder: SelectQueryBuilder<User>) {
    super(builder)
  }
  withId?(userId: number): UsersQuery {
    this.andWhere(`user.id = :userId`, { userId })
    return this
  }
  withActive?(): UsersQuery {
    this.andWhere(`user.status = :userStatus`, {
      userStatus: "active",
    })
    return this
  }
}

We can then modify the initial query above such that it becomes:

const usersQuery = new UsersQuery(
  getManager() //
    .getRepository(User)
    .createQueryBuilder("user")
)
usersQuery.withActive()

The benefit of doing it this way is that we have a repeatable method to query for active users. Furthermore, by having extended our query object from SelectQueryBuilder, we can pass this query object around and continue to build on it. Say we also wanted to find users who were created after a certain date, we could continue to chain the query like:

usersQuery
  .withActive()
  .andWhere(`user.createdAt > :date`, 
    { date: "2014-02-01" }
  )

Finally, as soon as we’re ready to return results from the query we can execute it:

const users = await usersQuery.getMany()