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.

Database Setup

AdonisJs has out of the box for SQL databases and offers a unified Javascript API to interact with databases. Below is the list of supported databases.

Supported Databases

  • PostgreSQL

  • SQLite

  • MySQL

  • MariaDB

  • Oracle

  • MSSQL

Configuration

Every AdonisJs application ships with the pre-configured support for Query builder and Lucid Orm all you have to do is tweak the configuration as per your needs.

By default configuration is set to make use of SQLite as the database under development, which can be changed using the config/database.js file.

config/database.js
module.exports = {
  connection: Env.get('DB_CONNECTION', 'sqlite'), (1)

  sqlite: {
    ....
  }, (2)

  mysql: {
    ....
  }
}
1 connection property defines the default connection to be used for making SQL queries.
2 Individual connection config is set next to the connection name. You are free to create multiple config blocks.

Basic Example

Let’s start playing the Database provider to by selecting all users from the users table.

Route
Route.get('/users', 'UsersController.index')
Controller
'use strict'

const Database = use('Database')

class UsersController {

  * index (request, response) {
    const users = yield Database.select('*').from('users')
    response.json(users)
  }

}
The query builder syntax is fluent which means you can chain methods to build a complete SQL query. Also, you will not have to change a line of code when you switch between multiple databases.

Database Drivers & Their Settings

Since AdonisJs supports all popular SQL databases, you have to install their equivalent drivers from npm and configure them as defined below.

MySQL

Available Driver(s)

mysql
mysql2

Configuration
mysql: {
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'root',
    password: '',
    database: 'adonis'
  }
}

Also, you can define a socket path to building the MySQL connection. Giving the socket path will ignore the host and port options.

mysql: {
  client: 'mysql',
  connection: {
    socketPath: '/path/to/socket.sock',
    user: 'root',
    password: '',
    database: 'adonis'
  }
}

SQLite

Available Driver(s)

sqlite3

Configuration
sqlite: {
  client: 'sqlite3',
  connection: {
    filename: Helpers.storagePath('development.sqlite')
  }
}

PostgreSQL

Available Driver(s)

pg

Configuration
pg: {
  client: 'pg',
  connection: {
    host: '127.0.0.1',
    port: 5432,
    user: '',
    password: '',
    database: 'adonis',
    ssl: false
  }
}

Also, you can pass a connection string

pg: {
  client: 'pg',
  connection: 'postgres://user:password@host:port/database?ssl=true'
}

Oracle

Available Driver(s)

oracle
strong-oracle

Configuration
oracle: {
  client: 'oracle',
  connection: {
    host: '127.0.0.1',
    port: 1521,
    user: '',
    password: '',
    database: 'adonis'
  }
}

MariaDB

Available Driver(s)

mariasql

Configuration
maria: {
  client: 'mariasql',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'root',
    password: '',
    database: 'adonis'
  }
}

MSSQL

Available Driver(s)

mssql

Configuration
mssql: {
  client: 'mssql',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'root',
    password: '',
    database: 'adonis'
  }
}

Debugging

Debugging database queries are handy to check database response time or to make sure that the query builder performs the correct query. Let’s go through a handful of different debugging strategies.

Globally

The easiest way to globally debug queries is to set the debug flag on the configuration block.

config/database.js
mysql: {
  client: 'mysql',
  connection: {},
  debug: true (1)
}

Also, you can turn debugging on within your code by listening for the query or sql events on the Database provider. The best place to register a listener is under app/Listeners/Http.js file.

app/Listeners/Http.js
Http.onStart = function () {
  Database.on('query', console.log)
  Database.on('sql', console.log)
}

The only difference between query and the sql event is the output.

SQL event output
+ 1.38 ms : select * from `users` where `username` = 'doe'
Query event output
{
  method: 'select',
  options: {},
  bindings: [ 'doe' ],
  sql: 'select * from `users` where `username` = ?'
}

Individual Query

Also, you can debug a single query by chaining the event listener when running the query.

yield Database
  .on('sql', console.log)
  .table('users')
  .where('username', 'doe')

Or

yield Database
  .debug()
  .table('users')
  .where('username', 'doe')