Database

Repositories

Use BaseRepository from @axiljs/orm for typed CRUD, pagination, soft deletes, eager loading and custom queries on PostgreSQL, MySQL and SQLite.

6 min readUpdated DocumentationEdit this page
On this page12

BaseRepository<T> wraps one entity's table in a class with ready-made CRUD methods. This page covers defining a repository, every built-in method and its options, adding custom queries, and how repositories differ from BaseModel models.

TypeScript
TS
1import { BaseRepository, ConnectionManager, Entity, PrimaryKey, Column } from '@axiljs/orm'
2
3@Entity('users')
4export class User {
5  @PrimaryKey() id!: number
6  @Column() name!: string
7  @Column({ unique: true }) email!: string
8  @Column({ nullable: true }) role!: string | null
9}
10
11export class UserRepository extends BaseRepository<User> {
12  constructor(db: ConnectionManager) {
13    super(User, db)
14  }
15
16  async findByEmail(email: string) {
17    return this.findOne({ email })
18  }
19}

The entity does not need to extend BaseModel. The repository reads its table name, primary key and @DeletedAt column from the entity decorators, and takes the database type from the connection, so no extra setup is needed.

Create and use

TypeScript
TS
1import { createConnection } from '@axiljs/orm'
2
3const db = await createConnection({ driver: 'postgres', url: process.env.DATABASE_URL })
4const users = new UserRepository(db)
5
6const ann = await users.create({ name: 'Ann', email: 'ann@axiljs.dev' })
7await users.findById(ann.id)
8await users.findByIdOrFail(ann.id)          // throws NotFoundError when missing
9await users.update(ann.id, { role: 'admin' })
10await users.delete(ann.id)

findByIdOrFail() throws NotFoundError from @axiljs/common, which carries status 404 and code NOT_FOUND.

Methods

Reading

MethodReturnsDescription
find(where?, options?)Promise<T[]>All rows matching every key in where
findOne(where, options?)Promise<T | null>First matching row
findById(id, options?)Promise<T | null>Row by primary key
findByIdOrFail(id, options?)Promise<T>Row by primary key, or NotFoundError
findWithRelations(where, relations, options?)Promise<T[]>Shorthand for find(where, { ...options, with: relations })
count(where?, options?)Promise<number>Count matching rows
paginate(options){ data, total, page, perPage, totalPages }One page of rows

where is an object of equality conditions joined with AND. Keys with an undefined value are skipped.

Writing

MethodReturnsDescription
create(data)Promise<T>Insert and return the stored row, including the generated key
createMany(data[])Promise<T[]>Insert rows one by one and return them
update(id, data)Promise<T>Update by primary key, return the fresh row (or NotFoundError)
updateWhere(where, data)Promise<number>Update matching rows, return the count
delete(id)Promise<void>Delete by key; soft-delete for @DeletedAt entities
deleteWhere(where)Promise<number>Delete (or soft-delete) matching rows
forceDelete(id)Promise<void>Permanently delete, even for @DeletedAt entities
restore(id)Promise<boolean>Clear deleted_at; true when a row was restored

undefined values in data are dropped before the query. On PostgreSQL create() reads the row back with RETURNING *; on MySQL and SQLite it fetches it by the insert ID, or by the key you supplied (for example a UUID).

Find options

The read methods take an optional FindOptions object:

OptionTypeDefaultDescription
withstring[]noneRelations to eager-load, for example ['author', 'tags'] or ['posts.comments']
withTrashedbooleanfalseInclude soft-deleted rows
onlyTrashedbooleanfalseReturn only soft-deleted rows
TypeScript
TS
1const posts = new PostRepository(db)
2
3await posts.find({ published: true }, { with: ['author'] })
4await posts.findWithRelations({ published: true }, ['author', 'tags'])
5await posts.find(undefined, { onlyTrashed: true })

Relations are loaded with one batched IN (...) query per relation. Declaring them is covered in Relations.

Pagination

TypeScript
TS
1const page = await users.paginate({
2  page: 2,
3  perPage: 20,
4  where: { role: 'admin' },
5  orderBy: { column: 'name', direction: 'ASC' }
6})
7
8// { data: [...], total: 57, page: 2, perPage: 20, totalPages: 3 }
OptionTypeDefault
pagenumber1
perPagenumber20
wherePartial<T>none
orderBy{ column, direction: 'ASC' | 'DESC' }none
with, withTrashed, onlyTrashedsee Find options

Soft deletes

For an entity with @DeletedAt(), delete() and deleteWhere() set deleted_at instead of removing rows, and every read, count() and update() skips soft-deleted rows.

TypeScript
TS
1await posts.delete(7)                              // UPDATE posts SET deleted_at = ...
2await posts.findById(7)                            // null
3await posts.findById(7, { withTrashed: true })     // the row
4await posts.restore(7)                             // true
5await posts.forceDelete(7)                         // DELETE FROM posts ...

restore() throws when the entity has no @DeletedAt column.

Custom queries

Inside a subclass, this.query(options?) returns a query builder already set to SELECT * FROM <table>, with the soft-delete scope applied. Finish it with all(), first() or count().

TypeScript
TS
1export class OrderRepository extends BaseRepository<Order> {
2  constructor(db: ConnectionManager) {
3    super(Order, db)
4  }
5
6  recentForCustomer(customerId: number, limit = 10) {
7    return this.query()
8      .where('customer_id', customerId)
9      .whereIn('status', ['paid', 'shipped'])
10      .orderBy('created_at', 'DESC')
11      .limit(limit)
12      .all<Order>()
13  }
14
15  countOpen() {
16    return this.query().where('status', '!=', 'closed').count()
17  }
18}

Subclasses can also use this.tableName, this.primaryKey, this.driver (for raw SQL with this.driver.query()) and this.driverType.

Column names

BaseRepository uses the keys of where and data as column names, and returns rows keyed by column name. Unlike BaseModel, it does not apply @Column({ name }) mappings and does not return class instances. Name the entity's properties after the columns when you use a repository:

TypeScript
TS
1@Entity('orders')
2export class Order {
3  @PrimaryKey() id!: number
4  @Column({ type: 'integer' }) customer_id!: number
5  @Column() status!: string
6  @CreatedAt() created_at!: Date
7}

Using a repository in a service

Build repositories once and share them. ConnectionManager.get() returns the connection registered by createConnection():

TypeScript
TS
1import { ConnectionManager } from '@axiljs/orm'
2
3export class UserService {
4  private users = new UserRepository(ConnectionManager.get())
5
6  list(page: number) {
7    return this.users.paginate({ page, perPage: 25, orderBy: { column: 'id', direction: 'DESC' } })
8  }
9
10  get(id: number) {
11    return this.users.findByIdOrFail(id)
12  }
13}

To run several repository calls atomically, see Transactions.

Repository or model

NeedUse
Instances with save(), dirty tracking and toJSON()BaseModel
Property names that differ from column namesBaseModel
Injectable data-access classes with custom query methodsBaseRepository
Plain objects ready to send as JSONBaseRepository

Was this page helpful?

Help improve the documentation

AxilJS is open source and documentation improvements are welcome.

AxilJS DocumentationMIT License · Built by SyntaxilitY