Connecting a database

The blog's posts and comments need to be stored somewhere. In AdonisJS the database is handled by Lucid — the official ORM: it runs SQL queries, runs migrations and provides models. You set up the connection once.

Installing

If you chose a database when creating the project, Lucid is already installed. Otherwise add it:

node ace add @adonisjs/lucid

The command asks which database to use, installs the driver, creates config/database.ts and adds variables to .env and start/env.ts.

Database	npm driver	When to choose
SQLite	`better-sqlite3`	Learning and small projects: the database is one file
PostgreSQL	`pg`	Production projects
MySQL	`mysql2`	If the hosting offers only MySQL

SQLite

SQLite needs no separate server. The settings look like this:

// config/database.ts
import app from '@adonisjs/core/services/app'
import { defineConfig } from '@adonisjs/lucid'
 
const dbConfig = defineConfig({
  connection: 'sqlite',
  connections: {
    sqlite: {
      client: 'better-sqlite3',
      connection: {
        filename: app.tmpPath('db.sqlite3'),
      },
      useNullAsDefault: true,
      migrations: {
        naturalSort: true,
        paths: ['database/migrations'],
      },
    },
  },
})
 
export default dbConfig

connection selects the default connection. app.tmpPath('db.sqlite3') is the path to tmp/db.sqlite3 in the project: the file appears on the first database query.

PostgreSQL

For PostgreSQL the access details come from .env:

DB_HOST=127.0.0.1
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=secret
DB_DATABASE=blog

// config/database.ts
import env from '#start/env'
import { defineConfig } from '@adonisjs/lucid'
 
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'),
      },
      migrations: {
        naturalSort: true,
        paths: ['database/migrations'],
      },
    },
  },
})
 
export default dbConfig

The blog database itself must be created beforehand, for example with createdb blog or in pgAdmin.

Checking the connection

Add a temporary route that runs a simple SQL query:

// start/routes.ts
import router from '@adonisjs/core/services/router'
import db from '@adonisjs/lucid/services/db'
 
router.get('/db-check', async () => {
  await db.rawQuery('select 1')
  return 'Database connected'
})

Open /db-check. If you see "Database connected", everything is set up. If not, the terminal shows the reason: wrong password, the database does not exist, the server is not running.

Common mistake

After switching from SQLite to PostgreSQL people forget to change connection at the top of config/database.ts, and the application keeps writing to the SQLite file. The connection value must match one of the keys of the connections object.

Thanks for reading!