Tweet
Tweet

Slide 1

Slide 1 text

Alec Strong Jake Wharton

Slide 2

Slide 2 text

data class Player(A val name: String, val ranking: Int, val country: String )A

Slide 3

Slide 3 text

@Entity data class Player(A val name: String, val ranking: Int, val country: String )A

Slide 4

Slide 4 text

@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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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 );

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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;

Slide 11

Slide 11 text

@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

Slide 12

Slide 12 text

@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

Slide 13

Slide 13 text

@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

Slide 14

Slide 14 text

@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'])

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

QueryBuilders

Slide 17

Slide 17 text

No content

Slide 18

Slide 18 text

QueryBuilders jakes.link/resurgence-of-sql

Slide 19

Slide 19 text

SQL Delight

Slide 20

Slide 20 text

SQLite Source Code Annotation
 Processor Reflection

Slide 21

Slide 21 text

SQLite Compiler Generated Code

Slide 22

Slide 22 text

CREATE TABLE player (A id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT NOT NULL, ranking INTEGER NOT NULL );

Slide 23

Slide 23 text

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 = ?;

Slide 24

Slide 24 text

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 = ?;

Slide 25

Slide 25 text

interface PlayerModel {A long id(); @NonNull String name(); @NonNull String country(); long ranking(); final class Factory {A public Factory(Creator creator); public SqlDelightQuery withRanking(long ranking); public RowMapper withRankingMapper(); }A }A

Slide 26

Slide 26 text

interface PlayerModel {A … final class Factory {A public Factory(Creator creator); … }B }A @AutoValue public abstract class MyPlayerModel implements PlayerModel {A public static Factory FACTORY = new Factory(AutoValue_MyPlayerModel::new) }A

Slide 27

Slide 27 text

SqlDelightStatement query = MyPlayerModel.FACTORY.withRanking(10); Observable> rank10 = db.createQuery(query.tables, query.statement, query.args) .mapToList(MyPlayerModel.FACTORY::withRankingMapper); interface PlayerModel {A … final class Factory {A public Factory(Creator creator); public SqlDelightQuery withRanking(long ranking); public RowMapper withRankingMapper(); }B }A

Slide 28

Slide 28 text

SQL Delight 0.7

Slide 29

Slide 29 text

No content

Slide 30

Slide 30 text

QueryBuilders jakes.link/embracing-sql

Slide 31

Slide 31 text

SQL Delight 1.0

Slide 32

Slide 32 text

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 (?, ?, ?);

Slide 33

Slide 33 text

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 (?, ?, ?);

Slide 34

Slide 34 text

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 (?, ?, ?);

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

class PlayerQueries : Transacter {A fun withRanking( ranking: Long, mapper: ( id: Long, name: String, country: String, ranking: Long ) -> T ): Query fun withRanking(ranking: Long): Query fun insertPlayer( name: String, country: String, ranking: Long ): Long }A

Slide 37

Slide 37 text

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) ) ;

Slide 38

Slide 38 text

class PlayerQueries : Transacter {A fun headToHead( firstPlayerName: String, secondPlayerName: String, mapper: ( player1Name: String, player2Name: String, player1Wins: Long, player2Wins: Long ) -> T ): Query fun headToHead( firstPlayerName: String, secondPlayerName: String ): Query }A

Slide 39

Slide 39 text

class PlayerQueries : Transacter {A fun withRanking( ranking: Long, mapper: ( id: Long, name: String, country: String, ranking: Long ) -> T ): Query fun withRanking(ranking: Long): Query fun insertPlayer( name: String, country: String, ranking: Long ): Long }A

Slide 40

Slide 40 text

class PlayerQueries : Transacter {A fun withRanking(ranking: Long): Query }A val player: Query = playerQueries.withRanking(10)

Slide 41

Slide 41 text

Query< > class PlayerQueries : Transacter {A fun withRanking(ranking: Long): Query }A val player: Player = playerQueries.withRanking(10).executeAsOne()

Slide 42

Slide 42 text

class PlayerQueries : Transacter {A fun withRanking(ranking: Long): Query }A val player: Observable> = playerQueries.withRanking(10) .asObservable() .ex

Slide 43

Slide 43 text

class PlayerQueries : Transacter {A fun withRanking(ranking: Long): Query }A val player: Observable> = playerQueries.withRanking(10) .asObservable() .mapToList()

Slide 44

Slide 44 text

class PlayerQueries : Transacter {A fun withRanking(ranking: Long, mapper: ( id: Long, name: String, country: String, ranking: Long ) -> T): Query }A val player = playerQueries.withRanking(10, ::MyPlayer) P l a y e r

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

CREATE TABLE player ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country NOT NULL, ranking INTEGER NOT NULL ); TEXT

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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 );

Slide 50

Slide 50 text

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 );

Slide 51

Slide 51 text

class QueryWrapper( database: SqlDatabase, internal val playerAdapter: Player.Adapter ) {A val playerQueries: PlayerQueries }A

Slide 52

Slide 52 text

class QueryWrapper( database: SqlDatabase, internal val playerAdapter: Player.Adapter ) {A val playerQueries: PlayerQueries }A interface Player {A class Adapter( val countryAdapter: ColumnAdapter ) }A

Slide 53

Slide 53 text

class QueryWrapper( database: SqlDatabase, internal val playerAdapter: Player.Adapter ) {A val playerQueries: PlayerQueries }A val queryWrapper = QueryWrapper( database = database, playerAdapter = Player.Adapter( countryAdapter = EnumColumnAdapter() ), )

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

SQLite Generated Code Compiler SQLite Migrations

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

SQL Delight 1.0

Slide 61

Slide 61 text

SQL Delight Multiplatform?

Slide 62

Slide 62 text

SQL Delight Multiplatform?

Slide 63

Slide 63 text

CashApp

Slide 64

Slide 64 text

db backend presenters api CashApp proto viewmodels

Slide 65

Slide 65 text

db

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

Generated Code SQL Delight db SQLite

Slide 68

Slide 68 text

Generated Code SQL Delight db SQLite Generated Swift Code Compiler

Slide 69

Slide 69 text

Generated Java wire protos .proto Generated C protoc

Slide 70

Slide 70 text

Generated Code db SQL Delight SQLite Generated Swift Code Compiler

Slide 71

Slide 71 text

Generated Code db SQL Delight SQLite

Slide 72

Slide 72 text

Generated Code Kotlin/Native kotlinc Android Native

Slide 73

Slide 73 text

Generated Code Kotlin/Native kotlinc Android Native kotlinc JVM kotlin2js JavaScript

Slide 74

Slide 74 text

Android JVM JavaScript Native

Slide 75

Slide 75 text

Android JVM JavaScript Native android.database.* android.database.sqlite.*

Slide 76

Slide 76 text

Android JVM JavaScript Native android.database.* android.database.sqlite.* sqlite3.h

Slide 77

Slide 77 text

Android JVM JavaScript Native android.database.* android.database.sqlite.* sqlite3.h java.sql.* org.sqlite.*

Slide 78

Slide 78 text

Android JVM JavaScript Native android.database.* android.database.sqlite.* sqlite3.h java.sql.* org.sqlite.* (xerial)

Slide 79

Slide 79 text

Android JVM JavaScript Native android.database.* android.database.sqlite.* sqlite3.h java.sql.* org.sqlite.* Node? WASM? Vanilla JS? (xerial)

Slide 80

Slide 80 text

android.database.* android.database.sqlite.* sqlite3.h Node? WASM? Vanilla JS? Generated Code Android JVM Native JavaScript java.sql.* org.sqlite.* (xerial)

Slide 81

Slide 81 text

android.database.* android.database.sqlite.* sqlite3.h Node? WASM? Vanilla JS? Generated Code Android Native JavaScript Database Abstraction java.sql.* org.sqlite.* (xerial) JVM

Slide 82

Slide 82 text

Database Abstraction SqlDatabase

Slide 83

Slide 83 text

Database Abstraction SqlDatabase SqlDatabase.Schema

Slide 84

Slide 84 text

Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement

Slide 85

Slide 85 text

Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction

Slide 86

Slide 86 text

Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter

Slide 87

Slide 87 text

Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter

Slide 88

Slide 88 text

Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlCursor

Slide 89

Slide 89 text

Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlCursor Query

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

Database Abstraction SqlDatabase SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlCursor Query

Slide 102

Slide 102 text

Database Abstraction SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter Query SqlDatabase SqlCursor

Slide 103

Slide 103 text

package com.squareup.sqldelight.db expect interface Closeable { fun close() } expect inline fun T.use(body: (T) -> R): R

Slide 104

Slide 104 text

package com.squareup.sqldelight.db expect interface Closeable { fun close() } expect inline fun 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.use(body: (T) -> R): R = kotlinIoUse(body)

Slide 105

Slide 105 text

package com.squareup.sqldelight.db expect interface Closeable { fun close() } expect inline fun T.use(body: (T) -> R): R package com.squareup.sqldelight.db actual interface Closeable { actual fun close() } actual inline fun T.use(body: (T) -> R): R { // Copy/paste JVM implementation… }

Slide 106

Slide 106 text

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' }

Slide 107

Slide 107 text

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' // ???

Slide 108

Slide 108 text

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!

Slide 109

Slide 109 text

apply plugin: 'org.jetbrains.kotlin.multiplatform' dependencies { compile 'com.squareup.sqldelight:runtime:1.0.0' }

Slide 110

Slide 110 text

apply plugin: 'org.jetbrains.kotlin.multiplatform' dependencies { compile 'com.squareup.sqldelight:runtime:1.0.0' } // settings.gradle enableFeaturePreview('GRADLE_METADATA')

Slide 111

Slide 111 text

com.squareup.sqldelight + runtime

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

runtime-1.0.0.module { "variants": [ { "name": "iosArm64-api", "available-at": { "url": "../../runtime-iosarm64/runtime-iosarm64-1.0.0.module" } }, ... ]
 }

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

com.squareup.sqldelight + runtime

Slide 116

Slide 116 text

com.squareup.sqldelight + runtime + runtime-common + runtime-iosarm64 + runtime-iosx64 + runtime-jvm + runtime-js

Slide 117

Slide 117 text

com.squareup.sqldelight + android-driver + runtime + runtime-common + runtime-iosarm64 + runtime-iosx64 + runtime-jvm + runtime-js

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

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

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

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

Slide 122

Slide 122 text

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

Slide 123

Slide 123 text

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')

Slide 124

Slide 124 text

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')

Slide 125

Slide 125 text

SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter Query SqlDatabase SqlCursor

Slide 126

Slide 126 text

SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlDatabase SqlCursor Query

Slide 127

Slide 127 text

SqlDatabase.Schema SqlPreparedStatement Transacter.Transaction Transacter SqlDatabase SqlCursor abstract class Query { }X

Slide 128

Slide 128 text

abstract class Query { fun execute(): SqlCursor fun executeAsList(): List fun executeAsOne(): RowType fun executeAsOneOrNull(): RowType? }X

Slide 129

Slide 129 text

abstract class Query { fun execute(): SqlCursor fun executeAsList(): List fun executeAsOne(): RowType fun executeAsOneOrNull(): RowType? fun addListener(listener: Listener) fun removeListener(listener: Listener) interface Listener { fun queryResultsChanged() }Y }X

Slide 130

Slide 130 text

abstract class Query { fun execute(): SqlCursor fun executeAsList(): List fun executeAsOne(): RowType fun executeAsOneOrNull(): RowType? fun addListener(listener: Listener) fun removeListener(listener: Listener) interface Listener { fun queryResultsChanged() }Y fun notifyDataChanged() }X

Slide 131

Slide 131 text

val usersQuery: Query = // … val users = usersQuery.executeAsList() // Show users somewhere…

Slide 132

Slide 132 text

val usersQuery: Query = // … usersQuery.addListener(object : Listener { override fun queryResultsChanged() { val users = usersQuery.executeAsList() // Show users somewhere… }G }) usersQuery.notifyDataChanged()

Slide 133

Slide 133 text

val usersQuery: Query = // … val executor = // … usersQuery.addListener(object : Listener { override fun queryResultsChanged() { executor.execute { val users = usersQuery.executeAsList() // Show users somewhere… }G }G }) usersQuery.notifyDataChanged()

Slide 134

Slide 134 text

val usersQuery: Query = // … 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()

Slide 135

Slide 135 text

val usersQuery: Query = // …

Slide 136

Slide 136 text

val usersQuery: Query = // … val usersObservable = usersQuery.asObservable()

Slide 137

Slide 137 text

val usersQuery: Query = // … val usersObservable: Observable = usersQuery.asObservable()

Slide 138

Slide 138 text

val usersQuery: Query = // … val usersObservable: Observable> = usersQuery.asObservable()

Slide 139

Slide 139 text

val usersQuery: Query = // … val usersObservable: Observable> = usersQuery.asObservable() .map { it.executeAsList() } Query

Slide 140

Slide 140 text

val usersQuery: Query = // … val usersObservable: Observable> = usersQuery.asObservable() .mapToList()

Slide 141

Slide 141 text

val usersQuery: Query = // …

Slide 142

Slide 142 text

val usersQuery: Query = // … val usersChannel: ReceiveChannel> = usersQuery.asChannel()

Slide 143

Slide 143 text

val usersQuery: Query = // … val usersChannel: ReceiveChannel> = usersQuery.asChannel() .map { it.executeAsList() } Query

Slide 144

Slide 144 text

val usersQuery: Query = // … val usersChannel: ReceiveChannel> = usersQuery.asChannel() .mapToList()

Slide 145

Slide 145 text

suspend fun users() { val usersQuery: Query = // … val usersChannel: ReceiveChannel> = usersQuery.asChannel() .mapToList() return usersChannel.receive() }

Slide 146

Slide 146 text

suspend fun users() { val usersQuery: Query = // … val usersChannel: ReceiveChannel> = usersQuery.asChannel() .mapToList() return usersChannel.receive() }

Slide 147

Slide 147 text

suspend fun users() { val usersQuery: Query = // … val usersChannel: ReceiveChannel> = usersQuery.asChannel() .mapToList() return usersChannel.receive() }

Slide 148

Slide 148 text

val usersQuery: Query = // …

Slide 149

Slide 149 text

val usersQuery: Query = // … val usersLiveData: LiveData> = usersQuery.asLiveData()B

Slide 150

Slide 150 text

val usersQuery: Query = // … val usersLiveData: LiveData> = usersQuery.asLiveDataList()B Query

Slide 151

Slide 151 text

val usersQuery: Query = // … val executor = // … val usersLiveData: LiveData> = usersQuery.asLiveDataList(executor)B

Slide 152

Slide 152 text

val pagedQuery = QueryDataSourceFactory( queryProvider = { limit, offset -> queries.users(limit, offset) }, count = queries.usersCount())

Slide 153

Slide 153 text

apply plugin: 'com.squareup.sqldelight'

Slide 154

Slide 154 text

apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.jvm'

Slide 155

Slide 155 text

apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.jvm' // automatically adds… dependencies { api 'com.squareup.sqldelight:runtime-jdk:1.0.0' }X

Slide 156

Slide 156 text

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')

Slide 157

Slide 157 text

apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.js'

Slide 158

Slide 158 text

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')

Slide 159

Slide 159 text

apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.multiplatform'

Slide 160

Slide 160 text

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')

Slide 161

Slide 161 text

apply plugin: 'com.squareup.sqldelight' apply plugin: 'org.jetbrains.kotlin.android'

Slide 162

Slide 162 text

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')

Slide 163

Slide 163 text

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')

Slide 164

Slide 164 text

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

Slide 165

Slide 165 text

IntelliJ Plugin

Slide 166

Slide 166 text

IntelliJ Plugin

Slide 167

Slide 167 text

IntelliJ Plugin

Slide 168

Slide 168 text

Gradle Plugin Compiler Generated Code IntelliJ Plugin

Slide 169

Slide 169 text

Gradle Plugin Compiler Generated Code IntelliJ Plugin

Slide 170

Slide 170 text

Gradle Plugin Compiler Generated Code IntelliJ Plugin

Slide 171

Slide 171 text

Gradle Plugin Compiler IntelliJ Plugin antlr SQLDelight 0.7

Slide 172

Slide 172 text

Gradle Plugin Compiler IntelliJ Plugin antlr PSI SQLDelight 0.7

Slide 173

Slide 173 text

Compiler Gradle Plugin IntelliJ Plugin PSI SQLDelight 1.0

Slide 174

Slide 174 text

Compiler Gradle Plugin IntelliJ Plugin sqlite-psi SQLDelight 1.0

Slide 175

Slide 175 text

JavaScript

Slide 176

Slide 176 text

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

Slide 177

Slide 177 text

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

Slide 178

Slide 178 text

Node WASM Vanilla JS runtime storage

Slide 179

Slide 179 text

Node WASM Vanilla JS sqlite3 runtime storage

Slide 180

Slide 180 text

Node WASM Vanilla JS sqlite3 sqlite-wasm runtime storage

Slide 181

Slide 181 text

Node WASM Vanilla JS sqlite3 sqlite-wasm sql.js runtime storage

Slide 182

Slide 182 text

Node WASM Vanilla JS sqlite3 sqlite-wasm sql.js filesystem runtime storage

Slide 183

Slide 183 text

Node WASM Vanilla JS sqlite3 sqlite-wasm sql.js filesystem ??? ??? runtime storage

Slide 184

Slide 184 text

iOS

Slide 185

Slide 185 text

let firstPlace = playerQueries.withRanking( ranking: 1 ).executeAsOne()

Slide 186

Slide 186 text

struct Player {A var id: NSNumber var name: String var country: String var ranking: NSNumber }A let firstPlace = playerQueries.withRanking( ranking: 1 ).executeAsOne()

Slide 187

Slide 187 text

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()

Slide 188

Slide 188 text

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

Slide 189

Slide 189 text

Generated Code SQL Delight SQLite Generated Swift Code Compiler

Slide 190

Slide 190 text

SQL Delight SQLite Compiler Generated Code Generated Swift Code

Slide 191

Slide 191 text

No content

Slide 192

Slide 192 text

No content

Slide 193

Slide 193 text

No content

Slide 194

Slide 194 text

No content

Slide 195

Slide 195 text

No content

Slide 196

Slide 196 text

No content

Slide 197

Slide 197 text

No content

Slide 198

Slide 198 text

No content

Slide 199

Slide 199 text

No content

Slide 200

Slide 200 text

withRanking: SELECT name FROM player WHERE ranking = ?; insert: INSERT INTO player (name, country, ranking) VALUES (?, ?, ?);

Slide 201

Slide 201 text

withRanking: SELECT name WHERE ranking = ?; insert: (name, country, ranking) VALUES (?, ?, ?); FROM player INSERT INTO player

Slide 202

Slide 202 text

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 } }

Slide 203

Slide 203 text

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 (?, ?, ?);

Slide 204

Slide 204 text

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 (?, ?, ?);

Slide 205

Slide 205 text

Other SQL Dialects

Slide 206

Slide 206 text

1.0!

Slide 207

Slide 207 text

1.0! (RC)

Slide 208

Slide 208 text

github.com/square/sqldelight

Slide 209

Slide 209 text

github.com/square/sqldelight jakes.link/resurgence-of-sql jakes.link/embracing-sql

Slide 210

Slide 210 text

github.com/square/sqldelight jakes.link/resurgence-of-sql jakes.link/embracing-sql IntelliJ Plugins – Friday @ 15:15, Berlagezaal

Slide 211

Slide 211 text

@Strongolopolis @JakeWharton