module.exports = {
connection: Env.get('DB_CONNECTION', 'sqlite'), (1)
sqlite: {
....
}, (2)
mysql: {
....
}
}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.
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.
PostgreSQL
SQLite
MySQL
MariaDB
Oracle
MSSQL
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.
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. |
Let’s start playing the Database provider to by selecting all users from the users table.
Route.get('/users', 'UsersController.index')'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. |
Since AdonisJs supports all popular SQL databases, you have to install their equivalent drivers from npm and configure them as defined below.
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: {
client: 'sqlite3',
connection: {
filename: Helpers.storagePath('development.sqlite')
}
}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: {
client: 'oracle',
connection: {
host: '127.0.0.1',
port: 1521,
user: '',
password: '',
database: 'adonis'
}
}maria: {
client: 'mariasql',
connection: {
host: '127.0.0.1',
port: 3306,
user: 'root',
password: '',
database: 'adonis'
}
}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.
The easiest way to globally debug queries is to set the debug flag on the configuration block.
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.
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.
+ 1.38 ms : select * from `users` where `username` = 'doe'{
method: 'select',
options: {},
bindings: [ 'doe' ],
sql: 'select * from `users` where `username` = ?'
}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')