version 1.0.0

ORM: Getting Started

Introduction
Generating models
Model conventions
Retrieving models
- Building queries
- Refreshing 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()

ORM: Getting Started

Introduction​

tip

Generating models​

Model conventions​

Table names​

Primary keys​

Default attributes vales​

tip

Schemas​

UUID Keys​

Database connections​

Retrieving models​

Building queries​

tip

Refreshing models​

Pagination​

Collections​

Retrieve single models & Aggregates​

Not found exceptions​

Retrieving aggregates​

warning

Inserting & Updating models​

Inserts​

Updates​

Mass updates​

Mass assignment​

Allowing mass assignment​

tip

Allowing mass assignment for some calls​

Insert or update (Upserts)​

Deleting models​

Deleting models using queries​

Soft deleting​

Restoring soft delete models​

Permanently deleting a model​

Querying soft delete models​

Retrieving only soft deleted models​

Introduction

Generating models

Model conventions

Table names

Primary keys

Default attributes vales

Schemas

UUID Keys

Database connections

Retrieving models

Building queries

Refreshing models

Pagination

Collections

Retrieve single models & Aggregates

Not found exceptions

Retrieving aggregates

Inserting & Updating models

Inserts

Updates

Mass updates

Mass assignment

Allowing mass assignment

Allowing mass assignment for some calls

Insert or update (Upserts)

Deleting models

Deleting models using queries

Soft deleting

Restoring soft delete models

Permanently deleting a model

Querying soft delete models

Retrieving only soft deleted models