Migrations

Migrations change the data of an application's entities in a controlled, versioned way: seed rows, backfill a field, remove rows, or copy data from one entity to another. They are written in a SQL-like syntax and executed automatically during kinotic sync.

An entity's schema is not what a migration changes. The entity class is the schema: kinotic sync publishes every entity before it runs the migrations, and publishing creates or updates the entity's backing store. A migration only moves data within that store.

File Naming

Migration files follow a strict naming convention:

V<number>__<description>.sql

Examples:

• V1__seed_roles.sql
• V2__backfill_status.sql
• V3__copy_legacy_orders.sql

The version number determines execution order. The double underscore (__) separates the version from the description.

How It Works

1. Entities are published, so every entity a migration names has its backing store
2. Migration files are discovered and sorted by version number
3. Only unapplied migrations are parsed, resolved and executed
4. Each migration is recorded after successful execution to prevent re-runs
5. Migrations run during kinotic sync

A migration names entities, never storage. INSERT INTO Person addresses the Person entity of the application being synchronized, and the platform resolves that name to the entity's backing store when the migration runs. A name that is not one of the application's published entities fails the run, as does a statement that is not one of the data statements below.

A migration file containing a syntax error fails the entire migration run — nothing from that file is applied and the file is not recorded. The error names the file, line, and offending token. A migration run is resolved as a whole before anything executes, so an unknown entity in the last file stops the first file from running too.

Example Migration

-- V1__seed_roles.sql
INSERT INTO Role (id, tenantId, name) VALUES ('role-admin', 'acme', 'Administrator') WITH REFRESH;
INSERT INTO Role (id, tenantId, name) VALUES ('role-member', 'acme', 'Member') WITH REFRESH;

Supported Statements

A migration uses the four data statements. Each names an entity of the application. Keywords match in any case, but entity and field names are case-sensitive and must be written the way the entity class spells them: Person and firstName, not person and FIRSTNAME. An entity name in the wrong case fails the run, naming the entity meant; a field name in the wrong case is not an error, so an UPDATE or DELETE that uses one changes no rows. See Case.

INSERT INTO

Add a row to an entity.

INSERT INTO Person (id, tenantId, firstName, lastName, age) VALUES ('p-1', 'acme', 'Jane', 'Doe', 28) WITH REFRESH;

The row has to carry the entity's id field, and on a multi-tenant entity its tenant field, with the same names the entity class gives them. The platform stores the row the way the entity's repository would have, so it is found by id and by tenant afterwards:

• The id field — the entity's @Id or @AutoGeneratedId field. A migration cannot have an id generated for it; every row states its own.
• The tenant field — on an entity whose multi-tenancy type is SHARED, the field holding its tenant: the entity's own @TenantId field where it declares one, otherwise the platform's tenant field, tenantId by default. A migration runs for the organization, not for a tenant, so every row says which tenant it belongs to.

Composite fields take an object literal, and a list of them an array of object literals:

INSERT INTO Person (id, tenantId, firstName, address, tags) VALUES (
    'p-1',
    'acme',
    'Jane',
    { street: '1 Main St', city: 'Springfield', zip: '11111' },
    [ { label: 'Role', value: 'admin' }, { label: 'Team', value: 'platform' } ]
) WITH REFRESH;

An application's migration leaves ROUTING and DOCUMENT_ID to the platform, which derives both from the row's id and tenant fields.

UPDATE ... SET ... WHERE

Modify existing rows.

UPDATE Person SET active = false WHERE age < 18 WITH REFRESH;

An UPDATE reaches every tenant's rows that match; narrow it with the tenant field when one tenant is meant:

UPDATE Person SET plan = 'trial' WHERE tenantId = 'acme' WITH REFRESH;

A composite field is set from the same literals INSERT uses. An object literal merges into the stored object — recursively, and leaving sub-fields it does not mention alone — so you can change one sub-field by naming only that one. Arrays and scalars are replaced outright:

-- street keeps its stored value; only city changes
UPDATE Person SET address = { city: 'Shelbyville' } WHERE id = 'p-1' WITH REFRESH;

Since omitting a sub-field keeps it, null is what clears one. The literal's nesting addresses the field, so any depth is reachable:

UPDATE Person SET address = { street: null } WHERE id = 'p-1' WITH REFRESH;          -- address.street
UPDATE Person SET address = { coords: { lat: null } } WHERE id = 'p-1' WITH REFRESH; -- address.coords.lat
UPDATE Person SET nickname = null WHERE id = 'p-1' WITH REFRESH;                     -- the whole field

DELETE FROM ... WHERE

Remove rows matching a condition. Like UPDATE, it reaches every tenant unless the condition names one.

DELETE FROM Person WHERE active = false WITH REFRESH;

REINDEX

Copy rows from one entity to another, for example when a field changes type and the entity is republished beside the old one.

REINDEX LegacyOrder INTO Order WITH (SKIP_IF_NO_SOURCE = TRUE);

Both names are entities of the application. The rows are copied as stored, so their ids and tenants travel with them.

A SCRIPT transforms each row as it is copied, in Painless. The row is ctx._source, and ctx.op set to 'noop' skips it or 'delete' removes it from the destination:

REINDEX LegacyOrder INTO Order WITH (
    SCRIPT = 'ctx._source.total = Double.parseDouble(ctx._source.total); if (ctx._source.status == "void") { ctx.op = "noop" }',
    SKIP_IF_NO_SOURCE = TRUE
);

The script reaches ctx only as ctx._source or ctx.op, and does not call metadata(); a row's index, id and routing are the platform's. A migration whose script does otherwise fails the run.

Best Practices

• Increment version numbers sequentially - Gaps are allowed but order must be maintained
• Use WITH REFRESH on INSERT, UPDATE, and DELETE when subsequent migrations depend on the changed data being immediately visible
• Use SKIP_IF_NO_SOURCE = TRUE on REINDEX operations for idempotent data migrations
• Keep migrations small and focused - One logical change per migration file
• Never modify a migration after it has been applied - Create a new migration instead

For the complete SQL grammar reference, see the Migration SQL Grammar Reference.