# Database Versioning

Every Workflow Engine upgrade can bring new tables, columns, or indexes to the persistence schema. Without an automated migration, each
upgrade means finding and running the right SQL script for your provider - and missing a step breaks the runtime.

Since Workflow Engine 13.0.0, the runtime provides automatic database schema migration through the `RunMigrations()` fluent method. Calling
`RunMigrations()` after configuring your persistence provider and before calling `StartAsync()`
creates or upgrades the required tables to match the runtime version.

With `RunMigrations()`, upgrading Workflow Engine is a single line change in your startup code. For example, moving from version 13.0.0 to
14.0.0 requires only updating the NuGet package reference - the migration runs automatically on the next startup.

## What it is

`RunMigrations()` is an extension method on `WorkflowRuntime` that applies the database schema changes needed for the current runtime
version. When you configure a persistence provider for SQL Server, PostgreSQL, MySQL, Oracle, or SQLite, the runtime knows which tables,
indexes, and columns each provider requires. Internally, `RunMigrations()` delegates to FluentMigrator, which tracks applied migrations in a
`VersionInfo` table and applies only the missing ones.

The migration is idempotent - running it multiple times on an up-to-date database produces no errors. During an upgrade from an older
Workflow Engine version, FluentMigrator detects which migration steps have already been applied and runs only the new ones.

**For developers:** Call `RunMigrations()` after `WithPersistenceProvider()` and before `StartAsync()` in your startup sequence. The method
returns the `WorkflowRuntime` instance so you can chain it:

```csharp
var runtime = new WorkflowRuntime()
    .WithPersistenceProvider(new MSSQLProvider(connectionString))
    .RunMigrations();

await runtime.StartAsync();
```

### Supported providers

`RunMigrations()` works with the following persistence providers. Each has its own set of migration scripts with provider-specific SQL
syntax.

Table: Persistence provider migration support

| Provider   | Migration support                                                      |
|------------|------------------------------------------------------------------------|
| SQL Server | Yes - uses `IF NOT EXISTS (...)` guard clauses                         |
| PostgreSQL | Yes - uses `CREATE TABLE IF NOT EXISTS` / `CREATE INDEX IF NOT EXISTS` |
| MySQL      | Yes                                                                    |
| Oracle     | Yes                                                                    |
| SQLite     | Yes                                                                    |
| MongoDB    | No - schema is managed differently                                     |

### Manual SQL scripts

For teams that prefer explicit control, each Workflow Engine NuGet package ships migration scripts for every version gap. These are embedded
SQL files in the provider assembly, versioned and ordered. You can extract and review them before applying, use them in a DBA-managed
maintenance window, or integrate them into an existing database change management process.

## Why it matters

Database Versioning delivers these outcomes:

- **Zero-mistake upgrades** - The runtime applies exactly the schema changes it needs. No risk of forgetting a migration step, running
  scripts in the wrong order, or using the wrong provider variant.

- **Single-step setup** - Initial deployment creates all required tables automatically. No need to hunt for the correct `CREATE TABLE`
  script for your provider.

- **Idempotent by design** - Calling `RunMigrations()` on every startup is safe. If the schema is already current, the method returns
  immediately with no side effects.

- **Manual option for DBAs** - SQL scripts are available in the provider assembly for teams that require explicit review and approval before
  schema changes.

## Who it is for

**Developer:** Call `RunMigrations()` after `WithPersistenceProvider()` in the startup chain. The method is safe to call on every startup.
For production deployments with strict change control, use the embedded SQL scripts instead.

**Enterprise architect:** Migrations use FluentMigrator under the hood with a `VersionInfo` table that tracks applied steps. The same
mechanism supports both automatic and manual workflows, and the migration assembly is provider-specific so each database type receives the
correct SQL syntax.

## When to use it

Use `RunMigrations()` in all environments after configuring the persistence provider. In development and staging, automatic migrations
reduce the risk of missing a step when switching branches or testing upgrades. In production, evaluate whether automatic or manual
migrations suit your deployment process.

Automatic migrations work well when you control the deployment pipeline and can start the application in maintenance mode. Manual migrations
give your DBA team a reviewable SQL script that can be version-controlled alongside application changes and tested against a production
clone.

## How it compares

The table below compares automatic migrations with manual script management.

Table: Comparison of database migration approaches

| Approach                          | What it requires                                                                                        | Result                                                                                             |
|-----------------------------------|---------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------|
| Manual SQL scripts                | Find the correct script for your provider and version gap, run it in the right order, verify it applied | Error-prone, easy to miss or misorder steps, requires DBA involvement for every upgrade            |
| Workflow Engine `RunMigrations()` | One line in startup code - `RunMigrations()` after `WithPersistenceProvider()`                          | Automatic, idempotent schema updates - no missing steps, no ordering errors, safe on every startup |

## Frequently asked questions

**When should I run RunMigrations?**

Call `RunMigrations()` after configuring the persistence provider and before calling `StartAsync()`. Running it on every application startup
is safe because the method is idempotent - it applies only missing migrations and returns immediately if the schema is current.

**Can I run migrations manually instead of using RunMigrations?**

Yes. Each provider assembly contains embedded SQL scripts ordered by version. You can extract them using a reflection tool or access them
through the provider package. Manual migration is the recommended approach for production environments where a DBA must review schema
changes before they are applied.

**Which persistence providers support RunMigrations?**

`RunMigrations()` works with SQL Server, PostgreSQL, MySQL, Oracle, and SQLite. Each provider has its own set of migration scripts with
database-specific SQL syntax. MongoDB manages schema differently and does not support `RunMigrations`.

**What happens if the database schema is newer than the runtime version?**

The runtime does not check for this condition. FluentMigrator applies migrations forward-only; it never reverts. If the database has a
schema from a newer runtime version, `RunMigrations()` runs successfully (no new migrations to apply), but the runtime may encounter
compatibility issues with its older binary. Ensure the runtime version matches or exceeds the schema version.

## See also

- Database Versioning Reference: Detailed migration commands, version tables, and troubleshooting.
- Persistence Providers: Supported databases, connection strings, and setup instructions.