/ 
DBIO-ClickHouse-0.900004
/ DBIO::ClickHouse::Deploy

NAME

DBIO::ClickHouse::Deploy - Deploy and upgrade ClickHouse schemas via test-deploy-and-compare

VERSION

version 0.900004

DESCRIPTION

DBIO::ClickHouse::Deploy orchestrates schema deployment and upgrades for ClickHouse with the family's test-deploy-and-compare strategy:

1. introspect the live database via the system.* catalog
2. deploy the desired schema into a throwaway scratch database
3. introspect that the same way
4. diff the two models with DBIO::ClickHouse::Diff
my $deploy = DBIO::ClickHouse::Deploy->new(
    schema => MyApp::DB->connect($dsn, $user, $pass),
);
$deploy->install;                        # fresh install
my $diff = $deploy->diff;                # or step by step
$deploy->apply($diff) if $diff->has_changes;
$deploy->upgrade;                        # convenience: diff + apply
$deploy->upgrade(allow_destructive => 1); # a diff that drops or converts values

Read "WHAT THIS DIFF WILL NOT DO" in DBIO::ClickHouse::Diff before wiring upgrade into anything automatic. A diff with a destructive operation runs only with allow_destructive ("_check_before_apply"). A change to a table's engine or MergeTree keys throws instead of rebuilding the table.

METHODS

temp_db_prefix

Prefix for the scratch database name; defaults to dbio_deploy_tmp_. The full name appends the PID and the current epoch second. Override per instance:

DBIO::ClickHouse::Deploy->new(schema => $schema, temp_db_prefix => 'scratch_');

_create_temp_db

my $name = $self->_create_temp_db($dbh);

Creates the scratch database and returns its name. The statement comes from "create_database" in DBIO::ClickHouse::DDL::Emit, which specifies no ENGINE, so it gets the server default (Atomic on any supported version).

_drop_temp_db

$self->_drop_temp_db($dbh, $name);

Drops the scratch database. IF EXISTS because this runs unconditionally, including on paths where the CREATE is the statement that failed.

install

$deploy->install;

Generates the install DDL via "install_ddl" in DBIO::ClickHouse::DDL and executes each statement against the connected database. For a fresh install on an empty database. Otherwise "install" in DBIO::Deploy::Base.

A source whose table or view is named database.table / database.view is made in that database ("qualified_tables" in DBIO::ClickHouse::DDL). The driver creates no database: one the server does not have ends the install before its first statement, with a DBIO::Exception naming the table and the database. The diff reads the current database alone, so the scratch database of a diff gets the tables and views of the current database only; a table or view of another database is installed, never compared, replaced or dropped.

diff

my $diff = $deploy->diff;

Introspects the live database, builds the desired model in a scratch database (see "THE SCRATCH DATABASE"), and returns a DBIO::ClickHouse::Diff. Throws if the schema asks for a change ClickHouse cannot make in place.

Overrides "diff" in DBIO::Deploy::Base for one reason: the base builds the diff from the two models alone, and the diff reads the IF [NOT] EXISTS capability off the storage it is handed. Here that is $self->schema->storage, so the statements apply and upgrade run carry the guards, and applying the same diff a second time after a partial failure is a no-op. It also hands the diff the SETTINGS the result classes declare ("declared_settings" in DBIO::ClickHouse::DDL), so a table the upgrade creates carries them, and the views the schema declares in the current database ("declared_views" in DBIO::ClickHouse::DDL, local_only), so a view the upgrade lacks is created. Everything else is the base's recipe (_introspect_current, _build_target_model).

_check_before_apply

$self->_check_before_apply($diff);

The driver's checks of "apply" in DBIO::Deploy::Base, run for a released diff before its first statement, against the live connection: "_refuse_nulls_in_narrowed_columns" and "_refuse_not_null_added_over_rows", then the base's. They read the data; diff, as_sql and summary stay data-free, and a dry_run checks nothing and asks the server nothing.

apply and upgrade are DBIO::Deploy::Base's as they are, with their arguments: a diff holding a destructive operation -- a drop, or a MODIFY COLUMN that can alter stored values ("lossy" in DBIO::ClickHouse::Diff::Column) -- runs only with allow_destructive => 1, given as argument or as attribute, and dry_run => 1 returns the diff without executing it.

$deploy->apply($diff, allow_destructive => 1);
$deploy->upgrade(dry_run => 1);

The ALTER TABLE ... MODIFY COLUMN and ADD INDEX statements it runs are accepted synchronously but applied as background mutations; watch system.mutations to see them finish.

_refuse_nulls_in_narrowed_columns

Every MODIFY COLUMN that turns a Nullable(...) column into a non-nullable one ("needs_null_check" in DBIO::ClickHouse::Diff::Column) is asked SELECT count() FROM t WHERE c IS NULL. A column that holds NULL rows ends the run with a DBIO::Exception naming table, column, row count and the way out, before any statement has been executed: the server would accept the MODIFY, switch the metadata at once and fail in the background mutation, leaving the column unreadable and the table's mutation queue stuck. The remedy is by hand:

ALTER TABLE t UPDATE c = <value> WHERE c IS NULL SETTINGS mutations_sync = 1

then run the upgrade again.

_refuse_not_null_added_over_rows

Likewise a non-nullable ADD COLUMN without a default to a table that holds rows ("needs_row_check" in DBIO::ClickHouse::Diff::Column) ends the run before the first statement: the server would fill the existing rows with the type's zero value.

upgrade

my $diff = $deploy->upgrade;

Convenience: "diff" then apply. Returns the diff object if changes were applied, or undef if the database was already up to date. Inherited from DBIO::Deploy::Base, with its allow_destructive and dry_run arguments.

A materialized view (or live or window view) that was created by hand and is not declared by the schema blocks upgrade: the diff refuses it loudly (see "View" in DBIO::ClickHouse::Diff, "Materialized views are refused") rather than dropping data. Declare it or drop it yourself before the upgrade.

THE SCRATCH DATABASE

SQLite and DuckDB build the desired-state model in :memory:. ClickHouse has no in-memory and no embedded mode at all -- not a :memory: DSN, not a file database, nothing that runs in-process. The only place a ClickHouse schema can be deployed is a ClickHouse server.

So the throwaway target is a scratch database on the very same server the live schema lives on:

CREATE DATABASE `dbio_deploy_tmp_<pid>_<epoch>`
  -> connect to it (DSN rewritten from the live connect_info)
  -> run install_ddl
  -> introspect
  -> disconnect
DROP DATABASE IF EXISTS `dbio_deploy_tmp_<pid>_<epoch>`

which is the same shape DBIO::MySQL::Deploy and DBIO::PostgreSQL::Deploy use, and is why this class subclasses DBIO::Deploy::Base::TempDatabase rather than overriding _build_target_model itself. Only the two-statement dialect seam ("_create_temp_db" / "_drop_temp_db") is ClickHouse's own.

The name carries the PID and the current epoch second, so two processes -- and the same process twice -- never collide.

What this costs you

The scratch database is real, on the real server. It is created empty, gets the schema (no rows), and is dropped again, so it costs a metadata write and whatever the DDL itself costs. But it does mean:

  • the connecting user needs CREATE DATABASE and DROP DATABASE. On a locked-down cluster, run diff from an account that has them, or generate the DDL offline with "install_ddl" in DBIO::ClickHouse::DDL and review it by hand.

  • on a replicated cluster, CREATE DATABASE without ON CLUSTER creates the scratch database on the coordinating node only. That is what we want -- it is throwaway -- but a Replicated* engine in the schema will want a ZooKeeper path, and those paths are not scoped to the scratch database. Deploy such schemas against a single node, or review the diff before applying.

Cleanup is guaranteed, and never eats the real error

The lifecycle is inherited verbatim from "_build_target_model" in DBIO::Deploy::Base::TempDatabase, which is already the try/finally shape this needs:

my $temp_db = $self->_create_temp_db($dbh);
my $model   = eval { $self->_deploy_and_introspect_temp($temp_db) };
my $err     = $@;
eval { $self->_drop_temp_db($dbh, $temp_db) };   # always, in its own eval
die $err if $err;                                # original error, unchanged

Three properties matter, and all three are already true there:

  • The drop always runs. It is not conditional on the deploy having succeeded, so a schema with one bad CREATE TABLE does not leak a scratch database.

  • The drop cannot swallow the original error. It is wrapped in its own eval, and $err was captured before it ran, so a cleanup that itself fails (server gone, permission revoked mid-run) cannot overwrite the exception that actually explains what went wrong.

  • The drop uses the live connection, not the scratch one. The scratch connection is opened and closed inside _deploy_and_introspect_temp; if it dies mid-flight the drop still has a working handle to issue DROP DATABASE on.

Why not a guard object. A DESTROY-based guard buys one thing this shape lacks -- cleanup on exit or an uncaught signal -- and costs several. It fires during global destruction, where the $dbh it needs may already be torn down, so the drop it exists to guarantee is exactly the one that can fail silently. And error handling inside DESTROY is where original exceptions get mangled -- this distribution has already had to replace core's scope-guard DESTROY for exactly that reason, see DBIO::ClickHouse::Storage::TxnScopeGuard. Adding a second guard with the same hazard, to cover a case (kill -9) that no in-process mechanism covers anyway, would trade a guarantee we have for one we cannot keep. A scratch database that survives a kill -9 is named dbio_deploy_tmp_* and is safe to drop by hand.

No transactions are involved

Nothing in this lifecycle depends on a transaction, and nothing may: this storage's txn_rollback always throws (docs/adr/0003-no-transactions-policy.md). The CREATE DATABASE / DROP DATABASE pair is the unit of recovery, which is why the drop is a plain statement in its own eval rather than a rollback.

"_execute_ddl" in DBIO::Deploy::Base would wrap its statement loop in $storage->txn_do if the storage reported the transactional_ddl capability. DBIO::ClickHouse::Storage declares _use_transactional_ddl(0), so the loop runs statement at a time and core emits its one-time "non-transactional DDL" notice. That notice is accurate: a multi-statement apply that fails half way through leaves the earlier statements applied. Re-running upgrade is safe -- the diff is recomputed against live state, so the second run picks up only what is left.

SEE ALSO

AUTHOR

DBIO Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.