Migrating from Realm to SQLDelight

In the early stages of the CrossPaste project, we chose Realm Kotlin as our persistence solution, leveraging its intuitive object mapping and reactive queries to quickly implement local data storage functionality. However, as the Kotlin compiler continued to evolve and Compose Multiplatform gradually matured, we gradually realized that Realm could no longer meet the long-term development needs of our project at this stage.

In November 2024, the official Realm maintainers clearly stated in issue #1852:

"Unfortunately, Realm is under minimum maintenance. It would be better for you to have your own fork."

This indicates that Realm Kotlin has entered a minimum maintenance state and still doesn't support Kotlin 2.1.0. Continuing to use Realm would seriously hinder the project's ability to keep up with new Kotlin versions, affecting overall dependency upgrades, cross-platform compatibility, and future maintenance.

Therefore, we decided to migrate our data persistence solution from Realm to SQLDelight for the following reasons:

1. ✅ Open source and transparent with active maintenance: SQLDelight is completely open source with stable community support.

2. ✅ Proven maturity in multiplatform projects: Multiple large Kotlin Multiplatform projects have adopted SQLDelight as their core database solution.

3. ✅ Return to SQL thinking with more controllable structural design: Compared to object storage patterns, SQLDelight uses explicit SQL files for table creation and query definitions, providing stronger readability, maintainability, and debugging capabilities.

This series of articles will combine the migration practices from CrossPaste to deeply explain how to reconstruct the original Realm data models based on SQLDelight, implement reactive queries, build full-text search capabilities, and gradually complete a robust and modern database migration process.

SQLDelight Plugin Configuration and Code Generation Mechanism

SQLDelight is a cross-platform database solution that generates type-safe Kotlin APIs through declarative SQL files and supports multiple platforms including Android, JVM, and Native.

🔧 Dependency and Plugin Configuration

We use libs.versions.toml to manage SQLDelight versions and dependencies:

[versions]
sqldelight = "2.0.2"

[libraries]
sqlite-driver = { module = "app.cash.sqldelight:sqlite-driver", version.ref = "sqldelight" }
sqldelight-coroutines-extensions = { module = "app.cash.sqldelight:coroutines-extensions", version.ref = "sqldelight" }

[plugins]
sqlDelight = { id = "app.cash.sqldelight", version.ref = "sqldelight" }

Add the plugin to the module's build.gradle.kts:

plugins {
    alias(libs.plugins.sqlDelight)
}

Configure the SQLDelight database generator:

sqldelight {
    databases {
        create("Database") {
            packageName = "com.crosspaste"
            dialect("app.cash.sqldelight:sqlite-3-25-dialect:2.0.2")
        }
    }
}

📁 Creating .sq Files and Automatic Database Code Generation

We add .sq files in commonMain/sqldelight and write standard SQL statements directly. For example, creating tables, indexes, triggers, etc., follows standard SQLite syntax:

CREATE TABLE IF NOT EXISTS PasteDataEntity (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    appInstanceId TEXT NOT NULL,
    favorite INTEGER AS Boolean NOT NULL,
    pasteAppearItem TEXT,
    pasteCollection TEXT NOT NULL,
    pasteType INTEGER NOT NULL DEFAULT -1,
    source TEXT,
    size INTEGER NOT NULL,
    hash TEXT NOT NULL,
    createTime INTEGER NOT NULL,
    pasteSearchContent TEXT,
    pasteState INTEGER NOT NULL DEFAULT 0,
    remote INTEGER AS Boolean NOT NULL
);

For queries, inserts, updates, etc., simply add a method name, and the SQLDelight plugin will automatically generate corresponding Kotlin calling functions:

createPasteDataEntity:
INSERT INTO PasteDataEntity(appInstanceId, favorite, pasteAppearItem, pasteCollection, pasteType, source, size, hash, createTime, pasteSearchContent, pasteState, remote)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

getLastId:
SELECT last_insert_rowid();

getPasteDataListLimit:
SELECT * FROM PasteDataEntity WHERE pasteState != -1 ORDER BY createTime DESC, id DESC LIMIT ?;

Through this approach, SQLDelight achieves the integration of "declarative SQL + automatic code generation + type safety", returning to SQL's controllability while retaining some of the convenience of ORM.

Table Structure Design to Replace Realm's Object Composition

Realm's data modeling approach centers on "object composition", supporting nested objects, List<T>, optional fields, and other flexible structures. This model is very intuitive for object-oriented developers, but in relational databases, we need to explicitly design tables and relational modeling.

When migrating Realm data to SQLDelight, we follow two strategies for table structure design:

🧩 Objects Requiring Queries/Filtering: Split into Independent Tables with Relations

If a nested object or elements in a list need to be individually filtered in queries (such as keyword searches, tag filtering, sorting, etc.), we recommend creating it as an independent table and relating it to the parent table through primary keys.

Depending on the specific data relationships, you can choose:

• One-to-one: Add FOREIGN KEY in the main table or child table
• One-to-many: Store the parent table's primary key in the child table
• Many-to-many: Use an intermediate table to maintain relationships

📦 Nested Objects Not Requiring Individual Queries: Store as JSON

If some nested objects exist only as part of the main table and won't be independently queried or filtered, they can be simplified by serializing directly as JSON and storing in a TEXT field. This approach is more lightweight, doesn't require additional tables, and reduces migration complexity.

In Kotlin, you can use kotlinx.serialization or Moshi to handle JSON encoding/decoding. This way, we only need to deserialize and use this data in business logic without direct database-level access.

How SQLDelight Implements Reactive Queries

Compared to Realm's LiveObject/Flow mechanism, SQLDelight provides a more lightweight, transparent, and composable approach to reactive queries. With coroutines extensions, it can expose database queries as Flow to the UI layer, enabling data-driven interface updates.

✅ Adding Reactive Support Dependencies

First, ensure coroutines-extensions is included in version management:

[libraries]
sqldelight-coroutines-extensions = { module = "app.cash.sqldelight:coroutines-extensions", version.ref = "sqldelight" }

Then add the dependency in build.gradle.kts:

implementation(libs.sqldelight.coroutines.extensions)

🔄 Declaring Reactive Query Functions

SQLDelight generates type-safe query interfaces based on named statements in .sq files. We just need to use .asFlow() with .map to implement reactive queries:

fun getPasteDataFlow(limit: Long): Flow<List<PasteData>> {
    return pasteDatabaseQueries
        .getPasteDataListLimit(limit, PasteData::mapper) // Mapper converts database rows to PasteData entities
        .asFlow()
        .map { it.executeAsList() }
        .catch { e ->
            logger.error(e) { "Error executing getPasteDataFlow query: ${e.message}" }
            emit(emptyList())
        }
}

• getPasteDataListLimit is the named query method we defined in the .sq file;

• PasteData::mapper is used to map raw fields to business layer data models;

• The returned Flow<List<PasteData>> can be directly used with Compose UI's collectAsState() for automatic refreshing.

🧠 How Does It Implement Reactivity?

You might wonder: SQLDelight doesn't have database observers, so how does it implement automatic Flow refreshing?

Just look at the generated code to understand. In build/generated/sqldelight/code/Database/, we can see insert and update functions like:

public fun createPasteDataEntity(...) {
    driver.execute(..., """INSERT INTO PasteDataEntity(...) VALUES (...)""", ...) {
        bindString(...) // Bind parameters
    }
    notifyQueries(...) { emit ->
        emit("PasteDataEntity")         // Notify affected query tables
        emit("PasteDataEntityFts")      // Including full-text index tables
    }
}

public fun updateFavorite(favorite: Boolean, id: Long) {
    driver.execute(..., """UPDATE PasteDataEntity SET favorite = ? WHERE id = ?""", ...) {
        bindBoolean(0, favorite)
        bindLong(1, id)
    }
    notifyQueries(...) { emit ->
        emit("PasteDataEntity")
        emit("PasteDataEntityFts")
    }
}

The key is that after each write operation, notifyQueries(...) is called to inform which tables or virtual tables have been modified, triggering corresponding queries to re-execute. As long as your query functions are created based on .asFlow(), they will be correctly notified and re-emit new data, achieving reactive updates.

Implementing Full-Text Search: FTS Support in SQLDelight

In the cross-platform application CrossPaste, supporting efficient search of historical paste data is a very important component. For this, we use SQLite's FTS5 extension and implement full-text indexing and queries through SQLDelight's .sq file definitions.

🔍 Creating Virtual Index Tables with FTS5

CREATE VIRTUAL TABLE IF NOT EXISTS PasteDataEntityFts USING fts5(
    pasteSearchContent,
    content=PasteDataEntity,
    content_rowid=id
);

Then we created three triggers to ensure that data changes in the main table are synchronized with the index table:

-- Insert index on insert
CREATE TRIGGER IF NOT EXISTS PasteDataEntityAI AFTER INSERT ON PasteDataEntity BEGIN
    INSERT INTO PasteDataEntityFts(rowid, pasteSearchContent)
    VALUES (new.id, new.pasteSearchContent);
END;

-- Delete index on delete
CREATE TRIGGER IF NOT EXISTS PasteData_AD AFTER DELETE ON PasteDataEntity BEGIN
    INSERT INTO PasteDataEntityFts(PasteDataEntityFts, rowid, pasteSearchContent)
    VALUES('delete', old.id, old.pasteSearchContent);
END;

-- Update index on update (delete old, insert new)
CREATE TRIGGER IF NOT EXISTS PasteData_AU AFTER UPDATE ON PasteDataEntity BEGIN
    INSERT INTO PasteDataEntityFts(PasteDataEntityFts, rowid, pasteSearchContent)
    VALUES('delete', old.id, old.pasteSearchContent);
    INSERT INTO PasteDataEntityFts(rowid, pasteSearchContent)
    VALUES (new.id, new.pasteSearchContent);
END;

This way, whenever paste content is updated, the FTS table automatically reflects the latest search index without additional maintenance logic.

🧠 Query Statement Design

Search statements in CrossPaste implement keyword matching through FTS and support filtering conditions across multiple dimensions:

SELECT PasteDataEntity.*
FROM PasteDataEntity
JOIN PasteDataEntityFts ON PasteDataEntity.id = PasteDataEntityFts.rowid
WHERE
    pasteState != -1
    AND PasteDataEntityFts MATCH :searchQuery
    AND CASE WHEN :appInstanceId IS NOT NULL
        THEN CASE WHEN :local
            THEN appInstanceId = :appInstanceId
            ELSE appInstanceId != :appInstanceId
        END
        ELSE TRUE
    END
    AND CASE WHEN :favorite IS NOT NULL
        THEN favorite = :favorite
        ELSE TRUE
    END
    AND CASE WHEN :pasteType IS NOT NULL
        THEN pasteType = :pasteType
        ELSE TRUE
    END
ORDER BY CASE WHEN :sort THEN createTime ELSE -createTime END DESC
LIMIT :number;

The core condition is: PasteDataEntityFts MATCH :searchQuery

This statement performs high-performance matching based on FTS5's prefix indexing mechanism.

🧩 Building Search Statements in Kotlin

In Kotlin, we construct searchQuery like this:

val searchQuery = "pasteSearchContent:(${searchTerms.joinToString(" AND ") { "$it*" }})"

pasteDatabaseQueries.complexSearch(
    local = local == true,
    appInstanceId = appInstanceId,
    favorite = favorite,
    pasteType = pasteType?.toLong(),
    searchQuery = searchQuery,
    sort = sort,
    number = limit.toLong(),
    mapper = PasteData::mapper,
)

You'll notice we add * to each keyword because FTS5 only supports prefix search, not infix or suffix. Therefore, we concatenate prefix expressions for all keywords with AND.

❗ Why Is Tokenization Necessary?

FTS5 uses the unicode61 tokenizer by default, which works well for English and space-separated languages but has poor support for languages without spaces like Chinese, Japanese, and Korean. For Chinese, if we want to search for "微信截图" (WeChat screenshot), and the original text is:

"这是我刚刚在微信中收到的一张截图。" (This is a screenshot I just received in WeChat.)

The default tokenizer might treat the entire sentence as one long string, unable to correctly identify "微信" (WeChat) or "截图" (screenshot) as independent search units. This would cause queries like MATCH '微信*' to fail because the vocabulary doesn't have index entries starting with "微信".

✅ Solution: Preprocessing Text with Tokenization

To solve this problem, we need to tokenize the original text before writing to the database, converting it into "word segments" separated by spaces for FTS indexing. For example:

Original text:
这是我刚刚在微信中收到的一张截图。

Tokenization result:
这是 我 刚刚 在 微信 中 收到 的 一张 截图

Then concatenate into a space-separated string and store it in the pasteSearchContent field. FTS will create prefix indexes based on these tokens, enabling searches like "微信*" or "截图*" to match correctly.

🌐 Platform-Specific Tokenization Solutions Since SQLDelight is a cross-platform solution, we use different native tokenization implementations for each platform in the CrossPaste project:

Platform	Recommended Tokenization Method
Android	ICU4J (built into Android system) or jieba-android
JVM/Desktop	Use com.ibm.icu.text.BreakIterator for universal multilingual tokenization
iOS	Use system-native CFStringTokenizer or LinguisticTagger

As long as you ensure that what's written to the FTS index field is a high-quality keyword list separated by spaces, full-text search will perform well in all language environments.

✨ Significantly Improved Search Performance Thanks to this strategy, CrossPaste's search functionality performs excellently across diverse content including Chinese-English mixed text, emojis, links, and code snippets. Even though FTS itself only supports prefix search, through tokenization + universal concatenation:

val searchQuery = "pasteSearchContent:(${terms.joinToString(" AND ") { "$it*" }})"

We can still achieve a "full-text search" experience.