Embracing SQL Without Abstraction

5fe8f40633300a70ea088a594cb33031?s=47 Alec Strong
September 30, 2016

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

5fe8f40633300a70ea088a594cb33031?s=128

Alec Strong

September 30, 2016
Tweet

Transcript

  1. 3.

    What we want: • RxJava • Immutability • Type Safety

    • Compiler Errors • SQLite features
  2. 4.

    What we’re given: • Android’s SQLite bindings • SQLiteOpenHelper •

    Cursor • SQLiteDatabase • ContentValues • ContentProvider/ContentResolver
  3. 6.

    What we’re given: db.rawQuery("SELECT * FROM user WHERE name IN

    (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });
  4. 7.

    What we’re given: db.rawQuery("SELECT * FROM " + User.TABLE_NAME +

    " WHERE name IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });
  5. 8.

    What we’re given: db.rawQuery("SELECT * FROM " + User.TABLE_NAME +

    
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });
  6. 11.

    Research • Reflection • Synchronous • Lack of Type Safety

    • Underpowered Query APIs • Missing SQLite features
  7. 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();
 }
  8. 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();
 }
  9. 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());
 }
 }
  10. 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=?;
  11. 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=?;
  12. 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');
  13. 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
  14. 22.

    Not Scaffold • Need to address type safety • IntColumn,

    StringColumn • How do you model scope
  15. 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.
 );
  16. 27.

    SQLBrite • Won’t do perfect emission • False positives •

    But how do we know when a table is updated? • Require table names
  17. 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);
  18. 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))
 );
 };
  19. 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
  20. 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);
  21. 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);
  22. 35.

    
 public abstract long userId();
 public abstract String name();
 public

    abstract String photoUrl();
 public abstract Calendar birthDate();

  23. 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))
 );
 };
  24. 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
 );
 };
  25. 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
  26. 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;

  27. 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;
 });
  28. 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);
  29. 44.
  30. 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);

  31. 46.

    
 public abstract long userId();
 public abstract String name();
 public

    abstract String photoUrl();
 public abstract Calendar birthDate();
  32. 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();
  33. 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
 );
 };

  34. 49.

    
 
 public static final Func1<Cursor, User> MAPPER =
 cursor

    -> User_Mapper.map(cursor, AutoValue_User::new); 

  35. 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;
 }
 

  36. 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> { }
  37. 53.

    MarshalMappers • Uses reflection for custom types • Still has

    the AutoValue “fourth wall” • No real direction
  38. 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
  39. 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
 );
  40. 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
 );
  41. 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
 );
  42. 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
 );
  43. 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
 );
  44. 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');
  45. 63.

    Type Safety select_squares:
 SELECT *
 FROM user
 WHERE name IN

    ('John', 'Nelson', 'Logan'); • Already know table schemas • NoColumnDefinition name
  46. 64.

    A Proper IDE select_squares:
 SELECT *
 FROM user
 WHERE |

    IN ('John', 'Nelson', 'Logan'); • IntelliJ features |
  47. 65.

    A Proper IDE select_squares:
 SELECT *
 FROM user
 WHERE |

    IN ('John', 'Nelson', 'Logan'); • IntelliJ features • Enables fast query development |
  48. 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
  49. 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
  50. 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
  51. 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
  52. 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);
  53. 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);
  54. 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);
  55. 73.

    • Migration Verification • Testing support • Kotlin What’s next?

    select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name;
  56. 75.
  57. 76.

    • Table names known • So are filters SQLBrite select_squares:


    SELECT *
 FROM user
 WHERE name = :name OR last_name = :name; user