Schema Tools - Cadence

Cadence provides command-line tools for managing database schemas across Cassandra, MySQL, PostgreSQL, and other SQL databases. These tools handle initial setup, version management, and schema migrations.

Overview

The schema tools consist of:

cadence-cassandra-tool: Schema management for Cassandra
cadence-sql-tool: Schema management for SQL databases (MySQL, PostgreSQL, etc.)

Both tools support:

Database and keyspace creation
Initial schema setup
Version-controlled schema upgrades
Dry-run mode for testing migrations

Installation

Via Homebrew

brew install cadence-workflow

This installs both cadence-cassandra-tool and cadence-sql-tool. Schema files are located at: /usr/local/etc/cadence/schema/

Via Docker

The tools are included in the server image:

docker run --rm ubercadence/server:master cadence-cassandra-tool --help
docker run --rm ubercadence/server:master cadence-sql-tool --help

Build from Source

make cadence-cassandra-tool
make cadence-sql-tool

Binaries will be in the project root directory.

Cassandra Schema Tool

Initial Database Setup

Create Keyspace

Create keyspaces with appropriate replication:

# Using SimpleStrategy (development)
cadence-cassandra-tool \
  --ep $CASSANDRA_SEEDS \
  create -k cadence --rf 1

cadence-cassandra-tool \
  --ep $CASSANDRA_SEEDS \
  create -k cadence_visibility --rf 1

Production Example with NetworkTopologyStrategy:

cadence-cassandra-tool \
  --ep cassandra1.example.com,cassandra2.example.com \
  create -k cadence --rf 3 \
  --replication-strategy "NetworkTopologyStrategy" \
  --replication-factors "datacenter1:3,datacenter2:3"

Use Cassandra Calculator to determine appropriate replication factor. Cadence uses Quorum consistency for reads and writes.

Initialize Schema

Set up schema version tracking:

# Main schema
cadence-cassandra-tool \
  -ep 127.0.0.1 \
  -k cadence \
  setup-schema -v 0.0

# Visibility schema
cadence-cassandra-tool \
  -ep 127.0.0.1 \
  -k cadence_visibility \
  setup-schema -v 0.0

Apply Schema

Upgrade to the latest version:

# Main schema
cadence-cassandra-tool \
  -ep 127.0.0.1 \
  -k cadence \
  update-schema \
  -d ./schema/cassandra/cadence/versioned

# Visibility schema
cadence-cassandra-tool \
  -ep 127.0.0.1 \
  -k cadence_visibility \
  update-schema \
  -d ./schema/cassandra/visibility/versioned

Schema Upgrades

Dry Run

Test the upgrade without applying changes:

cadence-cassandra-tool \
  -ep 127.0.0.1 \
  -k cadence \
  update-schema \
  -d ./schema/cassandra/cadence/versioned \
  -v 0.40 \
  --dryrun

Apply Specific Version

Upgrade to a specific schema version:

cadence-cassandra-tool \
  -ep 127.0.0.1 \
  -k cadence \
  update-schema \
  -d ./schema/cassandra/cadence/versioned \
  -v 0.40

Version Compatibility

Match schema versions with server versions:

# For Cadence v0.21.3
git checkout v0.21.3
cadence-cassandra-tool \
  -ep 127.0.0.1 \
  -k cadence \
  update-schema \
  -d ./schema/cassandra/cadence/versioned \
  -v 0.21

Command Options

Option	Description
`--ep, --endpoint`	Cassandra seed nodes (comma-separated)
`-k, --keyspace`	Keyspace name
`-p, --port`	Cassandra port (default: 9042)
`-u, --user`	Username for authentication
`-pw, --password`	Password for authentication
`-d, --schema-dir`	Directory containing schema files
`-v, --version`	Target schema version
`--dryrun`	Preview changes without applying
`--rf, --replication-factor`	Replication factor

SQL Schema Tool

Initial Database Setup

Create Databases

Create databases for Cadence:

# MySQL
cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 3306 \
  --plugin mysql \
  create-database --db cadence

cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 3306 \
  --plugin mysql \
  create-database --db cadence_visibility

# PostgreSQL
cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 5432 \
  --plugin postgres \
  create-database --db cadence

cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 5432 \
  --plugin postgres \
  create-database --db cadence_visibility

Initialize Schema

Set up schema version tracking:

# Main schema
cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 3306 \
  --plugin mysql \
  --db cadence \
  setup-schema -v 0.0

# Visibility schema
cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 3306 \
  --plugin mysql \
  --db cadence_visibility \
  setup-schema -v 0.0

Apply Schema

Upgrade to the latest version:

# MySQL v8
cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 3306 \
  --plugin mysql \
  --db cadence \
  update-schema \
  -d ./schema/mysql/v8/cadence/versioned

cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 3306 \
  --plugin mysql \
  --db cadence_visibility \
  update-schema \
  -d ./schema/mysql/v8/visibility/versioned

# PostgreSQL
cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 5432 \
  --plugin postgres \
  --db cadence \
  update-schema \
  -d ./schema/postgres/cadence/versioned

Schema Upgrades

Dry Run

Test the upgrade:

cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 3306 \
  --plugin mysql \
  --db cadence \
  update-schema \
  -d ./schema/mysql/v8/cadence/versioned \
  -v 0.40 \
  --dryrun

Apply Specific Version

cadence-sql-tool \
  --ep $SQL_HOST_ADDR \
  -p 3306 \
  --plugin mysql \
  --db cadence \
  update-schema \
  -d ./schema/mysql/v8/cadence/versioned \
  -v 0.40

Command Options

Option	Description
`--ep, --endpoint`	Database host address
`-p, --port`	Database port (MySQL: 3306, PostgreSQL: 5432)
`--plugin`	Database type (mysql, postgres, etc.)
`--db, --database`	Database name
`-u, --user`	Username (env: SQL_USER)
`-pw, --password`	Password (env: SQL_PASSWORD)
`-d, --schema-dir`	Directory containing schema files
`-v, --version`	Target schema version
`--dryrun`	Preview changes without applying

Schema Directories

Schema files are organized by database and version:

schema/
├── cassandra/
│   ├── cadence/
│   │   └── versioned/
│   │       ├── v0.1/
│   │       ├── v0.2/
│   │       └── v0.40/
│   └── visibility/
│       └── versioned/
├── mysql/
│   └── v8/
│       ├── cadence/
│       │   └── versioned/
│       └── visibility/
│           └── versioned/
└── postgres/
    ├── cadence/
    │   └── versioned/
    └── visibility/
        └── versioned/

Development Workflows

Local Development Setup

Quick setup for local development:

# Using Make (requires Docker)
make install-schema

# Or with environment variables for SQL
SQL_USER=root SQL_PASSWORD=password make install-schema-mysql

Testing Schema Changes

Before applying in production:

Dry Run: Test the migration
Backup: Create database backup
Stage Testing: Apply to staging environment
Validation: Verify schema version and functionality
Production: Apply during maintenance window

Rollback Strategy

Schema changes are forward-only. For rollback:

Restore from backup
Revert to previous Cadence version
Apply matching schema version

Best Practices

Production Deployments

Always dry run migrations first
Backup databases before schema changes
Schedule maintenance for major upgrades
Test in staging with production-like data
Monitor performance during and after migration
Use NetworkTopologyStrategy for Cassandra (not SimpleStrategy)
Set appropriate replication factors (RF=3 recommended)

Version Management

Track schema versions in deployment documentation
Match server and schema versions during upgrades
Update visibility separately from main schema
Version control schema customizations

Security

Use environment variables for credentials
Restrict tool access to database administrators
Audit schema changes in production
Use read-only users for verification

Troubleshooting

Connection Failures

# Verify connectivity
telnet cassandra.example.com 9042
telnet mysql.example.com 3306

# Check credentials
SQL_USER=root SQL_PASSWORD=password cadence-sql-tool --ep localhost -p 3306 --plugin mysql --db cadence update-schema --dryrun -d ./schema/mysql/v8/cadence/versioned

Version Conflicts

Problem: Schema version mismatch Solution:

# Check current version
cadence-cassandra-tool -ep localhost -k cadence show-version

# Apply missing versions in order
cadence-cassandra-tool -ep localhost -k cadence update-schema -d ./schema/cassandra/cadence/versioned -v 0.35
cadence-cassandra-tool -ep localhost -k cadence update-schema -d ./schema/cassandra/cadence/versioned -v 0.40

Failed Migrations

Problem: Migration failed mid-execution Solution:

Check database logs for specific error
Verify schema version table state
Manually fix corrupted state if needed
Restore from backup if necessary
Retry migration

Next Steps

Learn about Web UI for workflow monitoring
Explore Benchmarking for performance testing
Configure Archival for long-term storage
Set up Dynamic Configuration for runtime tuning

Development Tools

Advanced

Documentation Index

​Overview

​Installation

​Via Homebrew

​Via Docker

​Build from Source

​Cassandra Schema Tool

​Initial Database Setup

​Create Keyspace

​Initialize Schema

​Apply Schema

​Schema Upgrades

​Dry Run

​Apply Specific Version

​Version Compatibility

​Command Options

​SQL Schema Tool

​Initial Database Setup

​Create Databases

​Initialize Schema

​Apply Schema

​Schema Upgrades

​Dry Run

​Apply Specific Version

​Command Options

​Schema Directories

​Development Workflows

​Local Development Setup

​Testing Schema Changes

​Rollback Strategy

​Best Practices

​Production Deployments

​Version Management

​Security

​Troubleshooting

​Connection Failures

​Version Conflicts

​Failed Migrations

​Next Steps

Overview

Installation

Via Homebrew

Via Docker

Build from Source

Cassandra Schema Tool

Initial Database Setup

Create Keyspace

Initialize Schema

Apply Schema

Schema Upgrades

Dry Run

Apply Specific Version

Version Compatibility

Command Options

SQL Schema Tool

Initial Database Setup

Create Databases

Initialize Schema

Apply Schema

Schema Upgrades

Dry Run

Apply Specific Version

Command Options

Schema Directories

Development Workflows

Local Development Setup

Testing Schema Changes

Rollback Strategy

Best Practices

Production Deployments

Version Management

Security

Troubleshooting

Connection Failures

Version Conflicts

Failed Migrations

Next Steps