Installation and usage

Lucid comes pre-configured with the web and the api starter kits. However, you can install and manually configure it as follows inside an AdonisJS project.

Install the package from the npm packages registry using one of the following commands.

npm i @adonisjs/lucid

yarn add @adonisjs/lucid

pnpm add @adonisjs/lucid

Once done, you must run the following command to configure Lucid. You can optionally specify the database dialect you want to use using the --db flag. Following is the list of valid options.

sqlite
postgres
mysql
mssql

node ace configure @adonisjs/lucid

# Configure with MYSQL
node ace configure @adonisjs/lucid --db=mysql

Registers the following service provider inside the adonisrc.ts file.

{
  providers: [
    // ...other providers
    () => import('@adonisjs/lucid/database_provider'),
  ]
}

{
  commands: [
    // ...other commands
    () => import('@adonisjs/lucid/commands'),
  ]
}

Create the config/database.ts file.
Define the environment variables and their validations for the selected dialect.
Install required peer dependencies.

Configuration

The configuration for Lucid is stored inside the config/database.ts file.

Configuring read-write replicas

Lucid supports read-write replicas as a first-class citizen. You may configure one write database server, along with multiple read servers. All read queries are sent to the read servers in round-robin fashion, and write queries are sent to the write server.

Lucid does not perform any data replication for you. Therefore, you still have to rely on your database server for that.

In the following example, we define one write server and two read replicas. Since, Lucid will merge the properties from the connection object with every node of read-write connection objects, you do not have to repeat username and password properties.

const dbConfig = defineConfig({
  connection: 'postgres',
  connections: {
    postgres: {
      client: 'pg',
      connection: {
        host: env.get('DB_HOST'),
        port: env.get('DB_PORT'),
        user: env.get('DB_USER'),
        password: env.get('DB_PASSWORD'),
        database: env.get('DB_DATABASE'),
      },
      replicas: {
        read: {
          connection: [
            {
              host: '192.168.1.1',
            },
            {
              host: '192.168.1.2',
            },
          ],
        },
        write: {
          connection: {
            host: '196.168.1.3',
          },
        },
      },
      migrations: {
        naturalSort: true,
        paths: ['database/migrations'],
      },
    },
  },
})

Basic usage

Once you have configured Lucid, you can start using the Database query builder to create and execute SQL queries. In the following code examples, we perform CRUD operations on the posts table.

Select query with pagination

import db from '@adonisjs/lucid/services/db'
import { HttpContext } from '@adonisjs/core/http'

export default class PostsController {
  async index({ request }: HttpContext) {
    const page = request.input('page', 1)
    const limit = 20

    const posts = await db
      .query()
      .from('posts')
      .select('*')
      .orderBy('id', 'desc')
      .paginate(page, limit)

    return posts
  }
}

Insert query

import db from '@adonisjs/lucid/services/db'
import { HttpContext } from '@adonisjs/core/http'

export default class PostsController {
  async store({ request }: HttpContext) {
    const title = request.input('title')
    const description = request.input('description')

    const id = await db
      .insertQuery()
      .table('posts')
      .insert({
        title,
        description,
      })
      .returning('id')
  }
}

Update row by id

import db from '@adonisjs/lucid/services/db'
import { HttpContext } from '@adonisjs/core/http'

export default class PostsController {
  async update({ request, params }: HttpContext) {
    const id = params.id
    const title = request.input('title')
    const description = request.input('description')

    const updateRowsCount = await db
      .query()
      .from('posts')
      .where('id', id)
      .update({
        title,
        description,
      })
  }
}

Delete row by id

import db from '@adonisjs/lucid/services/db'
import { HttpContext } from '@adonisjs/core/http'

export default class PostsController {
  async delete({ request, params }: HttpContext) {
    const id = params.id

    const updateRowsCount = await db
      .query()
      .from('posts')
      .where('id', id)
      .delete()
  }
}

Switching between connections

Since, you can define multiple connections within the config/database.ts file. You may switch between them at runtime using the db.connection method. It accepts the connection name (as defined inside the config file) as a parameter and return an instance of QueryClient class for the mentioned connection.

import db from '@adonisjs/lucid/services/db'

/**
 * Get query client for "pg" connection
 */
const pg = db.connection('pg')

/**
 * Execute query
 */
await pg.query().select('*').from('posts')

Closing connections

You may close open connections using the db.manager.close method. The method accepts the connection name (as defined inside the config file) as a parameter and calls the disconnection method on the underlying connection class.

It is recommend to not close connections, unless you know that you will not use making more queries using the given connection.