To add a migration, create a class that inherits from ClickHouseMigration and annotate it with the corresponding attribute. The attribute specifies the migration's index and name. The index is used to order migrations, and both the index and name are used to identify the migration while retrieving current database state.

[ClickHouseMigration(20241206172050, "AddExampleTable")]
public class AddExampleTable_ExampleMigration : ClickHouseMigration
{
	protected override void Up(ClickHouseMigrationBuilder migrationBuilder)
	{
		migrationBuilder.AddRawSqlStatement(@"
create table if not exists example_table
(
	value		Int32
)
engine = MergeTree
order by value
");

		migrationBuilder.AddRawSqlStatement(@"
create table if not exists another_table
(
	another_value		Int64
)
engine = MergeTree
order by another_value
");
	}

	protected override void Down(ClickHouseMigrationBuilder migrationBuilder)
	{
		migrationBuilder.AddRawSqlStatement("drop table if exists example_table");

		migrationBuilder.AddRawSqlStatement("drop table if exists another_table");
	}
}

Sometimes migrations should only apply to specific ClickHouse server versions — especially when working with features that are introduced or deprecated over time. The ClickHouseMigrationBuilder supports conditional execution based on the current server version:

migrationBuilder.WhenVersion(v => v >= "23.3" && v < "25.0", mb =>
{
	mb.AddRawSqlStatement("create table if not exists legacy_feature (...) engine = ...");
});

Only the major and minor parts of the ClickHouse version are considered (e.g., 23.3.1 is treated as 23.3).

In addition to WhenVersion, the builder also provides convenience methods:

• SinceVersion(supportedSince, action) – applies the migration starting from the specified version (inclusive)
• ForVersionRange(supportedSince, deprecatedIn, action) – applies the migration for versions in the specified range (supportedSince is inclusive, deprecatedIn is exclusive)

You can load SQL statements directly from a file using AddSqlFileStatements builder method. This is useful for managing large or pre-existing SQL scripts while maintaining compatibility with ClickHouse's requirement for single-statement queries.

migrationBuilder.AddSqlFileStatements("Scripts/create_tables.sql");

The SQL file will be read and split into individual statements using a default parser that separates statements by semicolons. Each statement will be executed separately in the order defined.

If your SQL file uses a different format (e.g., statements separated by comments or custom delimiters), you can provide your own implementation of the ISqlStatementParser interface.

migrationBuilder.AddSqlFileStatements("Scripts/create_tables.sql", new MyCustomSqlParser());

All parsed statements from the file will be treated the same as those added manually with AddRawSqlStatement, and are executed in the order they appear in the file.

If you'd like to apply migrations without writing a C# class for each .sql file, consider using the ClickHouseDirectoryMigrationsLocator.

There is a class template available for creating migrations. Check its README for more details.

Migrations locator is a type that implements IClickHouseMigrationsLocator. The primary method it exposes is:

IEnumerable<ClickHouseMigration> GetMigrations();

The order in which migrations are returned is not significant, as they will be subsequently ordered by their indexes.

To utilize context-specific migrations, implement the IClickHouseMigrationsLocator<TContext> interface. This allows for the retrieval of migrations that are specific to a given context, enhancing flexibility and modularity.

Several abstract or utility classes are provided to simplify locator implementation in common scenarios:

ClickHouseAssemblyMigrationsLocator locates migrations by scanning a given .NET assembly for non-abstract classes that inherit from ClickHouseMigration. Use this when your migrations are implemented as C# classes inside an assembly.

public class MyLocator : ClickHouseAssemblyMigrationsLocator
{
	protected override Assembly TargetAssembly => typeof(MyLocator).Assembly;
}

ClickHouseDirectoryMigrationsLocator locates file-based migrations in a specified directory. Each .sql file must follow a naming convention like 0001_CreateTable.up.sql or 0001_CreateTable.down.sql by default. Underscores _ are allowed in migration names. Up, Down, and sql suffixes are case-insensitive. Down migration is optional.

public class MyLocator : ClickHouseDirectoryMigrationsLocator
{
	protected override string DirectoryPath => "Migrations/Scripts";
}

You can customize:

• FileNameParser: how filenames are parsed into index, name, and direction
• SqlStatementParser: how the file contents are split into statements

protected override IMigrationFileNameParser FileNameParser => new MyFileNameParser();
protected override ISqlStatementParser SqlStatementParser => new MySqlStatementParser();

AggregateClickHouseMigrationsLocator combines results from multiple locators into a single collection.

public class MyLocator : AggregateClickHouseMigrationsLocator
{
	protected override IEnumerable<IClickHouseMigrationsLocator> Locators =>
	[
		new MyAssemblyLocator(),
		new MyDirectoryLocator()
	];
}

Useful when you need to combine both code-based and file-based migrations, or merge migrations from different modules.

ClickHouseNullMigrationsLocator is a dummy locator that returns no migrations.

IClickHouseMigrationsLocator locator = new ClickHouseNullMigrationsLocator();

Helpful for testing or when you want to disable migrations in a particular context.

Migration instructions is a type that implements IClickHouseMigrationInstructions. It is resolved through dependency injection (DI), allowing it to reference other services, such as configs. The following properties are exposed:

• ConnectionString: Provides the connection string for the ClickHouse database.
• DatabaseName: Provides the name of the database used for the migrations table. If the database does not exist, it will be created with the Atomic engine. This property fetches the value of the 'database' parameter from the connection string provided by GetConnectionString() by default. Use this property if the table containing the applied migrations should not be stored in the same database (database is used since there is no schema concept in ClickHouse and there is no overhead on creating databases, so it might be considered as a namespace). It might be useful if you need to create the application database via a migration.
• RollbackOnMigrationFail: Indicates whether migrations should be rolled back if a failure occurs during their execution. Since ClickHouse does not support DDL transactions, enabling this feature will trigger the Down() method for a failed migration. It is recommended to enable this feature only for idempotent migrations to ensure safe rollback operations. Default Value: false.
• HttpClient: Optional HttpClient used to execute migration statements. If provided, it will not be disposed internally. It is recommended to use an instance created via IHttpClientFactory to avoid lifecycle and socket issues.
• HistoryTableName: Migrations history table name. Default value: db_migrations_history.
• ClusterName: Cluster name where the migrations history table is stored. Ignored by default.
• ReplicatedHistoryTableArgs: Replication settings for the migrations history table. If specified, the history table will be created as a ReplicatedMergeTree using the provided ZooKeeper path and replica name. If null, a non-replicated engine will be used instead.

To add simple migrations, use the pre-built IServiceCollection extension method AddClickHouseMigrations<TInstructions, TLocator>. Here, TInstructions should implement IClickHouseMigrationInstructions and TLocator should implement IClickHouseMigrationsLocator.

services.AddClickHouseMigrations<ClickHouseMigrationInstructions, ClickHouseMigrationsLocator>();

For context-specific migrations, first, set up the instructions by calling AddClickHouseMigrations<TInstructions>. Then, use AddClickHouseContextMigrations<TContext, TLocator> for each context, where TLocator implements IClickHouseMigrationsLocator<TContext>.

services.AddClickHouseMigrations<ClickHouseMigrationInstructions>();

services.AddClickHouseContextMigrations<FirstContext, FirstContextMigrationsLocator>;
services.AddClickHouseContextMigrations<SecondContext, SecondContextMigrationsLocator>;

There are two nearly identical ways to apply or rollback migrations:

1. IClickHouseMigrator (or IClickHouseMigrator<TContext> for context-specific migrations) service with two primary methods:

• ApplyMigrationsAsync: Searches for available migrations using the migrations locator, checks the current database state, and applies any missing migrations (ascending by index) to bring the database up to date.
• RollbackAsync: Rolls back all migrations applied after the specified target migration index, reversing the changes in the opposite order of application until the target migration becomes the last applied migration.

var migrator = serviceProvider.GetRequiredService<IClickHouseMigrator>();

await migrator.ApplyMigrationsAsync();

await migrator.RollbackAsync(targetMigrationIndex);

2. IServiceProvider extensions provide similar methods to IClickHouseMigrator and include: ClickHouseMigrateAsync and ClickHouseRollbackAsync with generic versions for context-specific migrations.

await serviceProvider.ClickHouseMigrateAsync();

await serviceProvider.ClickHouseRollbackAsync(targetMigrationIndex);

Repository link

docker-compose examples:

Single clickhouse database example (ClickHouse + SchemaSpy + Nginx)

Multiple databases example (ClickHouse + Postgres + SchemaSpy + Nginx)

Containerized web service for visualizing ClickHouse data flows in development environments.

Repository link