SQLlin Kotlin Multiplatform ORM

SQLlin provides annotations to define table primary keys. You cannot mix @PrimaryKey and @CompositePrimaryKey in the same class.

Single Primary Key (@PrimaryKey)

• Auto-incrementing Long: The property must be nullable (Long?). This maps to SQLite's INTEGER PRIMARY KEY (alias for rowid). When inserting, passing null allows SQLite to auto-generate the ID.
• Other types (String, Int, etc.): The property must be non-nullable. You must provide a unique value upon insertion.
• autoIncrement parameter: Enables stricter behavior using the AUTOINCREMENT keyword, ensuring IDs are never reused. This only applies to Long? types.

Composite Primary Key (@CompositePrimaryKey)

• Use this when a key consists of multiple columns.
• Apply @CompositePrimaryKey to multiple properties within the class.
• All properties marked with @CompositePrimaryKey must be non-nullable.

// Single Primary Key (Auto-increment)
@DBRow
@Serializable
data class Person(
    @PrimaryKey(autoIncrement = true)
    val id: Long? = null,
    val name: String,
    val age: Int,
)

// Composite Primary Key
@DBRow
@Serializable
data class Enrollment(
    @CompositePrimaryKey
    val studentId: Long,
    @CompositePrimaryKey
    val courseId: Long,
    val enrollmentDate: String,
)

The DSLDBConfiguration (currently experimental) allows you to manage your database schema using a type-safe SQL DSL instead of raw SQL strings within the create and upgrade callbacks.

Note: DSLDBConfiguration is intended to eventually replace DatabaseConfiguration. It is recommended to use it for new projects to ensure future compatibility.

Available DSL operations in these callbacks include CREATE, DROP, and ALTER (e.g., ALERT_ADD_COLUMN), which mirror the operations available in standard database { } blocks.

import com.ctrip.sqllin.driver.DSLDBConfiguration
import com.ctrip.sqllin.dsl.Database

val database = Database(
    DSLDBConfiguration(
        name = "Person.db",
        path = getGlobalDatabasePath(),
        version = 1,
        create = {
            // Use type-safe DSL instead of raw SQL
            CREATE(PersonTable)
        },
        upgrade = { oldVersion, newVersion ->
            when (oldVersion) {
                1 -> {
                    // Example: Add a new column in version 2
                    PersonTable ALERT_ADD_COLUMN PersonTable.email
                }
            }
        }
    )
)

SQLlin provides two ways to define primary keys depending on whether you have a single column or multiple columns as the key.

Single Primary Key (@PrimaryKey)

• Auto-incrementing Long: The property must be nullable (Long?). This maps to SQLite's INTEGER PRIMARY KEY (rowid alias). When inserting, passing null allows SQLite to generate the ID.
• Other types (String, Int, etc.): The property must be non-nullable. You must provide a unique value during insertion.
• autoIncrement parameter: Enables stricter behavior using the AUTOINCREMENT keyword to ensure row IDs are never reused. This only applies to Long? properties.

Composite Primary Key (@CompositePrimaryKey)

Use this when the primary key consists of multiple columns.

• Apply @CompositePrimaryKey to multiple properties in the same class.
• All properties marked with @CompositePrimaryKey must be non-nullable.
• Constraint: You cannot mix @PrimaryKey and @CompositePrimaryKey in the same class.

Note: For Long? auto-incrementing keys, use @PrimaryKey(autoIncrement = true).

// Single Primary Key
@DBRow
@Serializable
data class Person(
    @PrimaryKey(autoIncrement = true)
    val id: Long? = null,
    val name: String,
    val age: Int,
)

// Composite Primary Key
@DBRow
@Serializable
data class Enrollment(
    @CompositePrimaryKey
    val studentId: Long,
    @CompositePrimaryKey
    val courseId: Long,
    val enrollmentDate: String,
)

SQLlin supports foreign keys to maintain referential integrity.

1. Enable Foreign Key Enforcement (CRITICAL)

By default, SQLite does not enforce foreign keys. You must explicitly enable it for every connection before creating tables:

database {
    PRAGMA_FOREIGN_KEYS(true)
    CREATE(UserTable)
    CREATE(OrderTable)
}

2. Defining Foreign Keys

Method 1: Column-level (@References)

Recommended for simple, single-column foreign keys.

@References(
    tableName = "User",
    foreignKeys = ["id"],
    trigger = Trigger.ON_DELETE_CASCADE
)
val userId: Long

Method 2: Table-level (@ForeignKeyGroup + @ForeignKey)

Required for composite foreign keys (referencing multiple columns).

@ForeignKeyGroup(
    group = 0,
    tableName = "Product",
    trigger = Trigger.ON_DELETE_CASCADE,
    constraintName = "fk_product"
)
data class OrderItem(
    @ForeignKey(group = 0, reference = "categoryId") val productCategory: Int,
    @ForeignKey(group = 0, reference = "productCode") val productCode: String,
)

3. Supported Triggers

// Example of @References
@DBRow
@Serializable
data class Order(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(
        tableName = "User",
        foreignKeys = ["id"],
        trigger = Trigger.ON_DELETE_CASCADE
    )
    val userId: Long,
    val amount: Double
)

Use these annotations to enforce uniqueness constraints on your columns.

Single Column Uniqueness (@Unique)

• Enforces that no two rows have the same value in a specific column.
• Multiple NULL values are allowed (as NULL != NULL in SQL).
• To prevent NULL values, use a non-nullable Kotlin type (e.g., String).

Multi-Column Uniqueness (@CompositeUnique)

• Ensures the combination of multiple columns is unique.
• Grouping: Use the group parameter to define which properties belong to which constraint. Properties with the same group number are combined into a single constraint. If no group is specified, it defaults to 0.
• A single property can belong to multiple groups by providing multiple indices (e.g., @CompositeUnique(0, 1)).

// Single Unique
@Unique val email: String

// Composite Unique (Group 0)
@CompositeUnique(0) val studentId: Int,
@CompositeUnique(0) val courseId: Int

// Multiple Groups
@CompositeUnique(0, 1) val userId: Int, // Part of groups 0 and 1
@CompositeUnique(0) val eventType: String, // Part of group 0
@CompositeUnique(1) val timestamp: Long, // Part of group 1