Introduction
Migrations are like version control for your database, allowing your team to define and share the application's database schema definition. If you have ever had to tell a teammate to manually add a column to their local database schema after pulling in your changes from source control, you've faced the problem that database migrations solve.
Generating Migrations
You may use the make:migration Vania
command to generate a database migration. The new migration will be placed in your lib/database/migrations
directory:
vania make:migration create_post_table
Vania will use the name of the migration to attempt to guess the name of the table and whether or not the migration will be creating a new table, If Vania is able to determine the table name from the migration name, Vania will pre-fill the generated migration file with the specified table. Otherwise, you may simply specify the table in the migration file manually.
Migration Structure
A migration class contains two methods: up
and down
. The up
method is used to add new tables, columns, or indexes to your database, while the down
method should reverse the operations performed by the up
method.
import 'package:vania/database.dart';
import 'package:vania/migration.dart';
class CreateUserTable extends Migration {
@override
Future<void> up() async {
await createTable('users', (Schema table) {
table.id();
table.string('name').comment('Name of the User');
table
.string('email')
.unique('email_unique')
.notNull()
.comment('Email of the User');
table.timeStamps();
})
.engine('InnoDB')
.comment('Users table')
.charset('utf8mb4')
.collate('utf8mb4_unicode_ci')
.autoIncrement(1000);
}
@override
Future<void> down() async {
await drop('users');
}
}
Running Migrations
To run all of your outstanding migrations, execute the migrate Artisan command:
vania migrate
Rolling Back Migrations
To roll back the latest migration operation, you may use the rollback
Vania command. This command rolls back the last "batch" of migrations, which may include multiple migration files:
vania 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:
vania migrate:rollback --step 5
You may roll back a specific "batch" of migrations by providing the batch
option to the rollback
command, where the batch
option corresponds to a batch value within your application's migrations
database table. For example, the following command will roll back all migrations in batch three:
vania migrate:rollback --batch 5
The migrate:reset
command will roll back all of your application's migrations:
vania migrate:reset
Roll Back and 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:
vania migrate:refresh
Drop All Tables and Migrate
The migrate:fresh
command will drop all tables from the database and then execute the migrate
command:
vania migrate:fresh
Creating Tables
To create a new database table, use the create
method.
await create('users', (Schema table) {
table.id();
});
When creating the table, you may use any of the column methods to define the table's columns.
Available Column Types
bigIncrements()
The bigIncrements
method creates an auto-incrementing UNSIGNED
BIGINT
(primary key) equivalent column:
table.bigIncrements('id')
bigInt()
The bigInt
method creates a BIGINT
equivalent column:
table.bigInt('votes')
binary()
The binary
method creates a BLOB
equivalent column:
table.binary('photo')
boolean()
The boolean
method creates a BOOLEAN
equivalent column:
table.boolean('confirmed')
char()
The char
method creates a CHAR
equivalent column with a given length:
table.char('name',length: 100)
dateTime()
The dateTime
method creates a DATETIME
equivalent column:
table.dateTime('created_at')
date()
The date
method creates a DATE
equivalent column:
table.date('created_at')
decimal()
The decimal
method creates a DECIMAL
equivalent column with the given precision (total digits) and scale (decimal digits):
table.decimal('amount',precision:8,scale:2)
double()
The double
method creates a DOUBLE
equivalent column with the given precision (total digits) and scale (decimal digits):
table.double('amount',precision:8,scale:2)
enumType()
The enum
method creates an ENUM
equivalent column with the given valid values:
table.enumType('difficulty', enumValues: ['easy', 'hard'])
float()
The float
method creates a FLOAT
equivalent column with the given precision (total digits) and scale (decimal digits):
table.float('amount',precision:8,scale:2)
geometryCollection()
The geometryCollection
method creates a GEOMETRYCOLLECTION
equivalent column:
table.geometryCollection('positions')
geometry()
The geometry
method creates a GEOMETRY
equivalent column:
table.geometry('positions')
id()
The id
method is an alias of the bigIncrements
method. By default, the method will create an id column; however, you may pass a column name if you would like to assign a different name to the column:
table.id()
integer()
The integer
method creates an INTEGER
equivalent column:
table.integer('votes')
json()
The json
method creates a JSON
equivalent column:
table.json('options')
lineString()
The lineString
method creates a LINESTRING
equivalent column:
table.lineString('positions')
longText()
The longText
method creates a LONGTEXT
equivalent column:
table.longText('description')
mediumInt()
The mediumInt
method creates a MEDIUMINT
equivalent column:
table.mediumInt('votes')
mediumText()
The mediumText
method creates a MEDIUMTEXT
equivalent column:
table.mediumText('description')
multiLineString()
The multiLineString
method creates a MULTILINESTRING
equivalent column:
table.multiLineString('positions')
multiPolygon()
The multiPolygon
method creates a MULTIPOLYGON
equivalent column:
table.multiPolygon('positions')
point()
The point
method creates a POINT
equivalent column:
table.point('positions')
polygon()
The polygon
method creates a POLYGON
equivalent column:
table.polygon('positions')
set()
The set
method creates a SET
equivalent column with the given list of valid values:
table.set('positions',setValues:[])
smallInt()
The smallInt
method creates a SMALLINT
equivalent column:
table.smallInt('votes');
softDeletes()
The softDeletes
method adds a nullable deleted_at TIMESTAMP
equivalent column. This column is intended to store the deleted_at timestamp:
table.softDeletes('deleted_at');
string()
The string
method creates a VARCHAR
equivalent column of the given length:
table.string('name');
text()
The text
method creates a TEXT
equivalent column:
table.text('description');
time()
The time
method creates a TIME
equivalent column:
table.time('sunrise');
timestamp()
The timestamp
method creates a TIMESTAMP
equivalent column:
table.timestamp('sunrise');
tinyInt()
The tinyInt
method creates a TINYINT
equivalent column:
table.tinyInt('votes');
tinyText()
The tinyText
method creates a TINYTEXT
equivalent column:
table.tinyText('notes');
uuid()
The uuid
method creates a UUID
equivalent column:
table.uuid('id');
year()
The year
method creates a YEAR
equivalent column:
table.year('birth_year');
foreign()
Creates a foreign key constraint on the specified [columnName
] in the current table,
referencing the [referencesColumn
] in the [referencesTable
].
By default, the foreign key is not constrained, meaning it allows NULL values.
Set [constrained
] to true
to enforce the foreign key constraint.
You can specify the behavior on update and delete actions using [onUpdate
] and [onDelete
]
parameters. By default, both are set to 'NO ACTION'.
table.foreign('product_id', 'products', 'id', constrained: true, onDelete: 'CASCADE');
This creates a foreign key on the 'product_id' column in the current table, which references the 'id' column in the 'products' table. The foreign key is constrained and set to 'CASCADE' on delete.