Database Version Control with Migrations
Database migrations are version control for your database schema, allowing you to evolve your database structure alongside your application versions. Each migration represents a specific version change in your application's database schema, ensuring your database structure stays synchronized with your codebase as your application evolves.
This guide covers how to implement and manage these version-based database changes on Zerops, focusing on PostgreSQL as the recommended database system.
Environment Configuration​
Configure your database connection in your environment variables:
Before running migrations in production, it's strongly recommended to back up your database. Zerops provides automated daily backups - see our backup documentation for details.
Running Migrations​
Automatic Migrations​
The most reliable way to manage migrations in your deployment pipeline is through automatic execution. Configure this in your zerops.yml
:
When running automatic migrations in production, the --force
flag is necessary to bypass Laravel's safety prompt. Without this flag, Laravel asks for confirmation to help prevent accidental data loss.
The --isolated
flag prevents multiple servers from running migrations simultaneously by using a cache lock.
Manual Migrations​
For development and troubleshooting purposes, you can execute migrations manually through SSH:
Migration Commands​
Essential migration commands for your workflow:
# Create a new migration file with timestamp
php artisan make:migration create_users_table
# Execute all pending migrations
php artisan migrate
# Revert the most recent migration operation
php artisan migrate:rollback
# Reset and rerun all migrations (warning: destroys existing data)
php artisan migrate:fresh
# Display current migration status
php artisan migrate:status
Best Practices​
Migration Structure​
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
* Creates the users table with essential fields.
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id(); // Auto-incrementing primary key
$table->string('name'); // User's full name
$table->string('email')->unique(); // Unique email address
$table->timestamp('email_verified_at')->nullable(); // Email verification timestamp
$table->string('password'); // Hashed password
$table->rememberToken(); // Remember me token
$table->timestamps(); // Created_at and updated_at timestamps
});
}
/**
* Reverse the migrations.
* Removes the users table completely.
*/
public function down()
{
Schema::dropIfExists('users');
}
};
Safe Migration Practices​
- Implement Reversible Changes
- Use Foreign Key Contraints
- Handle Large Tables Efficiently
public function up()
{
// Step 1: Add nullable column to prevent blocking operations
Schema::table('large_table', function (Blueprint $table) {
$table->string('new_column')->nullable();
});
// Step 2: Update data in manageable chunks
DB::table('large_table')
->orderBy('id')
->chunk(1000, function ($records) {
foreach ($records as $record) {
DB::table('large_table')
->where('id', $record->id)
->update(['new_column' => 'default_value']);
}
});
}
Testing Migrations​
Create a Test Database​
Add a testing connection to your config/database.php
:
Migration Test Example​
Create a test file at tests/Unit/MigrationTest.php
:
/**
* Test migration execution and schema verification
*/
public function test_migrations_can_be_run()
{
// Execute all migrations
Artisan::call('migrate');
// Verify table creation
$this->assertTrue(Schema::hasTable('users'));
// Verify column structure
$this->assertTrue(Schema::hasColumns('users', [
'id', 'name', 'email', 'password'
]));
}
- Always create a backup before running migrations in production
- Use database transactions for complex migrations
- Thoroughly test migrations in development environment
- Implement seeders for initial data population
- Monitor execution time for migrations on large tables
Troubleshooting​
Common migration issues and their solutions:
- Migration Timeout Configure longer timeout in zerops.yml:
- Database Lock Timeout Adjust PDO settings in
config/database.php
:
- Reset Migration State Commands: