Migrations

• Introduction
• Configuration
• Create Migrations
  • Quickly Create
• Migration Structure
  • Go Language Migration
• Register Migrations
• Run Migrations
• Rolling Back Migrations
  • Roll Back & Migrate Using A Single Command
  • Drop All Tables & Migrate
• Tables
  • Create Table
  • Check If Table / Column Exists
  • Database Connection
  • Update Table
  • Rename Column
  • Add Table Comment
  • Rename / Drop Table
• Columns
  • Available Column Types
  • Column Modifiers
  • Drop Column
• Indexes
  • Create Index
  • Rename Index
  • Drop Index
  • Create Foreign Key
  • Drop Foreign Key

Introduction

When multiple people collaborate to develop applications, it's crucial to have a standardized database structure for synchronization. Without this, there could be chaos as everyone's individual data won't match up. Database migration is the solution to this problem. The database structure is version-controlled to ensure its consistency within all developers.

Configuration

The database migration files are stored in the database/migrations directory. You can configure the database connection information in the config/database.go file.

"migrations": map[string]any{
  // You can cumstomize the table name of migrations
  "table":  "migrations",
},

Create Migrations

Use the make:migration command to create the migration:

go run . artisan make:migration create_users_table

This command will generate migration files in the database/migrations directory. Each migration file will begin with a timestamp, which Goravel will use to determine the execution order of the migration files.

Quickly Create

Use create_users_table to automatically generate a table containing the infrastructure of users:

^create_(\w+)_table$
^create_(\w+)$

Use add_avatar_to_users_table to automatically generate a structure for adding fields to the users table:

_(to|from|in)_(\w+)_table$
_(to|from|in)_(\w+)$

If the above conditions are not matched, the framework will generate an empty migration file.

Migration Structure

Go Language Migration

The migration struct contains two methods: Up and Down. The Up method is used to add new tables, columns, or indexes to the database, while the Down method is used to undo the operations performed by the Up method. In these two methods, you can use facades.Schema() to create and operate database tables. For available methods, see the documentation. The following migration will create a users table:

package migrations

import (
	"github.com/goravel/framework/contracts/database/schema"
	"github.com/goravel/framework/facades"
)

type M20241207095921CreateUsersTable struct {
}

// Signature The unique signature for the migration.
func (r *M20241207095921CreateUsersTable) Signature() string {
	return "20241207095921_create_users_table"
}

// Up Run the migrations.
func (r *M20241207095921CreateUsersTable) Up() error {
	if !facades.Schema().HasTable("users") {
		return facades.Schema().Create("users", func(table schema.Blueprint) {
			table.ID()
			table.String("name").Nullable()
			table.String("email").Nullable()
			table.Timestamps()
		})
	}

	return nil
}

// Down Reverse the migrations.
func (r *M20241207095921CreateUsersTable) Down() error {
	return facades.Schema().DropIfExists("users")
}

Set Migration Connection

If the migration will interact with a database connection other than the application's default database connection, you should use the migration's Connection method:

func (r *M20241207095921CreateUsersTable) Connection() string {
  return "connection-name"
}

Register Migrations

You need to register the migration files in the database/kernel.go file after the migration files are generated, if the migration files are generated by the make:migration command, the framework will automatically register them.

// database/kernel.go
func (kernel Kernel) Migrations() []schema.Migration {
	return []schema.Migration{
		&migrations.M20241207095921CreateUsersTable{},
	}
}

Run Migrations

To run all of your outstanding migrations, execute the migrate Artisan command:

go run . artisan migrate

If you would like to see which migrations have run thus far, you may use the migrate:status Artisan command:

go run . artisan migrate:status

Rolling Back Migrations

To roll back the latest migration, use the rollback Artisan command. This command rolls back the last "batch" of migrations, which may include multiple migration files:

go run . artisan migrate:rollback

You may roll back a limited number of migrations by providing the step option to the rollback command. For example, the following command will roll back the last five migrations:

go run . artisan migrate:rollback --step=5

The migrate:reset command will roll back all of your application's migrations:

go run . artisan migrate:reset

Roll Back & Migrate Using A Single Command

The migrate:refresh command will roll back all of your migrations and then execute the migrate command. This command effectively re-creates your entire database:

go run . artisan migrate:refresh

You may roll back and re-migrate a limited number of migrations by providing the step option to the refresh command. For example, the following command will roll back and re-migrate the last five migrations:

go run . artisan migrate:refresh --step=5

Drop All Tables & Migrate

The migrate:fresh command will drop all tables from the database and then execute the migrate command:

go run . artisan migrate:fresh

Tables

Create Table

facades.Schema().Create("users", func(table schema.Blueprint) {
  table.ID()
  table.String("name").Nullable()
  table.String("email").Nullable()
  table.Timestamps()
})

Check If Table / Column Exists

if facades.Schema().HasTable("users") {}
if facades.Schema().HasColumn("users", "email") {}
if facades.Schema().HasColumns("users", []string{"name", "email"}) {}
if facades.Schema().HasIndex("users", "email_unique") {}

Database Connection

facades.Schema().Connection("sqlite").Create("users", func(table schema.Blueprint) {
  table.ID()
})

Update Table

facades.Schema().Table("users", func(table schema.Blueprint) {
  table.String("name").Nullable()
})

Rename Column

facades.Schema().Table("users", func(table schema.Blueprint) {
  table.RenameColumn("old_name", "new_name")
})

Add Table Comment

facades.Schema().Table("users", func(table schema.Blueprint) {
  table.Comment("user table")
})

Rename / Drop Table

facades.Schema().Rename("users", "new_users")
facades.Schema().Drop("users")
facades.Schema().DropIfExists("users")

Columns

Available Column Types

Boolean Types

Boolean

String & Text Types

Char, Json, LongText, MediumText, String, Text, LongText, TinyText, Uuid, Ulid

Numeric Types

BigIncrements, BigInteger, Decimal, Double, Float, ID, Increments, Integer, IntegerIncrements, MediumIncrements, MediumInteger, SmallIncrements, SmallInteger, TinyIncrements, TinyInteger, UnsignedBigInteger, UnsignedInteger, UnsignedMediumInteger, UnsignedSmallInteger, UnsignedTinyInteger

Date & Time Types

Date, DateTime, DateTimeTz, SoftDeletes, SoftDeletesTz, Time, TimeTz, Timestamp, TimestampTz, Timestamps, TimestampsTz

Other Types

Enum, Morphs, NullableMorphs, NumericMorphs, UuidMorphs, UlidMorphs

Enum

Create an Enum field that can be stored in Mysql according to the type in []any, but in Postgres, Sqlite, and Sqlserver databases, it is a String type.

table.Enum("difficulty", []any{"easy", "hard"})
table.Enum("num", []any{1, 2})

ID

The ID method is an alias for the BigIncrements method. By default, this method will create an id column; however, if you would like to assign a different name to the column, you may pass the column name:

table.ID()
table.ID("user_id")

SoftDeletes

The SoftDeletes method adds a nullable deleted_at TIMESTAMP column. This column is intended to store the deleted_at timestamp required for the Orm "soft delete" feature:

table.SoftDeletes()

Custom column

If you are using column types that framework does not support yet, you can use the Column method to customize the field type:

table.Column("geometry", "geometry")

Column Modifiers

In addition to the column types listed above, when adding a column to a database table, you can also add "modifiers" to the column. For example, to allow a column to be "nullable," you can use the Nullable method:

facades.Schema().Table("users", func(table schema.Blueprint) {
  table.String("name").Nullable()
})

The following table contains all available column modifiers:

Modified	Description
.Always()	The value of this column is always generated by the database system, and users cannot directly insert or modify it (only PostgreSQL)
.AutoIncrement()	Sets an integer column as auto-incrementing (primary key)
.After("column")	Sets the column after the specified column (MySQL only)
.Comment("my comment")	Adds a comment to the column (MySQL / PostgreSQL)
.Change()	Modify the column structure (MySQL / PostgreSQL / Sqlserver)
.Default(value)	Sets the default value for the column
.First()	Sets the column as the first column (MySQL only)
.GeneratedAs()	Sets the value of the column to be generated by the database system (only PostgreSQL)
.Nullable()	Allows NULL values to be inserted into the column
.Unsigned()	Sets an integer column as UNSIGNED (MySQL only)
.UseCurrent()	Sets a timestamp column to use CURRENT_TIMESTAMP as the default value
.UseCurrentOnUpdate()	Sets a timestamp column to use CURRENT_TIMESTAMP when the record is updated (MySQL only)

Drop Column

facades.Schema().Table("users", func(table schema.Blueprint) {
  table.DropColumn("name")
  table.DropColumn("name", "age")
})

Indexes

Create Index

facades.Schema().Table("users", func(table schema.Blueprint) {
  // Add primary key
  table.Primary("id")
  // Add composite primary key
  table.Primary("id", "name")

  // Add unique index
  table.Unique("name")
  table.Unique("name", "age")

  // Add normal index
  table.Index("name")
  table.Index("name", "age")

  // Add fulltext index
  table.FullText("name")
  table.FullText("name", "age")
})

Rename Index

facades.Schema().Table("users", func(table schema.Blueprint) {
  table.RenameIndex("users_name_index", "users_name")
})

Drop Index

facades.Schema().Table("users", func(table schema.Blueprint) {
  table.DropPrimary("id")
  table.DropUnique("name")
  table.DropUniqueByName("name_unique")
  table.DropIndex("name")
  table.DropIndexByName("name_index")
  table.DropFullText("name")
  table.DropFullTextByName("name_fulltext")
})

Create Foreign Key

facades.Schema().Table("posts", func(table schema.Blueprint) {
  table.UnsignedBigInteger("user_id")
  table.Foreign("user_id").References("id").On("users")
})

Drop Foreign Key

facades.Schema().Table("users", func(table schema.Blueprint) {
  table.DropForeign("user_id")
  table.DropForeignByName("user_id_foreign")
})