How to be a Database Lumberjack

Handling SQL in Android can be hard, boring and very very repetitive, for that reason most people either don't use DBs at all or they decide to switch to an ORM.

In this talk I want to bring that old friend back on steroids with the help of at least 1 lib, so the development can be much easy and pleasant.

Transcript

1. How to be a Database Lumberjack (In an ORM world) Alberto Ballano Android Developer @ XING AG
2. WHW (why? how? what?)
3. Why? - DBs are the most efficient way of storing mid-large amount of long-living data.
4. Why? - DBs are the most efficient way of storing mid-large amount of long-living data. - Basic SQL is boring and very annoying to write.
5. Why? - DBs are the most efficient way of storing mid-large amount of long-living data. - Basic SQL is boring and very annoying to write. - Most people don’t know/care about performance.
6. What about ORM? Pros: - Easier to use than SQLiteDatabase/ContentResolver - You don't have to deal with SQL - Object mapping Cons: - ORM can generate slow and/or crazy SQL queries - ORM often becomes bottleneck (performance & flexibility) in critical period of application development (quoted from Artem Zinnatullin in reddit)
7. What about ORM? Pros: - Apparently easier to use than SQLiteDatabase/ContentResolver - You won’t learn SQL - Object mapping? Cons: - Less powerful than SQL - You have to deal with ORM (not even talking about migrations) - ORM can generate slow and/or crazy SQL queries - ORM often becomes bottleneck (performance & flexibility) in critical period of application development (quoted from myself)
8. But Realm... - Lib size (.jar is around 4MB) - Method count - Autoincrement - Complex queries - Models need to extend RealmObject - Concurrency
9. DB performance? Is that a thing?
10. DB performance? Is that a thing? - Query on UI thread
11. DB performance? Is that a thing? - Query on UI thread - SELECT * for only 1 column
12. DB performance? Is that a thing? - Query on UI thread - SELECT * for only 1 column - No Null/count check
13. DB performance? Is that a thing? - Query on UI thread - SELECT * for only 1 column - No Null/count check - [Bonus] SQL injection available!
14. DB vs ORM ORM - Usually faster development at the beginning (depends on the lib) - Powerful enough for day to day requirements - Enough control with right lib - Ok to good speed always (except for SugarORM)
15. DB vs ORM DB - Can be harder at the beginning, specially without right tools/knowledge - Very powerful - Full control - Optimal speed if used well
16. How?
17. How? - SQLDelight
18. How? - SQLDelight (+ optional plugin)
19. How? - SQLDelight (+ optional plugin) - SQLBrite
20. How? - SQLDelight (+ optional plugin) - SQLBrite - [Extra] Autovalue/Retrolambda/Kotlin for reduced amount of code
21. How? - SQLDelight (+ optional plugin) - SQLBrite - [Extra] Autovalue/Retrolambda/Kotlin for reduced amount of code
22. How? - Define your model
23. How? - Define your model - Create your .sq file
24. How? - Define your model - Create your .sq file - Create the queries
25. How? - Define your model - Create your .sq file - Create the queries - Implement your generated Model + Factory + Mapper (Cursor -> Article) + Marshall (Article -> CV) + Adapter (for custom types) + Implementation class (for JOINs)
26. How? - Profit
27. What?
28. What? - Pure SQL -> JavaModel
29. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?"
30. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq)
31. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example)
32. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example) - Low model pollution
33. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example) - Low model pollution - Implement interface + declare Factory & Mapper (the rest can be getters!).
34. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example) - Low model pollution - Implement interface + declare Factory & Mapper (the rest can be getters!). - Easy conversion cursor -> model and viceversa
35. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example) - Low model pollution - Implement interface + declare Factory & Mapper (the rest can be getters!). - Easy conversion cursor -> model and viceversa - Easy storage of custom classes (via adapter)
36. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example) - Low model pollution - Implement interface + declare Factory & Mapper (the rest can be getters!). - Easy conversion cursor -> model and viceversa - Easy storage of custom classes (via adapter) // Hello old friend Calendar ;)
37. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example) - Low model pollution - Implement interface + declare Factory & Mapper (the rest can be getters!). - Easy conversion cursor -> model and viceversa - Easy storage of custom classes (via adapter) // Hello old friend Calendar ;) - Listen for changes in tables in the Rx way.
38. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example) - Low model pollution - Implement interface + declare Factory & Mapper (the rest can be getters!). - Easy conversion cursor -> model and viceversa - Easy storage of custom classes (via adapter) // Hello old friend Calendar ;) - Listen for changes in tables in the Rx way. - Relatively* easy Join projections
39. What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) - Anyone can write the SQL model (Backend devs, for example) - Low model pollution - Implement interface + declare Factory & Mapper (the rest can be getters!). - Easy conversion cursor -> model and viceversa - Easy storage of custom classes (via adapter) // Hello old friend Calendar ;) - Listen for changes in tables in the Rx way. - Relatively* easy Join projections *Really easy if using AutoValue
40. Links http://blog.alexsimo.com/delightful-persistence-android/ https://github.com/square/sqldelight https://github.com/google/auto/tree/master/value
41. The end Q&A
42. The end Thank you!