Creating and Managing Patches

Creating and Managing Patches#

Patches are SQL files applied by SchemaPatch sequences. They are used for schema changes after the base schema version.

Patch Structure#

A patch should include:

  • A header describing the patch version and purpose.

  • Idempotent schema changes where practical.

  • A final update to the schema version and clean state stored in basyxsystem.

Example:

-- ============================================================================
-- Project        : Eclipse BaSyx
-- File Type      : SQL Patch Script
-- Patch Version  : 1.0.2
-- Description    : Adds example table indexes.
-- ============================================================================

CREATE INDEX IF NOT EXISTS ix_example_table_name ON example_table(name);

UPDATE basyxsystem
SET schema_version = 'v1.0.2',
    state = 'clean'
WHERE identifier = (
  SELECT identifier
  FROM basyxsystem
  ORDER BY identifier ASC
  LIMIT 1
);

Version Handling Requirements#

The Go code decides whether to execute a patch by comparing:

  • The current schema version from basyxsystem.schema_version.

  • The target version passed to NewSchemaPatch.

The patch SQL file is responsible for updating the database version. The Go patch sequence does not update the version after executing the patch.

This means every patch that changes the schema version must update basyxsystem.schema_version and leave basyxsystem.state as clean.

Registering a Patch#

Patch files are registered explicitly in main.go:

schemInit.Register(steps.NewSchemaPatch(execCtx, filepath.Join(patchBasePath, "1_0_1.sql"), "v1.0.1"))

To add a new patch, register it after older patches. Patch target versions must use semantic versioning:

schemInit.Register(steps.NewSchemaPatch(execCtx, filepath.Join(patchBasePath, "1_0_2.sql"), "v1.0.2"))
schemInit.Register(steps.NewSchemaPatch(execCtx, filepath.Join(patchBasePath, "1_1_0.sql"), "v1.1.0"))

Mandatory Patch Contents#

Every released patch must include:

  • SQL changes required for that version.

  • Safe guards such as IF EXISTS or IF NOT EXISTS where appropriate.

  • A database version update to the target version.

  • A version value matching the target version registered in Go.

Warning

Existing patches must NEVER be modified after release.

Important Patch Disclaimer#

Released patches are part of the migration history. Changing an already released patch can break reproducibility because two installations may both claim to have applied the same patch version while having different database structures.

If a released patch contains an issue, create a new patch that corrects it. Do not edit the old patch.

Why This Rule Matters#

Immutable patches provide:

  • Reproducible migrations across environments.

  • Consistent troubleshooting and support.

  • Deterministic upgrade paths.

  • Clear auditability of database changes.