Hermes

Maintain your ClickHouse migrations.

Hermes is a simple database migration tool specifically designed for ClickHouse databases.

Installation

Prerequisites

• Python 3.12 or higher
• ClickHouse server
• Docker (for running tests)

Install from source

git clone https://github.com/your-org/hermes.git
cd hermes
uv sync

Quick Start

1. Initialize Hermes in your project:

uv run hermes init

This creates a hermes.toml configuration file with default settings. You can optionally specify a custom migrations folder:

uv run hermes init my_migrations

2. Set up environment variables (.env):

# Option 1: Direct URI
CLICKHOUSE_URI=clickhouse://user:password@localhost:9002/default

# Option 2: Individual parameters
CLICKHOUSE_HOST=localhost
CLICKHOUSE_PORT=9002
CLICKHOUSE_DATABASE=default
CLICKHOUSE_USER=test
CLICKHOUSE_PASSWORD=test

3. Create your first migration:

uv run hermes new --message "create users table"

4. Run migrations:

uv run hermes upgrade head

Migrating from Old Version Format

Important: Version Format Change

Previous versions of Hermes used UUID4-based version identifiers with descriptive messages (e.g., abc123def456--create_users_table). The new version format uses datetime UTC timestamps in the format %Y%m%d%H%M%S (e.g., 20241215143022).

Migration Steps

If you're upgrading from the old version format, follow these steps carefully:

1. Upgrade to latest migration (CRITICAL): Before running the version migration, ensure your database is at the latest migration version:

   uv run hermes upgrade head

   This is the most important step. The migration will fail if your database is not up-to-date.

2. Backup your migrations directory: Create a backup of your migrations folder before proceeding:

   cp -r migrations migrations_backup

3. Run the version migration:

   uv run hermes migrate-versoning

This command will:

• Validate that your database is at the latest version
• Create new migration directories with datetime-based versions
• Copy all SQL files from old migrations to new ones
• Update the database migration table from ch_migrations to hermes_migrations
• Delete old migration directories

Note: Once you've migrated to the new version format, you cannot revert to the old format without restoring from backup.

Usage

Commands

init - Initialize Hermes configuration

Create a hermes.toml configuration file in the current directory.

# Initialize with default migrations folder
uv run hermes init

# Initialize with custom migrations folder
uv run hermes init my_migrations

Arguments:

• folder_name (optional): Custom name for the migrations directory (default: versions)

Note: This command will fail if a hermes.toml file already exists in the current directory.

new - Create a new migration

Create a new migration file with upgrade and downgrade SQL scripts.

uv run hermes new --message "your migration description"
uv run hermes new -m "create users table"

# With custom config
uv run hermes new -m "add indexes" --config-path custom.toml

Options:

• --message, -m (required): Description of the migration
• --config-path: Path to configuration file (default: hermes.toml)

upgrade - Apply migrations

Run migrations forward to upgrade your database schema.

# Upgrade to latest (head)
uv run hermes upgrade head

# Upgrade to specific version
uv run hermes upgrade abc123def456

# With custom config
uv run hermes upgrade head --config-path custom.toml

Options:

• revision (required): Target revision (head for latest, or specific version ID)
• --config-path: Path to configuration file (default: hermes.toml)

downgrade - Rollback migrations

Run migrations backward to downgrade your database schema.

# Downgrade to base (remove all migrations)
uv run hermes downgrade base

# Downgrade to specific version
uv run hermes downgrade abc123def456

# With custom config
uv run hermes downgrade base --config-path custom.toml

Options:

• revision (required): Target revision (base for empty database, or specific version ID)
• --config-path: Path to configuration file (default: hermes.toml)

Configuration

Hermes uses TOML configuration files. Create a hermes.toml file in your project root:

# Migration settings
migrations-location = "migrations"    # Directory to store migration files

# Logging configuration
log-level = "info"                   # debug, info, warning, error, critical
log-to-file = true                   # Write logs to file
log-to-stream = true                 # Write logs to console
log-file-path = "hermes.log"         # Log file path

Environment Variables

Set up your ClickHouse connection using environment variables:

Option 1: Direct URI

CLICKHOUSE_URI=clickhouse://user:password@host:port/database

Option 2: Individual Parameters

CLICKHOUSE_HOST=localhost
CLICKHOUSE_PORT=9002
CLICKHOUSE_DATABASE=default
CLICKHOUSE_USER=test
CLICKHOUSE_PASSWORD=test

Migration Files

When you create a new migration, Hermes generates a directory structure:

migrations/
 abc123def456_create_users_table/
     info.toml        # Migration metadata
     upgrade.sql      # Forward migration SQL
     downgrade.sql    # Rollback migration SQL

Example upgrade.sql:

CREATE TABLE users (
    id UInt64,
    name String,
    email String,
    created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY id;

Example downgrade.sql:

DROP TABLE IF EXISTS users;

Development

Setup Development Environment

# Clone repository
git clone https://github.com/your-org/hermes.git
cd hermes

# Install dependencies
uv sync --group dev

# Install pre-commit hooks
uv run pre-commit install

Running Tests

# Run all tests
uv run pytest

# Run only unit tests (fast)
uv run pytest -m "not integration"

# Run only integration tests (requires Docker)
uv run pytest -m integration

# Run specific test file
uv run pytest src/tests/test_utils_unit.py -v

For Podman Users

To run tests with Podman instead of Docker:

# Enable Podman socket
systemctl --user enable --now podman.socket

# Set environment variables and run tests
export DOCKER_HOST="unix:///run/user/$UID/podman/podman.sock"
export TESTCONTAINERS_RYUK_DISABLED="true"

# Now run tests normally
uv run pytest -m integration

Code Quality

# Format code
uv run ruff format

# Lint code
uv run ruff check

Docker Environment

For development and testing, use the included Docker Compose setup:

# Start ClickHouse
docker compose up -d

# Stop ClickHouse
docker compose down