Clickhouse Migrations Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require dmamontov/clickhouse-migrations-bundle
   

   Add the bundle to config/bundles.php if not auto-discovered.

2. Configure ClickHouse Client: Define the ClickHouseDB\Client service in config/services.yaml:

   services:
       ClickHouseDB\Client:
           arguments:
               $connectParams:
                   host: 'http://localhost'
                   port: 8123
                   username: 'default'
                   password: ''
               $settings:
                   database: 'your_database'
   

3. Verify Bundle Config: Ensure config/packages/clickhouse_migrations.yaml exists (defaults are fine for most cases).

4. First Migration: Generate and run a migration:

   bin/console clickhouse:migrations:generate
   bin/console clickhouse:migrations:migrate
   

First Use Case

• Schema Changes: Need to add a table or alter a schema? Generate a migration:

  bin/console clickhouse:migrations:generate --name=create_users_table
  

  Edit the generated file in clickhouse_migrations/ (e.g., YYYYMMDDHHMMSS_create_users_table.php) to include your SQL (e.g., CREATE TABLE users (...) ENGINE = MergeTree()).

Implementation Patterns

Workflows

1. Migration Development:

   • Generate migrations with --name for clarity:

     bin/console clickhouse:migrations:generate --name=add_index_to_users
     

   • Use up/down methods in the migration class:

     public function up(Connection $connection): void
     {
         $connection->execute("ALTER TABLE users ADD INDEX idx_email (email)");
     }
     public function down(Connection $connection): void
     {
         $connection->execute("ALTER TABLE users DROP INDEX idx_email");
     }
     

2. Running Migrations:

   • Apply all pending migrations:

     bin/console clickhouse:migrations:migrate
     

   • Rollback the last migration:

     bin/console clickhouse:migrations:rollback
     

   • Reset the entire migration history (use with caution!):

     bin:console clickhouse:migrations:reset
     

3. Integration with Symfony Lifecycle:

   • Run migrations during deployment via a post-deploy hook (e.g., in deploy.php):

     $this->run("php bin/console clickhouse:migrations:migrate --no-interaction");
     

Integration Tips

• Custom Migration Paths: Override migrations_path in clickhouse_migrations.yaml to store migrations in a version-controlled directory (e.g., config/migrations).

• Environment-Specific Migrations: Use Symfony’s %env% or %kernel.environment% to conditionally load migrations:

  migrations_path: '%kernel.project_dir%/migrations/%kernel.environment%'
  

• Dependency Management: Ensure migrations run in a specific order by naming them logically (e.g., create_table_x_before_table_y.php).

• Testing: Use migrations:reset in phpunit.xml to ensure a clean state for tests:

  <env name="KERNEL_CLASS" value="App\Tests\AppKernel"/>
  <env name="APP_ENV" value="test"/>
  <env name="APP_DEBUG" value="true"/>
  <env name="CLICKHOUSE_MIGRATIONS_RESET" value="true"/>
  

Gotchas and Tips

Pitfalls

1. Connection Issues:

   • Symptom: Migrations fail silently or throw ConnectionException.
   • Fix: Verify ClickHouseDB\Client is properly configured in services.yaml. Test connectivity with:

     bin/console debug:container ClickHouseDB\Client
     

   • Debugging: Enable verbose output:

     bin/console clickhouse:migrations:migrate --verbose
     

2. Migration Table Conflicts:

   • Symptom: migrations_versions table already exists but is corrupted.
   • Fix: Manually drop the table and reset migrations:

     DROP TABLE IF EXISTS migrations_versions;
     

     Then run:

     bin/console clickhouse:migrations:reset
     

3. SQL Syntax Errors:

   • Symptom: ClickHouse rejects SQL (e.g., unsupported syntax in down()).
   • Fix: Validate SQL using the ClickHouse CLI or Playground.

4. Namespace Collisions:

   • Symptom: Migrations fail to load due to duplicate class names.
   • Fix: Customize migrations_namespace in clickhouse_migrations.yaml:

     migrations_namespace: 'App\Migrations\ClickHouse'
     

Debugging

• Dry Runs: Use --dry-run to preview SQL without executing:

  bin/console clickhouse:migrations:migrate --dry-run
  

• Logging: Enable debug mode in config/packages/dev/clickhouse_migrations.yaml:

  clickhouse_migrations:
      debug: true
  

Extension Points

1. Custom Migration Commands: Extend the bundle by creating a custom command (e.g., for batch migrations):

   // src/Command/CustomMigrationCommand.php
   namespace App\Command;
   use Symfony\Component\Console\Command\Command;
   use Symfony\Component\Console\Input\InputInterface;
   use Symfony\Component\Console\Output\OutputInterface;
   use Doctrine\DBAL\Connection;
   
   class CustomMigrationCommand extends Command
   {
       protected static $defaultName = 'app:clickhouse:batch-migrate';
       private $connection;
   
       public function __construct(Connection $connection)
       {
           parent::__construct();
           $this->connection = $connection;
       }
   
       protected function execute(InputInterface $input, OutputInterface $output): int
       {
           $this->connection->execute("SOURCE /path/to/batch.sql");
           return Command::SUCCESS;
       }
   }
   

2. Pre/Post-Migration Hooks: Subscribe to the clickhouse_migrations.migrate event in a service:

   # config/services.yaml
   services:
       App\EventListener\MigrationListener:
           tags:
               - { name: kernel.event_listener, event: clickhouse_migrations.migrate, method: onMigrate }
   

3. Custom Migration Table: Override the table_name to use an existing table (e.g., for multi-tenant setups):

   clickhouse_migrations:
       table_name: 'tenant_1_migrations'
   

Tips

• Idempotent Migrations: Design migrations to be safely rerunnable (e.g., check for table existence):

  public function up(Connection $connection): void
  {
      $connection->execute("CREATE TABLE IF NOT EXISTS users (...) ENGINE = MergeTree()");
  }
  

• Backup First: Always back up your ClickHouse database before running migrations in production:

  clickhouse-client --query="CREATE DATABASE backup_db AS backup_db CLONE database_name"
  

• Atomic Migrations: Use transactions where supported (ClickHouse 21.6+):

  public function up(Connection $connection): void
  {
      $connection->beginTransaction();
      try {
          $connection->execute("CREATE TABLE users (...)");
          $connection->commit();
      } catch (\Exception $e) {
          $connection->rollBack();
          throw $e;
      }
  }