Leave your room behind

SQLDelight vs Room

Our own story towards SQLite
What is a good android SQLite abstraction?
Schema generation
Statements
Migrations
RxJava, Paging libraries
Future

1. Leave your Room behind Rakhimov Andrii Lead Android Engineer at Lalafo andrii.rakhimov@gmail.com
2. Agenda ➔ Our own story towards SQLite ➔ What is a good android SQLite abstraction? ➔ Schema generation ➔ Statements ➔ Migrations ➔ RxJava, Paging libraries ➔ Future
3. Story
4. Story Internal Analytics Categories Countries Chats Messages Favorite Ads
5. Forever...
6. Shared Preferences <?xml version='1.0' encoding='utf-8' standalone='yes' ?> <map> <string name="countries"> [{"id":1,"name":"Azərbaycan","code":"AZ","default_language":"az_AZ","alias":"azerbaijan","d omain":"lalafo.az","time_zone":"Asia/Baku"...}]</string> <boolean name="innertube_safety_mode_enabled" value="false" /> <long name="delayed_event_last_dispatch_time_ms" value="1551718329606" /> </map>
7. Shared Preferences + Simple + Works well below ~200 of values - Slow, file will be rewritten each time - Slow, manual sorting, filtering of data - Data duplication - Threading - Scales badly
8. What to choose? ORMs Object Databases Room SQLDelight Realm ?
9. SQLDelight ➔ Simple enough ➔ Embraces SQL ➔ Typesafe ➔ Full Kotlin ➔ IDE support ➔ Multiplatform
10. • Proper SQLite abstraction
11. • Proper SQLite abstraction
12. Proper SQLite abstraction
13. SQLDriver Proper SQLite abstraction
14. Cool! Proper SQLite abstraction
15. Proper SQLite abstraction ➔ Extensible, i.e. can easily swap platform or Android SQLite implementation ➔ Simple ➔ Testable
16. Driver val driver: SqlDriver = AndroidSqliteDriver(Database.Schema, context, "test.db")
17. Schema generation and types CREATE TABLE some_types ( some_long INTEGER NOT NULL PRIMARY KEY, -- Stored as INTEGER in db, retrieved as Long some_double REAL, -- Stored as REAL in db, retrieved as Double some_string TEXT, -- Stored as TEXT in db, retrieved as String some_blob BLOB, -- Stored as BLOB in db, retrieved as ByteArray some_int INTEGER AS Int, -- Stored as INTEGER in db, retrieved as Int some_short INTEGER AS Short, -- Stored as INTEGER in db, retrieved as Short some_float REAL AS Float -- Stored as REAL in db, retrieved as Float );
18. Custom Column Types import ...Message; CREATE TABLE messages( _id TEXT NOT NULL PRIMARY KEY, message BLOB AS Message ); class SerializeAdapter<T : Any> : ColumnAdapter<T, ByteArray> { override fun decode(bytes: ByteArray) = SerializeUtils.deserialize(bytes) as T override fun encode(t: T): ByteArray = SerializeUtils.serialize(t) } val db = LalafoDb.invoke( AndroidSqliteDriver(helper), Messages.Adapter(SerializeAdapter<Message>()) )
19. Enums Import ....SendStatus; CREATE TABLE messages( _id TEXT NOT NULL PRIMARY KEY, status TEXT AS SendStatus ); val db = LalafoDb.invoke( AndroidSqliteDriver(helper), Messages.Adapter(EnumColumnAdapter()) )
20. SQLite statements getMessages: SELECT * FROM messages WHERE chat_id = ? ORDER BY creation_time DESC LIMIT 60 val messages = lalafoDb.messagesQueries.getMessages(chatId) val list: List<Messages> = messages.executeAsList()
21. SQLite statements removeMessage: DELETE FROM messages WHERE _id = ?; lalafoDb.messagesQueries.removeMessage(messageId)
22. Driver in real app @Provides @Singleton static SupportSQLiteOpenHelper provideSQLiteOpenHelper(App context) { SupportSQLiteOpenHelper.Configuration configuration = SupportSQLiteOpenHelper.Configuration.builder(context) .name(DbCallback.DB_NAME) .callback(new DbCallback(context)) .build(); SupportSQLiteOpenHelper.Factory factory = new FrameworkSQLiteOpenHelperFactory(); return factory.create(configuration); } @Provides @Singleton static LalafoDb provideDb(SupportSQLiteOpenHelper helper) { return LalafoDb.Companion.invoke( new AndroidSqliteDriver(helper), new Categories.Adapter(new SerializeAdapter<>()), ... new Messages.Adapter(new EnumColumnAdapter<>(SendStatus.values()) )...
23. Migrations DROP TABLE IF EXISTS chats; DROP TABLE IF EXISTS messages; CREATE TABLE chats( thread_id TEXT NOT NULL PRIMARY KEY, updated INTEGER NOT NULL, ... ); CREATE TABLE messages( _id TEXT NOT NULL PRIMARY KEY, creation_time INTEGER NOT NULL, ... );
24. Migrations
25. RxJava getMessages: SELECT * FROM messages WHERE chat_id = ? ORDER BY creation_time DESC LIMIT 60 val messagesStream = lalafoDb.messagesQueries .getMessages(chatId) .asObservable(Schedulers.io()) .mapToList()
26. Android Paging countMessages: SELECT count(*) FROM messages; getMessages: SELECT * FROM messages LIMIT :limit OFFSET :offset; val messagesQueries = lalafoDb.messagesQueries val dataSource = QueryDataSourceFactory( queryProvider = messagesQueries::getMessage, countQuery = messagesQueries.countMessages() ).create()
27. Demo
28. Multiplatform Schema & Statements Generated Code SqlDriver AndroidSqlDriver NativeSqlDriver
29. Multiplatform val driver: SqlDriver = NativeSqliteDriver(Database.Schema, "test.db")
30. Coroutines ➔ Soon….
31. Other SQL dialects
32. Take responsibility •
33. Healthy competition creates better products
34. Fragments @SuppressWarnings("deprecation") Object getLastCustomNonConfigurationInstance()
35. Do not blindly take what Google propose. For us SQL-first, full Kotlin and multiplatform are big advantages. What about You?
36. Thank you! Questions and answers Rakhimov Andrii Lead Android Engineer at Lalafo andrii.rakhimov@gmail.com https://github.com/ar-g