Database Models

Table of Contents

What is a Database Model?
Creating Database Model
What are Migrations?
Creating Migrations
SQLite Driver & Running Migrations

So far we have covered the basics of setting up a new AdonisJs application by creating Routes, Controllers and rendering Views. In this tutorial, we will take a step further by learning about Database Models.

AdonisJs supports a majority of SQL databases. Here is a complete guide on Database Setup. We will start by creating migrations and Data models which are also known as Lucid models.

What is a Database Model?

Each Database model represents a single SQL table inside your database. For example:

Model	Database Table
User	users
Post	posts
Category	categories

Model

Database Table

User

users

Post

posts

Creating Database Model

Models live inside a dedicated directory called app/Model. Let’s make use of ace to create the Post model.

./ace make:model Post

Output

create: app/Model/Post.js

app/Model/Post.js

'use strict'

const Lucid = use('Lucid')

class Post extends Lucid {
}

module.exports = Post

Each model is a dedicated ES2015 Class just like our Controllers, but each model will extend Lucid to make it different from an ordinary class.

You never have to touch your models in most cases. So we will leave this file as it is and switch to another task.

What are Migrations?

Our next step is to choose a database we want to work with and create the necessary database tables. For the sake of simplicity, we will stick to SQLite. However, you are free to use MYSQL or PostgreSQL.

Migration is a process of creating database tables using the Javascript code. Migrations have lots of benefits as compared to the standard workflow of creating database tables using SequelPro, Workbench, etc. Check out the Migrations guide to learn more about them.

Creating Migrations

As always ace (our buddy) is going to create a migration for us.

./ace make:migration posts --create=posts

Output

create: database/migrations/1464075245386_posts.js

Quickly open this file and explore the options to create database table without touching the SQL interface.

database/migrations/1464075245386_posts.js

'use strict'

const Schema = use('Schema')

class PostSchema extends Schema {

  up () {
    this.create('posts', (table) => {
      table.increments()
      table.string('title')
      table.text('content')
      table.timestamps()
    })
  }

  down () {
    this.drop('posts')
  }

}

module.exports = PostSchema

Wow! It is so simple to define a database table within your standard Js files. Just keep a note of the following points when working with migrations

The up method is used to create a table, or add new fields to the existing table, etc.
The down is always opposite of up. It is used when you want to undo the changes you just made, also known as rollback.

SQLite Driver & Running Migrations

Finally, we need to run this migration to execute SQL query and create the database table called posts. Before that, we need to make sure that SQLite is setup properly.

Installing SQLite3 driver

npm i --save sqlite3

Running Migrations

./ace migration:run

Output

✔ Database migrated successfully.

We covered a lot in this tutorial, especially if you are new to these concepts. That’s the thing about frameworks you need to spend few days to get a solid grasp of available tools and options to make your development cycle a lot easier in future.