Database: Query Builder

See how to use the Athenna database query builder.

Introduction

Athenna database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works perfectly with all of Athenna supported database drivers.

Running database queries

Retrieve all rows from a table

You may use the table() method provided by the Database facade to begin a query. The table() method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the findMany() method:

import { Database } from '@athenna/database'

const users = await Database.table('users')
  .select('id', 'name')
  .whereILike({ name: '%Valmir Barbosa%' })
  .orderBy('name', 'ASC')
  .findMany()

Athenna also provides the collection() method that returns a Collection instance containing the results of the query. You may access each column's value using the all() method:

const collection = await Database.table('users')
  .select('id', 'name')
  .whereILike({ name: '%Valmir Barbosa%' })
  .orderBy('name', 'ASC')
  .collection()

const users = collection.all()

tip

Athenna collections provide a variety of extremely powerful methods for mapping and reducing data. For more information on Athenna collections, check out the collection documentation.

Retrieve a single row

If you just need to retrieve a single row from a database table, you may use the Database facade's find() method. This method will return a single object:

const user = await Database.table('users')
  .select('id', 'name')
  .where({ name: 'Rodrigo Kamada' })
  .find()

Retrieve values of a single column

If you only need the value of one column, you don't need to retrieve the whole row and then pick the value. The pluck() method returns the value of the given column directly from the first row found:

const email = await Database.table('users')
  .where({ id: 1 })
  .pluck('email')

console.log(email) // 'lenon@athenna.io'

When you need the values of many rows, use the pluckMany() method. It returns an array with the values of the given column:

const emails = await Database.table('users')
  .whereNull('deletedAt')
  .pluckMany('email')

console.log(emails) // ['lenon@athenna.io', 'txsoura@athenna.io']

Validate if exists

If you just need to validate if exists any row with determined constraints, you may use the Database facade's exists() method. This method will return true if the row exists:

const hasUser = await Database.table('users')
  .select('id', 'name')
  .where({ name: 'Rodrigo Kamada' })
  .exists()

Get the client and query builder of a driver

To get the vanilla client or query builder of your connection driver, you can use the getClient() and getQueryBuilder() method:

// Knex client
const client = Database.connection('postgres').getClient()

await client.close()

// Knex query builder
const queryBuilder = Database.connection('postgres').getQueryBuilder()

const result = await queryBuilder
  .where({ id: 1, status: 'ACTIVE' })
  .andWhere('status', 'PENDING')

Agreggates

The query builder also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

const numberOfUsers = await Database.table('users').count()
const maxPriceOrder = await Database.table('orders').max('price')

Of course, you may combine these methods with other clauses to fine-tune how your aggregate value is calculated:

const priceAverage = await Database.table('orders')
  .where('finalized', true)
  .avg('price')

Select statements

You may not always want to select all columns from a database table. Using the select() method, you can specify a custom "select" clause for the query:

const { id, name } = await Database.table('users')
  .select('id', 'name')
  .find()

If you want to select all fields you can use the * operator:

const { id, name, email } = await Database.table('users')
  .select('*')
  .find()

Raw expressions

Sometimes you may need to insert an arbitrary string into a query. To create a raw string expression, you may use the raw() method provided by the Database facade:

const users = await Database.table('users')
  .select(Database.raw('count(*) as users_count, status'))
  .where('status', '<>', 1)
  .groupBy('status')
  .findMany()

You can also use await to wait your query to finish:

const users = await Database.raw('SELECT * FROM users')

caution

You should be extremely careful to avoid creating SQL injection vulnerabilities using the raw() method.

Raw methods

Instead of using the Database.raw() method, you may also use the following methods to insert a raw expression into various parts of your query. Remember, Athenna can not guarantee that any query using raw expressions is protected against SQL injection vulnerabilities.

`selectRaw()`

The selectRaw() method can be used in place of select(Database.raw(/* ... */)). This method accepts an optional array of bindings as its second argument:

const orders = await Database.table('orders')
  .selectRaw('price * ? as price_with_tax', [1.0825])
  .findMany()

`whereRaw() / orWhereRaw()`

The whereRaw() and orWhereRaw() methods can be used to inject a raw "where" clause into your query. These methods accept an optional array of bindings as their second argument:

const orders = await Database.table('orders')
  .whereRaw('price > IF(state= "TX", ?, 100)', [200])
  .findMany()

`havingRaw() / orHavingRaw()`

The havingRaw() and orHavingRaw() methods may be used to provide a raw string as the value of the "having" clause. These methods accept an optional array of bindings as their second argument:

const orders = await Database.table('orders')
  .select('department', Database.raw('SUM(price) as total_sales'))
  .groupBy('department')
  .havingRaw('SUM(price) > ?', [2500])
  .findMany()

`orderByRaw()`

The orderByRaw() method may be used to provide a raw string as the value of the "order by" clause:

const orders = await Database.table('orders')
  .orderByRaw('updated_at - created_at DESC')
  .findMany()

`groupByRaw()`

The groupByRaw() method may be used to provide a raw string as the value of the "group by" clause:

const orders = await Database.table('orders')
  .select('city', 'state')
  .groupByRaw('city, state')
  .findMany()

Joins

Inner join clause

The query builder may also be used to add join clauses to your queries. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You may even join multiple tables in a single query:

const users = await Database.table('users')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .join('orders', 'users.id', '=', 'orders.user_id')
  .select('users.*', 'contacts.phone', 'orders.price')
  .findMany()

Other join clauses

If you would like to perform a "left join" or "right join" instead of an "inner join", use the leftJoin() or rightJoin() methods. They have the same signature of join method:

const users = await Database.table('users')
  .leftJoin('contacts', 'users.id', '=', 'contacts.user_id')
  .rightJoin('orders', 'users.id', '=', 'orders.user_id')
  .select('users.*', 'contacts.phone', 'orders.price')
  .findMany()

You can use any of the join types bellow in your queries:

leftJoin()
rightJoin()
crossJoin()
fullOuterJoin()
leftOuterJoin()
rightOuterJoin()

Advanced join clauses

You may also specify more advanced join clauses using the Database facade:

const users = await Database.table('users')
  .join('contacts', join => join.on('users.id', '=', 'contacts.user_id').orOn(/* ... */))
  .findMany()

Basic where clauses

Where clauses

You may use the query builder's where() method to add "where" clauses to the query. The most basic call to the where() method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

For example, the following query retrieves users where the value of the votes column is equal to 100 and the value of the age column is greater than 35:

const user = await Database.table('users')
  .where('votes', '=', 100)
  .where('age', '>', 35)
  .find()

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where() method. Athenna will assume you would like to use the = operator:

const users = await Database.table('users')
  .where('votes', 100)
  .findMany()

As previously mentioned, you may use any operator that is supported by your database system:

const users = await Database.table('users')
  .where('votes', '>=', 100)
  .findMany()

const users = await Database.table('users')
  .where('votes', '<>', 100)
  .findMany()

// `!=` works exactly like `<>`, use the one you prefer
const users = await Database.table('users')
  .where('votes', '!=', 100)
  .findMany()

const users = await Database.table('users')
  .where('name', 'like', 'J%')
  .findMany()

You may also pass an object of conditions, but remember that when using objects the operation is always going to be =:

const users = await Database.table('users')
  .where({ name: 'João Lenon', deletedAt: null })
  .findMany()

Or where clauses

When chaining together calls to the query builder's where() method, the "where" clauses will be joined together using the and operator. However, you may use the orWhere() method to join a clause to the query using the or operator. The orWhere() method accepts the same arguments as the where() method:

const users = await Database.table('users')
  .where('votes', '>', 100)
  .orWhere('name', 'João')
  .findMany()

Where not clauses

The whereNot() and orWhereNot() methods may be used to negate a given constraint. For example, the following query excludes the product with id ten:

const products = await Database.table('products')
  .whereNot('id', 10)
  .findMany()

Additional where clauses

`whereLike() / orWhereLike()`

The whereLike() or orWhereLike() methods verifies that a column's value matches a given pattern. Use % as a wildcard:

const users = await Database.table('users')
  .whereLike('email', '%@athenna.io')
  .findMany()

`whereILike() / orWhereILike()`

The whereILike() or orWhereILike() methods work like whereLike(), but they ignore the letter case, so joão and JOÃO are the same:

const users = await Database.table('users')
  .whereILike('name', '%joão%')
  .findMany()

If you also want to ignore accents, so joao finds João and vice versa, set the unaccent option to true:

const users = await Database.table('users')
  .whereILike('name', '%joao%', { unaccent: true })
  .findMany()

note

The unaccent option only works with the postgres driver and it requires the unaccent extension to be installed in your database. You can install it with a migration:

public async up(db: DatabaseImpl) {
  await db.raw('CREATE EXTENSION IF NOT EXISTS unaccent')
}

The mysql, sqlite and mongo drivers simply ignore this option.

`whereBetween() / orWhereBetween()`

The whereBetween() or orWhereBetween() methods verifies that a column's value is between two values:

const users = await Database.table('users')
  .whereBetween('votes', [1, 100])
  .findMany()

`whereNotBetween() / orWhereNotBetween()`

The whereNotBetween() or orWhereNotBetween() methods verifies that a column's value lies outside two values:

const users = await Database.table('users')
  .whereNotBetween('votes', [1, 100])
  .findMany()

`whereIn() / orWhereIn()`

The whereIn() or orWhereIn() methods verifies that a given column's value is contained within the given array:

const users = await Database.table('users')
  .whereIn('id', [1, 2, 3])
  .findMany()

`whereNotIn() / orWhereNotIn()`

The whereNotIn() or orWhereNotIn() methods verifies that the given column's value is not contained in the given array:

const users = await Database.table('users')
  .whereNotIn('id', [1, 2, 3])
  .findMany()

`whereNull() / orWhereNull()`

The whereNull() or orWhereNull() methods verifies that the value of the given column is NULL:

const users = await Database.table('users')
  .whereNull('deletedAt')
  .findMany()

`whereNotNull() / orWhereNotNull()`

The whereNotNull() or orWhereNotNull() methods verifies that the column's value is not NULL:

const users = await Database.table('users')
  .whereNotNull('deletedAt')
  .findMany()

Logical grouping

Sometimes you may need to group several "where" clauses within parentheses in order to achieve your query's desired logical grouping. In fact, you should generally always group calls to the orWhere() method in parentheses in order to avoid unexpected query behavior. To accomplish this, you may pass a closure to the where method:

const users = await Database.table('users')
  .where('name', '=', 'João')
  .where(query => {
    query
      .where('votes', '>', 100)
      .orWhere('title', '=', 'Admin')
  })
  .findMany()

As you can see, passing a closure into the where() method instructs the query builder to begin a constraint group. The closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group. The example above will produce the following SQL:

select * from users where name = 'João' and (votes > 100 or title = 'Admin')

warning

You should always group orWhere() calls in order to avoid unexpected behavior when global scopes are applied.

Advanced where clauses

Where exists clauses

The whereExists(), orWhereExists(), whereNotExists() and orWhereNotExists() methods allows you to write "where exists" SQL clauses. They accept a closure which will receive a query builder instance, allowing you to define the query that should be placed inside the "exists" clause:

const users = await Database.table('users')
  .whereExists(Database.table('orders')
                       .selectRaw(1)
                       .whereRaw("`orders`.`user_id` = `users`.`id`"))
  .findMany()

The query above will produce the following SQL:

select * from users
where exists (
    select 1
    from orders
    where orders.user_id = users.id
)

JSON where clauses

Many applications store flexible data inside JSON columns, like a metadata or settings column. Athenna lets you filter by the values inside these columns using the -> operator to walk through the JSON keys:

const users = await Database.table('users')
  .where('settings->theme', 'dark')
  .findMany()

You can go as deep as you need and use any of the operators you already know:

const products = await Database.table('products')
  .where('metadata->dimensions->weight', '>', 10)
  .findMany()

The -> operator also works with orWhere(), whereILike(), orWhereILike() and orderBy(). If you prefer to make it explicit that you are filtering a JSON column, you can use the whereJson() and orWhereJson() methods. They have the same signature of where():

const products = await Database.table('products')
  .whereJson('metadata->color', 'blue')
  .orWhereJson('metadata->stock', '<', 5)
  .findMany()

Filtering missing JSON keys

To find rows where a JSON key is null or doesn't even exist, use the whereJsonNull() method. The whereJsonNotNull() method does the opposite, matching only rows where the key exists and is not null:

const avatarsWithoutTrial = await Database.table('avatars')
  .whereJsonNull('metadata->trialEndsAt')
  .findMany()

const avatarsWithTrial = await Database.table('avatars')
  .whereJsonNotNull('metadata->trialEndsAt')
  .findMany()

You can also use orWhereJsonNull() and orWhereJsonNotNull() to join these clauses using the or operator.

Searching

Building a search box is something almost every application needs. Usually, you want to find a term in more than one column, like the name or the email of a user. The search() method does exactly that for you:

const users = await Database.table('users')
  .search(['name', 'email'], 'lenon')
  .findMany()

The query above will produce the following SQL:

select * from users where (name ilike '%lenon%' or email ilike '%lenon%')

Notice that all the columns are grouped inside parentheses, so you can safely combine search() with any other where() clause. JSON columns are also supported:

const users = await Database.table('users')
  .where('status', 'active')
  .search(['name', 'metadata->nickname'], 'lenon')
  .findMany()

If the term is empty ('', null or undefined), the search() method does nothing. This means you can pass the value received in your request directly, without any if:

const users = await Database.table('users')
  .search(['name', 'email'], request.query('search'))
  .paginate()

Just like whereILike(), you can also ignore accents by setting the unaccent option (postgres only):

const users = await Database.table('users')
  .search(['name'], 'joao', { unaccent: true })
  .findMany()

tip

Using models? The model search() method can also search inside the columns of your relations. Check the ORM searching documentation section.

Full text search

The search() method is great for small tables, but ILIKE '%term%' needs to look into every row of your table. When you have a lot of data, or you want smarter matching (words in any order, ignoring stop words, etc), use the full text search engine of your database with the whereFullText() method:

const posts = await Database.table('posts')
  .whereFullText(['title', 'body'], 'athenna framework')
  .findMany()

By default, every word of the term must be present in the columns. If you want your users to be able to use operators like quoted phrases ("exact phrase"), -word to exclude a word or OR, set the mode option to boolean:

const posts = await Database.table('posts')
  .whereFullText(['title', 'body'], '"query builder" -mongo', {
    mode: 'boolean'
  })
  .findMany()

You can also set the language that should be used to understand the words of your term (default is simple):

const posts = await Database.table('posts')
  .whereFullText('body', 'desenvolvedores', { language: 'portuguese' })
  .findMany()

You can use orWhereFullText() to join the clause using the or operator.

warning

Full text search works differently in each database, so be sure to read the notes of the driver you are using:

postgres: works without an index, but it will be slow on big tables. To make it fast, create a GIN index using exactly the same expression and language used by Athenna:

await db.raw(`
  CREATE INDEX posts_fulltext ON posts
  USING GIN (to_tsvector('simple', coalesce("title", '') || ' ' || coalesce("body", '')))
`)

mysql: requires a FULLTEXT index covering exactly the same columns you pass to whereFullText(). The language option is ignored:

await db.alterTable('posts', table => {
  table.index(['title', 'body'], 'posts_fulltext', 'FULLTEXT')
})

sqlite: real full text search requires a special FTS5 table, so Athenna falls back to a LIKE '%term%' search across your columns. This keeps your code working when using SQLite in your tests and Postgres or MySQL in production. All options are ignored.
mongo: requires a text index in your collection. Since Mongo only allows one text index per collection, the columns you pass are ignored and the index decides which fields are searched. The language option is forwarded as $language.

Ordering, grouping, limit & skip

Ordering

The `orderBy()` method

The orderBy() method allows you to sort the results of the query by a given column. The first argument accepted by the orderBy() method should be the column you wish to sort by, while the second argument determines the direction of the sort and may be either asc, ASC, desc or DESC:

const users = await Database.table('users')
  .orderBy('name', 'desc')
  .findMany()

To sort by multiple columns, you may simply invoke orderBy() as many times as necessary:

const users = await Database.table('users')
  .orderBy('name', 'desc')
  .orderBy('email', 'asc')
  .findMany()

When the column may have null values, you can choose if they should come first or last in your results using the nulls option:

const tasks = await Database.table('tasks')
  .orderBy('dueDate', 'asc', { nulls: 'last' })
  .findMany()

note

Postgres and SQLite use NULLS FIRST/LAST and MySQL emulates the same behavior for you. The mongo driver ignores this option and always sorts null values first in ASC and last in DESC.

You can also order by a value inside a JSON column using the -> operator:

const products = await Database.table('products')
  .orderBy('metadata->priority', 'desc')
  .findMany()

The `latest()` & `oldest()` methods

The latest() and oldest() methods allow you to easily order results by date. By default, the result will be ordered by the table's createdAt column:

const user = await Database.table('users')
  .latest()
  .find()

Or, you may pass the column name that you wish to sort by:

const user = await Database.table('users')
  .oldest('updatedAt')
  .find()

Grouping

The `groupBy()` & `having()` methods

As you might expect, the groupBy() and having() methods may be used to group the query results. The having method's signature is similar to that of the where() method:

const users = await Database.table('users')
  .groupBy('account_id')
  .having('account_id', '>', 100)
  .findMany()

You can use the havingBetween() method to filter the results within a given range:

const users = await Database.table('users')
  .selectRaw('count(id) as number_of_users, account_id')
  .groupBy('account_id')
  .havingBetween('number_of_users', [0, 100])
  .findMany()

You may pass multiple arguments to the groupBy() method to group by multiple columns:

const users = await Database.table('users')
  .groupBy('first_name', 'status')
  .having('account_id', '>', 100)
  .findMany()

To build more advanced having() statements, see the havingRaw() method.

Limit & Offset

You may use the offset() and limit() methods to limit the number of results returned from the query or to skip a given number of results in the query:

const users = await Database.table('users')
  .offset(10)
  .limit(5)
  .findMany()

tip

The offset() method is equivalent to skip() and the limit() method is equivalent to take(). take() and skip() are usually used by other query builders.

Conditional clauses

Sometimes you may want certain query clauses to apply to a query based on another condition. For instance, you may only want to apply a where() statement if a given input value is present on the incoming HTTP request. You may accomplish this using the when() method:

const role = request.payload('role')

await Database.table('users')
  .when(role, (query, role) => query.where('roleId', role))
  .findMany()

The when() method only executes the given closure when the first argument is true. If the first argument is false, the closure will not be executed. So, in the example above, the closure given to the when() method will only be invoked if the role field is present on the incoming request and evaluates to a truthy value.

You can also add two when() methods to your query to execute a different closure when the role IS NOT present in your query:

const role = request.payload('role')

await Database.table('users')
  // Executes if role is present
  .when(role, (query, role) => query.where('roleId', role))
  // Executes if role is not present
  .when(!role, (query, role) => query.where('roleId', role))
  .findMany()

Pagination

You can paginate the results of your database using the paginate() method. This method support 3 arguments, the first argument is the page (default value is 0) it defines the page where your pagination will start, the second is the limit (default value is 10) it defines the limit of data that will be retrieved per page and the third one defines the resource url that Athenna will use to create the pagination links:

const { data, meta, links } = await Database.table('users')
  .whereNull('deletedAt')
  .paginate(0, 10, '/users')

If you prefer, you can also pass these values as an object:

const { data, meta, links } = await Database.table('users')
  .whereNull('deletedAt')
  .paginate({ page: 0, limit: 10, resourceUrl: '/users' })

The data param is where all the data retrieved from database will stay:

console.log(data) // -> [{...}]

The meta param will have information about the pagination such as the total of items finds using that query, items per page, total pages left, current page and the number of itens in that specific execution:

console.log(meta)
/**
* {
*    totalItems: 10,
*    itemsPerPage: 10,
*    totalPages: 10,
*    currentPage: 1,
*    itemCount: 10
* }
*/

The links object will help ho is consuming you API to know what is the next resource to call to go through your paginated data:

console.log(links)
/**
* {
*   next: '/users?page=2&limit=10',
*   previous: '/users?page=0&limit=10',
*   last: '/users?page=10&limit=10,
*   first: '/users?&limit=10'
* }
*/

Insert statements

The query builder also provides the create() and createMany() methods that may be used to insert records into the database table. The create() method accepts a record with columns names and values:

const user = await Database.table('users').create({
    name: 'Valmir Barbosa',
    email: 'valmirphp@gmail.com'
})

The createMany() method accepts an array of records:

const users = await Database.table('users').createMany([
    {
        name: 'Valmir Barbosa',
        email: 'valmirphp@gmail.com'
    },
    {
        name: 'Danrley Morais',
        email: 'danrley.morais@gmail.com'
    }
])

Create or update (Upsert)

The createOrUpdate() method will insert records that do not exist and update the records that already exist with new values that you may specify. The method's first argument consists of the values to insert or update, while the second argument is the column that uniquely identify records within the associated table (the default is id). In the example above we are going to create a new record in the users table only if the txsoura@athenna.io email is not already registered in users table:

const user = await Database.table('users')
  .createOrUpdate({
    name: 'Victor Tesoura',
    email: 'txsoura@athenna.io'
  }, 'email') // <- The uniquely identifier

Create or ignore

Sometimes you only want to create a record if it doesn't exist yet, and do nothing otherwise. The createOrIgnore() method uses your where() clauses to look for an existing record. If one is found, nothing is created and null is returned. Otherwise, the new record is created and returned:

const user = await Database.table('users')
  .where('email', 'txsoura@athenna.io')
  .createOrIgnore({ name: 'Victor Tesoura', email: 'txsoura@athenna.io' })

if (!user) {
  // The user already existed, nothing was created.
}

Create or first

The createOrFirst() method is similar, but it always returns a record: the one that already existed, or the one that has just been created:

const user = await Database.table('users')
  .where('email', 'txsoura@athenna.io')
  .createOrFirst({ name: 'Victor Tesoura', email: 'txsoura@athenna.io' })

tip

What makes these two methods special is that they are safe against race conditions. Imagine two requests trying to create the same user at the exact same time: both may check that the user doesn't exist and both may try to insert it. Instead of failing with a unique violation error, these methods let the database solve the conflict (ON CONFLICT DO NOTHING / INSERT IGNORE). For this protection to work, remember to create a unique constraint or index for the columns used in your where() clauses.

note

The mongo driver doesn't have an ON CONFLICT equivalent. If two requests create the same document at the same time, one of them will throw a UniqueViolationException. You still need a unique index in your collection to prevent the duplicated document.

Update statements

In addition to inserting records into the database, the query builder can also update existing records using the update() method. The update() method, like the create() method, accepts a record with columns names and values indicating the columns to be updated. You may constrain the update query using where clauses. In the example above we are going to "undo" the soft delete by searching for all records where the deletedAt column is not null and setting it to null:

const user = await Database.table('users')
  .whereNotNull('deletedAt')
  .update({ deletedAt: null })

Need to compute the new value in the database itself? You can pass a Database.raw() expression as the value of any column:

await Database.table('users')
  .where('id', 1)
  .update({ lastLoginAt: Database.raw('CURRENT_TIMESTAMP') })

Incrementing & decrementing

The query builder also provides convenient methods for incrementing or decrementing the value of a given column. Both of these methods accept at least one argument: the column to modify:

await Database.table('users').increment('votes')
await Database.table('users').where('id', 1).increment('votes')

await Database.table('users').decrement('votes')
await Database.table('users').where('id', 1).decrement('votes')

Updating JSON columns

Updating a single key of a JSON column usually means: read the row, change the object in your code and save it back. Besides being verbose, if two requests do this at the same time, one of them will lose its changes. Athenna provides helpers that do these updates directly in the database, in a single statement.

Merging objects

The mergeJson() method merges an object into a JSON column. The keys you pass replace the existing ones and everything else in the column is kept untouched:

// metadata before: { crawlStarted: true, pages: 10 }
await Database.table('integrations')
  .where('id', id)
  .mergeJson('metadata', { crawlFinished: true })
// metadata after: { crawlStarted: true, pages: 10, crawlFinished: true }

note

The merge is shallow, which means only the first level keys are merged. If the column is null, it will be treated as an empty object ({}).

Incrementing & decrementing numbers

The incrementJson() and decrementJson() methods work like increment() and decrement(), but for a number inside a JSON column. Use the -> operator to point to the key you want to change and, optionally, the amount as the second argument:

await Database.table('integrations')
  .where('id', id)
  .incrementJson('metadata->stats->indexedPages')

await Database.table('integrations')
  .where('id', id)
  .decrementJson('metadata->credits', 5)

If the key doesn't exist or the column is null, the value starts from 0. Missing parent keys are created for you.

Combining with other columns

The methods above update only the JSON column. If you need to update other columns in the same statement, use the Database.jsonMerge() and Database.jsonIncrement() helpers inside a regular update():

await Database.table('integrations')
  .where('id', id)
  .update({
    status: 'done',
    metadata: Database.jsonMerge({ crawlFinished: true })
  })

await Database.table('integrations')
  .where('id', id)
  .update({
    status: 'indexing',
    metadata: Database.jsonIncrement('stats->indexedPages', 10)
  })

Notice that the path given to jsonIncrement() is relative to the column, so we use stats->indexedPages instead of metadata->stats->indexedPages.

Delete statements

The query builder's delete() method may be used to delete records from the table. You may constrain delete statements by adding "where" clauses before calling the delete() method:

await Database.table('users').delete()
await Database.table('users').where('votes', '>', 100).delete()

If you wish to truncate an entire table, which will remove all records from the table and reset the auto-incrementing ID to zero, you may use the truncate method:

const tableName = 'users'

await Database.truncate(tableName)

Pessimistic locking

Pessimistic locking lets you lock the rows selected by your query, so no other transaction can change them until yours is finished. This is useful when you need to read a value and update it based on what you have read, like a wallet balance.

The forUpdate() method locks the rows for update. Any other transaction trying to lock or change the same rows will wait until your transaction is committed or rolled back:

const trx = await Database.startTransaction()

try {
  const wallet = await trx.table('wallets')
    .where('userId', userId)
    .forUpdate()
    .find()

  await trx.table('wallets')
    .where('userId', userId)
    .update({ balance: wallet.balance - amount })

  await trx.commitTransaction()
} catch (error) {
  await trx.rollbackTransaction()

  throw error
}

The forShare() method creates a shared lock instead. Other transactions can still read the rows, but they can't change them until your transaction finishes:

const product = await trx.table('products')
  .where('id', productId)
  .forShare()
  .find()

Not waiting for locked rows

By default, your query waits if the rows are already locked by another transaction. You can change this behavior by combining forUpdate() or forShare() with one of the following methods:

skipLocked(): ignore the rows that are already locked and return only the free ones. Very useful to build job queues where many workers pick tasks from the same table.
noWait(): fail immediately if any of the rows is already locked.

const jobs = await trx.table('jobs')
  .where('status', 'pending')
  .limit(10)
  .forUpdate()
  .skipLocked()
  .findMany()

warning

Locks only make sense inside a transaction, since they are released as soon as the transaction ends. These methods are not available for the mongo driver, and the sqlite driver simply ignores them since SQLite doesn't support row locks.

tip

Need to make sure only one process runs a piece of code at a time, even when there are no rows to lock yet? Use the Database.lock() method.

Debugging

You may use the dump() method while building a query to dump the current query bindings and SQL. The dump() method will display the debug information and continue executing the code:

const users = await Database.table('users')
  .whereNull('deletedAt')
  .dump() // <- Will log in the terminal your query until this point
  .oldest('deletedAt')
  .dump() // <- Will log in the terminal your query until this point
  .findMany()

Introduction​

Running database queries​

Retrieve all rows from a table​

Retrieve a single row​

Retrieve values of a single column​

Validate if exists​

Get the client and query builder of a driver​

Agreggates​

Select statements​

Raw expressions​

Raw methods​

selectRaw()​

whereRaw() / orWhereRaw()​

havingRaw() / orHavingRaw()​

orderByRaw()​

groupByRaw()​

Joins​

Inner join clause​

Other join clauses​

Advanced join clauses​

Basic where clauses​

Where clauses​

Or where clauses​

Where not clauses​

Additional where clauses​

whereLike() / orWhereLike()​

whereILike() / orWhereILike()​

whereBetween() / orWhereBetween()​

whereNotBetween() / orWhereNotBetween()​

whereIn() / orWhereIn()​

whereNotIn() / orWhereNotIn()​

whereNull() / orWhereNull()​

whereNotNull() / orWhereNotNull()​

Logical grouping​

Advanced where clauses​

Where exists clauses​

JSON where clauses​

Filtering missing JSON keys​

Searching​

Full text search​

Ordering, grouping, limit & skip​

Ordering​

The orderBy() method​

The latest() & oldest() methods​

Grouping​

The groupBy() & having() methods​

Limit & Offset​

Conditional clauses​

Pagination​

Insert statements​

Create or update (Upsert)​

Create or ignore​

Create or first​

Update statements​

Incrementing & decrementing​

Updating JSON columns​

Merging objects​

Incrementing & decrementing numbers​

Combining with other columns​

Delete statements​

Pessimistic locking​

Not waiting for locked rows​

Debugging​

Introduction

Running database queries

Retrieve all rows from a table

Retrieve a single row

Retrieve values of a single column

Validate if exists

Get the client and query builder of a driver

Agreggates

Select statements

Raw expressions

Raw methods

`selectRaw()`

`whereRaw() / orWhereRaw()`

`havingRaw() / orHavingRaw()`

`orderByRaw()`

`groupByRaw()`

Joins

Inner join clause

Other join clauses

Advanced join clauses

Basic where clauses

Where clauses

Or where clauses

Where not clauses

Additional where clauses

`whereLike() / orWhereLike()`

`whereILike() / orWhereILike()`

`whereBetween() / orWhereBetween()`

`whereNotBetween() / orWhereNotBetween()`

`whereIn() / orWhereIn()`

`whereNotIn() / orWhereNotIn()`

`whereNull() / orWhereNull()`

`whereNotNull() / orWhereNotNull()`

Logical grouping

Advanced where clauses

Where exists clauses

JSON where clauses

Filtering missing JSON keys

Searching

Full text search

Ordering, grouping, limit & skip

Ordering

The `orderBy()` method

The `latest()` & `oldest()` methods

Grouping

The `groupBy()` & `having()` methods

Limit & Offset

Conditional clauses

Pagination

Insert statements

Create or update (Upsert)

Create or ignore

Create or first

Update statements

Incrementing & decrementing

Updating JSON columns

Merging objects

Incrementing & decrementing numbers

Combining with other columns

Delete statements

Pessimistic locking

Not waiting for locked rows

Debugging