Make your migrations safe
- PG 13+
- PHP 8.1
- Doctrine Migration
Because SQL migrations can execute heavy queries on database which can slow down your application.
*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 ☕
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')
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
Does it work with Migration bundle ?
Yes, of course. There is no incompatibility between this library and the doctrine/doctrine-migrations-bundle.
-
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
-
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:
-
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.
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