Migrations

Migrations let you evolve your data schema in a controlled, versioned way. They are written in a SQL-like syntax and executed automatically during kinotic sync.

File Naming

Migration files follow a strict naming convention:

V<number>__<description>.sql

Examples:

• V1__initial_schema.sql
• V2__add_email_field.sql
• V3__create_sensor_readings.sql

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

How It Works

1. Migration files are discovered and sorted by version number
2. Only unapplied migrations are parsed and executed
3. Each migration is recorded after successful execution to prevent re-runs
4. Migrations run during kinotic sync

Example Migration

-- V1__initial_schema.sql
CREATE TABLE users (name TEXT, age INTEGER, active BOOLEAN);

Supported Statements

CREATE TABLE

Create a new table with field mappings.

CREATE TABLE users (name TEXT, age INTEGER, active BOOLEAN);

CREATE COMPONENT TEMPLATE

Define reusable templates with settings that can be applied to multiple tables.

CREATE COMPONENT TEMPLATE my_settings WITH (number_of_shards = 1, number_of_replicas = 0);

CREATE INDEX TEMPLATE

Create templates that automatically apply to tables matching a name pattern.

CREATE INDEX TEMPLATE my_template MATCHING (logs-*) COMPOSED OF (my_settings);

ALTER TABLE ADD COLUMN

Add new fields to an existing table. Composite types are supported — you can add an OBJECT, NESTED, or UNION column just like any other type.

ALTER TABLE users ADD COLUMN email TEXT;
ALTER TABLE users ADD COLUMN address OBJECT (street TEXT, city KEYWORD, zip KEYWORD);

Composite Column Types

In addition to scalar types, columns can be structured objects.

OBJECT — a single embedded sub-document:

CREATE TABLE persons (
    id KEYWORD,
    address OBJECT (street TEXT, city KEYWORD, state KEYWORD, zip KEYWORD)
);

NESTED — an array of independently-queryable sub-documents. Use this when a field holds a list of items and you need to filter on individual list entries without cross-element matching:

CREATE TABLE articles (
    id KEYWORD,
    tags NESTED (label TEXT, value KEYWORD)
);

UNION — a field that can hold one of several named object variants. All variant fields are merged into a single object. Include a shared field (e.g. kind KEYWORD) in each variant so your application can identify which variant it received at runtime:

CREATE TABLE assets (
    id KEYWORD,
    item UNION (
        Book  (kind KEYWORD, title TEXT, isbn KEYWORD),
        Video (kind KEYWORD, title TEXT, duration INTEGER)
    )
);

All composite types support NOT INDEXED (except NESTED) to store data without making it searchable. They also support recursive nesting — an OBJECT inside a NESTED, for example, is valid.

For the complete syntax and options, see the Migration SQL Grammar Reference.

REINDEX

Migrate data between tables with advanced options.

REINDEX FROM old_index INTO new_index WITH (SKIP_IF_NO_SOURCE = TRUE);

INSERT INTO

Add records to a table.

INSERT INTO users (name, age, active) VALUES ('Jane', 28, TRUE) WITH REFRESH;

UPDATE ... SET ... WHERE

Modify existing records.

UPDATE users SET active = FALSE WHERE age < 18 WITH REFRESH;

DELETE FROM ... WHERE

Remove records matching a condition.

DELETE FROM users WHERE active = FALSE WITH REFRESH;

Best Practices

• Increment version numbers sequentially - Gaps are allowed but order must be maintained
• Use IF NOT EXISTS where available - Makes migrations safe to re-run if the tracking state is reset
• 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.