包简介

Make your migrations safe

README 文档

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

Authors

yousign/safe-migrations 适用场景与选型建议

yousign/safe-migrations 是一款基于 PHP 开发的 Composer 扩展包，目前已累计 30.47k 次下载、GitHub Stars 达 19，最近一次更新时间为 2023 年 11 月 24 日，在 PHP 生态内属于活跃度较高的组件。

我们在过去多个企业项目中使用过 yousign/safe-migrations 或与其功能相近的方案，如果你在选型或落地过程中遇到问题，例如 版本兼容、二次改造、私有化封装、与内部系统对接、生产 BUG 排查，欢迎联系我们协助评估。

围绕 yousign/safe-migrations 我们能提供哪些服务？

定制开发 / 二次开发

基于 yousign/safe-migrations 在你已有业务上做功能扩展、字段裁剪、UI 适配、与内部账号 / 权限 / 日志系统的深度对接。

BUG 修复 & 性能优化

线上偶发问题、内存泄漏、慢查询、并发异常等排查修复；针对高流量场景做缓存、队列、索引层面的调优。

项目外包 & 长期维护

承接完整的项目从需求 → 设计 → 开发 → 上线 → 长期运维；也可按月提供技术保姆服务。

yvsm@zunyunkeji.com QQ：316430983 微信：yvsm316 西安尊云信息科技 · 专注 PHP / Go / 分布式系统研发

yousign/safe-migrations

包简介