Indexes

Declare indexes

Kronos reads @TableIndex metadata when wrapper.table.createTable(...) or wrapper.table.syncTable(...) builds DDL. The runtime metadata shape is KTableIndex.

For a single-column index, declare a class-level @TableIndex with one column name.

Use 📚Table and Column for table and column name mapping before adding index definitions.

Single-column index

Use one column in columns.

single column

import com.kotlinorm.annotations.Table
import com.kotlinorm.annotations.TableIndex
import com.kotlinorm.interfaces.KPojo

@Table("tb_user")
@TableIndex(name = "idx_user_name", columns = ["name"])
data class User(
    val id: Int? = null,
    val name: String? = null
) : KPojo

Mysql

CREATE INDEX `idx_user_name` ON `tb_user` (`name`)

Unique composite index

Set type = "UNIQUE" for a unique index. Set method only when the target database supports the method.

unique composite

import com.kotlinorm.annotations.Column
import com.kotlinorm.annotations.Table
import com.kotlinorm.annotations.TableIndex
import com.kotlinorm.interfaces.KPojo

@Table("tb_user")
@TableIndex(
    name = "idx_user_tenant_email",
    columns = ["tenant_id", "email"],
    type = "UNIQUE",
    method = "BTREE"
)
data class UserAccount(
    val id: Int? = null,
    @Column("tenant_id")
    val tenantId: Int? = null,
    val email: String? = null
) : KPojo

Mysql

CREATE UNIQUE INDEX `idx_user_tenant_email` ON `tb_user` (`tenant_id`, `email`) USING BTREE

PostgreSQL

CREATE UNIQUE INDEX "idx_user_tenant_email" ON "public"."tb_user" USING BTREE ("tenant_id", "email")

Concurrent PostgreSQL index

concurrently = true renders CREATE INDEX CONCURRENTLY. Kronos executes concurrent index statements outside the transactional DDL batch.

concurrently

import com.kotlinorm.annotations.Column
import com.kotlinorm.annotations.Table
import com.kotlinorm.annotations.TableIndex
import com.kotlinorm.interfaces.KPojo

@Table("tb_event")
@TableIndex(
    name = "idx_event_created_at",
    columns = ["created_at"],
    method = "BTREE",
    concurrently = true
)
data class EventLog(
    val id: Int? = null,
    @Column("created_at")
    val createdAt: String? = null
) : KPojo

PostgreSQL

CREATE INDEX CONCURRENTLY "idx_event_created_at" ON "public"."tb_event" USING BTREE ("created_at")

concurrently is intended for PostgreSQL. Other databases may reject this DDL keyword.

Runtime KTableIndex

KTableIndex is the index metadata object used by generated metadata and table synchronization. Regular model classes usually use @TableIndex; manually assembled metadata can create KTableIndex directly.

kotlin

import com.kotlinorm.beans.dsl.KTableIndex

val userNameIndex = KTableIndex(
    name = "idx_user_name",
    columns = arrayOf("name"),
    type = "NORMAL",
    method = "BTREE"
)

Attached to table metadata, the DDL shape is:

Mysql

CREATE INDEX `idx_user_name` ON `tb_user` (`name`) USING BTREE

Field behavior

FieldCurrent behavior
nameIndex name rendered in CREATE INDEX.
columnsColumn names rendered in order.
type"UNIQUE" marks the index as unique. "NORMAL" and blank are omitted. Other values are copied before INDEX for dialects that support them, such as MySQL FULLTEXT, MySQL SPATIAL, SQL Server NONCLUSTERED, or Oracle BITMAP.
methodBlank, "UNIQUE", and values equal to type are omitted as methods. Other values are copied into USING <method>.
concurrentlyRenders CONCURRENTLY on create/drop index statements and is meant for PostgreSQL.

Dialect notes

DialectPractical index settings
MySQLUse type = "UNIQUE" for unique indexes. method = "BTREE" or "HASH" is rendered after the column list. FULLTEXT and SPATIAL can be passed through type.
PostgreSQLUse method = "BTREE", "HASH", "GIST", "SPGIST", "GIN", or "BRIN" when the database supports it. concurrently = true renders CREATE INDEX CONCURRENTLY.
SQLiteUse blank type/method or type = "UNIQUE". SQLite create-table DDL uses CREATE INDEX IF NOT EXISTS.
H2Use blank type/method or type = "UNIQUE". The default normal-index method is BTREE.
SQL ServerUse type = "CLUSTERED", "NONCLUSTERED", "XML", or "SPATIAL" according to the target table. method = "UNIQUE" also marks the index as unique.
OracleUse type = "UNIQUE" or "BITMAP" when needed. Oracle identifiers are rendered with the Oracle dialect rules.
DM8Use type = "UNIQUE" for unique indexes. Use database-specific migrations for additional DM8 index options.