Repositories
Use BaseRepository from @axiljs/orm for typed CRUD, pagination, soft deletes, eager loading and custom queries on PostgreSQL, MySQL and SQLite.
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.
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
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
| Method | Returns | Description |
|---|---|---|
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
| Method | Returns | Description |
|---|---|---|
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:
| Option | Type | Default | Description |
|---|---|---|---|
with | string[] | none | Relations to eager-load, for example ['author', 'tags'] or ['posts.comments'] |
withTrashed | boolean | false | Include soft-deleted rows |
onlyTrashed | boolean | false | Return only soft-deleted rows |
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
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 }| Option | Type | Default |
|---|---|---|
page | number | 1 |
perPage | number | 20 |
where | Partial<T> | none |
orderBy | { column, direction: 'ASC' | 'DESC' } | none |
with, withTrashed, onlyTrashed | see 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.
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().
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:
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():
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
| Need | Use |
|---|---|
Instances with save(), dirty tracking and toJSON() | BaseModel |
| Property names that differ from column names | BaseModel |
| Injectable data-access classes with custom query methods | BaseRepository |
| Plain objects ready to send as JSON | BaseRepository |
Related
Was this page helpful?