Story and experience about the migration of our database @ Busuu from SQLite to Room. Efficiency comparisons, benchmarking and personal views.
By @niksheva and @clau_cookie
Londroid - Android Meetup,
2018
No compile time checks 1MB BLOB Limitation Zero configuration Serverless Single file database Readable source code Handwritten SQL SQLite - embedded, open source and lightweight database
extends SQLiteOpenHelper { public void onCreate(SQLiteDatabase db) { // create db } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // upgrade existing db } }
extends SQLiteOpenHelper { public void onCreate(SQLiteDatabase db) { // create db } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // upgrade existing db } }
database.exec(CommunityExerciseImageTable.buildCreateTableQuery()); database.exec(CommunityExerciseCommentTable.buildCreateTableQuery()); database.exec(CommunityExerciseTable.buildCreateTableQuery()); // user database.exec(CommunityExerciseImageTable.buildCreateTableQuery()); database.exec(CommunityExerciseCommentTable.buildCreateTableQuery()); Define models Create db Create tables Copy the db Pre-bundler
final ContentValues values = new ContentValues(); final String userId = user.getId(); if (userId != null) { values.put(UserTable.COL_ID, userId); } final String name = user.getName(); if (name != null) { values.put(UserTable.COL_PERSONAL_NAME, name); } database.insertOrThrow(UserTable.TABLE_NAME, values); } Save data Read data
3.0 s (3012 ms) 3.1 s (3110 ms) 2 lessons 4.9 s (4915 ms) 5.7 s (9740 ms) 3 lessons 5.5 s (5542 ms) 6.1 s (9124 ms) 8 lessons 8.1 s (8133 ms) 11.2 s (11204 ms) Lower end devices
declaration @Entity( tableName = LESSONS_DB, indices = [(Index("remoteId", unique = true))] ) data class LessonEntity( @PrimaryKey(autoGenerate = true) val id: Int, val remoteId: String, val type: String, val bucket: Int?, val description: String, val thumbnail: String, val title: String )
[(Index("remoteId", unique = true))] ) data class LessonEntity( @PrimaryKey(autoGenerate = true) val id: Int, val remoteId: String, val type: String, val bucket: Int?, val description: String, val thumbnail: String, val title: String val newfield: String, ) Easy to upgrade Add a column in a table and increase database version
@Query("SELECT * FROM $ACTIVITIES_DB WHERE unitId = :unitId") abstract fun loadActivitiesWithUnitId(unitId: String): Maybe<List<ActivityEntity>> @Insert(onConflict = OnConflictStrategy.REPLACE) abstract fun insertExercise(exercise: ExerciseEntity) @Query("SELECT * FROM $GROUP_LEVEL_DB") abstract fun loadGroupLevels(): Flowable<List<GroupLevelEntity>> @Query("SELECT * FROM $EXERCISES_DB") abstract fun loadAllExercises(): Single<List<ExerciseEntity>> }
2.9 s (2912 ms) 3.0 s (3012 ms) 3.1 s (3110 ms) 2 lessons 4.9 s (4915 ms) 9.7 s (9740 ms) 3 lessons 5.5 s (5542 ms) 9.1 s (9124 ms) 4 lessons 8.1 s (8133 ms) 11.2 s (11204 ms)