scima

Schema migrations tool (Go) targeting multi-database support starting with SAP HANA.

Features (initial)

• Versioned SQL migrations (up/down) stored as files: NNNN_description.up.sql and NNNN_description.down.sql
• Tracks applied versions in a table schema_migrations
• CLI commands: init, status, up, down
• Pluggable dialect interface (HANA first)
• Structured logging and observability hooks

Quick start

# Set environment variables or use flags
scima init --driver hana --dsn "hdb://user:pass@host:30015" --migrations-dir ./migrations
scima up --driver hana --dsn "hdb://user:pass@host:30015" --migrations-dir ./migrations
scima status --driver hana --dsn "hdb://user:pass@host:30015" --migrations-dir ./migrations

# Postgres example
scima up --driver postgres --dsn "postgres://user:pass@localhost:5432/mydb?sslmode=disable" --migrations-dir ./migrations
scima status --driver postgres --dsn "postgres://user:pass@localhost:5432/mydb?sslmode=disable" --migrations-dir ./migrations

Migration files

0010_create_users_table.up.sql
0010_create_users_table.down.sql

You can write portable migrations using schema placeholders that are substituted at runtime:

Placeholder	Description
{{schema}}	Required schema name (error if --schema not provided)
{{schema?}}	Optional schema prefix: becomes schema. when provided, otherwise empty
\\{{schema}}, \\{{schema?}}	Escape sequence: leaves token literal (no substitution)

Examples:

## How schema is used

The `--schema` flag serves two purposes:

1. **Migration tracking table**: The schema is used to qualify the migration bookkeeping table (e.g., `schema_migrations` becomes `<schema>.schema_migrations`).
2. **SQL placeholders**: Any migration SQL file containing the placeholders `{{schema}}` or `{{schema?}}` will have these tokens replaced with the provided schema value (or omitted if not set and using the optional form).

This allows you to:
- Track migrations in a schema-specific table
- Write portable migration SQL that adapts to different schemas without duplicating files

**Escaping placeholders:**
To prevent substitution and keep the literal token in your SQL, prefix the placeholder with a single backslash (e.g., `\{{schema}}`). This is a literal backslash in your SQL file, not Go string escaping.
-- Uses required schema placeholder
CREATE TABLE {{schema}}.users (
	id BIGSERIAL PRIMARY KEY,
	username VARCHAR(255) NOT NULL
);

-- Optional: will create table in default schema if none supplied
CREATE TABLE {{schema?}}audit_log (
	id BIGSERIAL PRIMARY KEY,
	event TEXT NOT NULL
);

-- Escaped tokens remain untouched
-- This will literally create table named {{schema}}.raw_data (assuming dialect allows curly braces)
CREATE TABLE \{{schema}}.raw_data(id INT);
-- Optional escaped
CREATE TABLE \{{schema?}}metrics(id INT);

Run with a schema:

scima up --driver postgres --dsn "$PG_DSN" --schema tenant_a --migrations-dir ./migrations

If {{schema}} appears and --schema is omitted, the command errors.

Future roadmap

Near-term enhancements

• Dialect-specific migrations: for portability you can keep separate directories (e.g. migrations_pg/) when syntax differs (Postgres vs HANA column add syntax). The CLI currently points to one directory; run with --migrations-dir per dialect.

1. HTTP API wrapper: expose endpoints /status, /up, /down allowing remote orchestration; use the same internal migrator package.
2. Multi-tenancy: strategy options
   • Separate schemas/databases per tenant (pass tenant DSN). Maintain a migration state table per tenant.
   • Single database with tenant-specific migration table names: schema_migrations_<tenant>. Provide an abstraction: TenantProvider enumerating active tenants; loop applying migrator logic.
3. Non-SQL migration formats: introduce interface ExecutableMigration allowing Go-based transformations or a declarative YAML -> generated SQL.
4. Additional dialects: PostgreSQL, MySQL, SQLite. Implement their EnsureMigrationTable and DML specifics (placeholder syntax differences).
5. Embedded migrations: use Go 1.22 embed package for packaging migrations into binary; precedence rules between disk and embedded.
6. Observability: add events channel + optional Prometheus counters (scima_migrations_applied_total, timings) and OpenTelemetry tracing around each statement.

Longer-term ideas

• Automatic diff-based migration generation (introspect schema, produce delta SQL).
• Rollback safety analysis (flag irreversible statements like DROP COLUMN without data copy).
• Pluggable concurrency lock (advisory lock or lock table) to prevent double-run.
• Dry-run planner output (list statements without execution).
• Guardrails for production (confirmation prompts, window scheduling).

Development

Run tests:

go test ./...

Run example applying sample migrations (requires valid DSN):

scima status --driver hana --dsn "$HANA_DSN"
scima up --driver hana --dsn "$HANA_DSN"
scima status --driver hana --dsn "$HANA_DSN"

Revert last migration:

scima down --driver hana --dsn "$HANA_DSN" --steps 1

Contributing

PRs welcome. Add tests next to code files (*_test.go).