Pulumi ClickHouse Provider

A Pulumi dynamic provider for managing ClickHouse databases and tables with TypeScript support.

Features

• Database Management: Create, update, and delete ClickHouse databases
• Table Management: Full CRUD operations for ClickHouse tables
• Advanced Table Features:
  • Materialized columns
  • Custom engines (MergeTree, etc.)
  • Partitioning and ordering
  • Primary keys and sampling
  • TTL configuration
  • Custom settings
• Safety Features:
  • allowDrop flags to prevent accidental deletions
  • Column protection (prevents removal without explicit permission)
• Effect.ts Integration: Built with Effect for robust error handling and functional programming
• TypeScript Support: Full type safety and IntelliSense

Installation

npm install @anglinb/pulumi-clickhouse
# or
yarn add @anglinb/pulumi-clickhouse
# or
bun add @anglinb/pulumi-clickhouse

Quick Start

import { ClickhouseDatabase, ClickhouseTable } from "@anglinb/pulumi-clickhouse";

// ClickHouse connection configuration
const clickhouseConfig = {
  url: "http://localhost:8123",
  username: "default", 
  password: "your-password",
  database: "system"
};

// Create a database
const myDatabase = new ClickhouseDatabase("my-database", {
  name: "analytics_db",
  engine: "Atomic",
  comment: "Analytics database for user events",
  settings: {
    default_table_engine: "MergeTree"
  },
  allowDrop: true
}, clickhouseConfig);

// Create a table
const eventsTable = new ClickhouseTable("events-table", {
  name: "user_events", 
  databaseName: myDatabase.name,
  engine: "MergeTree()",
  columns: [
    {
      name: "event_id",
      type: "UInt64",
      comment: "Unique event identifier"
    },
    {
      name: "user_id", 
      type: "UInt64",
      comment: "User identifier"
    },
    {
      name: "event_name",
      type: "String",
      comment: "Name of the event"
    },
    {
      name: "timestamp",
      type: "DateTime",
      default: "now()",
      comment: "Event timestamp"
    },
    {
      name: "event_date",
      type: "Date", 
      materialized: "toDate(timestamp)",
      comment: "Materialized date column for partitioning"
    }
  ],
  orderBies: ["timestamp", "user_id"],
  partitionBy: "toYYYYMM(event_date)",
  primaryKeys: ["event_id"],
  allowDrops: true
}, clickhouseConfig);

Configuration

ClickHouse Connection Config

interface ClickhouseProviderConfig {
  url: string;        // ClickHouse HTTP interface URL
  username: string;   // Database username
  password: string;   // Database password  
  database: string;   // Default database (usually "system")
}

Database Management

Creating a Database

const database = new ClickhouseDatabase("resource-name", {
  name: "my_database",
  engine: "Atomic",                    // Database engine
  comment: "Description of database",  // Optional
  settings: {                          // Optional settings
    default_table_engine: "MergeTree"
  },
  allowDrop: true                      // Required for deletion
}, clickhouseConfig);

Database Properties

// Access database properties
const dbName = database.name;        // Output<string>
const dbEngine = database.engine;    // Output<string>
const dbUuid = database.uuid;        // Output<string>
const dbComment = database.comment;  // Output<string>

Table Management

Basic Table Creation

const table = new ClickhouseTable("table-resource", {
  name: "user_data",
  databaseName: "my_database", 
  engine: "MergeTree()",
  columns: [
    { name: "id", type: "UInt64" },
    { name: "name", type: "String" },
    { name: "email", type: "String" }
  ],
  orderBies: ["id"],
  allowDrops: true
}, clickhouseConfig);

Advanced Table Features

const advancedTable = new ClickhouseTable("advanced-table", {
  name: "analytics_events",
  databaseName: "analytics", 
  engine: "MergeTree()",
  columns: [
    {
      name: "event_id",
      type: "UInt64", 
      comment: "Primary identifier"
    },
    {
      name: "user_id",
      type: "UInt64"
    },
    {
      name: "event_time", 
      type: "DateTime",
      default: "now()"
    },
    {
      name: "event_date",
      type: "Date",
      materialized: "toDate(event_time)", // Materialized column
      comment: "Auto-generated date for partitioning"
    },
    {
      name: "properties",
      type: "String"
    }
  ],
  // Table structure
  orderBies: ["event_time", "user_id"],     // ORDER BY clause
  partitionBy: "toYYYYMM(event_date)",      // PARTITION BY
  primaryKeys: ["event_id"],                // PRIMARY KEY
  sampleBy: "event_id",                     // SAMPLE BY
  
  // Performance and storage
  ttl: "event_time + INTERVAL 90 DAY",     // TTL policy
  settings: {                               // Table settings
    "index_granularity": "8192",
    "merge_max_block_size": "8192"
  },
  
  // Metadata and safety
  comment: "User analytics events table",
  allowDrops: true,                         // Allow destructive operations
  
  // Optional cluster support
  clusterName: "my_cluster"                 // For distributed setups
}, clickhouseConfig);

Table Properties

// Access table properties
const tableName = table.name;           // Output<string>
const tableDatabase = table.databaseName; // Output<string>
const tableEngine = table.engine;       // Output<string>
const tableColumns = table.columns;     // Output<TableColumn[]>
const tableUuid = table.uuid;          // Output<string>

Column Types and Features

Basic Columns

{
  name: "user_id",
  type: "UInt64",
  comment: "User identifier"
}

Default Values

{
  name: "created_at", 
  type: "DateTime",
  default: "now()",
  comment: "Creation timestamp"
}

Materialized Columns

{
  name: "user_name_upper",
  type: "String", 
  materialized: "upper(user_name)",
  comment: "Uppercase version of user name"
}

Safety Features

Database Protection

const protectedDb = new ClickhouseDatabase("protected-db", {
  name: "important_data",
  engine: "Atomic",
  allowDrop: false  // Prevents accidental deletion
}, config);

// This will fail:
// protectedDb.delete() -> Error: allowDrop must be set to true

Column Protection

const protectedTable = new ClickhouseTable("protected-table", {
  name: "critical_data",
  databaseName: "production",
  engine: "MergeTree()",
  columns: [
    { name: "id", type: "UInt64" },
    { name: "data", type: "String" }
  ],
  allowDrops: false  // Prevents column removal/modification
}, config);

// Removing columns will require allowDrops: true

Error Handling

The provider includes comprehensive error logging with SQL context:

// Errors include the full SQL query and ClickHouse error details
try {
  const table = new ClickhouseTable("bad-table", {
    name: "invalid-name!",  // Invalid character
    databaseName: "nonexistent",
    engine: "InvalidEngine",
    columns: []
  }, config);
} catch (error) {
  console.log(error.message);
  // Output:
  // ClickHouse query failed
  // Query: CREATE TABLE `nonexistent`.`invalid-name!` ...
  // Cause: DB::Exception: Invalid table name...
}

Development

Building

# Build the library
bun run build

# Build just TypeScript 
bun run build:lib

# Build just Effect client
bun run build:effect

# Watch mode
bun run build:watch

Testing

# Run tests (requires ClickHouse running)
bun run test

# Run specific test file
bun vitest run ./test/clickhouse-table.test.ts

# Watch mode
bun run test:watch

ClickHouse Setup

For development, start ClickHouse with Docker:

# Start ClickHouse
bun run docker:up

# Stop ClickHouse  
bun run docker:down

Or use the provided docker-compose.yml:

version: '3.8'
services:
  clickhouse:
    image: clickhouse/clickhouse-server:latest
    ports:
      - "8123:8123"  # HTTP interface
      - "9000:9000"  # Native interface
    environment:
      CLICKHOUSE_PASSWORD: clickhouse
    volumes:
      - clickhouse_data:/var/lib/clickhouse

volumes:
  clickhouse_data:

Examples

See the /examples directory for complete working examples:

• Basic database and table creation
• Advanced table features
• Production configurations
• Error handling patterns

Requirements

• Node.js 18+
• ClickHouse 21.3+
• Pulumi 3.0+

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Ensure all tests pass: bun run test
6. Submit a pull request

License

MIT

Support

For issues and questions:

• Open an issue on GitHub
• Check the examples directory
• Review the test files for usage patterns