Don't fear SQL (360AnDev 2017)

Don’t fear SQL: A better way to store and handle data with SQLBrite && SQLDelight

For many people, SQL can be intimidating when writing mobile apps. To avoid its complexity, various libraries started to implement techniques such as Object-Relational Mapping. They ease basic operations on complex objects, but come with a set of downsides such as decreased performance and the learning curve of a new library.

Square’s libraries SQLBrite and SQLDelight improve data manipulation by embracing all the powerful capabilities of SQLite while removing common frictions like runtime crashes, boilerplate code, and type-unsafe APIs.

In this talk I’ll present the reactive mindset behind SQLBrite and the code-generation capabilities of SQLDelight. When combined, these two libraries will help you architect and code safer and faster with queries autocompletion, code reuse, and much more.

Video: https://academy.realm.io/posts/360-andev-2017-leandro-favarin-sqlbrite-sqdelight/

Transcript

1. Don’t fear SQL A better way to store and handle data with SQLBrite & SQLDelight Leandro Favarin
2. Outline
3. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite 5. SQLDelight & SQLBrite 6. Room 7. Review
4. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite 5. SQLDelight & SQLBrite 6. Room 7. Review
5. SQLite
6. SQLite in Android
7. SQLite in Android – pros • Default choice for persisting relational data
8. SQLite in Android – pros • Default choice for persisting relational data • Powerful (cover incredibly amount of use cases)
9. SQLite in Android – pros • Default choice for persisting relational data • Powerful (cover incredibly amount of use cases) • Efficient
10. SQLite in Android – pros • Default choice for persisting relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast
11. SQLite in Android – pros • Default choice for persisting relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast • Stable
12. SQLite in Android – pros • Default choice for persisting relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast • Stable • Cross-platform
13. SQLite in Android – cons • It’s hard, because: • Cursors • Verboseness • No type-safety Implemented as an interface on top of a native SQLite bundle
14. SQLite in Android – cons Example of query from developer.android.com:
15. SQLite in Android – example SQLiteDatabase db = mDbHelper.getReadableDatabase();
 
 // Define a projection that specifies which columns from the database
 // you will actually use after this query.
 String[] projection = {
 FeedEntry._ID,
 FeedEntry.COLUMN_NAME_TITLE,
 FeedEntry.COLUMN_NAME_SUBTITLE
 };
 
 // Filter results WHERE "title" = 'My Title'
 String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
 String[] selectionArgs = { "My Title" };
 
 // How you want the results sorted in the resulting Cursor
 String sortOrder =
 FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
 
 Cursor cursor = db.query(
 FeedEntry.TABLE_NAME, // The table to query
 projection, // The columns to return
 selection, // The columns for the WHERE clause
 selectionArgs, // The values for the WHERE clause
 null, // don't group the rows
 null, // don't filter by row groups
 sortOrder // The sort order
 );
16. SQLite in Android – example // Filter results WHERE "title" = 'My Title'
 String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
 String[] selectionArgs = { "My Title" };
 
 // How you want the results sorted in the resulting Cursor
 String sortOrder =
 FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
17. SQLite in Android – example // Filter results WHERE "title" = 'My Title'
 String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
 String[] selectionArgs = { "My Title" };
 
 // How you want the results sorted in the resulting Cursor
 String sortOrder =
 FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
18. SQLite in Android – example // Filter results WHERE "title" = 'My Title'
 String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
 String[] selectionArgs = { "My Title" };
 
 // How you want the results sorted in the resulting Cursor
 String sortOrder =
 FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
19. SQLite in Android – example SQLiteDatabase db = mDbHelper.getReadableDatabase();
 
 // Define a projection that specifies which columns from the database
 // you will actually use after this query.
 String[] projection = {
 FeedEntry._ID,
 FeedEntry.COLUMN_NAME_TITLE,
 FeedEntry.COLUMN_NAME_SUBTITLE
 };
 
 // Filter results WHERE "title" = 'My Title'
 String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
 String[] selectionArgs = { "My Title" };
 
 // How you want the results sorted in the resulting Cursor
 String sortOrder =
 FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
 
 Cursor cursor = db.query(
 FeedEntry.TABLE_NAME, // The table to query
 projection, // The columns to return
 selection, // The columns for the WHERE clause
 selectionArgs, // The values for the WHERE clause
 null, // don't group the rows
 null, // don't filter by row groups
 sortOrder // The sort order
 );
20. SQLite in Android – example SQLiteDatabase db = mDbHelper.getReadableDatabase();
 
 // Define a projection that specifies which columns from the database
 // you will actually use after this query.
 String[] projection = {
 FeedEntry._ID,
 FeedEntry.COLUMN_NAME_TITLE,
 FeedEntry.COLUMN_NAME_SUBTITLE
 };
 
 // Filter results WHERE "title" = 'My Title'
 String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
 String[] selectionArgs = { "My Title" };
 
 // How you want the results sorted in the resulting Cursor
 String sortOrder =
 FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
 
 Cursor cursor = db.query(
 FeedEntry.TABLE_NAME, // The table to query
 projection, // The columns to return
 selection, // The columns for the WHERE clause
 selectionArgs, // The values for the WHERE clause
 null, // don't group the rows
 null, // don't filter by row groups
 sortOrder // The sort order
 ); List<BdItem> result = new ArrayList <>();
 while(cursor.moveToNext()) {
 long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
 String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
 String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
 DbItem item = new DbItem(itemId, title, subtitle);
 result.add(item);
 }
 cursor.close();
21. SQLite in Android – example List<BdItem> result = new ArrayList <>();
 while(cursor.moveToNext()) {
 long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
 String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
 String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
 DbItem item = new DbItem(itemId, title, subtitle);
 result.add(item);
 }
 cursor.close();
22. SQLite in Android – example List<BdItem> result = new ArrayList <>();
 while(cursor.moveToNext()) {
 long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
 String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
 String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
 DbItem item = new DbItem(itemId, title, subtitle);
 result.add(item);
 }
 cursor.close();
23. SQLite in Android – example List<BdItem> result = new ArrayList <>();
 while(cursor.moveToNext()) {
 long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
 String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
 String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
 DbItem item = new DbItem(itemId, title, subtitle);
 result.add(item);
 }
 cursor.close();
24. SQLite in Android – example List<BdItem> result = new ArrayList <>();
 while(cursor.moveToNext()) {
 long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
 String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
 String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
 DbItem item = new DbItem(itemId, title, subtitle);
 result.add(item);
 }
 cursor.close();
25. SQLite in Android – example List<BdItem> result = new ArrayList <>();
 while(cursor.moveToNext()) {
 long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
 String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
 String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
 DbItem item = new DbItem(itemId, title, subtitle);
 result.add(item);
 }
 cursor.close();
26. SQLite in Android Certainly there has to be a better way
27. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite 5. SQLDelight & SQLBrite 6. Room 7. Review
28. ORM libraries
29. ORM libraries What is ORM?
30. What is ORM? Technique for converting data between incompatible type systems in OO languages … basically: Converting between complex data types to “primitives" Wraps a SQL source Examples: greenDAO, OrmLite, Cupboard, Core Data (iOS)
31. ORM – cons • New APIs to be learned
32. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities
33. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities • Decreased performance
34. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers
35. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Objects are usually mutable
36. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Objects are usually mutable • Lock-in effect
37. SQL should be embraced, not hidden
38. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite 5. SQLDelight & SQLBrite 6. Room 7. Review
39. SQLDelight
40. SQLDelight • Empower SQL(ite) by embracing it • Type-safe APIs to read & write the rows of your tables • Organizes your SQL statements • Easy to access from Java
41. SQLDelight – how it works • Generates code from SQL `CREATE TABLE` statements • Create special files containing all SQL-related content • Extension: .sq • Runs on compile-time
42. SQLDelight – build.gradle buildscript {
 repositories {
 mavenCentral()
 }
 dependencies {
 classpath 'com.squareup.sqldelight:gradle-plugin:0.6.1'
 }
 }
 
 apply plugin: 'com.squareup.sqldelight'
43. SQLDelight – IntelliJ plugin
44. SQLDelight – IntelliJ plugin Provides language-level features for .sq files, including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion • Generate Model files after edits • Right click to copy as valid SQLite • Compiler errors in IDE click through to file
45. SQLDelight – IntelliJ plugin
46. SQLDelight – keep in mind • The file structure of your project is crucial for this to work • .sq files must be in a folder matching the directory structure
47. SQLDelight example
48. SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java ...
49. SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java ... - sqldelight/ - com/example/project/models/ - Book.sq ...
50. SQLDelight – Book.sq CREATE TABLE book ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, num_chapters INTEGER );
51. SQLDelight – Book.sq CREATE TABLE book ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, num_chapters INTEGER ); selectAll: SELECT * FROM book;
52. SQLDelight – Book.sq CREATE TABLE book ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, num_chapters INTEGER ); selectAll: SELECT * FROM book; insertRow: INSERT INTO book(title, author, num_chapters) VALUES (?, ?, ?);
53. SQLDelight – … generates: public interface BookModel { String TABLE_NAME = "book"; String _ID = "_id"; String TITLE = "title"; String AUTHOR = "author"; String NUM_CHAPTERS = "num_chapters"; String CREATE_TABLE = "" + "CREATE TABLE book (\n" + " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + " title TEXT NOT NULL,\n" + " author TEXT NOT NULL,\n" + " num_chapters INTEGER\n" + ")"; long _id(); @NonNull String title(); @NonNull String author(); @Nullable Long num_chapters(); ... }
54. SQLDelight – … generates: long _id(); @NonNull String title(); @NonNull String author(); @Nullable Long num_chapters();
55. SQLDelight – … generates: public interface BookModel { String TABLE_NAME = "book"; String _ID = "_id"; String TITLE = "title"; String AUTHOR = "author"; String NUM_CHAPTERS = "num_chapters"; String CREATE_TABLE = "" + "CREATE TABLE book (\n" + " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + " title TEXT NOT NULL,\n" + " author TEXT NOT NULL,\n" + " num_chapters INTEGER\n" + ")"; long _id(); @NonNull String title(); @NonNull String author(); @Nullable Long num_chapters(); ... }
56. SQLDelight – … generates: class Mapper<T extends BookModel> implements RowMapper<T> { @Override public T map(@NonNull Cursor cursor) { return bookModelFactory.creator.create( cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getLong(3) ); } } class Mapper<T extends BookModel> implements RowMapper<T> { @Override public T map(@NonNull Cursor cursor) { return bookModelFactory.creator.create( cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getLong(3) ); } }
57. SQLDelight – Book.java @AutoValue public abstract class Book implements BookModel { }
58. SQLDelight – Book.java @AutoValue public abstract class Book implements BookModel { public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); }
59. SQLDelight – Book.java @AutoValue public abstract class Book implements BookModel { public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); public static final Mapper<Book> ROW_MAPPER = FACTORY.selectAllMapper(); }
60. SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query = Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { } }
61. SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query = Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { List<Book> result = new ArrayList <>(c.getCount()); while (c.moveToNext()) { result.add(Book.ROW_MAPPER.map(c)); } return result; } }
62. SQLDelight – projections, joins Each SELECT statement will have: • an interface • mapper • method to create a new instance of the mapper
63. SQLDelight – types • Identical to SQLite column definitions • Plus extra column constraint for Java type
64. SQLDelight – types CREATE TABLE some_types ( some_long INTEGER, -- Stored as INTEGER, retrieved as Long some_double REAL, -- Stored as REAL, retrieved as Double some_string TEXT, -- Stored as TEXT, retrieved as String some_blob BLOB, -- Stored as BLOB, retrieved as byte[] some_int INTEGER AS Integer, -- Stored as INTEGER, retrieved as Integer some_short INTEGER AS Short, -- Stored as INTEGER, retrieved as Short some_float REAL AS Float -- Stored as REAL, retrieved as Float );
65. SQLDelight – booleans CREATE TABLE some_types ( some_long INTEGER, -- Stored as INTEGER, retrieved as Long some_double REAL, -- Stored as REAL, retrieved as Double some_string TEXT, -- Stored as TEXT, retrieved as String some_blob BLOB, -- Stored as BLOB, retrieved as byte[] some_int INTEGER AS Integer, -- Stored as INTEGER, retrieved as Integer some_short INTEGER AS Short, -- Stored as INTEGER, retrieved as Short some_float REAL AS Float, -- Stored as REAL, retrieved as Float some_boolean INTEGER AS Boolean DEFAULT 0 -- Stored as INTEGER, retrieved as Boolean );
66. SQLDelight – custom classes import org.threeten.bp.ZonedDateTime; CREATE TABLE book ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT, publishedAt TEXT AS ZonedDateTime );
67. SQLDelight – custom classes class ZonedDateTimeColumnAdapter implements ColumnAdapter<ZonedDateTime, String> { static final DateTimeFormatter FORMATTER = DateTimeFormatter.ISO_OFFSET_DATE_TIME; @NonNull @Override public ZonedDateTime decode(String databaseValue) { return FORMATTER.parse(databaseValue, ZonedDateTime.FROM); } @Override public String encode(@NonNull ZonedDateTime value) { return FORMATTER.format(value); } } class ZonedDateTimeColumnAdapter implements ColumnAdapter<ZonedDateTime, String> { static final DateTimeFormatter FORMATTER = DateTimeFormatter.ISO_OFFSET_DATE_TIME; @NonNull @Override public ZonedDateTime decode(String databaseValue) { return FORMATTER.parse(databaseValue, ZonedDateTime.FROM); } @Override public String encode(@NonNull ZonedDateTime value) { return FORMATTER.format(value); } }
68. SQLDelight – enums • Includes a built-in ColumnAdapter for storing an enum as TEXT
69. SQLDelight – views • Same treatment in generated code as tables with their own model interface
70. Does it work with Kotlin?
71. SQLDelight – Kotlin • Mostly works!
72. SQLDelight – Kotlin • Mostly works! • .. just have to redundantly declare getters
73. SQLDelight – Kotlin CREATE TABLE book ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT, edition INTEGER AS Integer NOT NULL ); selectAll: SELECT * FROM book; insertRow: INSERT INTO book(title, author, edition) VALUES (?, ?, ?);
74. SQLDelight – Kotlin data class Book( val _id: Long, val title: String, val author: String?, val edition: Int ) : BookModel { companion object { val FACTORY = BookModel.Factory( ::Book) val SELECT_ALL_MAPPER = FACTORY.selectAllMapper() } override fun _id() = _id override fun title() = title override fun author() = author override fun edition() = edition } data class Book( val _id: Long, val title: String, val author: String?, val edition: Int ) : BookModel { companion object { val FACTORY = BookModel.Factory( ::Book) val SELECT_ALL_MAPPER = FACTORY.selectAllMapper() } override fun _id() = _id override fun title() = title override fun author() = author override fun edition() = edition }
75. SQLDelight – migrations • Least supported part of SQLDelight • Library has potential to execute migrations for you • Likely to be implemented using specific files .sqm (issue #89) • Today’s solution: write strings in your SQLiteOpenHelper … or • Create a migration .sq with unused `CREATE TABLE` statement • Write your migration like any SQL statement • Cons: Unused generated code
76. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite 5. SQLDelight & SQLBrite 6. Room 7. Review
77. SQLBrite
78. SQLBrite • Wrapper around SQLiteOpenHelper which introduces reactive stream semantics to SQL operations
79. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive stream semantics to SQL operations
80. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive stream semantics to SQL operations • Updates to the subscribed table(s) will trigger notifications
81. SQLBrite – build.gradle compile ‘com.squareup.sqlbrite2:sqlbrite:2.0.0’
82. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .build();
83. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message -> Timber.tag("Database").v(message)) .build();
84. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder()
 .logger(message -> Timber.tag("Database").v(message))
 .queryTransformer()
 .build();
85. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message -> Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());
86. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message -> Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io()); db.setLoggingEnabled(boolean);
87. SQLBrite – content resolvers SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message -> Timber.tag("Database").v(message)) .build(); BriteContentResolver db = sqlBrite.wrapContentProvider( contentResolver, Schedulers.io()); db.setLoggingEnabled(boolean);
88. SQLBrite – powerful logging QUERY (0ms) tables: [book] sql: SELECT * FROM book WHERE title = ?1 AND author = ?2 args: ["1984","George Orwell"] EXECUTE SQLiteProgram: INSERT INTO book(title, author) VALUES(?, ?)
89. SQLBrite – BookRepo.java Observable<List<Book >> getAllBooks(BriteDatabase db) { QueryObservable books = db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> { Cursor run = query.run(); // parse data }); }
90. SQLBrite – BookRepo.java BriteDatabase db = // ...; QueryObservable books = db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> /* ... */);
91. SQLBrite – BookRepo.java BriteDatabase db = // ...; QueryObservable books = db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> /* ... */); db.insert("book", Book.create("1984", "George Orwell")); db.delete("book", "title = ?", "The Great Gatsby"); db.update("book", ...); BriteDatabase db = // ...; QueryObservable books = db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> /* ... */); db.insert("book", Book.create("1984", "George Orwell")); db.delete("book", "title = ?", "The Great Gatsby"); db.update("book", ...); Will emit updates to subscribers
92. SQLBrite – transactions • Can be used to group changes and be notified only once • transaction.markSuccessful() then triggers emission
93. Does it work with Kotlin?
94. SQLBrite – Kotlin • Yes! • Kotlin extensions methods are also provided
95. SQLBrite – build.gradle compile ‘com.squareup.sqlbrite2:sqlbrite:2.0.0’
96. SQLBrite – build.gradle compile ‘com.squareup.sqlbrite2:sqlbrite:2.0.0’ compile ‘com.squareup.sqlbrite2:sqlbrite-kotlin:2.0.0’
97. SQLBrite – Kotlin module Provides mapTo* extension functions: • Observable<Query>.mapToOne() • Observable<Query>.mapToOneOrDefault() • Observable<Query>.mapToOptional() • Observable<Query>.mapToList()
98. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite 5. SQLDelight & SQLBrite 6. Room 7. Review
99. SQLDelight & SQLBrite
100. SQLDelight & SQLBrite CREATE TABLE book ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT, author TEXT, publishedAt TEXT AS ZonedDateTime ); selectByPublishedAt: SELECT * FROM book WHERE publishedAt = ?; insertRow: INSERT INTO book(title, author, publishedAt) VALUES (?, ?, ?);
101. SQLDelight & SQLBrite @AutoValue public abstract class Book implements BookModel { static Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new, new ZonedDateTimeColumnAdapter()); static Mapper<Book> ROW_MAPPER = FACTORY.selectByPublishedAtMapper(); }
102. SQLDelight & SQLBrite Observable<Book> getBookByPublishedDate(BriteDatabase db, ZonedDateTime publishedAt) { SqlDelightStatement query = Book.FACTORY.selectByPublishedAt(publishedAt); return db.createQuery(query.tables, query.statement, query.args) .mapToOne(Book.ROW_MAPPER ::map); } Observable<Book> getBookByPublishedDate(BriteDatabase db, ZonedDateTime publishedAt) { SqlDelightStatement query = Book.FACTORY.selectByPublishedAt(publishedAt); return db.createQuery(query.tables, query.statement, query.args) .mapToOne(Book.ROW_MAPPER ::map); } Observable<Book> getBookByPublishedDate(BriteDatabase db, ZonedDateTime publishedAt) { SqlDelightStatement query = Book.FACTORY.selectByPublishedAt(publishedAt); return db.createQuery(query.tables, query.statement, query.args) .mapToOne(Book.ROW_MAPPER ::map); }
103. How would it look like using traditional Android APIs?
104. Traditional Android APIs Book getBookByPublishedDate(SQLiteOpenHelper openHelper, ZonedDateTime publishedAt) { SQLiteDatabase db = openHelper.getReadableDatabase(); String[] projection = { Book.TITLE, Book.AUTHOR, Book.PUBLISHEDAT }; String selection = Book.PUBLISHEDAT + " = ?"; String[] selectionArgs = {FORMATTER.format(publishedAt)}; Cursor cursor = db.query( Book.TABLE_NAME, projection, selection, selectionArgs, null, null, null); Book book = null; if (cursor.moveToNext()) { String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE)); String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR)); String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT)); ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr, ZonedDateTime.FROM); book = Book.create(title, author, parsed); } cursor.close(); return book; }
105. Traditional Android APIs Book getBookByPublishedDate(SQLiteOpenHelper openHelper, ZonedDateTime publishedAt) { SQLiteDatabase db = openHelper.getReadableDatabase(); String[] projection = { Book.TITLE, Book.AUTHOR, Book.PUBLISHEDAT }; String selection = Book.PUBLISHEDAT + " = ?"; String[] selectionArgs = {FORMATTER.format(publishedAt)}; Cursor cursor = db.query( Book.TABLE_NAME, projection, selection, selectionArgs, null, null, null); Book book = null; if (cursor.moveToNext()) { String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE)); String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR)); String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT)); ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr, ZonedDateTime.FROM); book = Book.create(title, author, parsed); } cursor.close(); return book; } Book getBookByPublishedDate(SQLiteOpenHelper openHelper, ZonedDateTime publishedAt) { SQLiteDatabase db = openHelper.getReadableDatabase(); String[] projection = { Book.TITLE, Book.AUTHOR, Book.PUBLISHEDAT }; String selection = Book.PUBLISHEDAT + " = ?"; String[] selectionArgs = {FORMATTER.format(publishedAt)}; Cursor cursor = db.query( Book.TABLE_NAME, projection, selection, selectionArgs, null, null, null); Book book = null; if (cursor.moveToNext()) { String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE)); String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR)); String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT)); ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr, ZonedDateTime.FROM); book = Book.create(title, author, parsed); } cursor.close(); return book; }
106. SQLDelight & SQLBrite public class BookRepository {
 private final BriteDatabase db;
 private final Book.InsertRow insertRow;
 
 public BookRepository(BriteDatabase db) {
 this.db = db;
 insertRow = new Book.InsertRow(db.getWritableDatabase());
 }
 
 public void insert(Book book) {
 insertRow.bind(book.title(), book.author());
 db.executeInsert(insertRow.table, insertRow.program);
 }
 } public class BookRepository {
 private final BriteDatabase db;
 private final Book.InsertRow insertRow;
 
 public BookRepository(BriteDatabase db) {
 this.db = db;
 insertRow = new Book.InsertRow(db.getWritableDatabase());
 }
 
 public void insert(Book book) {
 insertRow.bind(book.title(), book.author());
 db.executeInsert(insertRow.table, insertRow.program);
 }
 } public class BookRepository {
 private final BriteDatabase db;
 private final Book.InsertRow insertRow;
 
 public BookRepository(BriteDatabase db) {
 this.db = db;
 insertRow = new Book.InsertRow(db.getWritableDatabase());
 }
 
 public void insert(Book book) {
 insertRow.bind(book.title(), book.author());
 db.executeInsert(insertRow.table, insertRow.program);
 }
 }
107. SQLDelight & SQLBrite • SQLDelight and SQLBrite are highly optimized • Lightweight (~160 methods + generated code)
108. Powerful combination
109. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite 5. SQLDelight & SQLBrite 6. Room 7. Review
110. Room
111. Room (briefly)
112. Room • Released this year at Google I/O • Database part of Architecture Components • Aims for the same goal as to SQLDelight & SQLBrite
113. Room • Avoids boilerplate code • Compile-time checked SQL queries • Allows asynchronous queries Aims for the same goal as to SQLDelight & SQLBrite: More information: Data Persistence In Android: There’s Room For Improvement Florina Muntenescu
114. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite 5. SQLDelight & SQLBrite 6. Room 7. Review
115. Review
116. Review • Type-safe marshalling & unmarshalling • Compile-time SQL validation • SQL autocompletion • Incentivizes immutability • All SQLite feature-set at your disposal • Migration process is not ideal SQLDelight
117. Review • Type-safe marshalling & unmarshalling • Compile-time SQL validation • SQL autocompletion • Incentivizes immutability • All SQLite feature-set at your disposal • Migration process is not ideal SQLDelight • Content providers support • Real-time UI updates • Agnostic to object mapper or SQL source SQLBrite
118. Questions?
119. Thanks. @leandrofavarin www.leandrofavarin.com