Save 37% off PRO during our Black Friday Sale! »

Don't fear SQL: A better way to store and handle data with SQLBrite & SQLDelight (GDG Berlin Android Jun-2017)

Don't fear SQL: A better way to store and handle data with SQLBrite & SQLDelight (GDG Berlin Android Jun-2017)

Fe32de590ba76907182df4e2a211e086?s=128

Leandro Favarin

June 28, 2017
Tweet

Transcript

  1. Don’t fear SQL A better way to store and handle

    data with SQLBrite & SQLDelight Leandro Favarin
  2. SQLite

  3. SQLite in Android

  4. SQLite in Android – pros • Default choice for persisting

    relational data
  5. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful
  6. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful • Efficient
  7. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful • Efficient • Fast
  8. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful • Efficient • Fast • Stable
  9. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful • Efficient • Fast • Stable • Flexible
  10. SQLite in Android – cons • It’s hard!

  11. SQLite in Android – cons • It’s hard! • Cursors

  12. SQLite in Android – cons • It’s hard! • Cursors

    • Verboseness
  13. SQLite in Android – cons • It’s hard! • Cursors

    • Verboseness • No type-safety
  14. ORM libraries

  15. What is ORM? Technique for converting data between incompatible type

    systems in OO languages
  16. What is ORM? Technique for converting data between incompatible type

    systems in OO languages … basically: Converting between complex data types to "primitives"
  17. What is ORM? Technique for converting data between incompatible type

    systems in OO languages … basically: Converting between complex data types to "primitives" Examples: greenDAO, OrmLite, Realm
  18. ORM – cons • New APIs to be learned

  19. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities
  20. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance
  21. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers
  22. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Lock-in effect
  23. SQLDelight

  24. SQLDelight • Generates Java models from your SQL `CREATE TABLE`

    statements
  25. SQLDelight • Generates Java models from your SQL `CREATE TABLE`

    statements • Type-safe APIs to read & write the rows of your tables
  26. SQLDelight • Generates Java models from your SQL `CREATE TABLE`

    statements • Type-safe APIs to read & write the rows of your tables • Keep your SQL statements together, organized, and easy to access from Java
  27. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including:
  28. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • Syntax highlighting
  29. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • Syntax highlighting • Refactoring/Find usages
  30. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion
  31. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion • Generate Model files after edits
  32. 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
  33. 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
  34. SQLDelight – keep in mind • The file structure of

    your project is crucial for this to work
  35. SQLDelight – keep in mind • The file structure of

    your project is crucial for this to work • SQLDelight files must be in a folder matching the file structure of the java project
  36. SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java

  37. SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java

    ... - sqldelight/ - com/example/project/models/ - Book.sq ...
  38. 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 (?, ?, ?);
  39. SQLDelight – implementation @AutoValue public abstract class Book implements BookModel

    { }
  40. SQLDelight – implementation @AutoValue public abstract class Book implements BookModel

    { public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); }
  41. SQLDelight – implementation @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(); }
  42. 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(); ... }
  43. 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) ); } }
  44. SQLDelight – … generates: public SqlDelightStatement selectAll() { return new

    SqlDelightStatement("" + "SELECT *\n" + "FROM book", new String[0], Collections.<String>singleton("book")); }
  45. SQLDelight – … generates: class InsertRow extends SqlDelightCompiledStatement.Insert { public

    InsertRow(SQLiteDatabase database) { super("book", database.compileStatement("" + "INSERT INTO book(title, author, num_chapters)\n" + "VALUES (?, ?, ?)")); } public void bind(@NonNull String title, @NonNull String author, long num_chapters) { program.bindString(1, title); program.bindString(2, author); program.bindLong(3, num_chapters); } }
  46. SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query =

    Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { } }
  47. 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; } }
  48. SQLDelight – projections, joins Each SELECT statement will have:

  49. SQLDelight – projections, joins Each SELECT statement will have: •

    an interface
  50. SQLDelight – projections, joins Each SELECT statement will have: •

    an interface • mapper
  51. SQLDelight – projections, joins Each SELECT statement will have: •

    an interface • mapper • method on the factory to create a new instance of the mapper
  52. SQLDelight – types • Identical to SQLite column definitions

  53. SQLDelight – types • Identical to SQLite column definitions •

    Plus extra column constraint for Java type
  54. 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 );
  55. 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 );
  56. 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 );
  57. 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); } }
  58. SQLDelight – enums • Includes a ColumnAdapter for storing an

    enum as TEXT
  59. SQLDelight – enums @AutoValue public abstract class Book implements BookModel

    { public enum Genre { ROMANCE, ADVENTURE, SELF_HELP, BIOGRAPHY, FICTION, DRAMA } ColumnAdapter<Genre, String> GENRE_ADAPTER = EnumColumnAdapter.create(Genre.class); Factory<Book> FACTORY = new Factory <>(new Creator <>() {}, GENRE_ADAPTER); }
  60. SQLDelight – enums import com.leandrofavarin.books.entities.Book; CREATE TABLE book ( genre

    TEXT AS Book.Genre );
  61. SQLDelight – views • Same treatment in generated code as

    tables with their own model interface
  62. SQLDelight – views CREATE TABLE book ( _id INTEGER NOT

    NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL ); listing_view: CREATE VIEW listing AS SELECT coalesce(title,'') || coalesce(author,'') AS identification FROM book; selectListing: SELECT * FROM listing;
  63. SQLDelight – views @AutoValue public abstract class Book implements BookModel

    { Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); RowMapper<Listing> ROW_MAPPER = FACTORY.selectListingMapper(AutoValue_Book_Listing ::new); @AutoValue static abstract class Listing implements ListingModel { } }
  64. Kotlin?

  65. SQLDelight – kotlin • Mostly just works!

  66. SQLDelight – kotlin • … mostly just works! • Have

    to redundantly declare getters
  67. 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 (?, ?, ?);
  68. 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 }
  69. SQLDelight – migrations • Least supported part of SQLDelight

  70. SQLDelight – migrations • Least supported part of SQLDelight •

    Likely to be implemented as .sqm files
  71. SQLDelight – migrations • Least supported part of SQLDelight •

    Likely to be implemented as .sqm files • Solution: write strings in the open helper
  72. SQLBrite

  73. SQLBrite • Wrapper around SQLiteOpenHelper which introduces reactive stream semantics

    to SQL operations
  74. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive

    stream semantics to SQL operations
  75. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive

    stream semantics to SQL operations • Updates to the specified table(s) will trigger additional notifications for as long as you remain subscribed to the publisher
  76. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive

    stream semantics to SQL operations • Updates to the specified table(s) will trigger additional notifications for as long as you remain subscribed to the publisher • SQLBrite's coordinates and composes update notifications to tables such that you can update queries as soon as data changes
  77. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .build();

  78. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->

    Timber.tag("Database").v(message)) .build();
  79. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder()
 .logger(message ->

    Timber.tag("Database").v(message))
 .queryTransformer()
 .build();
  80. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->

    Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());
  81. 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);
  82. 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);
  83. SQLBrite – powerful logging QUERY (0ms) tables: [book] sql: SELECT

    * FROM book WHERE title = ?1 AND author = ?2 args: ["1984","Fitzgerald"] EXECUTE SQLiteProgram: INSERT INTO book(title, author) VALUES(?, ?)
  84. 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 }); }
  85. SQLBrite – BookRepo.java Observable<List<Book >> getAllBooks(BriteDatabase db) { QueryObservable books

    = db.createQuery("book", "SELECT * FROM book"); AtomicInteger queries = new AtomicInteger(); books.subscribe(query -> queries.getAndIncrement()); System.out.println("Queries: " + queries.get()); // Prints 1 db.insert("book", createBook("1984", "George Orwell")); db.insert("book", createBook("The Great Gatsby", "Fitzgerald")); System.out.println("Queries: " + queries.get()); // Prints 3 }
  86. SQLBrite – BookRepo.java Observable<List<Book >> getAllBooks(BriteDatabase db) { QueryObservable books

    = db.createQuery("book", "SELECT * FROM book"); AtomicInteger queries = new AtomicInteger(); books.subscribe(query -> queries.getAndIncrement()); System.out.println("Queries: " + queries.get()); // Prints 1 BriteDatabase.Transaction transaction = db.newTransaction(); try { db.insert("book", createBook("1984", "George Orwell")); db.insert("book", createBook("The Great Gatsby", "Fitzgerald")); transaction.markSuccessful(); } finally { transaction.end(); } System.out.println("Queries: " + queries.get()); // Prints 2 }
  87. SQLDelight & SQLBrite

  88. SQLDelight & SQLBrite CREATE TABLE book (
 _id INTEGER NOT

    NULL PRIMARY KEY AUTOINCREMENT,
 title TEXT NOT NULL,
 author TEXT NOT NULL
 );
 
 selectAll:
 SELECT *
 FROM book;
 
 insertRow:
 INSERT INTO book(title, author)
 VALUES (?, ?);
 
 deleteByTitle:
 DELETE FROM book
 WHERE title = ?;
  89. SQLDelight & SQLBrite @AutoValue public abstract class Book implements BookModel

    {
 Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new);
 
 Mapper<Book> ROW_MAPPER = FACTORY.selectAllMapper();
 }
  90. SQLDelight & SQLBrite Observable<List<Book >> getAllBooksStream() {
 SqlDelightStatement query =

    Book.FACTORY.selectAll();
 return db.createQuery(query.tables, query.statement, query.args)
 .mapToList(Book.ROW_MAPPER ::map);
 }
  91. SQLDelight & SQLBrite public class BookRepository {
 private final BriteDatabase

    db;
 private final Book.DeleteByTitle deleteByTitle;
 
 public BookRepository(BriteDatabase db) {
 this.db = db;
 insertRow = new Book.InsertRow(db.getWritableDatabase());
 }
 
 public void insert(Book book) {
 insertRow.bind(book.title(), book.author());
 long result = db.executeInsert(insertRow.table, insertRow.program);
 }
 }
  92. SQLDelight & SQLBrite public class BookRepository {
 private final BriteDatabase

    db;
 private final Book.InsertRow insertRow;
 private final Book.DeleteByTitle deleteByTitle;
 
 public BookRepository(BriteDatabase db) {
 this.db = db;
 insertRow = new Book.InsertRow(db.getWritableDatabase());
 deleteByTitle = new Book.DeleteByTitle(db.getWritableDatabase());
 }
 
 public void insert(Book book) {
 insertRow.bind(book.title(), book.author());
 long result = db.executeInsert(insertRow.table, insertRow.program);
 }
 
 public void delete(Book book) {
 deleteByTitle.bind(book.title());
 long result = db.executeUpdateDelete(deleteByTitle.table, deleteByTitle.program);
 }
 }
  93. Powerful combination

  94. Review

  95. Review – pros

  96. Review – pros • Type-safe marshalling & unmarshalling

  97. Review – pros • Type-safe marshalling & unmarshalling • Compile-time

    SQL validation
  98. Review – pros • Type-safe marshalling & unmarshalling • Compile-time

    SQL validation • SQL autocompletion
  99. Review – pros • Type-safe marshalling & unmarshalling • Compile-time

    SQL validation • SQL autocompletion • Immutability
  100. Review – pros • Type-safe marshalling & unmarshalling • Compile-time

    SQL validation • SQL autocompletion • Immutability • All SQLite feature-set at your disposal
  101. Review – pros • Type-safe marshalling & unmarshalling • Compile-time

    SQL validation • SQL autocompletion • Immutability • All SQLite feature-set at your disposal • Real-time UI updates
  102. Review – cons (Jun 2017)

  103. Review – cons (Jun 2017) • SQLBrite latest release only

    works with RxJava 1
  104. Review – cons (Jun 2017) • SQLBrite latest release only

    works with RxJava 1 (RxJava 2 support is imminent, available on SNAPSHOT)
  105. Review – cons (Jun 2017) • SQLBrite latest release only

    works with RxJava 1 (RxJava 2 support is imminent, available on SNAPSHOT) • Migrations are mostly done manually
  106. Questions?

  107. Thanks. @leandrofavarin www.leandrofavarin.com