Skip to main content

ORM: Query Builder

See how to retrieve, insert, update and delete models using the query builder in Athenna Framework.

Retrieving models​

Once you have created a model and its associated database table, you are ready to start retrieving data from your database.

note

To see how to create and configure your model, check the getting started documentation section of the ORM.

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 '#src/models/Flight'

const where = { active: 1 }
const flights = await Flight.findMany(where)

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.

Hidding fields​

Sometimes you might need to hide some sensitive field from your model queries, to do so, you can set the isHidden property to true in your @Column() annotation:

import { BaseModel } from '@athenna/database'

export class User extends BaseModel {
  @Column({ isHidden: true })
  public password: string

  /*...*/
}

Everytime you call the toJSON() method of your models, Athenna will ignore all the hidden columns from your model and return the rest.

Retrieve hidden fields​

Is possible to bypass the isHidden validation using the withHidden option of the toJSON() method:

const user = await User.query()
  .select('id')
  .select('password')
  .find()

const { password } = user.toJSON({ withHidden: true }) 👈

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 where = { destination: 'Paris' }
const flights = await Flight.collection(where)

const availableFlights = flights.reject(flight => flight.cancelled)

And of course, if you need to add more specific constraints to your query, you can also use the collection() method when working directly with the model query builder:

const flights = await Flight.query()
  .select('name', 'destination', 'cancelled')
  .where('destination', 'Paris')
  .collection()

const availableFlights = flights.reject(flight => flight.cancelled)

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 query() method:

const page = 0
const limit = 10
const resourceUrl = '/flights'

const { data, meta, links } = await Flight.query()
  .where({ active: 1 })
  .paginate(page, limit, resourceUrl)

Searching​

The model search() method works like the query builder search(), looking for a term in many columns at once. The difference is that, with models, you can also search inside the columns of your relationships using the relation.column notation:

const users = await User.query()
  .search(['name', 'email', 'profile.bio', 'metadata->nickname'], 'lenon')
  .paginate()

The query above will find all the users where the name, the email, the nickname inside the metadata JSON column or the bio of the user's profile contains lenon. Nested relationships are supported too, like orders.product.name.

Remember that an empty term doesn't change your query, so you can pass the value received in your request directly. Also, to ignore accents, use the unaccent option (postgres only):

const users = await User.query()
  .search(['name', 'profile.bio'], request.query('search'), {
    unaccent: true
  })
  .findMany()

Full text search​

If your tables are big, prefer the fullTextSearch() method. It has the same signature of search(), but it uses the full text search engine of your database:

const posts = await Post.query()
  .fullTextSearch(['title', 'body', 'author.name'], 'athenna')
  .findMany()
warning

Full text search requires some indexes to be created in your database, and each driver has its own rules. Be sure to read the full text search documentation section before using it.

Filtering​

When building APIs, it's very common to let the client decide how the data should be filtered, ordered and which relationships should be loaded. The filter() method receives all these instructions in a single object and applies them in your query:

const users = await User.query()
  .filter({
    select: ['id', 'name', 'status'],
    includes: ['profile'],
    where: [
      { field: 'status', op: '=', value: 'active' },
      { field: 'createdAt', op: '>=', value: '2026-01-01' },
      { field: 'profile.bio', op: 'contains', value: 'developer' }
    ],
    orderBy: [{ field: 'createdAt', direction: 'DESC' }]
  })
  .findMany()

These are the operators available in the where filters:

OperatorWhat it does
= / !=Equal / not equal. With null, checks if the value is (not) null.
> / >= / < / <=Compare values.
in / not_inThe value is (not) inside the given array.
between / not_betweenThe value is (not) between the two values of the array.
contains / not_containsThe value (does not) contain the given text, ignoring the case.

Just like search(), the field can be a column (status), a column of a relationship (profile.bio, at any depth) or a key inside a JSON column (metadata->mode). Filters for the same field are combined using and.

When using the HTTP server, you don't need to build this object yourself. The request.filters() method parses and validates the filters sent in the query string, so you can give them directly to your query:

// GET /users?search=lenon&where=status:=:active&orderby=-createdAt&page=1&limit=20
const filters = request.filters({
  where: ['status', 'createdAt', 'profile.bio'],
  orderBy: ['createdAt', 'name'],
  includes: ['profile']
})

const users = await User.query()
  .search(['name', 'email'], filters.search)
  .filter(filters)
  .paginate({ page: filters.page, limit: filters.limit })
caution

The filter() method doesn't validate anything, it trusts the object it receives. Never pass filters that came from your users without validating which fields they are allowed to use, like request.filters() does.

note

The page, limit and search properties are ignored by filter(). Use paginate() and search() for them, as shown in the example above.

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 returns 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 () => {
    // ...
  })

Find or create​

The findOrCreate() method looks for a model using the given constraints. If the model exists, it's returned. If not, a new one is created with the data you have passed and returned:

const flight = await Flight.findOrCreate(
  { number: 'FR 900' },
  { number: 'FR 900', destination: 'Paris' }
)

const flight = await Flight.query()
  .where('number', 'FR 900')
  .findOrCreate({ number: 'FR 900', destination: 'Paris' })
tip

If two requests may call findOrCreate() at the same time with the same values, prefer using createOrFirst(), which is safe against race conditions.

Retrieving a single column​

If you just need the value of a single column, use the pluck() method. To get the values of all the models found, use pluckMany():

const name = await Flight.pluck('name', { id: 1 })
const names = await Flight.pluckMany('name', { active: true })

const name = await Flight.query().where('id', 1).pluck('name')
const names = await Flight.query().where('active', true).pluckMany('name')

Validate model existence​

You can also validate if a model exists with determined constraints by using the exists() method:

const existsFlight = await Flight.exists({ id: 1 })

const existsFlight = await Flight.query()
  .where('id', 1)
  .exists()

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 NotFoundDataException 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:

const flight = new Flight()

flight.name = 'Brazil to Ukraine'

await flight.save()
tip

To validate if a model is persisted in database you can use the isPersisted() method:

const flight = new Flight()

flight.name = 'Brazil to Ukraine'

if (!flight.isPersisted()) {
  await flight.save()
}

In this example, we assign the name field to the name attribute of the #src/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:

const flight = await Flight.create({ name: 'Brazil to Angola' })

However, we highly recommend that before using the create() method, you specify the persist field as false in fields you dont want to be persisted. 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:

const flight = await Flight.query()
  .where({ id: 1 })
  .find()

flight.name = 'Paris to London'

await flight.save()
tip

To validate if some change has been done in the model after it was retrieved from database you may use the isDirty() method:

const flight = await Flight.query()
  .where({ id: 1 })
  .find()

if (!flight.isDirty()) {
  flight.name = 'Paris to London'

  await flight.save()
}

Also to get only the values that were modified you may use the dirty() method:

const flight = await Flight.query()
  .where({ id: 1 })
  .find()

flight.name = 'Paris to London'

const { name } = flight.dirty()

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:

const flight = await Flight.create({
  name: 'London to Korea',
})

However, before using the create() method, we extremely recommend you to specify which fields on your model class should not be persisted in database. 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 properties you dont want to be persisted in database. You may do this using by setting the persist property to false in your @Column() annotation. For example, let's make the isAdmin attribute a filed that could not be persisted:

import { BaseModel } from '@athenna/database'

export class Flight extends BaseModel {
  @Column() // By default persist is already `true`
  public name: string

  @Colum({ persist: false })
  public isAdmin: boolean

  /*...*/
}

Once you have specified which attributes are mass assignable or not, 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 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 cleanPersist = false 

await Flight.create(data, cleanPersist)
await Flight.query().create(data, cleanPersist)

await Flight.createMany([data], cleanPersist)
await Flight.query().createMany([data], cleanPersist)

await Flight.createOrUpdate(where, data, cleanPersist)
await Flight.query().createOrUpdate(where, data, cleanPersist)

await Flight.update(where, data, cleanPersist)
await Flight.query().update(where, data, cleanPersist)

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'
  })

Create or ignore​

The createOrIgnore() method only creates the model if no model matches your constraints. If one already exists, nothing happens and null is returned:

const where = { email: 'lenon@athenna.io' }
const data = { name: 'João Lenon', email: 'lenon@athenna.io' }

const user = await User.createOrIgnore(where, data)

const user = await User.query()
  .where('email', 'lenon@athenna.io')
  .createOrIgnore(data)

if (!user) {
  // The user was already registered.
}

Create or first​

The createOrFirst() method always returns a model: the one that already existed or the one that has just been created:

const user = await User.createOrFirst(where, data)

const user = await User.query()
  .where('email', 'lenon@athenna.io')
  .createOrFirst(data)

With SQL drivers, both methods are safe against race conditions: if two requests try to create the same model at the same time, none of them will fail with a unique violation error (with mongo, one of them throws a UniqueViolationException instead). For this to work, make sure your table has a unique constraint or index for the columns used in your constraints. To learn more, check the query builder documentation.

Updating JSON columns​

All the JSON helpers of the database query builder are also available for your models. They update only what you need, directly in the database, and return the updated models just like update():

await Integration.query()
  .where('id', id)
  .mergeJson('metadata', { crawlFinished: true })

await Integration.query()
  .where('id', id)
  .incrementJson('metadata->stats->indexedPages')

await Integration.query()
  .where('id', id)
  .decrementJson('metadata->credits', 5)

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" 👈

Deleting models​

To delete a model, you may call the delete() method on the model instance:

import { Flight } from '#src/models/Flight'

const where = { id: 1 }
await Flight.delete(where)

You can also delete an instance directly:

import { Flight } from '#src/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()

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 just need to set up one column with isDeleteDate as true:

export class Flight extends BaseModel {
  @Column({ isDeleteDate: true }) 
  public deletedAt: Date

  /*...*/
}

Now, when you call the delete() method on the model, Athenna will update your model with 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()

The restore() method works like save(): any change you have done in the model will also be persisted while restoring it:

flight.name = 'Brazil to Ukraine (rescheduled)'

await flight.restore() // Restores and updates the name in one go

You may also use the restore() method in a query to restore multiple models. You can optionally pass the data that should be updated while restoring them:

const flights = await Flight.query()
  .where('airlineId', 1)
  .restore()

const flights = await Flight.restore(
  { airlineId: 1 },
  { status: 'scheduled' }
)

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()

Transactions​

To run model queries inside a database transaction, use the setTransaction() method of the model query builder. All the queries using the same transaction will be committed or rolled back together:

import { Database } from '@athenna/database'

const trx = await Database.startTransaction()

try {
  const user = await User.query()
    .setTransaction(trx)
    .create({ name: 'João Lenon' })

  await Wallet.query()
    .setTransaction(trx)
    .create({ userId: user.id, balance: 0 })

  await trx.commitTransaction()
} catch (error) {
  // Neither the user nor the wallet will be created.
  await trx.rollbackTransaction()

  throw error
}

Combined with the pessimistic locking methods, you can safely read and update a model while blocking other transactions from changing it at the same time:

const trx = await Database.startTransaction()

const wallet = await Wallet.query()
  .setTransaction(trx)
  .where('userId', userId)
  .forUpdate()
  .find()

wallet.balance -= amount

await Wallet.query()
  .setTransaction(trx)
  .where('id', wallet.id)
  .update({ balance: wallet.balance })

await trx.commitTransaction()