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.