pipes-deploy-clickhouse-local

Pipes: ClickHouse Local Deployer

Safety Notice

This listing is imported from skills.sh public index metadata. Review upstream SKILL.md and repository scripts before running.

Copy this and send it to your AI assistant to learn

Install skill "pipes-deploy-clickhouse-local" with this command: npx skills add subsquid-labs/agent-skills/subsquid-labs-agent-skills-pipes-deploy-clickhouse-local

Pipes: ClickHouse Local Deployer

Specialized agent for deploying Subsquid Pipes indexers to local ClickHouse instances running in Docker.

When to Use This Skill

Activate when:

  • User wants to test indexer locally before production

  • User needs local development environment

  • User mentions "test locally", "docker", or "local ClickHouse"

Your Role

Deploy indexers to local ClickHouse by:

  • Setting up/validating Docker ClickHouse container

  • Creating databases

  • Configuring indexer for local deployment

  • Running migrations and starting sync

  • Verifying data flow

  • Setting up MCP for queries

Pre-Deployment Checklist

Required Information

Docker Container Details

CONTAINER_NAME: [e.g., "clickhouse" or auto-detect] CLICKHOUSE_PASSWORD: [from container or "default" for new] DATABASE_NAME: [e.g., "pipes"]

Indexer Details

PROJECT_PATH: [path to indexer project] START_BLOCK: [block number to start from]

Validation Questions

Do you have Docker installed and running?

  • Check: docker ps

  • If not: Install Docker Desktop

Do you have an existing ClickHouse container?

  • Check: docker ps | grep clickhouse

  • If yes: Reuse it

  • If no: Create new one

What database name should we use?

  • Default: "pipes"

  • Dedicated per indexer: Recommended for clarity

Deployment Workflow

Step 1: Check/Setup ClickHouse Container

Check for existing ClickHouse

EXISTING_CONTAINER=$(docker ps --filter "name=clickhouse" --format "{{.Names}}" | head -n 1)

if [ -z "$EXISTING_CONTAINER" ]; then echo "No ClickHouse found, creating new container..."

Create new container

docker run -d
--name clickhouse
-p 8123:8123
-p 9000:9000
-e CLICKHOUSE_PASSWORD=default
-e CLICKHOUSE_USER=default
clickhouse/clickhouse-server:latest

CONTAINER_NAME="clickhouse" CLICKHOUSE_PASSWORD="default"

Wait for container to be ready

sleep 5

else echo "Using existing container: $EXISTING_CONTAINER" CONTAINER_NAME=$EXISTING_CONTAINER

Get password from container

CLICKHOUSE_PASSWORD=$(docker inspect $CONTAINER_NAME |
grep -A 10 "Env" | grep CLICKHOUSE_PASSWORD |
cut -d'=' -f2 | tr -d '",')

If no password found, assume "default"

if [ -z "$CLICKHOUSE_PASSWORD" ]; then CLICKHOUSE_PASSWORD="default" fi fi

echo "Container: $CONTAINER_NAME" echo "Password: $CLICKHOUSE_PASSWORD"

Step 2: Verify Container Health

Test connection

docker exec $CONTAINER_NAME clickhouse-client
--password "$CLICKHOUSE_PASSWORD"
--query "SELECT 1"

Expected output: 1

If error: Container may not be ready, wait and retry

Step 3: Create Database

Create database

docker exec $CONTAINER_NAME clickhouse-client
--password "$CLICKHOUSE_PASSWORD"
--query "CREATE DATABASE IF NOT EXISTS $DATABASE_NAME"

Verify database exists

docker exec $CONTAINER_NAME clickhouse-client
--password "$CLICKHOUSE_PASSWORD"
--query "SHOW DATABASES" | grep $DATABASE_NAME

Step 4: Clear Sync Table (If Reusing Database)

If deploying new indexer to existing database

docker exec $CONTAINER_NAME clickhouse-client
--password "$CLICKHOUSE_PASSWORD"
--query "DROP TABLE IF EXISTS $DATABASE_NAME.sync"

echo "Sync table cleared - indexer will start from configured block"

Step 5: Configure Indexer

Update .env file in project:

cd $PROJECT_PATH

Update .env with local configuration

cat > .env << EOF CLICKHOUSE_URL=http://localhost:8123 CLICKHOUSE_DATABASE=$DATABASE_NAME CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=$CLICKHOUSE_PASSWORD EOF

echo ".env configured for local deployment"

Step 6: Validate Table Names

cd $PROJECT_PATH

Extract schema table names

grep "CREATE TABLE" migrations/.sql |
awk '{print $3}' | sed 's/..//' | sort > /tmp/schema_tables.txt

Extract code table references

grep -rh "INSERT INTO|FROM |DELETE FROM" src/ |
grep -oE "(FROM|INTO) [a-z_.]+" | awk '{print $2}' |
sed 's/.*.//' | sort -u > /tmp/code_tables.txt

Compare

DIFF_OUTPUT=$(diff /tmp/schema_tables.txt /tmp/code_tables.txt)

if [ -n "$DIFF_OUTPUT" ]; then echo "Table name mismatches found:" echo "$DIFF_OUTPUT" exit 1 else echo "Table names validated" fi

Step 7: Start Indexer

cd $PROJECT_PATH

Start in background

bun run dev 2>&1 | tee indexer.log & INDEXER_PID=$!

echo "Indexer started (PID: $INDEXER_PID)" echo "Logs: tail -f $PROJECT_PATH/indexer.log"

CRITICAL: Check first log line for start block:

Watch logs

tail -f indexer.log | grep -m 1 "indexing from"

Expected: "Start indexing from [your-start-block]"

Wrong: "Resuming from [different-block]"

If wrong block:

  • Kill indexer: kill $INDEXER_PID

  • Clear sync table (Step 4)

  • Restart (Step 7)

Step 8: Verify Data Flow (30-Second Check)

Wait 30 seconds

sleep 30

Check row count

ROW_COUNT=$(docker exec $CONTAINER_NAME clickhouse-client
--password "$CLICKHOUSE_PASSWORD"
--database "$DATABASE_NAME"
--query "SELECT COUNT(*) FROM $MAIN_TABLE")

if [ "$ROW_COUNT" -gt 0 ]; then echo "Data flowing: $ROW_COUNT events indexed" else echo "No data yet - checking logs..." tail -20 indexer.log | grep -i error fi

Step 9: Sample Data Quality

Get sample data

docker exec $CONTAINER_NAME clickhouse-client
--password "$CLICKHOUSE_PASSWORD"
--database "$DATABASE_NAME"
--query "SELECT * FROM $MAIN_TABLE LIMIT 3 FORMAT Vertical"

Validate:

- Addresses are valid (0x... format)

- Amounts are reasonable

- Timestamps are correct

- All fields populated

Step 10: Setup MCP Access

Configure MCP for local ClickHouse

claude mcp add -t stdio
-e CLICKHOUSE_HOST=localhost
-e CLICKHOUSE_PORT=8123
-e CLICKHOUSE_USER=default
-e CLICKHOUSE_PASSWORD="$CLICKHOUSE_PASSWORD"
-e CLICKHOUSE_SECURE=false
-e CLICKHOUSE_DATABASE="$DATABASE_NAME"
-- clickhouse /path/to/.local/bin/mcp-clickhouse

echo "MCP configured - restart Claude Code to use"

Success Criteria

Deployment successful when:

  • Docker container running

  • Database created

  • Indexer starts from correct block

  • Data appears within 30 seconds

  • Data quality looks good

  • MCP configured for queries

Output Format

After successful deployment:

Local Deployment Summary

ClickHouse Container

  • Container: $CONTAINER_NAME
  • Status: Running
  • Ports: 8123 (HTTP), 9000 (Native)
  • Password: $CLICKHOUSE_PASSWORD

Database

  • Name: $DATABASE_NAME
  • Tables: [list of tables created]

Indexer Status

  • Project: $PROJECT_PATH
  • Start Block: $START_BLOCK
  • Current Block: [latest-block]
  • Events Indexed: [count]
  • Status: Syncing
  • PID: $INDEXER_PID

Quick Commands

Check Sync Status

docker exec $CONTAINER_NAME clickhouse-client \
  --password "$CLICKHOUSE_PASSWORD" \
  --database "$DATABASE_NAME" \
  --query "SELECT COUNT(*) as events, MAX(block_number) as latest_block FROM $MAIN_TABLE"

View Logs

tail -f $PROJECT_PATH/indexer.log

Stop Indexer

kill $INDEXER_PID

Query Data (MCP)

# Restart Claude Code, then:
mcp__clickhouse__run_select_query("SELECT * FROM $DATABASE_NAME.$MAIN_TABLE LIMIT 10")

Monitoring

Monitor Sync Progress

watch -n 5 "docker exec $CONTAINER_NAME clickhouse-client \
  --password '$CLICKHOUSE_PASSWORD' \
  --database '$DATABASE_NAME' \
  --query 'SELECT COUNT(*) as events, MAX(block_number) as block FROM $MAIN_TABLE'"

Check Performance

docker exec $CONTAINER_NAME clickhouse-client \
  --password "$CLICKHOUSE_PASSWORD" \
  --query "
SELECT
  table,
  formatReadableSize(sum(bytes)) as size,
  formatReadableQuantity(sum(rows)) as rows
FROM system.parts
WHERE database = '$DATABASE_NAME' AND active
GROUP BY table
"

## Troubleshooting

### Container Won't Start

**Error**: `docker: Error response from daemon: port is already allocated`

**Solution**: Use existing container or stop the conflicting one:
```bash
# Find conflicting process
lsof -i :8123

# Stop existing ClickHouse container
docker stop clickhouse
docker rm clickhouse

# Start new container
[Step 1]

Authentication Failed

Error: Authentication failed: password is incorrect

Solution: Get correct password from container:

docker inspect $CONTAINER_NAME | grep CLICKHOUSE_PASSWORD

# Update .env with correct password

Database Doesn't Exist

Error: Database $DATABASE_NAME does not exist

Solution: Run Step 3 (Create Database)

Wrong Start Block

Error: Indexer says "Resuming from X" instead of your start block

Solution: Run Step 4 (Clear Sync Table)

Zero Data After 30 Seconds

Error: COUNT(*) returns 0

Investigation:

# Check logs for errors
tail -50 indexer.log | grep -i error

# Verify container is running
docker ps | grep clickhouse

# Test database connection
docker exec $CONTAINER_NAME clickhouse-client \
  --password "$CLICKHOUSE_PASSWORD" \
  --query "SELECT 1"

Best Practices

1. Use Dedicated Databases

# Instead of sharing "pipes" database:
DATABASE_NAME="uniswap_base"      # For Uniswap Base indexer
DATABASE_NAME="morpho_ethereum"   # For Morpho Ethereum indexer

# Benefits:
# - No sync table conflicts
# - Easier to drop/recreate
# - Clear data organization

2. Named Containers

# Use descriptive container names:
docker run -d --name clickhouse-indexers ...
docker run -d --name clickhouse-dev ...
docker run -d --name clickhouse-test ...

3. Volume Persistence

# Add volume for data persistence:
docker run -d \
  --name clickhouse \
  -p 8123:8123 \
  -p 9000:9000 \
  -v clickhouse-data:/var/lib/clickhouse \
  -e CLICKHOUSE_PASSWORD=default \
  clickhouse/clickhouse-server:latest

4. Resource Limits

# For resource-intensive indexers:
docker run -d \
  --name clickhouse \
  --memory=4g \
  --cpus=2 \
  -p 8123:8123 \
  -e CLICKHOUSE_PASSWORD=default \
  clickhouse/clickhouse-server:latest

Related Skills

- pipes-deploy-clickhouse-cloud - Cloud deployment

- DEPLOYMENT_OPTIONS.md - Railway and other deployment options

- pipes-new-indexer - Create indexers

- pipes-troubleshooting - Fix errors

Official Subsquid Documentation

- llms.txt - Quick local deployment reference

- skill.md - Docker and local development guide

Source Transparency

This detail page is rendered from real SKILL.md content. Trust labels are metadata-based hints, not a safety guarantee.

Open in GitHubOpen in ClawHub

Related Skills

Related by shared tags or category signals.

Coding

pipes-deploy-clickhouse-cloud

No summary provided by upstream source.

Repository SourceNeeds Review
3-subsquid-labs
Automation

pipes-new-indexer

No summary provided by upstream source.

Repository SourceNeeds Review
4-subsquid-labs
Automation

pipes-performance

No summary provided by upstream source.

Repository SourceNeeds Review
4-subsquid-labs
Automation

pipes-troubleshooting

No summary provided by upstream source.

Repository SourceNeeds Review
3-subsquid-labs