Migration SQL Grammar

Overview

This grammar reference applies to migration scripts used for schema and data migrations in Kinotic. Migration scripts use a SQL dialect designed for schema and data management.

All statements must end with a semicolon (;). Identifiers must start with a letter or underscore and can contain letters, numbers, and underscores. Strings are enclosed in single quotes ('...').

Statements Overview

• CREATE TABLE
• CREATE DATA STREAM
• CREATE COMPONENT TEMPLATE
• CREATE INDEX TEMPLATE
• REINDEX
• INSERT
• UPDATE
• DELETE
• Comments

CREATE TABLE

Creates an Elasticsearch index with the specified field mappings.

Syntax:

CREATE TABLE [IF NOT EXISTS] <index_name> (<column_name> <type> [, <column_name> <type>]*) ;

• IF NOT EXISTS (optional): Only create the index if it does not already exist.
• <type>: See Supported Types.

Example:

CREATE TABLE IF NOT EXISTS products (
    name TEXT,
    sku KEYWORD,
    price DOUBLE,
    inStock BOOLEAN,
    createdAt DATE
) ;

CREATE DATA STREAM

Creates an Elasticsearch data stream — an append-only, time-series store whose backing indices roll over automatically. Unlike CREATE TABLE, which creates a single index, this creates a data-stream-backing index template (matching the stream name) and then materializes the stream.

A managed @timestamp date field is always added to the mapping; every document written to a data stream must carry it. The declared columns use dynamic: strict mappings, the same as CREATE TABLE.

Syntax:

CREATE DATA STREAM <stream_name> (<column_name> <type> [, <column_name> <type>]*)
    [WITH (<option> [, <option>]*)] ;

Options:

<type>: See Supported Types.

Every data stream document must carry a @timestamp date field, which Elasticsearch requires and which is added to the mapping automatically — you do not (and cannot) declare it as a column. There are two ways to supply it at write time:

• Without TIME_REFERENCE — documents carry @timestamp directly (for a Java DTO, map a field with @JsonProperty("@timestamp")).
• With TIME_REFERENCE = '<col>' — the named column is the natural time field your code works with, and @timestamp is duplicated from it on write. Use this for streams backing entities whose time field carries a @TimeReference, so the stored document keeps both the friendly column (queryable by its own name) and @timestamp.

Example:

-- raw stream: writers provide @timestamp directly
CREATE DATA STREAM events (
    level   KEYWORD,
    message TEXT
) WITH (DATA_RETENTION = '30d') ;

-- entity-style stream: eventTime is the natural field, @timestamp rides alongside it
CREATE DATA STREAM sensor_readings (
    id        KEYWORD,
    eventTime DATE,
    value     DOUBLE
) WITH (DATA_RETENTION = '30d', TIME_REFERENCE = 'eventTime') ;

CREATE COMPONENT TEMPLATE

Creates a reusable component template that can be referenced by index templates.

Syntax:

CREATE COMPONENT TEMPLATE <template_name> (<definition> [, <definition>]*) ;

Definitions:

Example:

CREATE COMPONENT TEMPLATE base_settings (
    NUMBER_OF_SHARDS = 3,
    NUMBER_OF_REPLICAS = 1,
    createdAt DATE,
    updatedAt DATE
) ;

CREATE INDEX TEMPLATE

Creates an index template that applies settings and mappings to indices matching a pattern.

Syntax:

CREATE INDEX TEMPLATE <template_name> FOR '<pattern>' USING '<component_template>'
    [WITH (<definition> [, <definition>]*)] ;

• WITH (...) (optional): Additional definitions as in component templates.

Example:

CREATE INDEX TEMPLATE logs_template FOR 'logs-*' USING 'base_settings'
    WITH (NUMBER_OF_REPLICAS = 2, level KEYWORD) ;

REINDEX

Copies documents from one index to another with optional transformations.

Syntax:

REINDEX <source_index> INTO <dest_index> [WITH (<option> [, <option>]*)] ;

Options:

Example:

REINDEX old_products INTO new_products WITH (
    CONFLICTS = PROCEED,
    SLICES = AUTO,
    QUERY = 'status:active',
    SKIP_IF_NO_SOURCE = TRUE
) ;

If SKIP_IF_NO_SOURCE = TRUE, the reindex operation will be skipped (no error) if the source index does not exist. This is useful for idempotent migrations.

INSERT

Inserts a document into an index.

Syntax:

INSERT INTO <index_name> [(<column_name> [, <column_name>]*)]
    VALUES (<value> [, <value>]*) [WITH <option> [, <option>]*] ;

Options may appear in any order:

• REFRESH: Immediately refresh the index after insert, making the document searchable.
• ROUTING '<value>': Index the document under this routing value. A read that routes by the same value reaches it; without this the document is routed by its _id and lands on whichever shard that hashes to.
• DOCUMENT_ID '<value>': Index the document under this _id, instead of the value of the id column.

<value>: See Values — a literal, a parameter (:name), an object literal, or an array literal. A null stores no field at all, leaving the document the same as one whose column list never named it.

Example:

INSERT INTO products (name, sku, inStock)
    VALUES ('Widget', 'WDG-001', true) WITH REFRESH ;

Seeding a row that a reader addresses by a composite _id and reaches with a routed search needs both options, since the statement has no knowledge of how the row will be read:

INSERT INTO kinotic_application (id, organizationId, name)
    VALUES ('atlas-crm', 'kinotic-test', 'Atlas CRM')
    WITH REFRESH, ROUTING 'kinotic-test', DOCUMENT_ID 'kinotic-test-atlas-crm' ;

Inserting composite columns

OBJECT, NESTED, UNION, GEO_POINT, GEO_SHAPE, and JSON columns take an object literal — { field: value, ... } — as their value. A NESTED column holds a list, so its value is an array literal of object literals. Object and array literals nest to any depth.

CREATE TABLE persons (
    id      KEYWORD,
    address OBJECT (street TEXT, city KEYWORD, coords OBJECT (lat DOUBLE, lon DOUBLE)),
    tags    NESTED (label TEXT, value KEYWORD)
) ;

INSERT INTO persons (id, address, tags) VALUES (
    'p-1',
    { street: '1 Main St', city: 'Springfield', coords: { lat: 30.26, lon: -97.74 } },
    [ { label: 'Release', value: 'v1' }, { label: 'Area', value: 'sql' } ]
) WITH REFRESH ;

A UNION column is stored as the merged flat object, so its value is an object literal carrying the discriminator plus that variant's fields:

INSERT INTO assets (id, item)
    VALUES ('a-1', { kind: 'Book', title: 'Refactoring', isbn: '978-0134757599' }) ;

Because composite mappings are dynamic: strict, a sub-field that is not declared in the CREATE TABLE is rejected by Elasticsearch when the statement runs — a mistyped sub-field name fails the migration rather than being stored.

The id column

When the column list includes id, its value is used as the document's unique storage identifier. If the column list omits id, a random storage identifier is auto-generated.

This matters because every find-by-id lookup resolves the document by its _id. A row inserted without an id column cannot be retrieved by its logical identifier later — it will only be discoverable via search.

As a rule: if the target table has a logical id field, always include the id column in the INSERT so _id stays in sync with the document's id value.

-- Recommended: id column promoted to _id
INSERT INTO users (id, email, active)
    VALUES ('user-001', 'jane@example.com', true)
    WITH REFRESH ;

-- Avoid when the table has a logical id field: the row will get a random _id
INSERT INTO users (email, active) VALUES ('jane@example.com', true) ;

UPDATE

Updates documents matching a where clause.

Syntax:

UPDATE <index_name> SET <field> = <expression> [, <field> = <expression>]*
    WHERE <where_clause> [WITH REFRESH] ;

• WITH REFRESH (optional): Immediately refresh the index after update.
• <expression>: See Expressions — a value (including an object or array literal), a parameter (:name), or a binary expression (e.g., age + 1).

Example:

UPDATE products SET inStock = false, updatedAt = '2024-01-15'
    WHERE sku == 'WDG-001' WITH REFRESH ;

Updating composite columns

A composite column is set from the same object and array literals INSERT uses:

UPDATE persons
   SET address = { street: '99 Elm St', city: 'Shelbyville' },
       tags    = [ { label: 'Area', value: 'grammar' }, { label: 'Release', value: 'v2' } ]
 WHERE id == 'p-1' WITH REFRESH ;

An object literal is merged into the stored object rather than replacing it, matching the merge Elasticsearch performs for a partial document update. Sub-fields the statement does not mention keep their stored values, so a single sub-field can be changed by naming only that one:

-- street and zip keep their stored values; only city changes
UPDATE persons SET address = { city: 'Shelbyville' } WHERE id == 'p-1' ;

The merge is recursive, so an object inside an object merges the same way:

-- coords.lon is left alone
UPDATE persons SET address = { coords: { lat: 30.26 } } WHERE id == 'p-1' ;

Everything that is not an object is replaced outright — arrays, including a NESTED column's list, are never appended to or merged element-wise:

-- tags becomes exactly this one element, whatever it held before
UPDATE articles SET tags = [ { label: 'Area', value: 'grammar' } ] WHERE id == 'a-1' ;

The stored side decides too: if the column currently holds a scalar, an array, or nothing at all, there is nothing to merge into, so the object is written as though merged into an empty one.

Because omitting a sub-field keeps it, null is what removes one. Name the column on the left of the assignment and the sub-field inside the literal: the nesting of the literal is what addresses the field, at any depth.

-- removes address.street; city and the rest of the address are untouched
UPDATE persons SET address = { street: null } WHERE id == 'p-1' ;

-- removes address.coords.lat; lon and the rest of coords are untouched
UPDATE persons SET address = { coords: { lat: null } } WHERE id == 'p-1' ;

-- removes the whole address column
UPDATE persons SET address = null WHERE id == 'p-1' ;

A cleared field is gone rather than stored as an empty value, leaving the document exactly as if it had never carried one. Nothing is stored as null, so a later read gives you the same absent field either way.

A dot-path is not valid on the left of an assignment — SET address.street = null does not parse, because SET takes a column name. The literal above is the form that reaches a sub-field.

DELETE

Deletes documents matching a where clause.

Syntax:

DELETE FROM <index_name> WHERE <where_clause> [WITH REFRESH] ;

• WITH REFRESH (optional): Immediately refresh the index after delete.

Example:

DELETE FROM products WHERE inStock == false WITH REFRESH ;

Comments

-- This is a comment

Comments start with -- and continue to the end of the line. Comments are ignored by the parser.

Where Clauses

Where clauses are used in UPDATE and DELETE statements.

Syntax:

<field> <operator> <value>
(<where_clause>)
<where_clause> AND <where_clause>
<where_clause> OR <where_clause>

Operators: ==, !=, <, >, <=, >=

Values: String, number, or boolean literal, or parameter (:name). Object and array literals cannot be compared.

Example:

WHERE status == 'archived' AND createdAt < '2023-01-01'
WHERE (category == 'electronics' OR category == 'appliances') AND price > 100

Values

Object and array literals nest to any depth, so an OBJECT holding a NESTED holding another OBJECT is written as one literal. Field names in an object literal are bare identifiers, or single-quoted strings when the name is not a valid identifier:

{ 'content-type': 'application/json', charset: 'utf-8' }

A parameter can stand anywhere a value can, including inside an object or array literal, and the same name may be used more than once in a statement:

UPDATE persons SET address = { street: :street, city: :city } WHERE id == :id ;
DELETE FROM events WHERE created < :cutoff OR updated < :cutoff ;

Values are supplied per execution, keyed by parameter name, so two parameters on the same field stay distinct — SET price = :newPrice WHERE price == :oldPrice resolves each from its own name. A statement that names a parameter with no value supplied fails, naming it.

Object and array literals are accepted wherever a value is written — INSERT ... VALUES (see Inserting composite columns) and UPDATE ... SET (see Updating composite columns). WHERE conditions take scalar values only.

Supported Types

The NOT INDEXED variant of each type stores the value but excludes it from the search index. This reduces storage and indexing overhead for fields that only need to be returned in results, not queried.

JSON Type

The JSON type stores an entire JSON object as a single field. All leaf values within the object — regardless of nesting depth — are indexed and queryable as strings. This approach avoids requiring a fixed schema for nested data, making it well-suited for semi-structured or dynamic payloads.

Key behaviors:

• Leaf values are indexed by dot-path. A field defined as metadata JSON indexes every leaf value under its dot-path — e.g. metadata.version, metadata.source.region — as an exact-match string.
• All comparisons are string-based. Even numeric or date values stored inside a JSON field are compared as strings. Use TEXT, INTEGER, DOUBLE, etc. for fields that require numeric or range queries.
• Dynamic nesting is supported. The sub-field structure does not need to be declared in the schema — any JSON shape can be stored and its leaf values will be indexed automatically.

Example:

CREATE TABLE events (
    id      UUID,
    ts      DATE,
    payload JSON
) ;

A record with payload = {"source": {"region": "us-east"}, "retries": 3} indexes the leaf values payload.source.region and payload.retries. Note that retries is indexed as the string '3', not the number 3.

Migration WHERE clauses accept only simple column names, so dot-path filtering is not available in UPDATE/DELETE statements.

For JSON NOT INDEXED, the object is stored and returned in query results but none of its sub-fields can be queried.

Composite Types

OBJECT, NESTED, and UNION allow structured sub-documents to be defined inline. All composite types enforce dynamic: strict on their sub-fields — new sub-fields must be added via ALTER TABLE, not inserted ad-hoc.

Composite types are recursive: OBJECT inside NESTED, NESTED inside OBJECT, and other combinations are all valid.

OBJECT

Defines a single embedded object. The sub-fields are stored inline with the parent document.

Syntax:

<column_name> OBJECT (<sub_column> <type> [, <sub_column> <type>]*) [NOT INDEXED]

• NOT INDEXED (optional): disables the object. The data is stored but cannot be searched or accessed via field paths.

Example:

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

-- Store but do not index the payload object
CREATE TABLE events (
    id KEYWORD,
    payload OBJECT (raw TEXT, size INTEGER) NOT INDEXED
);

NESTED

Defines an array of embedded objects where each element is independently queryable. Use NESTED instead of OBJECT when the field holds a list of items and you need to query across the list without cross-element matching.

Syntax:

<column_name> NESTED (<sub_column> <type> [, <sub_column> <type>]*)

NESTED does not support NOT INDEXED.

Example:

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

UNION

Defines a field that can hold one of several named object variants. All variant fields are merged into a single flat object. Fields that appear in multiple variants must have the same type.

Syntax:

<column_name> UNION (
    <VariantName> (<sub_column> <type> [, <sub_column> <type>]*) [, ...]
) [NOT INDEXED]

• NOT INDEXED (optional): disables the merged object.
• Include a shared discriminator field (e.g. kind KEYWORD) in each variant so application code can identify which variant it received.

Example:

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

The resulting item field is a single object with properties kind, title, isbn, and duration.

Expressions

Expressions appear on the right of an UPDATE ... SET assignment.

• Values: any form in Values — 'string', 123, 12.5, -3, true, false, { ... }, [ ... ]
• Parameters: :name
• Binary Expressions: <field> + <value>, <field> - <value>, etc.

A binary expression reads the named field from the stored document, so its operands are scalars; object and array literals are only meaningful as a whole value.

Reserved Keywords

The literals true, false, and null are lowercase and reserved — they cannot be used as an identifier, though an object literal can carry such a field name as a quoted key ({ 'null': 1 }). All other keywords are uppercase:

ABORT, ADD, ALTER, AND, AUTO, BINARY, BOOLEAN, COLUMN, COMPONENT, CONFLICTS, CREATE, DATA, DATA_RETENTION, DATE, DECIMAL, DELETE, DOUBLE, EXISTS, FLOAT, FOR, FROM, GEO_POINT, GEO_SHAPE, IF, INDEX, INDEXED, INSERT, INTEGER, INTO, JSON, KEYWORD, LONG, MAX_DOCS, NESTED, NOT, NUMBER_OF_REPLICAS, NUMBER_OF_SHARDS, OBJECT, OR, PROCEED, QUERY, REFRESH, REINDEX, SCRIPT, SET, SIZE, SLICES, SOURCE_FIELDS, STREAM, TABLE, TEMPLATE, TEXT, TIME_REFERENCE, TRUE, FALSE, UNION, UPDATE, USING, UUID, VALUES, WAIT, WHERE, WITH, SKIP_IF_NO_SOURCE