Database Dialect Support

Built-in Database Dialects

Kronos chooses the SQL dialect and table-structure statements from KronosDataSourceWrapper.dbType.

val wrapper = KronosJdbcWrapper(dataSource)

Kronos.dataSource = { wrapper }

println(wrapper.dbType)
println(wrapper.sqlDialect)

The complete built-in dialects map to these DBType values:

For database connection and JDBC driver examples, see 📚Connect to DB. For table DDL workflows, see 📚Schema Sync and 📚Create Table As Select.

Behavior matrix

Use this page as the dialect behavior matrix. Each row links to the section or page that shows the Kotlin entry point and the SQL shape for the built-in dialects.

Identifier Quoting

The same query uses the identifier quoting rules of the active dialect.

val users = User()
    .select { [it.id, it.name] }
    .toList()

SELECT `id`, `name`
FROM `user`

SELECT "id", "name"
FROM "user"

SELECT "id", "name"
FROM "user"

SELECT [id], [name]
FROM [user]

SELECT "ID", "NAME"
FROM "USER"

Pagination

page(pageIndex, pageSize) starts from page 1. Add withTotal() after page(...) when a named PageResult with total metadata is required. Kronos renders the matching pagination syntax for the active database.

val page = User()
    .select { [it.id, it.name] }
    .orderBy { it.id.asc() }
    .page(2, 20)
    .withTotal()
    .toList()

val users = page.records

SELECT `id`, `name`
FROM `user`
ORDER BY `id` ASC
LIMIT 20 OFFSET 20

SELECT "id", "name"
FROM "user"
ORDER BY "id" ASC
LIMIT 20 OFFSET 20

SELECT "id", "name"
FROM "user"
ORDER BY "id" ASC
LIMIT 20 OFFSET 20

SELECT [id], [name]
FROM [user]
ORDER BY [id] ASC
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

SELECT "ID", "NAME"
FROM "USER"
ORDER BY "ID" ASC
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

Upsert

onConflict() uses database uniqueness conflicts and renders the matching SQL for the active dialect. When on { ... } is omitted, Kronos infers the conflict target from KPojo uniqueness metadata: primary-key values and declared unique indexes.

User(id = 1, name = "Ada")
    .upsert { it.name }
    .onConflict()
    .execute()

INSERT INTO `user` (`id`, `name`) VALUES (:id, :name)
ON DUPLICATE KEY UPDATE `name` = :name

INSERT INTO "user" ("id", "name") VALUES (:id, :name)
ON CONFLICT ("id") DO UPDATE SET "name" = :name

INSERT INTO "user" ("id", "name") VALUES (:id, :name)
ON CONFLICT ("id") DO UPDATE SET "name" = :name

MERGE INTO [user] AS [t1]
USING (SELECT :id AS [id], :name AS [name]) AS [t2]
ON ([t1].[id] = [t2].[id])
WHEN MATCHED THEN UPDATE SET [t1].[name] = :name
WHEN NOT MATCHED THEN INSERT ([id], [name]) VALUES (:id, :name)

MERGE INTO "USER" "T1"
USING (SELECT :id AS "ID", :name AS "NAME") "T2"
ON ("T1"."ID" = "T2"."ID")
WHEN MATCHED THEN UPDATE SET "T1"."NAME" = :name
WHEN NOT MATCHED THEN INSERT ("ID", "NAME") VALUES (:id, :name)

Last Insert Id

An insert on an identity primary key can read lastInsertId when the insert uses .withId(). The built-in KronosJdbcWrapper reads JDBC generated keys during insert execution; wrappers that use a follow-up query use the dialect SQL below.

val id = User(name = "Kronos")
    .insert()
    .withId()
    .execute()
    .lastInsertId

SELECT LAST_INSERT_ID()

SELECT LASTVAL()

SELECT last_insert_rowid()

SELECT SCOPE_IDENTITY()

SELECT MAX("ID") FROM "USER"

Table Operations

table.createTable(...), table.syncTable(...), table.dropTable(...), and table.truncateTable(...) use the active database's DatabaseStatements.

val table = Kronos.dataSource.table

table.createTable(Account())
table.syncTable(Account())
table.truncateTable(Account())
table.dropTable(Account())

CREATE TABLE `account` (`id` SIGNED NOT NULL, `name` CHAR(64), `created_at` DATETIME)

CREATE TABLE "account" ("id" INTEGER NOT NULL, "name" VARCHAR(64), "created_at" TIMESTAMP)

CREATE TABLE "account" ("id" INTEGER NOT NULL, "name" VARCHAR(64), "created_at" TIMESTAMP)

CREATE TABLE [account] ([name] NVARCHAR(32), [created_at] DATETIME2)

CREATE TABLE "ACCOUNT" ("NAME" VARCHAR(4000), "SCORE" INT)

Schema sync reads the current table structure first, then executes the required DDL.

val changed = Kronos.dataSource.table.syncTable(User())

changed == false  // Kronos creates the target table
changed == true   // Kronos synchronizes the existing table by diff

Built-in Functions

The function DSL keeps the same Kotlin call shape while the active dialect selects its SQL form. Function availability and argument semantics are covered in 📚Functions.

val rows = User()
    .select { f.rand().alias("rand") }
    .toList()

SELECT RAND() AS rand
FROM `tb_user`

SELECT RANDOM() AS rand
FROM "tb_user"

SELECT (RANDOM() / 9223372036854775808.0 + 0.5) AS rand
FROM "tb_user"

SELECT RAND() AS rand
FROM "tb_user"

SELECT RAND() AS rand
FROM [tb_user]

SELECT DBMS_RANDOM.VALUE AS RAND
FROM "TB_USER"

SELECT DBMS_RANDOM.VALUE AS RAND
FROM "TB_USER"

H2

H2 uses quoted identifiers and H2 MERGE syntax for conflict-based upserts. Use the H2 JDBC configuration in 📚Connect to DB for an in-memory or server connection.

User(id = 1, name = "Ada")
    .upsert { it.name }
    .onConflict()
    .execute()

MERGE INTO "user" AS "t1"
USING (VALUES (:id, :name)) AS "t2" ("id", "name")
ON ("t1"."id" = "t2"."id")
WHEN MATCHED THEN UPDATE SET "name" = "t2"."name"
WHEN NOT MATCHED THEN INSERT ("id", "name") VALUES ("t2"."id", "t2"."name")

H2 identity fields use H2 identity columns. Insert tasks with .withId() return the JDBC-generated key.

DM8

DM8 uses Oracle-compatible query syntax and DM8-native identity columns. Configure its JDBC driver as shown in 📚Connect to DB.

@PrimaryKey(identity = true)
var id: Int? = null

Kronos renders this field as INT IDENTITY(1,1) in DM8 table DDL. Insert tasks with .withId() return the JDBC-generated key.

Database Enum Values and Extension

DBType also contains DB2, Sybase, OceanBase, and GaussDB for connection identification and dialect extension entry points.

val dbType = DBType.fromName(connection.metaData.databaseProductName)

For adding a new database dialect, see 📚Create Database Dialect.

For custom database connections, see 📚concept - data-source-wrapper.