Usage

Houseplant is a CLI tool that helps you manage database migrations for ClickHouse.

Basic Commands

Initialize a New Project

To create a new Houseplant project:

$ houseplant init

This will create the following structure:

ch/
├── migrations/
└── schema.sql

Generate a Migration

To generate a new migration:

$ houseplant generate "create users table"

This will create a new migration file in ch/migrations/ with a timestamp prefix, for example:

ch/migrations/20240320123456_create_users_table.yml

Migration File Structure

Migration files use YAML format and support different environments:

version: "20240320123456"
name: create_users_table
table: users

development: &development
  up: |
    CREATE TABLE {table} (
      id UInt64,
      name String
    ) ENGINE = MergeTree()
    ORDER BY id
  down: |
    DROP TABLE {table}

test:
  <<: *development

production:
  up: |
    CREATE TABLE ON CLUSTER '{cluster}' {table} (
      id UInt64,
      name String
    ) ENGINE = ReplicatedMergeTree()
    ORDER BY id
  down: |
    DROP TABLE ON CLUSTER '{cluster}' {table}

Running Migrations

Check Migration Status

To see the status of all migrations:

$ houseplant migrate:status

This will show which migrations have been applied and which are pending.

Apply Migrations

To run all pending migrations:

$ houseplant migrate:up

To migrate to a specific version:

$ houseplant migrate:up VERSION=20240320123456

Rollback Migrations

To roll back the last migration:

$ houseplant migrate:down

To roll back to a specific version:

$ houseplant migrate:down VERSION=20240320123456

Schema Management

Load Schema

To load existing schema migrations into the database without applying them:

$ houseplant db:schema:load

This is useful when setting up a new environment where the database and tables already exist.

Update Schema

The schema file (ch/schema.sql) is automatically updated after each migration. It contains:

  • Table definitions

  • Materialized view definitions

  • Dictionary definitions

Environment Support

Houseplant supports different environments through the HOUSEPLANT_ENV environment variable:

  • development (default)

  • test

  • production

Set the environment before running commands:

$ HOUSEPLANT_ENV=production houseplant migrate

Configuration

Houseplant uses the following environment variables:

  • HOUSEPLANT_ENV: The current environment (default: “development”)

  • CLICKHOUSE_HOST: ClickHouse server host

  • CLICKHOUSE_PORT: ClickHouse server port

  • CLICKHOUSE_USER: ClickHouse username

  • CLICKHOUSE_PASSWORD: ClickHouse password

  • CLICKHOUSE_DB: ClickHouse database name