tamimattafi/kabin

A Kotlin Multiplatform library for database storage inspired by Room

Kabin: Multiplatform Database Library

A Kotlin Multiplatform library for database storage, which aims to support all functionality offered by Room.

Kabin uses drivers from SQLDelight, offering a stable interaction with SQL on all targets supported by the latter.

Caution

This library is still under development. Avoid using it in production. You are very welcome to create issues and Pull Requests. Contribution will accelerate development, and pave the way for a production ready solution.

Showcase

Using Kabin is straight forward. Annotations are identical to those in Room, which means usage is identical too. Here's how you declare a simple database:

1. Create an Entity:

@Entity
data class UserEntity(
    @PrimaryKey
    val id: Int,
    val name: String
)

2. Create a Dao:

@Dao
interface UserDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertOrReplace(entity: UserEntity)
}

3. Create a Database:

@Database(
    entities = [
        UserEntity::class
    ],
    version = 1
)
interface SampleDatabase : KabinDatabase {
    val userDao: UserDao
}

Kabin will generate code for you and glue everything together.

4. Finally, create a platform configuration, then pass it to the newInstance method, to initialize SampleDatabase:

// Create configuration for every platform, here's an example for android
val configuration = KabinDatabaseConfiguration(
    context = this,
    name = "sample-database"
)

val sampleDatabase = SampleDatabase::class.newInstance(
    configuration,
    migrations = emptyList(),
    migrationStrategy = KabinMigrationStrategy.DESTRUCTIVE
)

For more advanced topics, read Room documentation and tutorials, and apply the same logic using Kabin.

Installation

Latest Kabin version

Add common modules to your sourceSet:

kotlin {
    sourceSets {
        val commonMain by getting {
            dependencies {
                // Kabin
                implementation("com.attafitamim.kabin:core:$kabin_version")
            }

            // Make generated code visible for commonMain
            kotlin.srcDir("$buildDir/generated/ksp/metadata/commonMain/kotlin/")
        }
    }
}

Add ksp compiler:

plugins {
    alias(libs.plugins.kotlin.multiplatform)
    alias(libs.plugins.ksp)
}

dependencies {
    add("kspCommonMainMetadata", "com.attafitamim.kabin:compiler:$kabin_version")
}

// Workaround for using KSP in common
tasks.withType<KotlinCompile<*>>().configureEach {
    if (name != "kspCommonMainKotlinMetadata") {
        dependsOn("kspCommonMainKotlinMetadata")
    }
}

afterEvaluate {
    tasks.filter { task ->
        task.name.contains("SourcesJar", true)
    }.forEach { task ->
        task.dependsOn("kspCommonMainKotlinMetadata")
    }
}

Optional

Configure ksp processor to generate more suitable code for you

ksp {
    // Use this prefix for fts tables to keep the old room scheme
    arg("FTS_TRIGGER_NAME_PREFIX", "room_fts_content_sync")
}

Available keys, with their default values

TABLE_SUFFIX("KabinTable")
ENTITY_MAPPER_SUFFIX("KabinMapper")
DATABASE_SUFFIX("KabinDatabase")
DAO_SUFFIX("KabinDao")
DAO_QUERIES_SUFFIX("KabinQueries")
INDEX_NAME_PREFIX("index")
FTS_TRIGGER_NAME_PREFIX("kabin_fts_content_sync")
BEFORE_UPDATE_TRIGGER_NAME_SUFFIX("BEFORE_UPDATE")
AFTER_UPDATE_TRIGGER_NAME_SUFFIX("AFTER_UPDATE")
BEFORE_DELETE_TRIGGER_NAME_SUFFIX("BEFORE_DELETE")
AFTER_INSERT_TRIGGER_NAME_SUFFIX("AFTER_INSERT")

Supported Room Features

This list shows Room features, which are already supported by Kabin, or under development

Note

To accelerate the development of certain features, create issues to increase priority, or upvote existing ones

@Entity

• tableName
• indices
• inheritSuperIndices
• primaryKeys
• foreignKeys
• ignoredColumns

@PrimaryKey

• autoGenerate
• Use @PrimaryKey on multiple columns
• Use @PrimaryKey on @Embedded columns

@Embedded

• prefix
• Nested @Embedded (@Embedded inside an @Embedded)
• Compound (@Embedded entity inside a class for working with @Relations)
• @Embedded columns as primary keys using @PrimaryKey

@ColumnInfo

• name
• typeAffinity
• index
• collate
• defaultValue

@Ignore

• Skip columns annotated with @Ignore

@ForeignKey

• entity
• parentColumns
• childColumns
• onDelete
• onUpdate
• deferred

@Index

• columns
• orders
• name
• unique

@Relation

• entity
• parentColumn
• entityColumn
• associateBy
• projection
• Detect entity from property type
• Detect entity from list property type
• Insert entities automatically when inserting Compound classes with @Embedded entities

@Junction

• value
• parentColumn
• entityColumn
• Retrieve data using @Junction table
• Create and insert @Junction entities automatically when inserting classes with @Relation

@Fts4

• contentEntity
• tokenizerArgs
• languageId
• notIndexed
• prefix
• order
• Create virtual table with triggers

@Dao

• Use coroutines and suspend functions
• Support Collection and Flow return types
• Execute operations on Dispatcher.IO
• Interfaces annotated with @Dao
• Abstract classes annotated with @Dao

@Insert

• entity
• onConflict
• Insert single entity, multiple entities as distinct parameters or lists of entities
• Insert Compound classes with @Embedded entities including their @Relation and @Junction

@Delete

• entity
• Delete single entity, multiple entities as distinct parameters or lists of entities
• Delete Compound classes with @Embedded entities including their @Relation and @Junction

@Update

• entity
• onConflict
• Update single entity, multiple entities as distinct parameters or lists of entities
• Update Compound classes with @Embedded entities including their @Relation and @Junction

@Upsert

Caution

This annotation is currently treated as @Insert with REPLACE strategy

• entity
• Use Upsert logic instead of simple insert with REPLACE strategy
• Upsert single entity, multiple entities as distinct parameters or lists of entities
• Upsert Compound classes with @Embedded entities including their @Relation and @Junction

@RawQuery

• observedEntities
• Detect observed entities by return type

@Query

• value
• Detect observed entities by return type
• Detect observed entities by queried tables
• Named parameters declared as :parameter
• Nullable parameters
• List and nullable list parameters
• Parameters declared as ?
• Highlight SQL Syntax
• Validate SQL Syntax
• Auto complete SQL Syntax and named parameters

@Transaction

• Functions with @Transaction annotation
• Functions working with multiple entity parameters, collections and compounds

@Database

• entities
• views
• version
• exportSchema
• autoMigrations
• Interfaces annotated with @Database
• Abstract classes annotated with @Database
• Generate adapters for primitive and enum classes
• Manual migration
• Destructive migration
• Validate Schema

@TypeConverters

Caution

This annotation can only accept converter object that implement app.cash.sqldelight.ColumnAdapter

• value
• builtInTypeConverters

@BuiltInTypeConverters

• enums (Enums are supported by default)
• uuid

@AutoMigration

• from
• to
• spec
• Support auto migration functionality

Additional Features

@Mappers

• Used to map results returned by a dao to data classes that are not entities or primitives
• This annotation is meant to be used with Database class
• value accepts object that implements KabinMapper<T>

Compound

• Classes that use @Embedded and @Relation annotations can be used with @Insert, @Upsert, @Delete and @Update
• @Junction inside a compound is automatically created and inserted as well

Plans and Priorities

1. Clean and refactor compiler and processor logic, make it more flexible and maintainable
2. Generate more optimized code
3. Fix bugs and issues
4. Implement more Room features, especially the essential ones for basic and simple apps
5. Add more features to make working with SQL easier and more interesting
6. Add multiplatform sample with UI
7. Make a stable release

kabin's Issues

Constraint foreign key exception when having conflicting operations inside one transaction

Problem

If we have several tables with foreign key relations, then inside one transaction do some operations influences on each other and get exception android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY).

Unlikely, in Room library the same tables and relations such transaction is considered valid.

Environment: kabin: 0.1.0-alpha04;
SQLite version (according to documentation): 3.39 (API 34).

Example

We have 2 tables and a dao.

@Entity
data class UserEntity(
    @PrimaryKey
    val id: Int,
    val name: String
)

@Entity(
    primaryKeys = ["id", "userId"],
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["id"],
            childColumns = ["userId"],
            onDelete = ForeignKey.Action.CASCADE,
            onUpdate = ForeignKey.Action.CASCADE
        )
    ],
)
data class UserAvatarEntity(
    val userId: Int,
    val id: String,
    val url: String
)

@Dao
interface IUserDaoSpecial {

    @Transaction
    suspend fun doSomeOperations() {
        val userEntity = UserEntitySpecial(
            id = 0,
            name = "John"
        )
        val userAvatarEntity = UserAvatarEntitySpec(
            userId = 0,
            id = "d64c2cd8-fd5f-4f06-844e-b4006b9464bb",
            url = "https://icon.jpg"
        )

        insertAvatar(userAvatarEntity)
        insertUser(userEntity)
    }

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: UserEntitySpecial)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAvatar(avatarEntitySpec: UserAvatarEntitySpec)
}

If we will invoke dao method, we will catch an exception.

android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)

Expected behavior: successful operations inside one transaction.
I guess this error is connected with deferred flag in ForeignKey.

Flow of a single element doesn't throw error when the element doesn't exist

Problem

Querying a reactive entity doesn't throw an exception, if the entity doesn't exist, since Dao is using mapToOneNotNull instead of mapToOne

However, Room uses a different behavior, it emits an error instead of suspending the Flow<T> forever while waiting for insert

Example

Here's a query that returns a Flow of a single element:

@Query("SELECT * FROM UserEntity WHERE name = :name AND sampleAge = :age")
suspend fun getEntityReactive(age: Int, name: String): Flow<UserEntity>

This will generate the following code:

override suspend fun getEntityReactive(age: Int, name: String): Flow<UserEntity> =
    queries.queryUserEntityByNameAge(name, age).asFlowIONotNull()

As we see, converting Query<T> to Flow<T> is done using asFlowIONotNull, but it will be more correct to use asFlowIO, which uses mapToOne under the hood

Primary keys are ignored, if the columns are inside an embedded class

Problem:

PRIMARY KEY declaration is missing from table creation query, if the primary key column is inside an @Embedded class, and it was assigned through primaryKeys inside @Entity annotation

Example

Here's an Entity that has an embedded identity class, which contains the column used as primary key:

@Entity(
    primaryKeys = ["identity_id"]
)
data class FileEntity(
    @Embedded("identity_")
    val identity: FileIdentityLocal,
    val fileReferenceBase64: String? = null,
    val date: Int? = null,
    val size: Int? = null
)

The generated query looks like this:

CREATE TABLE IF NOT EXISTS documententity
(
     identity_id               TEXT NOT NULL,
     identity_type             TEXT NOT NULL,
     identity_mimetype         TEXT,
     identity_fallbackmimetype TEXT,
     identity_volumeid         INTEGER,
     identity_accesshash       INTEGER,
     identity_datacenterid     INTEGER,
     filereferencebase64       TEXT,
     date                      INTEGER,
     size                      INTEGER
) 

As we can see, PRIMARY KEY is nowhere to be seen

Bug: db connection on android doesn't support ForeignKey

As said in documentation, connection should enable foreign key support in runtime.
Please, add configuration for this or make this behavior enabled by default

Bug: Flow doesn't emit new value when compound have relation to list of entities

Problem

When we have complex compound hierarchy with relation to a list of entities (or compounds) - reactive subscription via flow doesn't emit new values on changed entities.

Environment: kabin: 0.1.0-alpha05; android target.
SQLite version (according to documentation): 3.39 (API 34).

Example

data class Compound1(
    val entity: Entity1,
    
    @Relation(
        entity = Entity2::class,
        parentColumn = "id",
        entityColumn = "id"
    )
    val compounds2: List<Compound2>
)

@Dao
interface IEntity2Dao {

    @Query("DELETE FROM Entity2 WHERE id = :id")
    suspend fun removeEntity(id: String)
}

@Dao
interface IEntity1Dao {

    @Query("SELECT * FROM Entity1")
    suspend fun getCompound(id: String): Flow<Compound1>
}

to reproduce:

suspend fun reproducer(
    dao1: IEntity1Dao,
    dao2: IEntity2Dao
) {
    coroutineScope.launch {
        dao1.getCompound("id1").collect {
            // only first emit
        }

        dao2.removeEntity("id2")
    }
}

Bug: type mismatch for complex compounds having nullable and non-nullable types

Problem

If we will have complex compounds hierarchy, and in one place compound is considered nullable, an in another - not null, nullable query is used, and compiler throws "type mismatch" error.

Environment: kabin: 0.1.0-alpha05; android target.
SQLite version (according to documentation): 3.39 (API 34).

Example

data class ACompound(
    @Embedded
    val entity: AEntity,

    @Relation(
        entity = BEntity::class,
        parentColumn = "bId",
        entityColumn = "id"
    )
    // query1 returning nullable compound is generated
    val bCompound: bCompound?,
    
        @Relation(
        entity = CEntity::class,
        parentColumn = "cId",
        entityColumn = "id"
    )
    val cCompound: cCompound,
)

data class CCompound(
    @Embedded
    val entity: CEntity,

    @Relation(
        entity = BEntity::class,
        parentColumn = "bId",
        entityColumn = "id"
    )
    // query1 will be still used in dao, though we have non-nullable returning type
    val bCompound: bCompound,

Maybe we should add postfix "Optional" or "Nullable" to queries returning nullable type to avoid such problems.

iOS: Creating scheme throws SQLiteExceptionErrorCode: transaction within a transaction

Problem

After updating to 0.1.0-alpha06, iOS apps started failing during scheme creation:

SQLiteExceptionErrorCode: Sqlite operation failure cannot start a transaction within a transaction

Stack Trace:

co.touchlab.sqliter.interop.SQLiteExceptionErrorCode: Sqlite operation failure cannot start a transaction within a transaction
    at 0   ComposeApp                          0x108a31cef        kfun:kotlin.Throwable#<init>(kotlin.String?){} + 119 
    at 1   ComposeApp                          0x108a2b05f        kfun:kotlin.Exception#<init>(kotlin.String?){} + 115 
    at 2   ComposeApp                          0x1089e933b        kfun:co.touchlab.sqliter.interop.SQLiteException#<init>(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig){} + 115 
    at 3   ComposeApp                          0x1089e942f        kfun:co.touchlab.sqliter.interop.SQLiteExceptionErrorCode#<init>(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig;kotlin.Int){} + 199 
    at 4   ComposeApp                          0x1089e54a3        kfun:co.touchlab.sqliter.interop.ActualSqliteStatement#resetStatement(){} + 1127 
    at 5   ComposeApp                          0x1089fecd3        kfun:co.touchlab.sqliter.interop.SqliteStatement#resetStatement(){}-trampoline + 91 
    at 6   ComposeApp                          0x1089fb333        kfun:co.touchlab.sqliter.native.NativeStatement#resetStatement(){} + 531 
    at 7   ComposeApp                          0x1089fa623        kfun:co.touchlab.sqliter.native.NativeStatement#execute(){} + 679 
    at 8   ComposeApp                          0x1089fd38b        kfun:co.touchlab.sqliter.Statement#execute(){}-trampoline + 91 
    at 9   ComposeApp                          0x1089f838f        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.beginTransaction$lambda$0#internal + 67 
    at 10  ComposeApp                          0x1089f876b        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.$beginTransaction$lambda$0$FUNCTION_REFERENCE$0.invoke#internal + 79 
    at 11  ComposeApp                          0x1089f87e3        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.$beginTransaction$lambda$0$FUNCTION_REFERENCE$0.$<bridge-UNNN>invoke(co.touchlab.sqliter.Statement){}#internal + 99 
    at 12  ComposeApp                          0x108b7ce8f        kfun:kotlin.Function1#invoke(1:0){}1:1-trampoline + 107 
    at 13  ComposeApp                          0x1089dc3a3        kfun:co.touchlab.sqliter#withStatement__at__co.touchlab.sqliter.DatabaseConnection(kotlin.String;kotlin.Function1<co.touchlab.sqliter.Statement,0:0>){0§<kotlin.Any?>}0:0 + 279 
    at 14  ComposeApp                          0x1089f76bb        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection#beginTransaction(){} + 307 
    at 15  ComposeApp                          0x1089fce7b        kfun:co.touchlab.sqliter.DatabaseConnection#beginTransaction(){}-trampoline + 91 
    at 16  ComposeApp                          0x108a074cf        kfun:app.cash.sqldelight.driver.native.ThreadConnection#newTransaction(){}app.cash.sqldelight.Transacter.Transaction + 275 
    at 17  ComposeApp                          0x108a0691f        kfun:app.cash.sqldelight.driver.native.SqliterWrappedConnection#newTransaction(){}app.cash.sqldelight.db.QueryResult<app.cash.sqldelight.Transacter.Transaction> + 159 
    at 18  ComposeApp                          0x10a534977        kfun:app.cash.sqldelight.db.SqlDriver#newTransaction(){}app.cash.sqldelight.db.QueryResult<app.cash.sqldelight.Transacter.Transaction>-trampoline + 99 
    at 19  ComposeApp                          0x10a53150f        kfun:app.cash.sqldelight.SuspendingTransacterImpl.$transactionWithWrapperCOROUTINE$0.invokeSuspend#internal + 767 
    at 20  ComposeApp                          0x10a531f23        kfun:app.cash.sqldelight.SuspendingTransacterImpl.transactionWithWrapper#internal + 323 
    at 21  ComposeApp                          0x10a5310ff        kfun:app.cash.sqldelight.SuspendingTransacterImpl#transaction#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.
    SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any + 151 
    at 22  ComposeApp                          0x10a534703        kfun:app.cash.sqldelight.SuspendingTransacter#transaction#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any-trampoline + 127 
    at 23  ComposeApp                          0x10a52eb8f        kfun:app.cash.sqldelight.SuspendingTransacter#transaction$default#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.Int;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any + 211 
    at 24  ComposeApp                          0x10a54246b        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.$create$lambda$2COROUTINE$3.invokeSuspend#internal + 1319 
    at 25  ComposeApp                          0x10a542787        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.create$lambda$2#internal + 331 
    at 26  ComposeApp                          0x10a543afb        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.$create$lambda$2$FUNCTION_REFERENCE$1.invoke#internal + 115 
    at 27  ComposeApp                          0x108b826eb        kfun:kotlin.coroutines.SuspendFunction0#invoke#suspend(kotlin.coroutines.Continuation<1:0>){}kotlin.Any?-trampoline + 107 
    at 28  ComposeApp                          0x10a532b77        kfun:app.cash.sqldelight.db.QueryResult.AsyncValue#await#suspend(kotlin.coroutines.Continuation<1:0>){}kotlin.Any? + 175 
    at 29  ComposeApp                          0x1089ced57        kfun:app.cash.sqldelight.async.coroutines.object-1.create$lambda$0#internal + 243 
    at 30  ComposeApp                          0x1089cf0b7        kfun:app.cash.sqldelight.async.coroutines.object-1.$create$lambda$0$FUNCTION_REFERENCE$0.invoke#internal + 139 
    at 31  ComposeApp                          0x108b8242b        kfun:kotlin.Function2#invoke(1:0;1:1){}1:2-trampoline + 115 
    at 32  ComposeApp                          0x108a3a3b7        kfun:kotlin.coroutines.intrinsics.object-4.invokeSuspend#internal + 731 
    at 33  ComposeApp                          0x108b81d47     

It seems like scheme's create method is already wrapped inside a transaction by sqldelight, and kabin tries to wrap it again which leads to the exception above. Further investigation should include the migrate method.

Multiple and nullable list arguments break code-gen

Problem

1. Using lists multiple times inside a query breaks code-gen
2. Nullable lists are not handled when creating query arguments using createArguments

Example

Here's an example of a query having lists as arguments, which are used many times in different places, and are nullable

@Query("""
        SELECT * FROM SimpleEntity 
                WHERE(:types IS NULL OR type IN :types)
                AND (:statuses IS NULL OR status IN :statuses)
                AND (:startAt is NULL OR createdAt >= :startAt)
                AND (:endAt IS NULL OR createdAt <= :endAt)
                AND id IN (
                        SELECT parentId FROM SampleParticipantEntity
                        WHERE (:roles IS NULL OR type IN :roles) AND self = 1
                 )
        ORDER BY createdAt DESC LIMIT :limit
""")
suspend fun getSampleCompoundsReactive(
        statuses: List<String>?,
        types: List<String>?,
        roles: List<String>?,
        startAt: Int?,
        endAt: Int?,
        limit: Int
): Flow<List<SampleCompound>>

This will generate the following code:

val types = types.orEmpty()
val typesIndexes = createArguments(types.size)
val types = types.orEmpty()
val typesIndexes = createArguments(types.size)
val statuses = statuses.orEmpty()
val statusesIndexes = createArguments(statuses.size)
val statuses = statuses.orEmpty()
val statusesIndexes = createArguments(statuses.size)
val roles = roles.orEmpty()
val rolesIndexes = createArguments(roles.size)
val roles = roles.orEmpty()
val rolesIndexes = createArguments(roles.size)

Here we see two issues:

1. types and typesIndexes are doubled
2. createArguments will never return NULL, which is important for the correctness of queries like :types IS NULL OR type IN :types