How to be a Database Lumberjack (In an ORM world) Alberto Ballano Android Developer @ XING AG 

WHW (why? how? what?) 

Why? - DBs are the most efficient way of storing mid-large amount of long-living data. 

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. 

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. 

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) 

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) 

But Realm... - Lib size (.jar is around 4MB) - Method count - Autoincrement - Complex queries - Models need to extend RealmObject - Concurrency 

DB performance? Is that a thing? 

DB performance? Is that a thing? - Query on UI thread 

DB performance? Is that a thing? - Query on UI thread - SELECT * for only 1 column 

DB performance? Is that a thing? - Query on UI thread - SELECT * for only 1 column - No Null/count check 

DB performance? Is that a thing? - Query on UI thread - SELECT * for only 1 column - No Null/count check - [Bonus] SQL injection available! 

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) 

DB vs ORM DB - Can be harder at the beginning, specially without right tools/knowledge - Very powerful - Full control - Optimal speed if used well 

How? 

How? - SQLDelight 

How? - SQLDelight (+ optional plugin) 

How? - SQLDelight (+ optional plugin) - SQLBrite 

How? - SQLDelight (+ optional plugin) - SQLBrite - [Extra] Autovalue/Retrolambda/Kotlin for reduced amount of code 

How? - SQLDelight (+ optional plugin) - SQLBrite - [Extra] Autovalue/Retrolambda/Kotlin for reduced amount of code 

How? - Define your model 

How? - Define your model - Create your .sq file 

How? - Define your model - Create your .sq file - Create the queries 

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) 

How? - Profit 

What? 

What? - Pure SQL -> JavaModel 

What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" 

What? - Pure SQL -> JavaModel - Focus. Avoid "SELECT * FROM " + MY_TABLE + " WHERE " + MyTable.COLUMN + " = ?" - Everything in one place (src/main/sqldelight/your/package/model.sq) 

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) 

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 

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!). 

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 

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) 

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 ;) 

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. 

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 

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 

Links http://blog.alexsimo.com/delightful-persistence-android/ https://github.com/square/sqldelight https://github.com/google/auto/tree/master/value 

The end Q&A 

The end Thank you!