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.

Getting Started

Creating AdonisJs data-driven apps is greatly simplified via its powerful Query Builder, Lucid ORM, Migrations, Factories, and Seeds.

In this guide we’ll learn to setup and use the Database Provider.

The Data Provider uses Knex.js internally, so browse the Knex documentation whenever further information is required.

Supported Databases

The list of supported databases and their equivalent drivers are as follows:

Database NPM Driver

MariaDB

npm i mysql or npm i mysql2

MSSQL

npm i mssql

MySQL

npm i mysql or npm i mysql2

Oracle

npm i oracledb

PostgreSQL

npm i pg

SQLite3

npm i sqlite3

Setup

Installation

If the Database Provider (Lucid) is not installed, pull it from npm:

> adonis install @adonisjs/lucid

Next, register the following providers inside the start/app.js file:

start/app.js
const providers = [
  '@adonisjs/lucid/providers/LucidProvider'
]

const aceProviders = [
  '@adonisjs/lucid/providers/MigrationsProvider'
]
Many AdonisJs boilerplates have Lucid installed by default.

Configuration

The Database Provider uses the sqlite connection by default.

The default connection can be set via the config/database.js file:

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

All of the Knex configuration options are supported as is.

Basic Example

The AdonisJs Query Builder has a fluent API, meaning you can chain/append JavaScript methods to create your SQL queries.

For example, to select and return all users as JSON:

const Database = use('Database')

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

Where Clause

To add a where clause to a query, chain a where method:

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

To add another where clause, chain an orWhere method:

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

See the Query Builder documentation for the complete API reference.

Multiple Connections

By default, AdonisJs uses the connection value defined inside the config/database.js file when making database queries.

You can select any of the connections defined inside the config/database.js file at runtime to make your queries:

Database
  .connection('mysql')
  .table('users')
Since AdonisJs pools connections for reuse, all used connections are maintained unless the process dies.

To close a connection, call the close method passing any connection names:

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

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

Table Prefixing

The Database Provider can automatically prefix table names by defining a prefix value inside the config/database.js file:

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

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

Now, all queries on the sqlite connection will have my_ as their table prefix:

await Database
  .table('users')
  .select('*')
SQL Output
select * from `my_users`

withOutPrefix

If a prefix value is defined you can ignore it by calling withOutPrefix:

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

Debugging

Debugging database queries can be handy in both development and production.

Let’s go through the available strategies to debug queries.

Globally

Setting debug: true inside the database/config.js file enables debugging for all queries globally:

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

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

You can also debug queries via the Database Provider query event.

Listen for the query event by defining a hook inside the start/hooks.js file:

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

hooks.after.providersBooted(() => {
  const Database = use('Database')
  Database.on('query', console.log)
})
Create the start/hooks.js file if it does not exist.

Locally

You can listen for the query event per query at runtime:

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