Query Builder

The Database query builder is the first step towards constructing and executing SQL queries. Instead of writing SQL DSL by hand, you make use of the Javascript API for constructing queries.

By the end of this guide, you will know:

• How to construct and execute SQL queries
• Using different types of query builders for executing insert, select or raw queries

Using the Query Builder

You can get an instance of the query builder using the Database module. For example:

import Database from '@ioc:Adonis/Lucid/Database'

const users = await Database.query().select('*').from('users')

• The Database.query method creates a new query builder instance.
• The select method is used to select the columns.
• Finally the from method specifies the database table for the query.
• The result of the query is always an array of objects, unless the .first method is used.

Just like the select and the from methods, there are many more methods on the query builder to construct advanced and complex SQL queries.

Types of Query Builders

The Database.query method creates a query builder instance for selecting, updating or deleting rows. Whereas, to insert new data, you have to make use of the insert query builder.

Following is the list different query builders instances.

• Query builder for selecting, updating or deleting rows.

  Database.query().from('users')
  
  // Shortcut
  Database.from('users')
  

• Query builder for inserting new rows.

  Database.insertQuery().table('users')
  
  // Shortcut
  Database.table('users')
  

• Raw query builder for executing raw SQL queries. You will learn about raw queries later in this guide.

  Database.rawQuery('select * from users;')
  

Fetching Rows

Fetching rows is as simple as executing a select query using the Database query builder. The result of a query is always an array of objects, even when a single row is returned from the database.

const users = await Database.query().select('*').from('users')
// an array of users

If you always want a single row from the result set, then you can make use of the first method.

const user = await Database.query().select('*').from('users').first()

Inserting Rows

You make use of the insert query builder for inserting new rows to the database. For example:

await Database
  .insertQuery() // 👈 gives an instance of insert query builder
  .table('users')
  .insert({ username: 'virk', email: '[email protected]' })

The return value of the insert query is dependent on the database server in use.

• MySQL and SQLite will return the last inserted row id as an array with just one item. For example:

  const [ lastInsertId ] = await Database.table('users').insert({})
  

• For PostgreSQL, MSSQL and Oracle, you can make use of the returning method. The returning method can return value for a single column or multiple columns. For example:

  const [ id ] = await Database
    .table('users')
    .returning('id') 👈
    .insert({})
  
  // multiple columns
  const [{ username, id }] = await Database
    .table('users')
    .returning(['id', 'username']) 👈
    .insert({})
  

Multi Insert

You can make use of the multiInsert method in order to insert multiple rows in a single insert query.

await Database.table('users').multiInsert([
  { username: 'virk' },
  { username: 'romain' },
])

Updating and Deleting Rows

You can update and delete rows by using the standard query builder. For example:

await Database
  .from('users')
  .where('username', 'virk')
  .update({ account_status: 'verified' })

Or delete

await Database
  .from('posts')
  .where('slug', 'dummy-post')
  .delete()

Executing Raw Queries

Raw queries allows to execute a SQL statement from a string input. This is usually helpful, when you want to execute complex queries that are not supported by the standard query builder.

import Database from '@ioc:Adonis/Lucid/Database'

const user = await Database
  .rawQuery('select * from users where id = ?', [1])

• The rawQuery method accepts a total of two arguments.

• First argument is the SQL query.

• 2nd argument is an array of values to substitute the ? placeholders inside the SQL string. To prevent SQL injection, you must always define values as bindings and do not inline them inside the SQL string. For example:

  Prone to SQL injection

  Database.rawQuery('select * from users where id = 1')
  

  Safe from SQL injection

  Database.rawQuery('select * from users where id = ?', [1])
  

Aggregates

The aggregate methods like count, min, avg returns an array with the aggregate key and its value. For example:

import Database from '@ioc:Adonis/Lucid/Database'

const total = await Database.query().count('*').from('users')

// SQLITE: [{ "count(*)": 4 }]
// POSTGRESQL: [{ "count": "4" }]

As you can notice, the output of PostgreSQL and SQLite is different and hence not predictable. To encounter this behavior, it is recommended to always alias your aggregates.

await Database.query().count('* as total').from('users')

// SQLITE: [{ "total": 4 }]
// POSTGRESQL: [{ "total": "4" }]