Skip to main content
Skip to main content
🚧 Work in Progress

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:

zerops:
- setup: app
run:
envVariables:
DB_CONNECTION: pgsql
DB_HOST: ${db_hostname}
DB_PORT: 5432
DB_DATABASE: myapp
DB_USERNAME: ${db_user}
DB_PASSWORD: ${db_password}
Backup your data

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:

zerops.yml
zerops:
- setup: app
run:
initCommands:
- php artisan migrate --force --isolated
Caution

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.

Migrations in HA mode

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:

# Connect to your zerops project
zcli vpn up

# SSH into your service using it's hostname (app)
ssh app

# For MacOS users
ssh app.zerops

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​

database/migrations/create_users_table.php
<?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​

  1. Implement Reversible Changes
public function down()
{
// Always provide a way to undo migration changes
Schema::table('users', function (Blueprint $table) {
$table->dropColumn('new_column');
});
}
  1. Use Foreign Key Contraints
public function up()
{
Schema::table('posts', function (Blueprint $table) {
// Create relationship with cascading delete
$table->foreignId('user_id')
->constrained()
->onDelete('cascade');
});
}
  1. 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:

'testing' => [
'driver' => 'pgsql',
'host' => env('DB_TEST_HOST', '127.0.0.1'),
'database' => env('DB_TEST_DATABASE', 'testing'),
'username' => env('DB_TEST_USERNAME', 'postgres'),
'password' => env('DB_TEST_PASSWORD', ''),
],

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'
]));
}
Migration Tips
  • 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:

  1. Migration Timeout Configure longer timeout in zerops.yml:
zerops:
- setup: app
run:
initCommands:
- php artisan migrate --force --isolated --timeout=1000
  1. Database Lock Timeout Adjust PDO settings in config/database.php:
'pgsql' => [
// ...
'options' => [
PDO::ATTR_LOCK_TIMEOUT => 1000 // Milliseconds
]
],
  1. Reset Migration State Commands:
# Reset all migrations
php artisan migrate:reset

# Re-run all migrations
php artisan migrate

Additional Resources​