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.

ALTER TABLE users ADD COLUMN email TEXT;

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.