README

Make your migrations safe

🪄 Features

PG 13+
PHP 8.1
Doctrine Migration

🤷 Why?

Because SQL migrations can execute heavy queries on database which can slow down your application.

⚙️ Config

*For a Symfony > 6.x

Install in your project

$ composer req yousign/safe-migrations

Declare the Middleware in your services.yaml

parameters:
  env(ENABLE_RETRY_LOCK_TIMEOUT): false
  
services:
  Yousign\SafeMigrations\Doctrine\DBAL\Driver\Middleware\RetryLockTimeoutMiddleware:
    $isEnabled: '%env(bool:ENABLE_RETRY_LOCK_TIMEOUT)%'

Create a migration template migration.php.tpl

<?php

declare(strict_types=1);

namespace <namespace>;

use Doctrine\DBAL\Schema\Schema;
use Yousign\SafeMigrations\Doctrine\Migration;

class <className> extends Migration
{
    public function up(Schema $schema): void
    {
<up>
    }
}

Set this template as default for your migrations in doctrine_migrations.yaml

doctrine_migrations:
  custom_template: "%kernel.project_dir%/migrations/migration.php.tpl"

Enable the retry on lock through the env var in you .env<.environment>

ENABLE_RETRY_LOCK_TIMEOUT=true

That's it ☕

▶️ Usage

Migration class

When you generate a new migration, this one extend the Migration class of the library which expose the following safe methods.

Each of these methods will generate the right set of SQL requests to make the requested query safe.

Create table

$this->createTable(table: 'test', columnDefinitions: [
    'id UUID NOT NULL', 
    'PRIMARY KEY(id)',
])

Add foreign key

$this->addForeignKey(table: 'address', name: 'fk_address_contact', column: 'contact', referenceTable: 'contact', referenceColumn: 'id', options: 'ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE')

Rename constraint

$this->renameConstraint(table: 'address', from: 'id_pkey', to: 'pkey_id')

Comment on column

$this->commentOnColumn(table: 'address', name: 'name', comment: null)

Add index

Note: Adding an index on a table will execute an "analyze" on all columns of the table to update statistics

$this->addIndex(name: 'idx_contact_email', table: 'contact', columns: ['email'], unique: false, usingMethod: 'GIN', where: 'country = "France"')

Drop index

$this->dropIndex(name: 'idx_contact_email')

Rename index

$this->renameIndex(from: 'idx_email_signer', to: 'idx_signer_email')

Add column

$this->addColumn(table: 'contact', name: 'mobile', type: 'text', defaultValue: null, nullable: true)

Drop column

$this->dropColumn(table: 'contact', name: 'landline')

Set default on column

$this->setDefaultOnColumn(table: 'contact', name: 'email', value: "'noreply@undefined.org'")

Drop default on column

$this->dropDefaultOnColumn(table: 'contact', name: 'email')

Set column nullable

$this->setColumnNullable(table: 'contact', name: 'email')

Set column not nullable

$this->setColumnNotNullable(table: 'contact', name: 'email')

Migration execution

If there is a lock while executing a Doctrine migration, the migration will throw a DriverException (Doctrine\DBAL\Driver\Exception).

If the SQLSTATE value is 55P03 (lock_not_available), the query will be retried up to 3 times with a 10s interval before throwing the thrown exception if it does not succeed.

You will get the following output:

Failed after the 3 retry

$ bin/symfony console d:m:m

[notice] Migrating up to DoctrineMigrations\Version20231224200000
09:30:38 WARNING   [app] (1/3) Lock timeout reached: retrying in 10 seconds... ["sql" => "ALTER TABLE test_retry ADD COLUMN name text DEFAULT NULL"]
09:30:51 WARNING   [app] (2/3) Lock timeout reached: retrying in 10 seconds... ["sql" => "ALTER TABLE test_retry ADD COLUMN name text DEFAULT NULL"]
09:31:04 WARNING   [app] (3/3) Lock timeout reached: retrying in 10 seconds... ["sql" => "ALTER TABLE test_retry ADD COLUMN name text DEFAULT NULL"]
[error] Migration DoctrineMigrations\Version20231224200000 failed during Execution. Error: "An exception occurred while executing a query: SQLSTATE[55P03]: Lock not available: 7 ERROR:  canceling statement due to lock timeout"
09:31:17 CRITICAL  [console] Error thrown while running command "'d:m:m'". Message: "An exception occurred while executing a query: SQLSTATE[55P03]: Lock not available: 7 ERROR:  canceling statement due to lock timeout" - An exception occurred while executing a query: SQLSTATE[55P03]: Lock not available: 7 ERROR:  canceling statement due to lock timeout ["exception" => Doctrine\DBAL\Exception\DriverException^ { …},"command" => "'d:m:m'","message" => "An exception occurred while executing a query: SQLSTATE[55P03]: Lock not available: 7 ERROR:  canceling statement due to lock timeout"]

Succeed after 1 retry

bin/symfony console d:m:m

[notice] Migrating up to DoctrineMigrations\Version20231224200000
09:28:54 WARNING   [app] (1/3) Lock timeout reached: retrying in 10 seconds... ["sql" => "ALTER TABLE test_retry ADD COLUMN name text DEFAULT NULL"]
[notice] finished in 15446.1ms, used 38.5M memory, 2 migrations executed, 13 sql queries

[OK] Successfully migrated to version: DoctrineMigrations\Version20231224200000

📋 FAQ

Does it work with Migration bundle ?

Yes, of course. There is no incompatibility between this library and the doctrine/doctrine-migrations-bundle.

🔗 References

PostgreSQL doc: Multiversion Concurrency Control
PostgreSQL doc: lock behavior: must read, it will help you understand a lot of things about PG locks.

For example, some DDL queries require an exclusive lock on a table. When the table is concurrently accessed and modified by other processes, acquiring the lock may take a while. The lock request is waiting in a queue, and it may also block other queries on this table once it has been enqueued.
Examples of other safe migration frameworks:
Great article to introduce to ZDD

Les Patterns des Géants du Web – Zero Downtime Deployment - OCTO Talks !
Very good resources to know how to schema changes while keeping backward compatibility

https://databaserefactoring.com
GitLab explains to contributors how they manage ZDD with their database

https://docs.gitlab.com/ee/development/what_requires_downtime.html
Article that explain best practices for zero downtime schema changes and many tips

PostgreSQL at Scale: Database Schema Changes Without Downtime
Very good video that explains a lot of things about ZDD with Database schema changes:

Montée de version sans interruption (Nelson Dionisi)
Another good video on the subject but in English this time:

PHP UK Conference 2018 - Michiel Rook - Database Schema Migrations with Zero Downtime
Doctolib explains how they manage their migration without downtime, we basically implemented the same thing.

🤝 Contributing

Please read CONTRIBUTING.md for details on our code of conduct, and the process for submitting pull requests to us.

After writing your fix/feature, you can run following commands to make sure that everything is still ok.

# Install dev dependencies
$ make vendor

# Running tests and quality tools locally
$ make tests

yousign / safe-migrations

Maintainers

Details