Setup

In this guide, you will install @adonisjs/lucid package, along with the database driver of your choice. Lucid supports the following database engines.

• MySQL
• SQLite
• MSSQL
• PostgreSQL (along with Amazon Redshift)
• MariaDB
• and OracleDB

Install the package from the npm registry by running the following command.

npm i @adonisjs/[email protected]

yarn add @adonisjs/[email protected]

Next, you must setup Lucid by running the node ace invoke command. The following command will create the default config file and also register @adonisjs/lucid under the providers array.

node ace invoke @adonisjs/lucid

# ✔  create    config/database.ts
#    update    .env
#    update    tsconfig.json
#    update    .adonisrc.json
# ✔  create    ace-manifest.json

Congrats! You have configured Lucid. We will now continue with the setup for different database backends.

Configure SQLite

SQLite is a light weight file based database server. You can quickly get up and running with SQLite by just installing the database driver from npm.

npm i -D sqlite3

Next, open the config/database.ts file to review the configuration options.

{
  connection: Env.get('DB_CONNECTION', 'sqlite') as string,
  connections: {
    sqlite: {
      client: 'sqlite',
      connection: {
        filename: Application.tmpPath('db.sqlite3'),      },
      useNullAsDefault: true,
      healthCheck: false,
    },
  }
}

• Lucid relies on the DB_CONNECTION environment variable to decide the default database connection to use. So make sure to update this to sqlite inside the .env file.

  DB_CONNECTION=sqlite
  

• The database file lives inside the tmp path. So, do make sure to create the tmp directory inside the project root.

  mkdir tmp
  

Configure MySQL

The first step is to have MySQL server running on your computer. You can install MySQL for your operating system by following the official docs.

Once, the server is running, install the MySQL driver for Node.js from npm.

npm i mysql

Next, open the database config file to review the configuration options.

{
  connection: Env.get('DB_CONNECTION', 'mysql') as string,
  connections: {
    mysql: {
      client: 'mysql',
      connection: {
        host: Env.get('DB_HOST', '127.0.0.1') as string,        port: Number(Env.get('DB_PORT', 3306)),        user: Env.get('DB_USER', 'lucid') as string,        password: Env.get('DB_PASSWORD', 'lucid') as string,        database: Env.get('DB_NAME', 'lucid') as string,      },
      healthCheck: false,
    },
  }
}

• Lucid relies on the DB_CONNECTION environment variable to decide the default database connection to use. So make sure to update this to mysql inside the .env file.

• Also, the database connection values are read from the .env file. So make sure to update them as well.

  DB_CONNECTION=mysql
  DB_HOST=127.0.0.1
  DB_USER=root
  DB_PASSWORD=password
  DB_NAME=lucid
  

With MySQL, you can also connect using the unix domain socket. Setting the socketPath will ignore host and port.

connections: {
  mysql: {
    client: 'mysql',
    connection: {
      socketPath: '/path/to/socket.sock',      user: Env.get('DB_USER', 'lucid') as string,
      password: Env.get('DB_PASSWORD', 'lucid') as string,
      database: Env.get('DB_NAME', 'lucid') as string,
    },
    healthCheck: false,
  },
}

Configure PostgreSQL

Just like MySQL, the first step with PostgreSQL is also to install the database server. Make sure to follow the official guide on installation.

Once, the database server is running, install the PostgreSQL driver for Node.js from npm.

npm i pg

Next, open the database config file to review the configuration options.

{
  connection: Env.get('DB_CONNECTION', 'sqlite') as string,
  connections: {
    pg: {
      client: 'pg',
      connection: {
        host: Env.get('DB_HOST', '127.0.0.1') as string,        port: Number(Env.get('DB_PORT', 5432)),        user: Env.get('DB_USER', 'lucid') as string,        password: Env.get('DB_PASSWORD', 'lucid') as string,        database: Env.get('DB_NAME', 'lucid') as string,      },
      healthCheck: false,
    },
  }
}

Again, make sure to update the .env file with the correct DB_CONNECTION and other config values.

DB_CONNECTION=pg
DB_HOST=127.0.0.1
DB_USER=user
DB_PASSWORD=password
DB_NAME=lucid

With PostgreSQL, you can also pass the connection string instead of defining every connection property separately.

{
  connections: {
    pg: {
      client: 'pg',
      connection: 'postgres://someuser:[email protected]:5432/somedatabase',
      healthCheck: false,
    },    
  }
}

Other Database Servers

We haven't covered the setup for all possible databases. However, the process remains same for every other database server.

• Get the database server up and running.

• Install the driver from npm. Following is the list of drivers.

  npm install pg
  npm install sqlite3
  npm install mysql
  npm install mysql2
  npm install oracledb
  npm install mssql
  

• Finally, define a new connection inside the config/database.ts file

Verifying Connection

There are a couple of ways to verify the connection settings and ensure that you are able to connect to your database server successfully. One way is to use the health checks api and another one is to manually run a query inside a dummy route.

Health Checks

Lucid has inbuilt support for health checks and it works across all database engines. All you need to do is, enable the healthCheck flag on the database connection inside the config/database.ts file and then use the AdonisJS global Health check API, as shown in the following example.

import Route from '@ioc:Adonis/Core/Route'
import HealthCheck from '@ioc:Adonis/Core/HealthCheck'

Route.get('health', async ({ response }) => {
  const report = await HealthCheck.getReport()
  
  return report.healthy
    ? response.ok(report)
    : response.badRequest(report)
})

If you now visit http://localhost:3333/health, you must see the health check report for your database connection(s).

Manual Query

Another option is to perform a query inside a dummy route. For example:

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

Route.get('test', async () => {
  return Database.query().select('*').from('users')
})