Embracing SQL Without Abstraction

The story of how Square came to write SQLBrite and SQLDelight and the lessons we learned along the way.

Video: https://www.youtube.com/watch?v=ksgmm8VolT4

Transcript

1. Embracing SQL
 Without Abstraction Alec Strong
2. Cursor returned zero rows SELECT knowledge
 FROM alec
 WHERE venue='Droidcon'
 LIMIT 40min;
3. What we want: • RxJava • Immutability • Type Safety • Compiler Errors • SQLite features
4. What we’re given: • Android’s SQLite bindings • SQLiteOpenHelper • Cursor • SQLiteDatabase • ContentValues • ContentProvider/ContentResolver
5. What we’re given: db.rawQuery("SELECT * FROM user", new String[0]);
6. What we’re given: db.rawQuery("SELECT * FROM user WHERE name IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });
7. What we’re given: db.rawQuery("SELECT * FROM " + User.TABLE_NAME + " WHERE name IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });
8. What we’re given: db.rawQuery("SELECT * FROM " + User.TABLE_NAME + 
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });
9. What we want: RxJava Immutability Type Safety Compiler Errors SQLite features
10. What did we do: • Research
11. Research • Reflection • Synchronous • Lack of Type Safety • Underpowered Query APIs • Missing SQLite features
12. Scaffold @Table(User.TABLE_NAME)
 public interface User {
 String TABLE_NAME = "user";
 
 String USER_ID = "user_id";
 String NAME = "name";
 String PHOTO_URL = "photo_url";
 
 @PrimaryKey @Column(USER_ID) @AutoIncrement @NotNull
 String lookupKey();
 
 @NotNull @Column(NAME)
 String name();
 
 @NotNull @Column(PHOTO_URL)
 String photoUrl();
 }
13. Scaffold @Table(User.TABLE_NAME)
 public interface User {
 } public static final class ScUser implements User {
 public static Func1<Cursor, List<User>> mapRow();
 public static Func1<Cursor, User> single();
 }
14. Scaffold @Database (
 tables = {
 User.class
 },
 fileName = "my_database.db",
 version = 1,
 authority = BuildConfig.PACKAGE_NAME + ".data.db.Provider"
 )
 public final class MyDatabase extends ScaffoldDatabase {
 public MyDatabase(Context context) {
 super(context.getContentResolver());
 }
 }
15. Scaffold Observable<User> user = myDatabase.createQuery(
 Query.from(User.class).where(EQ(User.NAME, "John")))
 .map(ScUser.single());
16. Scaffold Observable<ScaffoldCursor> payments = myDatabase.createQuery(Query
 .from(Payment.class)
 .join(Customer.class)
 .on(EQ(Customer.TOKEN, Payment.CUSTOMER_TOKEN)))
 .join(User.class)
 .on(EQ(Customer.USER_ID, User.USER_ID))
 .where(EQ(Payment.TOKEN, paymentToken));
17. Observable<ScaffoldCursor> payments = myDatabase.createQuery(Query
 .from(Payment.class)
 .join(Customer.class)
 .on(EQ(Customer.TOKEN, Payment.CUSTOMER_TOKEN)))
 .join(User.class)
 .on(EQ(Customer.USER_ID, User.USER_ID))
 .where(EQ(Payment.TOKEN, paymentToken)); SELECT *
 FROM payment
 JOIN customer USING (customer_token)
 JOIN user USING (user_id)
 WHERE token=?;
18. Observable<ScaffoldCursor> payments = myDatabase.createQuery(Query
 .from(Payment.class)
 .join(Customer.class)
 .on(EQ(Customer.TOKEN, Payment.CUSTOMER_TOKEN)))
 .join(User.class, JoinType.LEFT)
 .on(EQ(Customer.USER_ID, User.USER_ID))
 .where(EQ(Payment.TOKEN, paymentToken)); SELECT *
 FROM payment
 JOIN customer USING (customer_token)
 LEFT JOIN user USING (user_id)
 WHERE token=?;
19. Observable<ScaffoldCursor> payments = myDatabase.createQuery(Query
 .from(Payment.class)
 .join(Customer.class)
 .on(EQ(Customer.TOKEN, Payment.CUSTOMER_TOKEN)))
 .join(User.class, JoinType.LEFT)
 .on(EQ(Customer.USER_ID, User.USER_ID))
 .where(EQ(Payment.TOKEN, paymentToken));
 .where(AND(
 EQ(Payment.TOKEN, paymentToken),
 OR(EQ(User.USER_ID, null), EQ(User.NAME, “Logan”))
 )); SELECT *
 FROM payment
 JOIN customer USING (customer_token)
 LEFT JOIN user USING (user_id)
 WHERE token=?;
 AND (user.used_id IS NULL OR user.name = 'Logan');
20. Scaffold • Many flaws • QueryBuilder • Closed API • Too busy as a library • RxJava, ContentProvider, QueryBuilder, SQLiteOpenHelper, Cursor -> Object mapping, Object -> ContentValues Marshaling • Prone to bugs • Inner queries and joins
21. What we want: RxJava Immutability Type Safety Compiler Errors SQLite features
22. Not Scaffold • Need to address type safety • IntColumn, StringColumn • How do you model scope
23. Not Scaffold • Need to address type safety • IntColumn, StringColumn • How do you model scope SELECT *
 FROM customer
 WHERE customer_name = (
 SELECT customer_name -- Works fine, variable is scoped in.
 FROM user
 ORDER BY customer_name -- No such column found.
 );
24. Putting Away The Scaffolding
25. What we want: RxJava Immutability Type Safety Compiler Errors SQLite features
26. What we want: RxJava
27. SQLBrite • Won’t do perfect emission • False positives • But how do we know when a table is updated? • Require table names
28. @AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL);
29. 
 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();

30. 
 public static final Func1<Cursor, User> MAPPER = cursor -> {
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL))
 );
 };
31. 
 public static final class Builder {
 private final ContentValues contentValues = new ContentValues();
 
 public Builder userId(long userId) {
 contentValues.put(USER_ID, userId);
 return this;
 }A
 
 public Builder name(String name) {
 contentValues.put(NAME, name);
 return this;
 }A
 
 public Builder photoUrl(String photoUrl) {
 contentValues.put(PHOTO_URL, photoUrl);
 return this;
 }A
 
 public ContentValues build() {
 return contentValues;
 }A
 }A
 }A
32. @AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL);
33. @AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 public static final String BIRTH_DATE = “birth_date";
 
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL,” //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);
34. 
 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();

35. 
 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();
 public abstract Calendar birthDate();

36. 
 public static final Func1<Cursor, User> MAPPER = cursor -> {
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL))
 );
 };
37. 
 public static final Func1<Cursor, User> MAPPER = cursor -> {
 Calendar calendar = Calendar.getInstance();
 calendar.setTimeInMillis( cursor.getLong(cursor.getColumnIndexOrThrow(BIRTH_DATE)));
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL)),
 calendar
 );
 };
38. 
 public static final class Builder {
 private final ContentValues contentValues = new ContentValues();
 
 public Builder userId(long userId) {
 contentValues.put(USER_ID, userId);
 return this;
 }A
 
 public Builder name(String name) {
 contentValues.put(NAME, name);
 return this;
 }A
 
 public Builder photoUrl(String photoUrl) {
 contentValues.put(PHOTO_URL, photoUrl);
 return this;
 }A
 
 public ContentValues build() {
 return contentValues;
 }A
 }A
 }A
39. 
 public Builder userId(long userId) {
 contentValues.put(USER_ID, userId);
 return this;
 }A
 
 public Builder name(String name) {
 contentValues.put(NAME, name);
 return this;
 }A
 
 public Builder photoUrl(String photoUrl) {
 contentValues.put(PHOTO_URL, photoUrl);
 return this;
 }A
 
 public Builder birthDate(Calendar birthDate) {
 contentValues.put(BIRTH_DATE, birthDate.getTimeInMillis());
 return this;
 }
 
 public ContentValues build() {
 return contentValues;

40. SQLBrite Observable<List<User>> users = db.createQuery(User.TABLE_NAME,
 "SELECT * FROM " + User.TABLE_NAME + 
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" })
 .map(query -> {
 Cursor cursor = query.run();
 List<User> users = new ArrayList<>();
 try {
 users.add(User.MAPPER.call(cursor));
 } finally {
 cursor.close();
 } return users;
 });
41. SQLBrite Observable<List<User>> users = db.createQuery(User.TABLE_NAME,
 "SELECT * FROM " + User.TABLE_NAME +
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" })
 .mapToList(User.MAPPER);
42. SQLBrite • Solved Rx problem • Requires table name and has false positives
43. MarshalMappers • Aimed at limiting boilerplate • AutoValue extension • Short life span
45. @AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 public static final String BIRTH_DATE = "birth_date";
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);

46. 
 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();
 public abstract Calendar birthDate();
47. 
 @Column(USER_ID) public abstract long userId();
 @Column(NAME) public abstract String name();
 @Column(PHOTO_URL) public abstract String photoUrl();
 @Column(value = BIRTH_DATE, marshalMapper = CalendarMarshalMapper.class)
 public abstract Calendar birthDate();
48. 
 
 public static final Func1<Cursor, User> MAPPER = cursor -> {
 Calendar calendar = Calendar.getInstance();
 calendar.setTimeInMillis( cursor.getLong(cursor.getColumnIndexOrThrow(BIRTH_DATE)));
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL)),
 calendar
 );
 };

49. 
 
 public static final Func1<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new); 

50. 
 public static final class Builder {
 private final ContentValues contentValues = new ContentValues();
 
 public Builder userId(long userId) {
 contentValues.put(USER_ID, userId);
 return this;
 }
 
 public Builder name(String name) {
 contentValues.put(NAME, name);
 return this;
 }
 
 public Builder photoUrl(String photoUrl) {
 contentValues.put(PHOTO_URL, photoUrl);
 return this;
 }
 
 public Builder birthDate(Calendar birthDate) {
 contentValues.put(BIRTH_DATE, birthDate.getTimeInMillis());
 return this;
 }
 

51. 
 public static final class Marshal extends User_Marshal<Marshal> { }
52. @AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 public static final String BIRTH_DATE = "birth_date";
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);
 
 @Column(USER_ID) public abstract long userId();
 @Column(NAME) public abstract String name();
 @Column(PHOTO_URL) public abstract String photoUrl();
 @Column(value = BIRTH_DATE, marshalMapper = CalendarMarshalMapper.class)
 public abstract Calendar birthDate(); 
 public static final Func1<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new); 
 public static final class Marshal extends User_Marshal<Marshal> { }
53. MarshalMappers • Uses reflection for custom types • Still has the AutoValue “fourth wall” • No real direction
54. The Turning Point • IntelliJ plugin vs Annotation Processor • Schema cannot be in java • Why not just use SQL? • Only additional information needed for java creation is type
55. @AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 public static final String BIRTH_DATE = “birth_date"; 
 
 public static final String CREATE_TABLE = String.format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);
 
 @Column(USER_ID) public abstract long userId();
 @Column(NAME) public abstract String name();
 @Column(PHOTO_URL) public abstract String photoUrl();
 CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT NOT NULL
 );
56. @AutoValue public abstract class User {
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);
 
 @Column(USER_ID) public abstract long userId();
 @Column(NAME) public abstract String name();
 @Column(PHOTO_URL) public abstract String photoUrl();
 @Column(value = BIRTH_DATE, marshalMapper = CalendarMarshalMapper.class)
 public abstract Calendar birthDate();
 
 public static final Func1<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal<Marshal> { }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT NOT NULL
 );
57. @AutoValue public abstract class User {
 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();
 public abstract Calendar birthDate(); 
 public static final Func1<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal<Marshal> { }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT NOT NULL
 );
58. @AutoValue public abstract class User {
 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();
 public abstract Calendar birthDate(); 
 public static final Func1<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal<Marshal> { }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT AS java.util.Calendar NOT NULL
 );
59. @AutoValue public abstract class User implements UserModel {
 public static final UserMapper MAPPER = new UserMapper( AutoValue_User::new, CALENDAR_ADAPTER);
 
 public static final class Marshal extends UserMarshal<Marshal> { Marshal() {
 super(CALENDAR_ADAPTER);
 } }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT AS java.util.Calendar NOT NULL
 );
60. CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT AS java.util.Calendar NOT NULL
 ); select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan');
61. What we want: Immutability Type Safety Compiler Errors SQLite features
62. What we want: Immutability Type Safety Compiler Errors SQLite features
63. Type Safety select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); • Already know table schemas • NoColumnDefinition name
64. A Proper IDE select_squares:
 SELECT *
 FROM user
 WHERE | IN ('John', 'Nelson', 'Logan'); • IntelliJ features |
65. A Proper IDE select_squares:
 SELECT *
 FROM user
 WHERE | IN ('John', 'Nelson', 'Logan'); • IntelliJ features • Enables fast query development |
66. Projections select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); • SELECT * SQL Anti-pattern SELECT * select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); SELECT name, photo_url
67. Projections select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); • SELECT * SQL Anti-pattern • Generate mapper per query SELECT * select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); SELECT name, photo_url
68. Projections select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); • SELECT * SQL Anti-pattern • Generate mapper per query • Creates strong bond between view and query SELECT * select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); SELECT name, photo_url
69. select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); Benefits • Shared Language • Confidence writing SQL • Tight coupling with views • Easier code review
70. select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN (?, ?, ?); What’s next? (?, ?, ?) public SqlDelightStatement select_squares( String name1, String name2, String name3);
71. select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ?; What’s next? ? public SqlDelightStatement select_squares( String name1, String name2, String name3); public SqlDelightStatement select_squares(String[] name);
72. What’s next? select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name = :name OR last_name = :name; name = :name OR last_name = :name public SqlDelightStatement select_squares( String name1, String name2, String name3); public SqlDelightStatement select_squares(String[] name); public SqlDelightStatement select_squares(String name);
73. • Migration Verification • Testing support • Kotlin What’s next? select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name;
74. SQLBrite select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name; user
75. • Table names known SQLBrite select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name; user
76. • Table names known • So are filters SQLBrite select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name; user
77. first_name last_name email github twitter melee_tag password Alec Strong anstrong22@gmail.com AlecStrong @Strongolopolis RewinD hunter2 contact_info