Now that we have at least a basic understanding of routing and how to use controllers within Adonis, we can now begin to look at database integration. So, in this lesson, we'll be going over how to install Lucid, Adonis' Object-Relational Mapping (ORM) system. We'll then end this lesson by getting our project setup with a MySQL database.

Supported Databases

Although I'll be using MySQL in this series you can use a number of other SQL-based databases with Adonis with very little differences in actual usage. At this time, the below are the databases Adonis supports.

• MySQL

• SQLite

• MSSQL

• PostgreSQL

• MariaDB

• OracleDB

If you don't have a database installed on your machine and that's not something you're prepared to do (we won't be covering how to set this up in this lesson), you can use a Database as a Service like Amazon RDS, DigitalOcean's Managed Databases, or ElephantSQL to create a database to work with.

Installing Lucid

To start, let's get Lucid installed and configured within our project. So, let's install Lucid by entering our terminal and running the below. Note that we're installing the alpha version because Adonis 5 is still in preview.

$ npm i @adonisjs/lucid@alpha

Now that we have Lucid installed within our project we can proceed by configuring it within our project. Adonis makes this nice and easy by providing the invoke command via the Ace CLI. This command will register Lucid within our project, give us a database configuration, and a few additional Ace commands. So, in our terminal we can run:

$ node ace invoke @adonisjs/lucid

We'll then be asked which database driver we want to use. Again, if you have a preference here feel free to go with your preference. I'll be using MySQL.

> Select the database driver you want to use
⚪ SQLite
🔵 MySQL / MariaDB
⚪ PostgreSQL
⚪ OracleDB
⚪ Microsoft SQL Server

Adonis will then use our selection here to define our newly generated database config at config/database.ts, populate default environment variables in our .env file for connecting our database, and install the dependencies needed for our selected database driver.

Next, Adonis will ask how we want to view instructions. In the browser will open the instructions up in the browser, and in the terminal will open them in our terminal. These instructions will provide us with type definitions for our new environment variables.

> Select where to display instructions
⚪ In the browser
🔵 In the terminal

# Open the env.ts file and paste the following code inside the Env.rules object
#   MYSQL_HOST: Env.schema.string({ format: 'host' }),
#   MYSQL_PORT: Env.schema.number(),
#   MYSQL_USER: Env.schema.string(),
#   MYSQL_PASSWORD: Env.schema.string.optional(),
#   MYSQL_DB_NAME: Env.schema.string(),

As the instructions state, all we need to do is copy the MYSQL (or whatever driver you selected) rules and paste them inside our Env.rules object inside our env.ts file.

// env.ts
import Env from '@ioc:Adonis/Core/Env'

export default Env.rules({
  // ... pre-existing definitions
  
  MYSQL_HOST: Env.schema.string({ format: 'host' }),
  MYSQL_PORT: Env.schema.number(),
  MYSQL_USER: Env.schema.string(),
  MYSQL_PASSWORD: Env.schema.string.optional(),
  MYSQL_DB_NAME: Env.schema.string(),
})

So, we now have Lucid installed and configured within our project. Next, we need a database to work with.

Creating Our Database

Let's go ahead and create the MySQL database we'll be working with for the remainder of this series. If you're using another database or a Database as a Service, feel free to jump to the next section "Connecting Our Database".

To start, let's head back into our terminal and jump into MySQL

$ mysql -u root -p
# Enter password:

mysql>

Next, let's create our database. I'll be calling mine adonis5.

mysql> CREATE DATABASE adonis5;
# Query OK, 1 row affected

That's all we need to do here so we can go ahead and exit MySQL. If you're jumping way ahead and working in production here, be sure that you go through the extra security precautions.

mysql> exit;
# Bye

Connecting Our Database

All that remains for us to do now is connect our newly created database and our Adonis project. So, let's jump into our .env file and populate the environment variables that were created when we invoked Lucid.

MYSQL_USER=your_user_here
MYSQL_PASSWORD=your_user_password_here
MYSQL_DB_NAME=your_database_name_here

# Here's my values for these as an example, yours will differ
MYSQL_USER=root
MYSQL_PASSWORD=\$Root123
MYSQL_DB_NAME=adonis5

If you're using a Database as a Service, you'll need to also change MYSQL_HOST and MYSQL_PORT to match what your database service provided you.

If you're using another database driver besides MySQL, your variable names will differ from mine.

New Ace Commands

When we invoked Lucid, Adonis added a couple of new Ace commands for us.

make

• make:migration - Make a new migration file

• make:model - Make a new Lucid model

• make:seeder - Make a new Seeder file

migration

• migration:rollback - Rollback migrations to a given batch number

• migration:run - Run pending migrations

• migration:status - Check migrations current status

Migrations

Migrations are how we're going to go about defining the tables and structures of those tables within our database. So, to create a new table we'd create a migration for that table. Then inside that migration define the table's columns, column types, rules, default values, etc.

Additionally, we'll also use migrations to alter existing tables. So, if we need to add, remove, or change a column in any way we'd create a new migration to alter an existing table.

The migration:run command will run any migrations that haven't already been run. Adonis will track which migrations have run inside an adonis_schema table in our database. Each time we run a set of migrations Adonis will increment a batch number.

The migration:rollback command will allow us to undo x-defined migration:run calls where x is the batch number. If we just call migration:rollback Adonis will rollback 1 batch, essentially undoing whatever the last migration:run altered.

This will click a lot more in the next lesson when we actually get to see these commands in action.

Models

Models are the Lucid definitions for our database tables. They define to Lucid our columns, their TypeScript types, their relationships, hooks, and a number of other things.

We'll get to see the full power of Lucid Models soon!

Verifying Our Database Connection

If, before moving onto the next lesson, you'd like to verify that your database connection is working properly; Adonis has you covered. There's a nifty "Health Check" module inside Adonis we can use to verify Lucid's connection is working properly.

You can paste the below into your start/routes.ts file.

// start/routes.ts
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)
})

Then, visit your application's new /health page to view Lucid's connection health. If all is well you should see something like the below.

Next Up

With Lucid installed and configured, we can begin creating models, migrations, and persisting some data. In the next lesson, we'll be doing a deep-dive on migrations. Then in the lesson after, we'll do the same for Models.