Migrations

• Preparing Table for Migration
• Creating Migrations
• Migration Structure
• Migrating
• Rolling Migrations Back
• Refreshing Database
• Tables
  • Creating Tables
  • Customizing Tables
  • Dropping Tables
  • Modifying Table
  • Executing Queries
• Customizing Columns
  • Column Binding Conventions

Migrations are like version control for your database. There are many advantages of using migrations — being able to easily create a database with ease, being able to share the same database schema with a team, being able to recreate a production database that is exactly like the development one as far as the schema is concerned, being able to quickly iterate on a database design, etc.

With Alpas's built-in migration support, you can take advantages of all the above benefits of migrations without breaking a sweat.

Preparing Table for Migration

Your Ozone table must extend from OzoneTable instead of just Table to support migration. Once it extends from OzoneTable, you can further customize the table's columns, such as marking it nullable, setting the size of a varchar column, etc.

Creating Migrations

You can create a migration by using the make:migration Alpas command. It takes the name of the migration, and the Ozone table instance to create.

# Create a migration for creating a table
$ alpas make:migration create_receipts_table --create=receipts

Migration Structure

Migrations work by tracking your migration files under database/migrations folders in a database table called migrations. Migrations files are named after the time when these files are created. This helps keep track of them easily as well as to sort them chronologically.

When a migration is ran, it checks if any of the migration files are already migrated or not. All the outstanding migrations are then ran in one batch, which is numbered. When performing the rollback, all the migrations from the latest batch are picked and performed rollback.

A migration file contains two methods — up() and down(). The up() method is conventionally used to add new tables or to modify an existing table, while the down() method, again conventionally, is used to "undo" the operations of the up() method. During the actual migration, Alpas calls the up() method and during the rollback it invokes the down() method.

class CreateReceiptsTable : Migration() {
    override fun up() {
        createTable(Receipts)
    }

    override fun down() {
        dropTable(Receipts)
    }
}

Migrating

Running migration is as easy as running the db:migrate Alpas command. This will migrate all of your outstanding migrations in one batch.

$ alpas db:migrate

Rolling Migrations Back

To rollback the latest migration batch, which may include multiple migrations, you can run db:rollback.

$ alpas db:rollback

Refreshing Database

During development, as you are iterating on your database schema and playing with some test data, you might want to undo all your database changes and start the database state from the scratch, basically re-creating your database. You can achieve this by rolling back few times and running migration or use the handydb:refresh command, which will drop all your tables and then execute the migrate command for you.

$ alpas db:refresh

You can also pass a --seed flag to automatically run the default seeder after the refreshing is completed.

Tables

Creating Tables

You can create a new table by calling the createTable() method and passing the object instance of an Ozone Table.

class CreateOrdersTables : Migration() {
    override fun up() {
        createTable(Receipts)
        createTable(Orders)
        createTable(Products)
    }
}

/info/Creating a table from a migration is only supported for SQLite, MySQL, and PostgresSQL at this time. For other vendors, you can run a raw query to perform the migration.

Customizing Tables

createTable() takes a lambda to let you further customize your table. This is usually helpful to, for an example, add some indices to your table.

class CreateReceiptsTable : Migration() {
    override fun up() {
        createTable(Receipts) {
            // add an index to email column
            addIndex("email")
        }
    }
}

Dropping Tables

To drop an Ozone table, call dropTable() method, passing object instance of an Ozone Table that you want to drop.

class CreateOrdersTables : Migration() {
    override fun down() {
        dropTable(Products)
        dropTable(Orders)
        dropTable(Receipts)
    }
}

Modifying Table

Ozone supports some basic table modification operations with addColumn() and dropColumn() methods.

class AddAvatarColumnsToUsersTable : Migration() {
    override fun up() {
        modifyTable(Users) {
            addColumn(Users.avatarUrl).after(Users.name)
            addColumn(Users.avatarProvider).after(Users.avatarUrl)
        }
    }

    override fun down() {
        modifyTable(Apps) {
            dropColumn("avatar_url", "avatar_provider")
        }
    }
}

You can create a modifying migration by using the make:migration --modify=<table> Alpas command.

Executing Queries

For advanced use cases and for features that are unavailable for certain database vendors, such as createTable(), you can migrate your database by passing a raw query to execute() method. Even if you have run a raw query, you'd still get the benefits of migrating your database up and down as this operation is tracked in the migrations table as well.

For an example, given that you have executed the proper query in the down() method, it will be applied when calling the alpas db:rollback command.

class CreateUsersTable : Migration() {
    override fun up() {
        val createQuery = """
            CREATE TABLE IF NOT EXISTS users (
              username varchar(45) NOT NULL,
              password varchar(450) NOT NULL,
              enabled integer NOT NULL DEFAULT '1',
              PRIMARY KEY (id)
            )
        """.trimIndent()

        execute(createQuery)
    }

    override fun down() {
        val dropQuery = "DROP TABLE users"
        execute(dropQuery)
    }
}

Customizing Columns

You can further customize a column by chaining a number of convenience methods on a column. These add extra attributes to a column metadata, which are used during migration.

Here is an example of columns customization of a fictional Users table.

object Users : OzoneTable<User>("users") {
    val id by bigIncrements("id")
    val pin by smallInt("pin").unsigned()
    val email by varchar("email").index().unique()
    val password by varchar("password").size(100)
    val name by varchar("name").index().nullable()
    val emailIsVerified by boolean("email_verified").default(false)
    val teamId by long("team_id").unsigned().reference { onDeleteCascade() }
    val createdAt by timestamp("created_at").useCurrent()
}

Column Binding Conventions

There are some columns that show up regularly in a database, especially with a web application — auto incrementing primary key, a created_at timestamp field, and an updated_at timestamp field. Ozone has a shortcut for declaring all of these fields within a table.

object Users: OzoneTable<User>() {

// Instead of declaring a primary this way...
val id by bigInt("id").autoIncrement().unsigned().primaryKey().bindTo{ it.id }
val id by int("id").autoIncrement().unsigned().primaryKey().bindTo{ it.id }

// ...you can declare it like this
val id by bigIncrements()
val id by increments()

// You can override the name of the column and the 
// consequently the name of the field it binds to
val id by bigIncrements("uid")

// Instead of declaring created_at and updated_at fields this way...
val createdAt by timestamp("created_at").nullable().useCurrent().bindTo { it.createdAt }
val updatedAt by timestamp("updated_at").nullable().useCurrent().bindTo { it.updatedAt }

// ...you can declare them like this:
val createdAt by createdAt()
val updatedAt by updatedAt()

// You can customize some of its properties as well
val createdAt by createdAt(name="created_date", nullable=false, useCurrent=false)

// ...

}

/info/Alpas currently doesn't support modifying existing columns of a table.