Introducing AdonisJS - Database setup

Let's talk about data. In this post, we will set up Lucid (AdonisJS SQL ORM) and make use of the migrations to create the necessary database tables.

Prerequisite

This article assumes that you already have some SQL database server setup on your computer and you know how to create a database manually.

If this is not the case, then I recommend using the sqlite database during the setup process. SQLite is a file-based database and doesn't anything else other than the sqlite3 npm package.

Lucid setup

Like always, the first step is to install the package from npm by running the following command.

npm i @adonisjs/lucid@alpha

Once done, run the following command to set up the package.

node ace invoke @adonisjs/lucid

The ace invoke command executes the instructions Javascript file exposed by the package.

• I decided to go with the PostgreSQL database. You can choose any of the available databases.
• Right after the setup command is completed, we need to copy the code for validating the environment variables to the env.ts file. Since environment variables are injected from the outside, AdonisJS recommends you validate them and ensure that your app is always running with the correct set of configuration values.
• The config/database.ts file holds all the configuration related to the database.
• Finally, the config file relies on the environment variables and you can update them inside the .env file. My values are

  PG_HOST=localhost
  PG_PORT=5432
  PG_USER=virk
  PG_PASSWORD=
  PG_DB_NAME=todo

Database Overview

Before we start writing any code, let me give you a brief overview of the AdonisJS data layer. We ship with an in-house ORM (Lucid) to work with the SQL databases.

Along with the support for all major SQL databases, the following are some of the hand-picked features of Lucid.

• Active record ORM: The data models of Lucid follows the Active record pattern to simplify the database interactions. All the base level features including CRUD operations, relationships, serializing models to JSON are covered.

• Database query builder: The database query builder is built on top of knex and allows you to construct SQL queries using the Javascript methods.
  The database query builder is one level down from the data models, meaning the queries you write are sent as it is to the database driver without any modifications. This is usually helpful when you are writing complex queries that model high-level API cannot construct for you.

• Schema migrations: Migrations allow you to create and evolve your database schema using code. It also comes with an inbuilt tracking system, so that every migration file is executed only once.

• Model factories: Factories allows you to create/persist data models using fake data. They are usually helpful when writing tests or when seeding a database with some initial set of records.

• Seeders: Database seeders can be used with or without factories to seed the database. For example, I will use factories to seed random data during development and may have a couple of seeders with real data like list of countries that should be in the database before deploying the app.

Schema migrations

As covered in the overview section the schema migrations allow us to create the database schema using code. So let's create the tables we need for our todo app.

Creating the categories table

node ace make:migration categories

# CREATE: database/migrations/1606195827305_categories.ts

Open the newly created file and paste the following code snippet inside it.

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class Categories extends BaseSchema {
  protected tableName = 'categories'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').notNullable().primary()
      table.string('name', 200).notNullable()
      table.string('slug', 200).notNullable().unique()
      table.string('color_code', 6).notNullable()
      table.timestamps()
    })
  }

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

• The migration file has two methods. The up method is used to perform an action that is executed during the node ace migration:run command.
• The down method should undo the action taken inside the up method. If the up method creates a table, then the down method should drop the table.
• In the above migration file we are creating the categories table with a total of four columns
  • The auto-increment id
  • The category name
  • The category slug. It is the URL friendly version of the category name
  • And finally a color code. This is just to enhance the UI.

Let's run this migration by executing the following ace command. Also, make sure that you have manually created the database first.

node ace migration:run

# migrated database/migrations/1606195827305_categories
# Migrated in 173 ms

If you re-run the same command, you will get the following message.

node ace migration:run

# Already up to date

This is because Lucid tracks the migration files and do not re-run them. However, during development you can rollback your changes using the following ace command.

node ace migration:rollback

Creating the todos table

Let's repeat the same process for creating the todos table.

node ace make:migration todos

# CREATE: database/migrations/1606197725778_todos.ts

Open the newly created file and paste the following code inside it.

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class Todos extends BaseSchema {
  protected tableName = 'todos'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').notNullable().primary()
      table
        .integer('category_id')
        .unsigned()
        .notNullable()
        .references('id')
        .inTable('categories')

      table.text('title').notNullable()
      table.boolean('is_completed').notNullable().defaultTo(false)
      table.timestamps()
    })
  }

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

In the todos migration file, we define a foreign key constraint on the category_id at the database level.

Finally, run the following command to create the todos table.

node ace migration:run

# migrated database/migrations/1606197725778_todos
# Migrated in 159 ms

Data models

Data models are ES6 classes, where each class is responsible for interacting with a single database table. For our application, we will need two data models - one for the todos table and another one for the categories table.

Also, I want to mention - Models and migrations are not inter-connected, they are two separate tools that performs different tasks. You can have data models without migrations and vice-versa.

Creating the Category model

Run the following command to create a model for the categories table.

node ace make:model Category

# CREATE: app/Models/Category.ts

Open the newly created file and paste the following code inside it.

import { DateTime } from 'luxon'
import { BaseModel, column } from '@ioc:Adonis/Lucid/Orm'

export default class Category extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public name: string

  @column()
  public slug: string

  @column()
  public colorCode: string

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime
}

• The first thing is you have to define all the table columns on your models and apply the @column decorator to them. This helps Lucid distinguish between the standard model properties and the table columns.
• The data types next to the column name are the Typescript data types. We are not dealing with database types like varchar here. For Typescript, there is nothing called varchar.
• Finally, we allow you to use camelCase property names inside your models and during the SQL queries, we will convert them to snake_case. For example: The colorCode will become color_code.

Creating the todos model

Let's create the model for the todos table and set up the relationship between the Todo and the Category models.

node ace make:model Todo

# CREATE: app/Models/Todo.ts

import { DateTime } from 'luxon'
import { BaseModel, column } from '@ioc:Adonis/Lucid/Orm'

export default class Todo extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public categoryId: number

  @column()
  public title: string

  @column()
  public isCompleted: boolean

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime
}

Setting up the relationship

Following are the relationships between the todos and the categories.

• Categories has many todos.
• A todo belongs to a category.

You don't have to define the relationship on both ends. Simply define the one's that you will be using inside your app. For example, We will be using the Category model to fetch the related todos, and therefore we will only setup the hasMany relationship.

Open the app/Models/Category.ts file and add the following import statements.

import { BaseModel, column, hasMany, HasMany } from '@ioc:Adonis/Lucid/Orm'
import Todo from 'App/Models/Todo'

Next, define the relationship as a property on the class.

export default class Category extends BaseModel {
  // ....

  @hasMany(() => Todo)
  public todos: HasMany<typeof Todo>
}

Demo time

With everything in place. Let's fire up the AdonisJS repl and run a few queries using our models.

node ace repl

To avoid too much typing, you can copy/paste the following code snippets inside the REPL session.

• Load models by running the following method.
  

  loadModels()
  

• Create personal category
  

  const personal = await models.Category.create({ name: 'Personal', slug: 'personal', colorCode: '4fd1c5' })
  

• Create work category
  

  const work = await models.Category.create({ name: 'Work', slug: 'work', colorCode: 'e53e3e' })
  

• Add todo inside the work category
  

  await work.related('todos').create({ title: 'Kick start "Introducing AdonisJS" articles series on dev.to' })
  

• Add todo inside the personal category
  

  await personal.related('todos').create({ title: 'Buy groceries when going back to home. Make sure to buy some chocolates too' })
  

You can open the database inside some GUI tool and verify that the rows are added to the respective database tables with correct foreign keys.