A Multiplatform Delight (KotlinConf 2018)

SQL Delight, a type-safe database API, recently completed migration from being a Java-generating, Android-specific tool to a Kotlin-generating, multiplatform one. Migrating an API from Java to Kotlin has obvious benefits, but adding multiplatform support for iOS introduces a dynamic which complicates the API, code generation, and runtime.

This talk will cover the challenges of platform-agnostic API design, type-safe multiplatform Kotlin code generation, and the integration of platform-specific runtimes such that the library not only runs efficiently on each platform but also integrates well with the other languages each might be using.

Presented with Alec Strong.

Video: https://youtu.be/WkIry790PHI

Transcript

1. Alec Strong Jake Wharton
2. data class Player(A val name: String, val ranking: Int, val country: String )A
3. @Entity data class Player(A val name: String, val ranking: Int, val country: String )A
4. @Entity data class Player(A val name: String, val ranking: Int, val country: String )A @Entity data class Match(A val player1: Player, val player2: Player, val winner: Player )A
5. Source Code Annotation
 Processor Reflection @Entity data class Player(A val name: String, val ranking: Int, val country: String )A @Entity data class Match(A val player1: Player, val player2: Player, val winner: Player )A
6. Source Code Annotation
 Processor Reflection SQLite @Entity data class Player(A val name: String, val ranking: Int, val country: String )A @Entity data class Match(A val player1: Player, val player2: Player, val winner: Player )A
7. CREATE TABLE player (A name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); CREATE TABLE match (A player1 INTEGER NOT NULL REFERENCES player, player2 INTEGER NOT NULL REFERENCES player, winner INTEGER NOT NULL REFERENCES player );
8. CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); CREATE TABLE match (A player1 INTEGER NOT NULL REFERENCES player, player2 INTEGER NOT NULL REFERENCES player, winner INTEGER NOT NULL REFERENCES player );B
9. CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); CREATE TABLE match (A player1 INTEGER NOT NULL REFERENCES player, player2 INTEGER NOT NULL REFERENCES player, winner INTEGER NOT NULL REFERENCES player, PRIMARY KEY (player1, player2) ) WITHOUT ROWID;B
10. SQLite Source Code Annotation
 Processor Reflection @Entity data class Player( val name: String, val ranking: Int, val country: String ) @Entity data class Match( val player1: Player, val player2: Player, val winner: Player ) CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); CREATE TABLE match (A player1 INTEGER NOT NULL REFERENCES player, player2 INTEGER NOT NULL REFERENCES player, winner INTEGER NOT NULL REFERENCES player, PRIMARY KEY (player1, player2) ) WITHOUT ROWID;
11. @Entity data class Player(A val name: String, val ranking: Int, val country: String )A @Entity data class Match(A val player1: Player, val player2: Player, val winner: Player )A
12. @Entity data class Player(A @PrimaryKey @Autoincrement val id: Int, val name: String, val ranking: Int, val country: String )A @Entity data class Match(A val player1: Player, val player2: Player, val winner: Player )A
13. @Entity data class Player(A @PrimaryKey @Autoincrement val id: Int, val name: String, val ranking: Int, val country: String )A @Entity(primaryKeys = ['player1', ‘player2']) data class Match(A val player1: Player, val player2: Player, val winner: Player )A
14. @Entity data class Player(A @PrimaryKey @Autoincrement val id: Int, val name: String, val ranking: Int, val country: String )A @Entity(primaryKeys = ['player1', ‘player2']) data class Match(A val player1: Player, val player2: Player, val winner: Player )A A @PrimaryKey @Autoincrement val id: Int, @Entity(primaryKeys = ['player1', ‘player2'])
15. ALTER TABLE player ADD COLUMN id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT; CREATE TABLE new_match ( player1 INTEGER NOT NULL REFERENCES player, player2 INTEGER NOT NULL REFERENCES player, winner INTEGER NOT NULL REFERENCES player, PRIMARY KEY (player1, player2) ) WITHOUT ROWID; -- migrate match to new_match
16. QueryBuilders
18. QueryBuilders jakes.link/resurgence-of-sql
19. SQL Delight
20. SQLite Source Code Annotation
 Processor Reflection
21. SQLite Compiler Generated Code
22. CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL );
23. CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); withRanking: SELECT * FROM player WHERE ranking = ?;
24. SQLite Compiler Generated Code CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); withRanking: SELECT * FROM player WHERE ranking = ?;
25. interface PlayerModel {A long id(); @NonNull String name(); @NonNull String country(); long ranking(); final class Factory<T extends PlayerModel> {A public Factory(Creator<T> creator); public SqlDelightQuery withRanking(long ranking); public RowMapper<T> withRankingMapper(); }A }A
26. interface PlayerModel {A … final class Factory<T extends PlayerModel> {A public Factory(Creator<T> creator); … }B }A @AutoValue public abstract class MyPlayerModel implements PlayerModel {A public static Factory<MyPlayerModel> FACTORY = new Factory(AutoValue_MyPlayerModel::new) }A
27. SqlDelightStatement query = MyPlayerModel.FACTORY.withRanking(10); Observable<List<MyPlayerModel>> rank10 = db.createQuery(query.tables, query.statement, query.args) .mapToList(MyPlayerModel.FACTORY::withRankingMapper); interface PlayerModel {A … final class Factory<T extends PlayerModel> {A public Factory(Creator<T> creator); public SqlDelightQuery withRanking(long ranking); public RowMapper<T> withRankingMapper(); }B }A
28. SQL Delight 0.7
30. QueryBuilders jakes.link/embracing-sql
31. SQL Delight 1.0
32. SQLite Compiler Generated Code CREATE TABLE player ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); withRanking: SELECT * FROM player WHERE ranking = ?; insertPlayer: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?);
33. CREATE TABLE player ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); withRanking: SELECT * FROM player WHERE ranking = ?; insertPlayer: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?);
34. SQLite Compiler Generated Code CREATE TABLE player ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); withRanking: SELECT * FROM player WHERE ranking = ?; insertPlayer: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?);
35. interface Player {A val id: Long val name: String val country: String val ranking: Long data class Impl(A override val id: Long, override val name: String, override val country: String, override val ranking: Long ) : Player }A
36. class PlayerQueries : Transacter {A fun <T : Any> withRanking( ranking: Long, mapper: ( id: Long, name: String, country: String, ranking: Long ) -> T ): Query<T> fun withRanking(ranking: Long): Query<Player> fun insertPlayer( name: String, country: String, ranking: Long ): Long }A
37. headToHead: SELECT player1.name AS player1Name, player2.name AS player2Name, count(nullif(match.winner = player1.id, 0)) AS player1Wins, count(nullif(match.winner = player2.id, 0)) AS player2Wins FROM match JOIN player AS player1 ON (player1.name = :firstPlayerName AND (player1.id = match.player1 OR player1.id = match.player2) ) JOIN player AS player2 ON (player2.name = :secondPlayerName AND (player2.id = match.player1 OR player2.id = match.player2) ) ;
38. class PlayerQueries : Transacter {A fun <T : Any> headToHead( firstPlayerName: String, secondPlayerName: String, mapper: ( player1Name: String, player2Name: String, player1Wins: Long, player2Wins: Long ) -> T ): Query<T> fun headToHead( firstPlayerName: String, secondPlayerName: String ): Query<com.sample.tennis.db.HeadToHead> }A
39. class PlayerQueries : Transacter {A fun <T : Any> withRanking( ranking: Long, mapper: ( id: Long, name: String, country: String, ranking: Long ) -> T ): Query<T> fun withRanking(ranking: Long): Query<Player> fun insertPlayer( name: String, country: String, ranking: Long ): Long }A
40. class PlayerQueries : Transacter {A fun withRanking(ranking: Long): Query<Player> }A val player: Query<Player> = playerQueries.withRanking(10)
41. Query< > class PlayerQueries : Transacter {A fun withRanking(ranking: Long): Query<Player> }A val player: Player = playerQueries.withRanking(10).executeAsOne()
42. class PlayerQueries : Transacter {A fun withRanking(ranking: Long): Query<Player> }A val player: Observable<Query<Player>> = playerQueries.withRanking(10) .asObservable() .ex
43. class PlayerQueries : Transacter {A fun withRanking(ranking: Long): Query<Player> }A val player: Observable<List<Player>> = playerQueries.withRanking(10) .asObservable() .mapToList()
44. class PlayerQueries : Transacter {A fun <T : Any> withRanking(ranking: Long, mapper: ( id: Long, name: String, country: String, ranking: Long ) -> T): Query<T> }A val player = playerQueries.withRanking(10, ::MyPlayer) P l a y e r
45. class PlayerQueries : Transacter {A }A val id = playerQueries.insertPlayer( name = "Roger Federer", country = "Switzerland", ranking = 2 ) fun insertPlayer( name: String, country: String, ranking: Long ): Long
46. Generated Code SQLite Compiler CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); withRanking: SELECT * FROM player WHERE ranking = ?; class PlayerQueries : Transacter {A fun insertPlayer( name: String, country: String, ranking: Long ): Long }A
47. CREATE TABLE player ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country NOT NULL, ranking INTEGER NOT NULL ); TEXT
48. CREATE TABLE player ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country NOT NULL, ranking INTEGER NOT NULL ); import com.tennis.db.CountryCode; TEXT AS CountryCode
49. import com.tennis.db.CountryCode; CREATE TABLE player ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT AS CountryCode NOT NULL, ranking INTEGER NOT NULL );
50. SQLite Compiler Generated Code import com.tennis.db.CountryCode; CREATE TABLE player ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT AS CountryCode NOT NULL, ranking INTEGER NOT NULL );
51. class QueryWrapper( database: SqlDatabase, internal val playerAdapter: Player.Adapter ) {A val playerQueries: PlayerQueries }A
52. class QueryWrapper( database: SqlDatabase, internal val playerAdapter: Player.Adapter ) {A val playerQueries: PlayerQueries }A interface Player {A class Adapter( val countryAdapter: ColumnAdapter<CountryCode, String> ) }A
53. class QueryWrapper( database: SqlDatabase, internal val playerAdapter: Player.Adapter ) {A val playerQueries: PlayerQueries }A val queryWrapper = QueryWrapper( database = database, playerAdapter = Player.Adapter( countryAdapter = EnumColumnAdapter() ), )
54. class QueryWrapper( database: SqlDatabase, internal val playerAdapter: Player.Adapter ) {A val playerQueries: PlayerQueries }A val queryWrapper = QueryWrapper( database = database, playerAdapter = Player.Adapter( countryAdapter = EnumColumnAdapter() ), ) val playerQueries: PlayerQueries = queryWrapper.playerQueries val playerQueries: PlayerQueries = queryWrapper.playerQueries val playerQueries: PlayerQueries
55. class QueryWrapper {A object Schema : SqlDatabase.Schema {A override val version: Int get() = 2 override fun create(db: SqlDatabaseConnection) override fun migrate( db: SqlDatabaseConnection, oldVersion: Int, newVersion: Int ) }A }A
56. SQLite Generated Code Compiler CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); withRanking: SELECT * FROM player WHERE ranking = ?; class QueryWrapper {A object Schema : SqlDatabase.Schema {A override val version: Int get() = 2 override fun create(db: SqlDatabaseConnection) override fun migrate( db: SqlDatabaseConnection, oldVersion: Int, newVersion: Int ) }A }A
57. SQLite Generated Code Compiler SQLite Migrations
58. ALTER TABLE player ADD COLUMN id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT; CREATE TABLE new_match ( player1 INTEGER NOT NULL REFERENCES player, player2 INTEGER NOT NULL REFERENCES player, winner INTEGER NOT NULL REFERENCES player, PRIMARY KEY (player1, player2) ) WITHOUT ROWID; -- migrate match to new_match
59. SQLite Migrations SQLite Generated Code Compiler ALTER TABLE player ADD COLUMN id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT; CREATE TABLE new_match ( player1 INTEGER NOT NULL REFERENCES player, player2 INTEGER NOT NULL REFERENCES player, winner INTEGER NOT NULL REFERENCES player, PRIMARY KEY (player1, player2) ) WITHOUT ROWID; -- migrate match to new_match
60. SQL Delight 1.0
61. SQL Delight Multiplatform?
62. SQL Delight Multiplatform?
63. CashApp
64. db backend presenters api CashApp proto viewmodels
65. db
66. SQLite Generated Code SQL Delight CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL ); withRanking: SELECT * FROM player WHERE ranking = ?; db
67. Generated Code SQL Delight db SQLite
68. Generated Code SQL Delight db SQLite Generated Swift Code Compiler
69. Generated Java wire protos .proto Generated C protoc
70. Generated Code db SQL Delight SQLite Generated Swift Code Compiler
71. Generated Code db SQL Delight SQLite
72. Generated Code Kotlin/Native kotlinc Android Native
73. Generated Code Kotlin/Native kotlinc Android Native kotlinc JVM kotlin2js JavaScript
74. Android JVM JavaScript Native
75. Android JVM JavaScript Native android.database.* android.database.sqlite.*
76. Android JVM JavaScript Native android.database.* android.database.sqlite.* sqlite3.h
77. Android JVM JavaScript Native android.database.* android.database.sqlite.* sqlite3.h java.sql.* org.sqlite.*
78. Android JVM JavaScript Native android.database.* android.database.sqlite.* sqlite3.h java.sql.* org.sqlite.* (xerial)
79. Android JVM JavaScript Native android.database.* android.database.sqlite.* sqlite3.h java.sql.* org.sqlite.* Node? WASM? Vanilla JS? (xerial)
80. android.database.* android.database.sqlite.* sqlite3.h Node? WASM? Vanilla JS? Generated Code Android JVM Native JavaScript java.sql.* org.sqlite.* (xerial)
81. android.database.* android.database.sqlite.* sqlite3.h Node? WASM? Vanilla JS? Generated Code Android Native JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) JVM
82. Database Abstraction SqlDatabase
83. Database Abstraction SqlDatabase SqlDatabase.Schema
84. Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement
85. Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction
86. Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter
87. Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter
88. Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlCursor
89. Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlCursor Query
90. android.database.* android.database.sqlite.* sqlite3.h Node? WASM? Vanilla JS? Generated Code Android JVM Native JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) S Sql Sql Tran T S Q
91. android.database.* android.database.sqlite.* sqlite3.h Node? WASM? Vanilla JS? Generated Code Android Native JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Android Driver knarch.db Driver JDBC Driver JS Driver JVM
92. android.database.* android.database.sqlite.* sqlite3.h Android Native Database Abstraction java.sql.* org.sqlite.* (xerial) Android Driver knarch.db Driver JDBC Driver JS JVM
93. android.database.* android.database.sqlite.* sqlite3.h Android Native java.sql.* org.sqlite.* (xerial) Android Driver knarch.db Driver JDBC Driver JS JVM AndroidX Sqlite Abstraction AndroidX Sqlite Framework Impl
94. android.database.* android.database.sqlite.* sqlite3.h Android Native java.sql.* org.sqlite.* (xerial) Android Driver knarch.db Driver JDBC Driver JS JVM AndroidX Sqlite Abstraction AndroidX Sqlite Framework Impl Android android.database.* net.sqlcipher.database.* AndroidX SQLCipher Impl
95. android. android. sqlite3.h Node? WASM? Vanilla JS? ed Native JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Android Driver knarch.db Driver JDBC Driver JS Driver JVM AndroidX Sqlite Abstraction AndroidX Sqlite Framework Impl android. net.sqlc AndroidX SQLCipher Impl
96. android. android. sqlite3.h Node? WASM? Vanilla JS? ed Native JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Android Driver knarch.db Driver JDBC Driver JS Driver JVM AndroidX Sqlite Abstraction AndroidX Sqlite Framework Impl android. net.sqlc AndroidX SQLCipher Impl AAA BBB
97. sqlite3.h Node? WASM? Vanilla JS? ed Native JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Driver knarch.db Driver JDBC Driver JS Driver JVM Abstraction android. net.sqlc AndroidX SQLCipher Impl AAA BBB
98. sqlite3.h ed Native Node? WASM? Vanilla JS? JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Driver knarch.db Driver JDBC Driver JVM Abstraction android. net.sqlc AndroidX SQLCipher Impl
99. android.database.* android.database.sqlite.* sqlite3.h Generated Code Android Native Node? WASM? Vanilla JS? JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Android Driver knarch.db Driver JDBC Driver JVM AndroidX Sqlite Abstraction AndroidX Sqlite Framework Impl Android android.database.* net.sqlcipher.database.* AndroidX SQLCipher Impl
100. android.database.* android.database.sqlite.* sqlite3.h Generated Code Android Native Node? WASM? Vanilla JS? JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Android Driver knarch.db Driver JDBC Driver JVM AndroidX Sqlite Abstraction AndroidX Sqlite Framework Impl Android android.database.* net.sqlcipher.database.* AndroidX SQLCipher Impl
101. Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlCursor Query
102. Database Abstraction SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter Query SqlDatabase SqlCursor
103. package com.squareup.sqldelight.db expect interface Closeable { fun close() } expect inline fun <T : Closeable?, R> T.use(body: (T) -> R): R
104. package com.squareup.sqldelight.db expect interface Closeable { fun close() } expect inline fun <T : Closeable?, R> T.use(body: (T) -> R): R package com.squareup.sqldelight.db import kotlin.io.use as kotlinIoUse actual typealias Closeable = java.io.Closeable actual inline fun <T : Closeable?, R> T.use(body: (T) -> R): R = kotlinIoUse(body)
105. package com.squareup.sqldelight.db expect interface Closeable { fun close() } expect inline fun <T : Closeable?, R> T.use(body: (T) -> R): R package com.squareup.sqldelight.db actual interface Closeable { actual fun close() } actual inline fun <T : Closeable?, R> T.use(body: (T) -> R): R { // Copy/paste JVM implementation… }
106. apply plugin: 'org.jetbrains.kotlin.platform.common' dependencies { compile 'com.squareup.sqldelight:runtime-common:1.0.0' } apply plugin: 'org.jetbrains.kotlin.platform.jvm' // Or .android dependencies { compile 'com.squareup.sqldelight:runtime-jdk:1.0.0' } apply plugin: 'org.jetbrains.kotlin.platform.js' dependencies { compile 'com.squareup.sqldelight:runtime-js:1.0.0' }
107. apply plugin: 'org.jetbrains.kotlin.platform.common' dependencies { compile 'com.squareup.sqldelight:runtime-common:1.0.0' } apply plugin: 'org.jetbrains.kotlin.platform.jvm' // Or .android dependencies { compile 'com.squareup.sqldelight:runtime-jdk:1.0.0' } apply plugin: 'org.jetbrains.kotlin.platform.js' dependencies { compile 'com.squareup.sqldelight:runtime-js:1.0.0' } apply plugin: 'org.jetbrains.kotlin.platform.native' // ???
108. apply plugin: 'org.jetbrains.kotlin.platform.common' dependencies { compile 'com.squareup.sqldelight:runtime-common:1.0.0' } apply plugin: 'org.jetbrains.kotlin.platform.jvm' // Or .android dependencies { compile 'com.squareup.sqldelight:runtime-jdk:1.0.0' } apply plugin: 'org.jetbrains.kotlin.platform.js' dependencies { compile 'com.squareup.sqldelight:runtime-js:1.0.0' } apply plugin: 'org.jetbrains.kotlin.platform.native' // nope!
109. apply plugin: 'org.jetbrains.kotlin.multiplatform' dependencies { compile 'com.squareup.sqldelight:runtime:1.0.0' }
110. apply plugin: 'org.jetbrains.kotlin.multiplatform' dependencies { compile 'com.squareup.sqldelight:runtime:1.0.0' } // settings.gradle enableFeaturePreview('GRADLE_METADATA')
111. com.squareup.sqldelight + runtime
112. com.squareup.sqldelight - runtime runtime-1.0.0-sources.jar runtime-1.0.0-javadoc.jar runtime-1.0.0.pom runtime-1.0.0.module
113. runtime-1.0.0.module { "variants": [ { "name": "iosArm64-api", "available-at": { "url": "../../runtime-iosarm64/runtime-iosarm64-1.0.0.module" } }, ... ]
 }
114. com.squareup.sqldelight - runtime runtime-1.0.0-sources.jar runtime-1.0.0-javadoc.jar runtime-1.0.0.pom runtime-1.0.0.module
115. com.squareup.sqldelight + runtime
116. com.squareup.sqldelight + runtime + runtime-common + runtime-iosarm64 + runtime-iosx64 + runtime-jvm + runtime-js
117. com.squareup.sqldelight + android-driver + runtime + runtime-common + runtime-iosarm64 + runtime-iosx64 + runtime-jvm + runtime-js
118. com.squareup.sqldelight - android-driver android-driver-1.0.0.aar android-driver-1.0.0-javador.jar android-driver-1.0.0-sources.jar android-driver-1.0.0-pom.xml + runtime + runtime-common + runtime-iosarm64 + runtime-iosx64 + runtime-jvm + runtime-js
119. com.squareup.sqldelight - android-driver android-driver-1.0.0.aar android-driver-1.0.0-javador.jar android-driver-1.0.0-sources.jar android-driver-1.0.0-pom.xml + runtime + runtime-common + runtime-iosarm64 + runtime-iosx64 + runtime-jvm + runtime-js android-driver-1.0.0-pom.xml + runtime-jvm
120. com.squareup.sqldelight - android-driver android-driver-1.0.0.aar android-driver-1.0.0-javador.jar android-driver-1.0.0-sources.jar android-driver-1.0.0-pom.xml + runtime + runtime-common + runtime-iosarm64 + runtime-iosx64 + runtime-jvm + runtime-js android-driver-1.0.0-pom.xml + runtime
121. com.squareup.sqldelight - android-driver android-driver-1.0.0.aar android-driver-1.0.0-javador.jar android-driver-1.0.0-sources.jar android-driver-1.0.0-pom.xml + runtime + runtime-common + runtime-iosarm64 + runtime-iosx64 + runtime-jvm + runtime-js android-driver-1.0.0-pom.xml + runtime-jdk
122. apply plugin: ‘com.android.application’ apply plugin: 'org.jetbrains.kotlin.multiplatform' dependencies {A implementation 'com.squareup.sqldelight:android-driver:1.0.0' }A kotlin {A sourceSets {A commonMain {A dependencies {A implementation project(':sqldelight-sample:sample-common') }A }A }A }A
123. apply plugin: ‘com.android.application’ apply plugin: 'org.jetbrains.kotlin.multiplatform' dependencies {A implementation 'com.squareup.sqldelight:android-driver:1.0.0' }A kotlin {A sourceSets {A commonMain {A dependencies {A implementation project(':sqldelight-sample:sample-common') }A }A }A }A A implementation 'com.squareup.sqldelight:android-driver:1.0.0' A A implementation project(':sqldelight-sample:sample-common')
124. apply plugin: ‘com.android.application’ apply plugin: 'org.jetbrains.kotlin.multiplatform' dependencies {A implementation (“com.squareup.sqldelight:android-driver:1.0.0) {A exclude group: 'com.squareup.sqldelight', module: 'runtime-jdk' }A }A kotlin {A sourceSets {A commonMain {A dependencies {A implementation project(':sqldelight-sample:sample-common') }A }A }A }A implementation ('com.squareup.sqldelight:android-driver:1.0.0') {A exclude group: 'com.squareup.sqldelight', module: 'runtime-jdk' }A implementation project(':sqldelight-sample:sample-common')
125. SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter Query SqlDatabase SqlCursor
126. SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlDatabase SqlCursor Query
127. SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlDatabase SqlCursor abstract class Query<out RowType : Any> { }X
128. abstract class Query<out RowType : Any> { fun execute(): SqlCursor fun executeAsList(): List<RowType> fun executeAsOne(): RowType fun executeAsOneOrNull(): RowType? }X
129. abstract class Query<out RowType : Any> { fun execute(): SqlCursor fun executeAsList(): List<RowType> fun executeAsOne(): RowType fun executeAsOneOrNull(): RowType? fun addListener(listener: Listener) fun removeListener(listener: Listener) interface Listener { fun queryResultsChanged() }Y }X
130. abstract class Query<out RowType : Any> { fun execute(): SqlCursor fun executeAsList(): List<RowType> fun executeAsOne(): RowType fun executeAsOneOrNull(): RowType? fun addListener(listener: Listener) fun removeListener(listener: Listener) interface Listener { fun queryResultsChanged() }Y fun notifyDataChanged() }X
131. val usersQuery: Query<User> = // … val users = usersQuery.executeAsList() // Show users somewhere…
132. val usersQuery: Query<User> = // … usersQuery.addListener(object : Listener { override fun queryResultsChanged() { val users = usersQuery.executeAsList() // Show users somewhere… }G }) usersQuery.notifyDataChanged()
133. val usersQuery: Query<User> = // … val executor = // … usersQuery.addListener(object : Listener { override fun queryResultsChanged() { executor.execute { val users = usersQuery.executeAsList() // Show users somewhere… }G }G }) usersQuery.notifyDataChanged()
134. val usersQuery: Query<User> = // … val executor = // … val mainThread = Handler(Looper.getMainLooper()) usersQuery.addListener(object : Listener { override fun queryResultsChanged() { executor.execute { val users = usersQuery.executeAsList() mainThread.post { // Show users somewhere… } }G }G }) usersQuery.notifyDataChanged()
135. val usersQuery: Query<User> = // …
136. val usersQuery: Query<User> = // … val usersObservable = usersQuery.asObservable()
137. val usersQuery: Query<User> = // … val usersObservable: Observable<User> = usersQuery.asObservable()
138. val usersQuery: Query<User> = // … val usersObservable: Observable<Query<User>> = usersQuery.asObservable()
139. val usersQuery: Query<User> = // … val usersObservable: Observable<List<User>> = usersQuery.asObservable() .map { it.executeAsList() } Query
140. val usersQuery: Query<User> = // … val usersObservable: Observable<List<User>> = usersQuery.asObservable() .mapToList()
141. val usersQuery: Query<User> = // …
142. val usersQuery: Query<User> = // … val usersChannel: ReceiveChannel<Query<User>> = usersQuery.asChannel()
143. val usersQuery: Query<User> = // … val usersChannel: ReceiveChannel<List<User>> = usersQuery.asChannel() .map { it.executeAsList() } Query
144. val usersQuery: Query<User> = // … val usersChannel: ReceiveChannel<List<User>> = usersQuery.asChannel() .mapToList()
145. suspend fun users() { val usersQuery: Query<User> = // … val usersChannel: ReceiveChannel<List<User>> = usersQuery.asChannel() .mapToList() return usersChannel.receive() }
146. suspend fun users() { val usersQuery: Query<User> = // … val usersChannel: ReceiveChannel<List<User>> = usersQuery.asChannel() .mapToList() return usersChannel.receive() }
147. suspend fun users() { val usersQuery: Query<User> = // … val usersChannel: ReceiveChannel<List<User>> = usersQuery.asChannel() .mapToList() return usersChannel.receive() }
148. val usersQuery: Query<User> = // …
149. val usersQuery: Query<User> = // … val usersLiveData: LiveData<Query<User>> = usersQuery.asLiveData()B
150. val usersQuery: Query<User> = // … val usersLiveData: LiveData<List<User>> = usersQuery.asLiveDataList()B Query
151. val usersQuery: Query<User> = // … val executor = // … val usersLiveData: LiveData<List<User>> = usersQuery.asLiveDataList(executor)B
152. val pagedQuery = QueryDataSourceFactory( queryProvider = { limit, offset -> queries.users(limit, offset) }, count = queries.usersCount())
153. apply plugin: 'com.squareup.sqldelight'
154. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.jvm'
155. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.jvm' // automatically adds… dependencies { api 'com.squareup.sqldelight:runtime-jdk:1.0.0' }X
156. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.jvm' // automatically adds… dependencies { api 'com.squareup.sqldelight:runtime-jdk:1.0.0' }X tasks.create('generateSqlDelight', SqlDelightTask) { // … }X tasks.getByName('compileKotlin').dependsOn('generateSqlDelight')
157. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.js'
158. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.js' // automatically adds… dependencies { api 'com.squareup.sqldelight:runtime-js:1.0.0' }X tasks.create('generateSqlDelight', SqlDelightTask) { // … }X tasks.getByName('compileKotlin').dependsOn('generateSqlDelight')
159. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.multiplatform'
160. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.multiplatform' // automatically adds… dependencies { api 'com.squareup.sqldelight:runtime:1.0.0' }X tasks.create('generateSqlDelight', SqlDelightTask) { // … }X tasks.getByName('compileKotlin').dependsOn('generateSqlDelight')
161. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.android'
162. apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.android' // automatically adds… dependencies { api 'com.squareup.sqldelight:runtime-jdk:1.0.0' }X tasks.create('generateSqlDelight', SqlDelightTask) { // … }X tasks.getByName('compileKotlin').dependsOn('generateSqlDelight')
163. m u l t i p l a t f o r m apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.android' // automatically adds… dependencies { api 'com.squareup.sqldelight:runtime-jdk:1.0.0' }X tasks.create('generateDebugSqlDelight', SqlDelightTask) { // … }X tasks.getByName('compileDebugKotlin').dependsOn('generateDebugSqlDelight') tasks.create('generateReleaseSqlDelight', SqlDelightTask) { // … }X tasks.getByName('compileReleaseKotlin').dependsOn('generateReleaseSqlDelight')
164. tasks.getByName('compileDebugKotlin').dependsOn('generateDebugSqlDelight') tasks.getByName('compileReleaseKotlin').dependsOn('generateReleaseSqlDelight') m u l t i p l a t f o r m apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.android' // automatically adds… dependencies { api 'com.squareup.sqldelight:runtime-jdk:1.0.0' }X tasks.create('generateDebugSqlDelight', SqlDelightTask) { /* … */ }X tasks.create('generateReleaseSqlDelight', SqlDelightTask) { /* … */ }X tasks.create('verifySqlDelightMigration', VerifyMigrationTask) { // … }X
165. IntelliJ Plugin
166. IntelliJ Plugin
167. IntelliJ Plugin
168. Gradle Plugin Compiler Generated Code IntelliJ Plugin
169. Gradle Plugin Compiler Generated Code IntelliJ Plugin
170. Gradle Plugin Compiler Generated Code IntelliJ Plugin
171. Gradle Plugin Compiler IntelliJ Plugin antlr SQLDelight 0.7
172. Gradle Plugin Compiler IntelliJ Plugin antlr PSI SQLDelight 0.7
173. Compiler Gradle Plugin IntelliJ Plugin PSI SQLDelight 1.0
174. Compiler Gradle Plugin IntelliJ Plugin sqlite-psi SQLDelight 1.0
175. JavaScript
176. sqlite3.h ed Native Node? WASM? Vanilla JS? JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Driver knarch.db Driver JDBC Driver JS Driver JVM Abstraction android. net.sqlc AndroidX SQLCipher Impl
177. sqlite3.h ed Native Node? WASM? Vanilla JS? JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) Driver knarch.db Driver JDBC Driver JVM Abstraction android. net.sqlc AndroidX SQLCipher Impl
178. Node WASM Vanilla JS runtime storage
179. Node WASM Vanilla JS sqlite3 runtime storage
180. Node WASM Vanilla JS sqlite3 sqlite-wasm runtime storage
181. Node WASM Vanilla JS sqlite3 sqlite-wasm sql.js runtime storage
182. Node WASM Vanilla JS sqlite3 sqlite-wasm sql.js filesystem runtime storage
183. Node WASM Vanilla JS sqlite3 sqlite-wasm sql.js filesystem ??? ??? runtime storage
184. iOS
185. let firstPlace = playerQueries.withRanking( ranking: 1 ).executeAsOne()
186. struct Player {A var id: NSNumber var name: String var country: String var ranking: NSNumber }A let firstPlace = playerQueries.withRanking( ranking: 1 ).executeAsOne()
187. struct Player {A var id: NSNumber var name: String var country: String var ranking: NSNumber }A func createPlayer(id: NSNumber, name: String, country: String, ranking: NSNumber) -> Player {A return Player(id: id, name: name, country: country, ranking: ranking) }A let firstPlace = playerQueries.withRanking( ranking: 1 ).executeAsOne()
188. struct Player {A var id: NSNumber var name: String var country: String var ranking: NSNumber }A func createPlayer(id: NSNumber, name: String, country: String, ranking: NSNumber) -> Player {A return Player(id: id, name: name, country: country, ranking: ranking) }A let firstPlace = playerQueries.withRanking( ranking: 1, mapper: createPlayer ).executeAsOne() as! Player
189. Generated Code SQL Delight SQLite Generated Swift Code Compiler
190. SQL Delight SQLite Compiler Generated Code Generated Swift Code
200. withRanking: SELECT name FROM player WHERE ranking = ?; insert: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?);
201. withRanking: SELECT name WHERE ranking = ?; insert: (name, country, ranking) VALUES (?, ?, ?); FROM player INSERT INTO player
202. withRanking: SELECT name FROM player WHERE ranking = ?; insert: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?); private inner class Insert { fun execute(): Long { val result = statement.execute() notifyQueries(queryWrapper.playerQueries.withRanking) return result } }
203. withRanking: SELECT name FROM player WHERE ranking = ?; insert: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?); private inner class Insert { fun execute(): Long { val result = statement.execute() notifyQueries(queryWrapper.playerQueries.withRanking) return result } } private inner class Insert { fun execute(): Long { val result = statement.execute() notifyQueries(queryWrapper.playerQueries.withRanking) return result } } withRanking: SELECT name FROM player WHERE ranking = ?; insert: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?);
204. withRanking: SELECT name FROM player WHERE ranking = ?; insert: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?); private inner class Insert { fun execute(): Long { val result = statement.execute() notifyQueries(queryWrapper.playerQueries.withRanking) return result } } withRanking: SELECT name FROM player WHERE ranking = ?; insert: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?);
205. Other SQL Dialects
206. 1.0!
207. 1.0! (RC)
208. github.com/square/sqldelight
209. github.com/square/sqldelight jakes.link/resurgence-of-sql jakes.link/embracing-sql
210. github.com/square/sqldelight jakes.link/resurgence-of-sql jakes.link/embracing-sql IntelliJ Plugins – Friday @ 15:15, Berlagezaal
211. @Strongolopolis @JakeWharton