You are viewing the legacy version of AdonisJS. Visit https://adonisjs.com for newer docs. This version will receive security patches until the end of 2021.

Introduction

Creating data driven apps is AdonisJs is comparatively simple with the help of rich Database query builder, Lucid ORM, migrations and database seeder.

In this guide, we understand the basics of Database provider.

AdonisJs makes use of Knex.js internally, so make sure to read the knex docs whenever required.

Supported databases

Following is the list of supported database and their equivalent drivers.

Database Npm driver

PostgreSQL

npm i pg

MySQL

npm i mysql or
npm i mysql2

SQLite3

npm i sqlite3

MariaDB

npm i mariasql

Oracle

npm i oracledb or
npm i strong-oracle

MSSQL

npm i mssql

Setup

Certain project boilerplates have lucid pre-configured by default. If not, here are the setup instructions.

Installation

adonis install @adonisjs/lucid

Once the installation has been done, register the providers inside start/app.js file.

const providers = [
  '@adonisjs/lucid/providers/LucidProvider'
]

const aceProviders = [
  '@adonisjs/lucid/providers/MigrationsProvider'
]

Configuration

Lucid makes use of sqlite by default. However, you can change the configuration from config/database.js file and choose from one of the available databases.

All of the knex configuration options are supported as it is.

Basic Example

Database query builder has fluent API, which means you can chain Javascript methods to create SQL queries.

Let’s see an example of selecting all users and returning them as JSON.

const Database = use('Database')

Route.get('/', async () => {
  return await Database.table('users').select('*')
})

Where clause

Adding a where clause is as simple as calling a method.

Database
  .table('users')
  .where('age', '>', 18)

An orWhere clause

Database
  .table('users')
  .where('age', '>', 18)
  .orWhere('vip', true)

Checkout the query builder docs for complete API reference.

Multiple connections

By default, AdonisJs makes use of the connection defined inside config/database.js file for making all database queries.

However, you can set multiple database connections inside config/database.js file, and at runtime, you can select any of the defined connections to make the queries.

Database
  .connection('mysql')
  .table('users')

Since AdonisJs creates a pool of connections and reuses them, all used connections are maintained unless the process dies.

To close a connection manually, simply call the close method with connection name(s).

const users = await Database
  .connection('mysql')
  .table('users')

// later close the connection
Database.close(['mysql'])

Table prefixing

The database provider allows transparent table prefixing for all queries by defining a prefix inside the config file.

config/database.js
module.exports = {
  connection: 'sqlite',

  sqlite: {
    client: 'sqlite3',
    prefix: 'my_'
  }
}

Now all queries on the sqlite connection have my_ as the table prefix.

await Database
  .table('users')
  .select('*')

Output

select * from `my_users`

withOutPrefix

Optionally you can skip the prefixing by calling withOutPrefix.

await Database
  .withOutPrefix()
  .table('users')

Debugging

Debugging database queries can be handy in development as well as in production. Let’s go through the available strategies on debugging queries.

Globally

By setting debug: true inside the config file enables debugging for all queries globally.

config/database.js
module.exports = {
  connection: 'sqlite',

  sqlite: {
    client: 'sqlite3',
    connection: {},
    debug: true
  }
}

Also, you can turn on debugging by listening for the query event on Database provider. The best place to register a listener is under the start/hooks.js file.

const { hooks } = require('@adonisjs/ignitor')

hooks.after.providersBooted(() => {
  const Database = use('Database')
  Database.on('query', console.log)
})

Individually

Also, you can listen for query event on a single query at runtime.

await Database
  .table('users')
  .select('*')
  .on('query', console.log)