ORM: Getting Started
Introduction
Generating models
Model conventions
Retrieving models
Pagination
Collections
Retrieve single models & Aggregates
Inserting & Updating models
Deleting models
Soft deleting
Introduction
Athenna has an object-relational mapper (ORM) that makes it enjoyable to interact with your database. When using the Athenna ORM, each database table has a corresponding "Model" that is used to interact with that table. In addition to retrieving records from the database table, the models allow you to insert, update, and delete records from the table as well.
tip
Before getting started, be sure to configure a database connection in your application's config/database.js
configuration file.
For more information on configuring your database, check out the database configuration documentation.
Generating models
To get started, let's create a model. Models typically live in the app/Models
directory and extend the
Model
class. You may use the make:model
Artisan
command to generate a new model:
node artisan make:model Flight
Model conventions
Models generated by the make:model
command will be placed in the app/Models
directory. Let's examine a basic model
class and discuss some of Model's key conventions:
import { Model } from '@athenna/database'
export class Flight extends Model {
}
Table names
After glancing at the example above, you may have noticed that we did not tell the model which database table corresponds to
our Flight
model. By convention, the "snake_case", plural name of the class will be used as the table name unless another
name is explicitly specified. So, in this case, the model will assume the Flight
model stores records in the flights
table,
while an AirTrafficController
model would store records in an air_traffic_controllers
table.
If your model's corresponding database table does not fit this convention, you may manually specify the model's table name by
defining a static getter table
on the model:
import { Model } from '@athenna/database'
export class Flight extends Model {
static get table() {
return 'my_flights'
}
/*...*/
}
Primary keys
The model will also assume that each model's corresponding database table has a primary key column named id. If necessary,
you may define a static getter primaryKey
on your model to specify a different column that serves as your model's primary
key:
import { Model } from '@athenna/database'
export class Flight extends Model {
static get primaryKey() {
return 'id'
}
/*...*/
}
Default attributes vales
By default, a newly instantiated model instance will not contain any attribute values. If you would like to define the default values
for some of your model's attributes, you may define a static getter attributes
on your model:
import { Uuid } from '@athenna/common'
import { Model } from '@athenna/database'
export class Flight extends Model {
static get attributes() {
return {
id: Uuid.generate()
}
}
/*...*/
}
As you can see we are defining an id
property in our static getter attributes
. This property will have the value of a generated
uuid randomly everytime that Athenna calls the attributes
getter. This means that attributes
is totally different from the default
values that you set in your database migrations. Athenna will call the attributes
getter everytime that create
and createMany
methods are called, this means that a new uuid will be generated for each call:
import { Flight } from '#app/Models/Flight'
const flight1 = await Flight.create()
const flight2 = await Flight.query().create()
console.log(flight1.id) // -> 43bf66ec-658a-4f59-8f89-2aac5ae96e6a
console.log(flight2.id) // -> cbe35c9c-60f3-11ed-9b6a-0242ac120002
tip
But always remember that if you have already set the attribute in one of these methods, the attributes
getter will not subscribe then:
import { Flight } from '#app/Models/Flight'
// Setting my own id attribute
const flight = await Flight.create({ id: '299dabf8-60f4-11ed-9b6a-0242ac120002' })
console.log(flight.id) // -> 299dabf8-60f4-11ed-9b6a-0242ac120002
Schemas
The schemas are very important for Athenna models to work as a "dictionary". When you define a value in your schema you need to
use the Column
class to define all the options available in your database:
import { Model, Column } from '@athenna/database'
export class Flight extends Model {
static schema() {
return {
id: Column.autoIncrementedInt(),
title: Column.string('flight_title'),
createdAt: Column.createdAt(),
updatedAt: Column.updatedAt(),
}
}
}
UUID Keys
Instead of using auto-incrementing integers as your model's primary keys, you may choose to use UUIDs instead. UUIDs are universally unique alphanumeric identifiers that are 36 characters long.
If you would like a model to use a UUID key instead of an auto-incrementing integer key, you may use the Column.autoIncrementedUuid
method in your primary key and then set what is your primary key (the default is already the id
property). Athenna will automatically
identify if you are using UUID's in your primary key and generate a new uuid after creating some record in your database:
import { Model, Column } from '@athenna/database'
export class Article extends Model {
static get primaryKey() {
return 'id'
}
static schema() {
return {
id: Column.autoIncrementedUuid(),
title: Column.string('my_title'),
}
}
}
const article = await Article.create({ title: 'Traveling to Mozambique' })
console.log(article.id) // -> 84b7cdcf-9e97-4547-ae3a-ec652c08f5ac
Database connections
By default, all models will use the default database connection that is configured for your application. If you would like
to specify a different connection that should be used when interacting with a particular model, you should define a
static getter connection
on the model:
import { Model } from '@athenna/database'
export class Article extends Model {
static get connection() {
return 'mysql'
}
/*...*/
}
Retrieving models
Once you have created a model and its associated database table, you are ready to start retrieving data from your database. You can think of each model as a
powerful query builder allowing you to fluently query the database table associated with the model. The model's findMany
method will retrieve all the records
from the model's associated database table:
import { Flight } from '#app/Models/Flight'
const flights = await Flight.findMany()
flights.forEach(flight => console.log(flight.name))
Building queries
The model findMany
method will return all the results in the model's table. However, since each model serves as a query builder, you may invoke the query
method first and add additional constraints to queries and then invoke the findMany
method to retrieve the results:
const flights = await Flight.query()
.where('active', 1)
.orderBy('name')
.limit(10)
.findMany()
tip
Since models are query builders, you should review all the methods provided by Athenna's query builder
.
You may use any of these methods when writing your model queries.
Refreshing models
If you already have an instance of an model that was retrieved from the database, you can "refresh" the model using the fresh
and refresh
methods. The fresh
method will re-retrieve the model from the database. The existing model instance will not be affected:
const flight = await Flight.find({ number: 'FR 900' })
const freshFlight = await flight.fresh()
The refresh
method will re-hydrate the existing model using fresh data from the database. In addition, all of its loaded relationships will be refreshed as well:
const flight = await Flight.find({ number: 'FR 900' })
flight.number = 'FR 456'
await flight.refresh()
console.log(flight.number) // -> "FR 900"
Pagination
The Athenna models also has a paginate
method that works exact like the paginate method from the query builder:
const page = 0
const limit = 10
const resourceUrl = '/flights'
const where = { active: 1 }
const { data, meta, links } = await Flight.paginate(page, limit, resourceUrl, where)
You can also use the paginate
method when working with the ModelQueryBuilder:
const page = 0
const limit = 10
const resourceUrl = '/flights'
const { data, meta, links } = await Flight.query()
.where({ active: 1 })
.paginate(page, limit, resourceUrl)
Collections
As we have seen, the models method findMany
retrieve multiple records from the database. However, the Athenna model has a collection
method that will also retrieve
multiple records from the database but return it as an instance of the Collection
class.
The Collection class provides a variety of helpful methods for interacting with data collections. For example, the reject
method may be used to remove models from a
collection based on the results of an invoked closure:
const flights = await Flight.collection({ destination: 'Paris' })
const availableFlights = flights.reject(flight => flight.cancelled)
Retrieve single models & Aggregates
In addition to retrieving all the records matching a given query, you may also retrieve single records using the find
method. Instead of returning an array or
collection of models, this method return a single model instance:
const flight = await Flight.find({ id: 1 })
const flight = await Flight.find({ active: 1 })
const flight = await Flight.query().where('active', 1).find()
Sometimes you may wish to perform some other action if no results are found. The findOr
method will return a single model instance or, if no results are found,
execute the given closure. The value returned by the closure will be considered the result of the method:
const flight = await Flight.findOr({ id: 1 }, async () => {
// ...
})
const flight = await Flight.query()
.where('legs', '>', 3)
.findOr(async () => {
// ...
})
Not found exceptions
Sometimes you may wish to throw an exception if a model is not found. This is particularly useful in routes or controllers. The findOrFail
method will retrieve
the first result of the query; however, if no result is found, an ModelNotFoundException
will be thrown:
const flight = await Flight.findOrFail({ id: 1 })
const flight = await Flight.query().where('legs', '>', 3).findOrFail()
Retrieving aggregates
When interacting with models, you may also use the count
, sum
, max
, and other aggregate methods provided by the Athenna query builder
.
As you might expect, these methods return a scalar value instead of a model instance:
const count = await Flight.query().where('active', 1).count()
const max = await Flight.query().where('active', 1).max('price')
warning
Aggregate methods will not exist directly in your models, you will always need to call the query
method first and then execute it using one of then.
Inserting & Updating models
Inserts
Of course, when using the models, we don't only need to retrieve then from the database. We also need to insert new records. Thankfully, the models makes it simple.
To insert a new record into the database, you should instantiate a new model instance and set attributes on the model. Then, call the save
method on the model instance:
import { Flight } from '#app/Models/Flight'
const flight = new Flight()
flight.name = 'Brazil to Ukraine'
await flight.save()
In this example, we assign the name
field to the name attribute of the #app/Models/Flight
model instance. When we call the save
method, a record will be inserted into
the database. The model's createdAt
and updatedAt
timestamps will automatically be set when the save
method is called, so there is no need to set them manually.
Alternatively, you may use the create
method to "save" a new model using a single statement. The inserted model instance will be returned to you by the create
method:
import { Flight } from '#app/Models/Flight'
const flight = await Flight.create({ name: 'Brazil to Angola' })
However, we highly recommend that before using the create
method, you specify the static getter persistOnly
on your model class. This property will help your models to
get protected against mass assignment vulnerabilities. To learn more about mass assignment, please consult the mass assignment documentation.
Updates
The save
method may also be used to update models that already exist in the database. To update a model, you should retrieve it and set any attributes
you wish to update. Then, you should call the model's save
method. Again, the updatedAt
timestamp will automatically be updated, so there is no need
to manually set its value:
import { Flight } from '#app/Models/Flight'
const flight = await Flight.query()
.where({ id: 1 })
.find()
flight.name = 'Paris to London'
await flight.save()
Mass updates
Updates can also be performed against models that match a given query. In this example, all flights that are active
and have a destination
of San Diego
will be marked as delayed:
await Flight.query()
.where('active', 1)
.where('destination', 'San Diego')
.update({ delayed: 1 })
The update
method expects a record of columns and value pairs representing the columns that should be updated. The update
method will always return one
instance of your model if your query only modifies one value. If you query modifies more than one the update
method will return an array of your models
instance.
Mass assignment
You may use the create
method to "save" a new model using a single statement. The inserted model instance will be returned to you by the method:
import { Flight } from '#app/Models/Flight'
const flight = await Flight.create({
name: 'London to Korea',
})
However, before using the create
method, we extremely recommend you to specify the static getter persistOnly
on your model class. This property are
will help you to stay protected against mass assignment vulnerabilities.
A mass assignment vulnerability occurs when a user passes an unexpected field using some object and that field changes a column in your database that
you did not expect. For example, a malicious user might send an isAdmin
parameter through an HTTP request, which is then passed to your model's create
method, allowing the user to escalate themselves to an administrator.
So, to get started, you should define which model attributes you want to make mass assignable. You may do this using the static getter persistOnly
on
the model. For example, let's make the name
attribute of our Flight model mass assignable:
import { Model } from '@athenna/database'
export class Flight extends Model {
static get persistOnly() {
return ['name']
}
/*...*/
}
Once you have specified which attributes are mass assignable, you may use the create
method to insert a new record in the database. The create
method returns the newly created model instance:
const flight = await Flight.create({ name: 'London to Paris' })
Allowing mass assignment
If you would like to make all of your attributes mass assignable, you may define your model's static getter persistOnly
as an array returning an
asterisk (['*']
). If you choose to unguarded your model, you should take special care to always hand-craft the arrays passed to create
and update
methods:
static get persistOnly() {
return ['*']
}
tip
By default, the Model class already implements the static getter persistOnly
returning the array with asterisk (['*']
). So if you don't want mass
assignment protection, you could only delete this getter from your model:
import { Model } from '@athenna/database'
export class Flight extends Model {
/*...*/
}
Allowing mass assignment for some calls
You can also allow mass assignment when calling your create
, createMany
, createOrUpdate
and update
methods:
const data = { name: 'Brazil to Mexico' }
const where = { active: 1 }
const ignorePersistOnly = true
await Flight.create(data, ignorePersistOnly)
await Flight.query().create(data, ignorePersistOnly)
await Flight.createMany([data], ignorePersistOnly)
await Flight.query().createMany([data], ignorePersistOnly)
await Flight.createOrUpdate(where, data, ignorePersistOnly)
await Flight.query().createOrUpdate(where, data, ignorePersistOnly)
await Flight.update(where, data, ignorePersistOnly)
await Flight.query().update(where, data, ignorePersistOnly)
Insert or update (Upserts)
Occasionally, you may need to update an existing model or create a new model if no matching model exists. The createOrUpdate
method will update the model
if some record is found by the query that you have built, otherwise the record will be created.
In the example below, if a flight exists with a departure
location of Oakland
and a destination
location of San Diego
, its price
and discounted
columns will be updated. If no such flight exists, a new flight will be created:
const where = {
departure: 'Oakland',
destination: 'San Diego'
}
const data = {
price: 99,
discounted: 1,
departure: 'Oakland',
destination: 'San Diego'
}
await Flight.createOrUpdate(where, data)
Or you can use the query builder instead:
await Flight.query()
.where('departure', 'Oakland')
.where('destination', 'San Diego')
.createOrUpdate({
price: 99,
discounted: 1,
departure: 'Oakland',
destination: 'San Diego'
})
Deleting models
To delete a model, you may call the delete
method on the model instance:
import { Flight } from '#app/Models/Flight'
const where = { id: 1 }
await Flight.delete(where)
You can also delete an instance directly:
import { Flight } from '#app/Models/Flight'
const where = { id: 1 }
const flight = await Flight.find(where)
await flight.delete()
You may call the truncate
method to delete all the model's associated database records. The truncate
operation will also reset any auto-incrementing
IDs on the model's associated table:
await Flight.truncate()
Deleting models using queries
Of course, you may build a query to delete all models matching your query's criteria. In this example, we will delete all flights that are marked as inactive:
await Flight.query().where('active', 0).delete()
Soft deleting
In addition to actually removing records from your database, the ORM can also "soft delete" models. When models are soft deleted, they are not actually
removed from your database. Instead, a deletedAt
attribute is set on the model indicating the date and time at which the model was "deleted". To enable
soft deletes for a model, you can just make the static getter isSoftDelete
return true in your model:
export class Flight extends Model {
static get isSoftDelete() {
return false
}
/*...*/
}
You should also add the deletedAt
column to your database table in your migrations:
import { Migration } from '@athenna/database'
export class FlightMigration extends Migration {
async up({ schema }) {
return schema.createTable('my_flights', table => {
table.increments('id')
table.string('flight_title')
table.timestamps(true, true, true)
table.dateTime('deletedAt').nullable().defaultTo(null)
})
}
async down({ schema }) {
return schema.dropTableIfExists('my_flights')
}
}
And in your Model schema:
export class Flight extends Model {
static get isSoftDelete() {
return false
}
static schema() {
return {
id: Column.autoIncrementedInt(),
title: Column.string('flight_title'),
createdAt: Column.createdAt(),
updatedAt: Column.updatedAt(),
deletedAt: Column.deletedAt(),
}
}
/*...*/
}
Now, when you call the delete
method on the model, Athenna will update your model with setting the current date and time in your deletedAt
column
However, the model's database record will be left in the table. When querying a model that uses soft deletes, the soft deleted models will automatically
be excluded from all query results:
const where = { id: 1 }
const flight = await Flight.find(where)
await flight.delete()
// The flight with id = 1 will not be inside the above array.
const flights = await Flight.findMany()
To determine if a given model instance has been soft deleted, you may use the isTrashed
method:
if (flight.isTrashed()) {
//
}
Restoring soft delete models
Sometimes you may wish to "un-delete" a soft deleted model. To restore a soft deleted model, you may call the restore
method on a model instance. The
restore
method will set the model's deletedAt
column to null
:
await flight.restore()
You may also use the restore
method in a query to restore multiple models:
const flights = await Flight.query()
.where('airlineId', 1)
.restore()
Permanently deleting a model
Sometimes you may need to truly remove a model from your database. You may use the delete
method with a true
value in the first argument to permanently
remove a soft deleted model from the database table:
const force = true
await flight.delete(force)
Querying soft delete models
As noted above, soft deleted models will automatically be excluded from query results. However, you may force soft deleted models to be included in a
query's results by calling the withTrashed
method on the query:
const flights = await Flight.query()
.withTrashed()
.where('airlineId', 1)
.findMany()
Retrieving only soft deleted models
The onlyTrashed
method will retrieve only soft deleted models:
const flights = await Flight.query()
.onlyTrashed()
.where('airlineId', 1)
.findMany()