Introduction

As Kotlin Multiplatform (KMP) continues to gain traction, developers are seeking robust solutions for cross-platform data storage. The androidx.sqlite library, part of Android Jetpack, introduces BundledSQLiteDriver—a modern, embedded SQLite driver designed for seamless use across Android, iOS, JVM, and desktop platforms. In this article, we’ll explore how BundledSQLiteDriver differs from the default Android SQLite implementation, its unique features, limitations, and performance characteristics.

Background: Room, androidx.sqlite, and Abstractions

Room and SQLite Access

Room is a popular ORM-like library for local data storage on Android, built on top of SQLite. Since early versions, Room has accessed SQLite through abstraction interfaces provided by the androidx.sqlite:sqlite library, such as:

• SupportSQLiteOpenHelper
• SupportSQLiteDatabase
• SupportSQLiteStatement

This abstraction allows Room to swap out the underlying SQLite implementation. The default is FrameworkSQLiteOpenHelper from androidx.sqlite:sqlite-framework, but third-party libraries like SQLCipher or requery/sqlite-android can also be used.

Limitations for Multiplatform

These original interfaces closely mirrored Android’s platform classes and depended on Android-specific types (e.g., Cursor, ContentObserver, Context). As a result, they were unsuitable for Kotlin Multiplatform projects.

The KMP Rewrite

With version 2.5.0, androidx.sqlite was refactored for Kotlin Multiplatform. New, platform-agnostic interfaces were introduced:

• SQLiteDriver: Opens databases and creates connections
• SQLiteConnection: Manages database interactions and statement creation
• SQLiteStatement: Represents prepared SQL statements

These interfaces are simpler, more concise, and closer to the SQLite C API, making them ideal for multiplatform use. They also allow for easy extension—such as logging, profiling, analytics, or encryption—via decorators.

Available SQLiteDriver Implementations

Three main implementations are provided:

• AndroidSQLiteDriver: Uses the system SQLite APIs; Android-only.
• NativeSQLiteDriver: For iOS, Mac, and Linux; links to the system’s libsqlite.
• BundledSQLiteDriver: Ships a custom SQLite build with your app; recommended for Android, iOS, Mac, Linux, and JVM.

While androidx.sqlite is designed as a foundation for higher-level frameworks, you can use it directly in your projects, including on the JVM as an alternative to SQLite JDBC.

Why Use BundledSQLiteDriver?

When using BundledSQLiteDriver on Android, you gain several advantages:

1. Modern SQLite Version

Unlike AndroidSQLiteDriver, which relies on the device’s system SQLite (often outdated), BundledSQLiteDriver always uses a recent SQLite release.

2. Predictable Behavior Across Devices

Since the SQLite library is bundled, you can count on consistent features, extensions, and query planner behavior, regardless of device or OS version.

3. Improved Performance

• Faster Queries: Benchmarks show 20–28% faster execution compared to the system driver.
• No Legacy Buffering: The system driver uses a fixed-size buffer (CursorWindow) for inter-process communication, which can be a bottleneck. BundledSQLiteDriver avoids this.

Trade-offs

• App Size: Bundling SQLite increases your APK by about 1MB.
• Memory Usage: Potentially higher memory consumption.

Limitations and Compatibility Considerations

While BundledSQLiteDriver brings many benefits, there are some important limitations, especially if you’re migrating from AndroidSQLiteDriver:

1. Localization Support

• No ICU Extension: Functions like case-insensitive LIKE, upper(), lower(), and regional COLLATE rules may not work as expected.
• No REGEXP Operator: Regular expression queries are unavailable.
• Workarounds: For Russian and English, you can normalize text case before saving. Alternatively, consider using Full-Text Search (FTS) with a Unicode tokenizer.

2. Android-Specific Extensions

• Only standard collations (BINARY, RTRIM, NOCASE) are available.
• Android-specific functions (e.g., PHONE_NUMBERS_EQUAL()) are not supported.

3. Missing SQLite Options

• Features like SQLITE_ENABLE_BYTECODE_VTAB and SQLITE_ENABLE_DBSTAT_VTAB are not enabled (as of this writing).
• Some legacy PRAGMAs and shared cache support are removed.

Unique Features of BundledSQLiteDriver

BundledSQLiteDriver unlocks several modern SQLite features not available in the default Android driver (as of SQLite 3.32.2):

• FTS5: Advanced full-text search extension.
• JSON Functions: Native JSON and JSONB support for storing and querying structured data.
• *R-Tree Indexes:** Efficient spatial indexing for range and geospatial queries.
• RIGHT and FULL OUTER JOIN: Expanded SQL join capabilities.
• ALTER TABLE DROP COLUMN: Remove columns from tables (with some restrictions).
• Enhanced UPSERT and RETURNING: More flexible conflict handling and ability to return generated values.
• UPDATE FROM: Update queries using data from other tables or subqueries.
• CTE Table Types: Specify materialization in Common Table Expressions.
• STAT4 Statistics: Improved query planner statistics for better performance.
• Extended Date/Time Functions: New functions and modifiers for date/time calculations.
• Additional Functions: concat(), string_agg(), unhex(), octet_length(), and more.
• PRAGMA table_list: Retrieve table and view metadata.
• STRICT Tables: Enforce strict schema and data insertion rules.
• Default PRAGMA synchronous: Now set to NORMAL for better performance.

Performance Benchmarks

Using Androidx Microbenchmark, tests compared AndroidSQLiteDriver and BundledSQLiteDriver on Android 13 with a large dataset (~600MB, 881k records):

• Database Creation: Faster with BundledSQLiteDriver
• Paged Selects: Faster with BundledSQLiteDriver
• Complex Queries: Faster with BundledSQLiteDriver

Lower is better: BundledSQLiteDriver consistently outperforms the system driver by 20–28%.

For full test details and code, see the sqlite-driver-benchmark repository.

Conclusion & Recommendations

BundledSQLiteDriver is a powerful choice for Kotlin Multiplatform and modern Android projects, offering:

• Access to the latest SQLite features
• Predictable, cross-platform behavior
• Significant performance improvements

However, consider the following before adopting it:

• Localization Needs: If your app relies heavily on advanced localization or regional collation, test thoroughly or consider alternatives.
• App Size Constraints: The increased APK size may be a concern for some projects.

Tip: Always benchmark with your own data and queries to ensure the driver meets your performance and compatibility requirements.

Further Reading:

• androidx.sqlite documentation
• SQLite Release Notes
• Kotlin Multiplatform Documentation

If you have questions or want to share your experience with BundledSQLiteDriver, let us know in the comments!