pillar

# Migrations

Pillar provides a migrations system to help you manage your ClickHouse database schema changes in a version-controlled manner.

## Migration Basics

Migrations are Elixir modules that define schema changes. Each migration has:

- A timestamp prefix for ordering
- An `up` function that applies changes
- An optional `down` function for rollbacks

Pillar automatically tracks which migrations have been applied in a special `pillar_migrations` table.

## Generating Migrations

Use the built-in mix task to generate new migrations:

```bash
mix pillar.gen.migration create_users_table
```

This creates a file in `priv/pillar_migrations` with a timestamp prefix, for example:
`priv/pillar_migrations/20250528120000_create_users_table.exs`

The generated file looks like:

```elixir
defmodule Pillar.Migrations.CreateUsersTable do
  def up do
    # Your migration SQL goes here
  end

  def down do
    # Optional: code to roll back this migration
  end
end
```

## Writing Migrations

### Single Statement Migration

For simple migrations with a single SQL statement:

```elixir
defmodule Pillar.Migrations.CreateUsersTable do
  def up do
    """
    CREATE TABLE IF NOT EXISTS users (
      id UInt64,
      name String,
      email String,
      created_at DateTime
    ) ENGINE = MergeTree()
    ORDER BY id
    """
  end

  def down do
    "DROP TABLE IF EXISTS users"
  end
end
```

### Multi-Statement Migration

For more complex migrations requiring multiple SQL statements, return a list of strings:

```elixir
defmodule Pillar.Migrations.CreateAnalyticsTables do
  def up do
    [
      """
      CREATE TABLE IF NOT EXISTS page_views (
        user_id UInt64,
        page_url String,
        timestamp DateTime
      ) ENGINE = MergeTree()
      ORDER BY (timestamp, user_id)
      """,
      
      """
      CREATE TABLE IF NOT EXISTS user_sessions (
        session_id String,
        user_id UInt64,
        start_time DateTime,
        duration UInt32
      ) ENGINE = MergeTree()
      ORDER BY (start_time, session_id)
      """
    ]
  end

  def down do
    [
      "DROP TABLE IF EXISTS user_sessions",
      "DROP TABLE IF EXISTS page_views"
    ]
  end
end
```

### Dynamic Migrations

You can also generate migrations dynamically:

```elixir
defmodule Pillar.Migrations.CreateShardedTables do
  def up do
    Enum.map(0..4, fn shard ->
      """
      CREATE TABLE IF NOT EXISTS events_shard_#{shard} (
        id UUID,
        user_id UInt64,
        event_type String,
        created_at DateTime
      ) ENGINE = MergeTree()
      ORDER BY (created_at, id)
      """
    end)
  end

  def down do
    Enum.map(0..4, fn shard ->
      "DROP TABLE IF EXISTS events_shard_#{shard}"
    end)
  end
end
```

## Running Migrations

To run migrations, you'll need to create a mix task:

```elixir
defmodule Mix.Tasks.Clickhouse.Migrate do
  use Mix.Task
  
  @shortdoc "Runs ClickHouse migrations"
  
  def run(args) do
    # Start necessary applications
    [:postgrex, :ecto, :pillar]
    |> Enum.each(&Application.ensure_all_started/1)
    
    # Parse command-line arguments
    {opts, _, _} = OptionParser.parse(args, strict: [env: :string])
    env = Keyword.get(opts, :env, "dev")
    
    # Get connection URL from config
    url_key = String.to_atom("clickhouse_#{env}_url")
    url = Application.get_env(:my_app, url_key)
    
    # Create connection and run migrations
    conn = Pillar.Connection.new(url)
    
    case Pillar.Migrations.migrate(conn) do
      :ok -> 
        Mix.shell().info("Migrations completed successfully")
      {:error, reason} -> 
        Mix.shell().error("Migration failed: #{inspect(reason)}")
        exit({:shutdown, 1})
    end
  end
end
```

Then run the migrations with:

```bash
mix clickhouse.migrate
# Or with environment specification
mix clickhouse.migrate --env=prod
```

## Migration Tracking

Pillar automatically tracks applied migrations in a table called `pillar_migrations` in your ClickHouse database.

This table contains:
- The migration version (derived from the timestamp)
- The migration name
- When the migration was applied

You can query this table to see which migrations have been applied:

```sql
SELECT * FROM pillar_migrations ORDER BY version
```

## Rollbacks

To implement rollbacks, create another mix task:

```elixir
defmodule Mix.Tasks.Clickhouse.Rollback do
  use Mix.Task
  
  @shortdoc "Rolls back ClickHouse migrations"
  
  def run(args) do
    # Start necessary applications
    [:postgrex, :ecto, :pillar]
    |> Enum.each(&Application.ensure_all_started/1)
    
    # Parse command-line arguments
    {opts, _, _} = OptionParser.parse(args, strict: [env: :string, steps: :integer])
    env = Keyword.get(opts, :env, "dev")
    steps = Keyword.get(opts, :steps, 1)
    
    # Get connection URL from config
    url_key = String.to_atom("clickhouse_#{env}_url")
    url = Application.get_env(:my_app, url_key)
    
    # Create connection and run migrations
    conn = Pillar.Connection.new(url)
    
    case Pillar.Migrations.rollback(conn, steps) do
      :ok -> 
        Mix.shell().info("Rollback completed successfully")
      {:error, reason} -> 
        Mix.shell().error("Rollback failed: #{inspect(reason)}")
        exit({:shutdown, 1})
    end
  end
end
```

Then roll back the most recent migration with:

```bash
mix clickhouse.rollback
# Or roll back multiple migrations
mix clickhouse.rollback --steps=3
```

## Best Practices

1. **Keep migrations immutable** after they've been applied to production
2. **Test migrations** thoroughly in development and staging environments
3. **Include both `up` and `down` functions** for all migrations
4. **Use appropriate ClickHouse engines** based on your query patterns
5. **Comment complex migrations** to explain the purpose and approach
6. **Consider data preservation** when altering tables
7. **Avoid long-running migrations** in production