Skip to main content

Database Repositories

Formidable includes a repository layer that abstracts the database layer. This layer is built on top of the Knex.js query builder.

Creating Repositories​

To generate a repository, execute the make:repository Craftsman command. All repositories generated by the framework will be placed in the app/Repositories directory:

node craftsman make:repository UserRepository

A repository contains one export by default: Repository. This export returns a class that extends the Repository class. The Repository class provides a few helper methods to make it easier to interact with the database.

Here's an example of a UserRepository:

app/Repositories/UserRepository.ts
import { Repository } from '@formidablejs/framework'

export class UserRepository extends Repository {
    /**
     * The table associated with the repsitory.
     */
    get tableName(): string {
        return 'users'
    }

    /**
     * Context reference.
     */
    static get context(): string {
        return 'UserRepository'
    }
}

Database Repository Conventions​

Repositories generated by the make:repository Craftsman command are placed in the app/Repositories directory. Within this directory, each repository is stored in a directory that matches the "namespace" of the repository. So, a UserRepository class is stored at app/Repositories/UserRepository.{ts,imba}.

Table Names​

By default, the Repository class will assume the table name of the repository matches the "snake case" plural name of the repository class. So, for example, the UserRepository class would assume the users table and TeamMemberRepository would assume the team_members table.

You may override this convention by defining a tableName getter on the repository class:

app/Repositories/UserRepository.ts
import { Repository } from '@formidablejs/framework'

export class UserRepository extends Repository {
    /**
     * The table associated with the repsitory.
     */
    get tableName(): string {
        return 'users'
    }

    /**
     * Context reference.
     */
    static get context(): string {
        return 'UserRepository'
    }
}

Primary Keys​

By default, the Repository class will assume the primary key of the repository's table is an auto-incrementing integer named id. You may override this convention by defining a primaryKey static getter on the repository class:

app/Repositories/UserRepository.ts
import { Repository } from '@formidablejs/framework'

export class UserRepository extends Repository {
    /**
     * Context reference.
     */
    static get context(): string {
        return 'UserRepository'
    }

    /**
     * Primary key.
     */
    static get primaryKey(): string {
        return 'uuid'
    }
}

Route Keys​

When doing Route Repository Binding, Formidable will dependency inject a repository record into a controller action based on the value of the route's URI segment. By default, Formidable uses id as the route key. You may override this convention by defining a routeKeyName getter on the repository class:

app/Repositories/UserRepository.ts
import { Repository } from '@formidablejs/framework'

export class UserRepository extends Repository {
    /**
     * Context reference.
     */
    static get context(): string {
        return 'UserRepository'
    }

    /**
     * Route key name.
     */
    get routeKeyName(): string {
        return 'uuid'
    }
}

Lets take a look at an example. If you have a route defined like this:

routes/api.ts
import { Route } from '@formidablejs/framework'
import { UserController } from '../app/Http/Controllers/UserController'

Route.get('users/:id', [UserController, 'show'])

And you have a controller like this:

app/Http/Controllers/UserController.ts
import { Controller, response } from '@formidablejs/framework'
import { UserRepository } from '../../Repositories/UserRepository'

export class UserController extends Controller {
    @user(UserRepository)
    async show(user: user): Promise {
        return response(await user)
    }
}

The user parameter will be a record from the users table where the id column matches the value of the id URI segment. However, if you change the routeKeyName to uuid on the UserRepository, the user parameter will be a record from the users table where the uuid column matches the value of the id URI segment.

Querying​

The Repository class provides a variety of helpful methods that may be used to build database queries. In addition to these methods, you may also use the Knex query builder to write queries by accessing the query property on a repository instance:

UserRepository.query().where('active', true).orderBy('created_at', 'desc').get()

get​

The get method returns all the records from the repository's table:

UserRepository.get()
info

The get method will also work with any query builder instance:

UserRepository.where('active', true).get()

first​

The first method returns the first record from the repository's table:

UserRepository.first()

The first method may also be used to retrieve the first record matching the given query conditions:

UserRepository.where('active', true).first()

find​

The find method returns the record matching the given primary key value. If no matching record exists, it returns null.

UserRepository.find(12)

where​

The where method may be used to add WHERE clauses to the query. The method accepts an object of key-value pairs, where the keys are the column names and the values are the values to be compared against.

In addition to comparing primitive values, you may also compare a column against another column:

UserRepository.where('created_at', '>', 'updated_at')

You may also chain multiple where methods to create more specific queries:

UserRepository.where('active', true).where('created_at', '>', 'updated_at')

whereNot​

The whereNot method verifies that the value of the given column is not equal to the given value:

UserRepository.whereNot('active', true)

whereIn​

The whereIn method verifies that a given column's value is contained within the given array:

UserRepository.whereIn('id', [1, 2, 3])

whereNull​

The whereNull method verifies that the value of the given column is NULL:

UserRepository.whereNull('updated_at')

whereNotNull​

The whereNotNull method verifies that the value of the given column is not NULL:

UserRepository.whereNotNull('updated_at')

whereExists​

The whereExists method filters the query based on the existence of any records in the subquery:

UserRepository.whereExists(function (query) {
    query.select('*').from('accounts').whereRaw('users.account_id = accounts.id')
})

whereNotExists​

The whereNotExists method filters the query based on the non-existence of any records in the subquery:

UserRepository.whereNotExists(function (query) {
    query.select('*').from('accounts').whereRaw('users.account_id = accounts.id')
})

whereBetween​

The whereBetween method verifies that a column's value is between two values:

UserRepository.whereBetween('id', [1, 100])

whereNotBetween​

The whereNotBetween method verifies that a column's value lies outside of two values:

UserRepository.whereNotBetween('id', [1, 100])

whereRaw​

The whereRaw method may be used to inject a raw WHERE clause into your query:

UserRepository.whereRaw('id = ?', [1])

whereLike​

The whereLike method may be used to add a where clause with case-sensitive substring comparison on a given column with a given value:

UserRepository.whereLike('name', 'John')

whereILike​

The whereILike method may be used to add a where clause with case-insensitive substring comparison on a given column with a given value:

UserRepository.whereILike('name', 'John')

whereJsonObject​

The whereJsonObject method may be used to add a where clause with json object comparison on given json column:

UserRepository.whereJsonObject('options', { active: true })

whereJsonPath​

The whereJsonPath method may be used to add a where clause with comparison of a value returned by a JsonPath given an operator and a value:

UserRepository.whereJsonPath('profile', '$.age', '>', 18)

whereJsonSupersetOf​

The whereJsonSupersetOf method may be used to add a where clause where the comparison is true if a json given by the column include a given value. Only on MySQL, PostgreSQL and CockroachDB:

UserRepository.whereJsonSupersetOf('profile', { age: 18 })

whereJsonSubsetOf​

The whereJsonSubsetOf method may be used to add a where clause where the comparison is true if a json given by the column is included in a given value. Only on MySQL, PostgreSQL and CockroachDB:

UserRepository.whereJsonSubsetOf('profile', { age: 18 })

groupBy​

The groupBy method may be used to group the query results by a given column:

UserRepository.groupBy('team_id')

groupByRaw​

The groupByRaw method may be used to group the query results by a raw SQL expression:

UserRepository.groupByRaw('extract(year from created_at)')

orderBy​

The orderBy method allows you to sort the result of the query by a given column. The first argument to the orderBy method should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc:

UserRepository.orderBy('name', 'desc')

You may also sort by multiple columns:

UserRepository.orderBy({
    column: 'name',
    order: 'desc'
}, {
    column: 'email',
    order: 'asc'
})

orderByRaw​

The orderByRaw method may be used to sort the query results by a raw SQL expression:

UserRepository.orderByRaw('updated_at - created_at DESC')

having​

The having method's signature is similar to that of the where method:

UserRepository.having('email', '>', 100)

havingRaw​

The havingRaw method may be used to inject a raw HAVING clause into your query:

UserRepository.havingRaw('id = ?', [1])

havingIn​

The havingIn method may be used to add a HAVING IN clause to the query:

UserRepository.havingIn('id', [1, 2, 3])

havingNotIn​

The havingNotIn method may be used to add a HAVING NOT IN clause to the query:

UserRepository.havingNotIn('id', [1, 2, 3])

havingBetween​

The havingBetween method may be used to add a HAVING BETWEEN clause to the query:

UserRepository.havingBetween('id', [1, 100])

havingNotBetween​

The havingNotBetween method may be used to add a HAVING NOT BETWEEN clause to the query:

UserRepository.havingNotBetween('id', [1, 100])

min​

The min method allows you to retrieve the minimum value of a given column:

UserRepository.min('age')

max​

The max method allows you to retrieve the maximum value of a given column:

UserRepository.max('age')

sum​

The sum method allows you to retrieve the sum of a given column:

LikeRepository.sum('count')

sumDistinct​

The sumDistinct method allows you to retrieve the sum of a given column with distinct values:

LikeRepository.sumDistinct('count')

avg​

The avg method allows you to retrieve the average of a given column:

LikeRepository.avg('count')

avgDistinct​

The avgDistinct method allows you to retrieve the average of a given column with distinct values:

LikeRepository.avgDistinct('count')

offset​

The offset method skips the given number of results:

UserRepository.offset(10)

limit​

The limit method limits the number of results returned from the query:

UserRepository.limit(10)

count​

The count method returns the total number of records matching the query constraints:

UserRepository.count()

countDistinct​

The countDistinct method returns the total number of distinct records matching the query constraints:

UserRepository.countDistinct()

softDelete​

The softDelete method marks the given records as deleted. Soft deleted records are not actually removed from your database. Instead, a deleted_at column is set on the record. When querying a repository that uses soft deletes, the soft deleted records will be excluded from the results:

UserRepository.where('active', false).softDelete()

restore​

The restore method restores the given records. Restored records will have their deleted_at column set to null:

UserRepository.where('active', false).restore()

withTrashed​

The withTrashed method may be used to include soft deleted records in the results:

UserRepository.withTrashed().where('active', false).get()

withoutTrashed​

The withoutTrashed method may be used to only include records that are not soft deleted:

UserRepository.withoutTrashed().where('active', false).get()

onlyTrashed​

The onlyTrashed method may be used to only retrieve soft deleted records:

UserRepository.onlyTrashed().where('active', false).get()

Pagination​

You may paginate query results using the pagination or autoPaginate methods. Both methods return an object with the data and pagination properties. The data property contains the query results and the pagination property contains the pagination information.

The pagination method, paginates the query results:

UserRepository.pagination({
    page: 1,
    perPage: 10,
    query: {},
    url: ''
})
PropertyTypeDescription
pagenumberThe current page. The default value is 1.
perPagenumber or nullThe number of records per page. The default value is 20.
queryobject or nullThe query object of the request.
urlstring or nullThe url of the request.

Or you can use the autoPaginate method:

UserRepository.autoPaginate(15)

The autoPaginate method accepts the number of records per page as the first argument. The default value is 20.

Here's an example response:

{
    "data": [
        {
            "id": 1001,
            "name": "Dr. Maureen Cormier",
            "email": "[email protected]",
            "password": "$2b$10$bqL4Z.gWsEMevmNDWtP0YOu03YOXREiCbfOktXSjK85JKGjY/RP2K",
            "remember_token": null,
            "email_verified_at": null,
            "created_at": "2023-12-16 05:58:26",
            "updated_at": "2023-12-16 05:58:26"
        },
        ...
    ],
    "pagination": {
        "total": 3000,
        "pageSize": 10,
        "currentPage": 1,
        "totalPages": 300,
        "pages": [1, 2, 3, 4, 5],
        "firstPage": 1,
        "lastPage": 300,
        "prevPage": null,
        "nextPage": 2,
        "links": {
            "1": {
                "label": 1,
                "active": true,
                "url": "http://127.0.0.1:3000/api/users/?value=&page=1"
            },
            "2": {
                "label": 2,
                "active": false,
                "url": "http://127.0.0.1:3000/api/users/?value=&page=2"
            },
            "3": {
                "label": 3,
                "active": false,
                "url": "http://127.0.0.1:3000/api/users/?value=&page=3"
            },
            "4": {
                "label": 4,
                "active": false,
                "url": "http://127.0.0.1:3000/api/users/?value=&page=4"
            },
            "5": {
                "label": 5,
                "active": false,
                "url": "http://127.0.0.1:3000/api/users/?value=&page=5"
            },
            "firstPage": {
                "label": "First",
                "active": true,
                "url": "http://127.0.0.1:3000/api/users/?value=&page=1"
            },
            "prevPage": null,
            "nextPage": {
                "label": "Next",
                "active": false,
                "url": "http://127.0.0.1:3000/api/users/?value=&page=2"
            },
            "lastPage": {
                "label": "Last",
                "active": false,
                "url": "http://127.0.0.1:3000/api/users/?value=&page=300"
            }
        }
    }
}