SQLDelight generates typesafe APIs from your SQL statements. It compile-time verifies your schema, statements, and migrations and provides IDE features like autocomplete and refactoring which make writing and maintaining SQL simple. SQLDelight currently supports the SQLite dialect and there are supported SQLite drivers on Android, JVM and iOS.
To use SQLDelight, apply the gradle plugin and put your SQL statements in a .sq
file in src/main/sqldelight
. Typically the first statement in the SQL file creates a table.
-- src/main/sqldelight/com/example/sqldelight/hockey/data/Player.sq
CREATE TABLE hockeyPlayer (
player_number INTEGER NOT NULL,
full_name TEXT NOT NULL
);
CREATE INDEX hockeyPlayer_full_name ON hockeyPlayer(full_name);
INSERT INTO hockeyPlayer (player_number, full_name)
VALUES (15, 'Ryan Getzlaf');
From this SQLDelight will generate a Database
Kotlin class with an associated Schema
object that can be used to create your database and run your statements on it. Doing this also requires a driver, which SQLDelight provides implementations of:
dependencies {
implementation "com.squareup.sqldelight:android-driver:1.1.3"
}
val driver: SqlDriver = AndroidSqliteDriver(Database.Schema, context, "test.db")
dependencies {
implementation "com.squareup.sqldelight:ios-driver:1.1.3"
}
// You'll also need to have SQLite linked via -lsqlite3 during compilation.
val driver: SqlDriver = NativeSqliteDriver(Database.Schema, "test.db")
dependencies {
implementation "com.squareup.sqldelight:sqlite-driver:1.1.3"
}
val driver: SqlDriver = JdbcSqliteDriver()
Database.Schema.create(driver)
SQL statements inside a .sq
file can be labeled to have a typesafe function generated for them available at runtime.
selectAll:
SELECT *
FROM hockeyPlayer;
insert:
INSERT INTO hockeyPlayer(player_number, full_name)
VALUES (?, ?);
Files with labeled statements in them will have a queries file generated from them that matches the .sq
file name - putting the above sql into Player.sq
generates PlayerQueries.kt
. To get a reference to PlayerQueries
you need to wrap the driver we made above:
// In reality the database and driver above should be created a single time
// and passed around using your favourite dependency injection/service locator/singleton pattern.
val database = Database(driver)
val playerQueries: PlayerQueries = database.playerQueries
println(playerQueries.selectAll().executeAsList())
// Prints [HockeyPlayer.Impl(15, "Ryan Getzlaf")]
playerQueries.insert(player_number = 10, full_name = "Corey Perry")
println(playerQueries.selectAll().executeAsList())
// Prints [HockeyPlayer.Impl(15, "Ryan Getzlaf"), HockeyPlayer.Impl(10, "Corey Perry")]
By default queries will return a data class implementation of the table schema. To override this behavior pass a custom mapper to the query function. Your custom mapper will receive typesafe parameters which are the projection of your select statement.
val selectAllNames = playerQueries.selectAll(mapper = { player_number, full_name -> full_name.toUppercase() })
println(selectAllNames.executeAsList())
// Prints ["RYAN GETZLAF", "COREY PERRY"]
In general you should be leveraging SQL to do custom projections whenever possible.
selectNames:
SELECT upper(full_name)
FROM hockeyPlayer;
val selectAllNames = playerQueries.selectNames()
println(selectAllNames.executeAsList())
// Prints ["RYAN GETZLAF", "COREY PERRY"]
But the custom mapping is there when this isn't possible, for example getting a Parcelable
type for query results on Android.
.sq
files use the exact same syntax as SQLite, including SQLite Bind Args. If a statement contains bind args, the associated method will require corresponding arguments.
selectByNumber:
SELECT *
FROM hockeyPlayer
WHERE player_number = ?;
val selectNumber10 = playerQueries.selectByNumber(player_number = 10)
println(selectNumber10.executeAsOne())
// Prints "Corey Perry"
Sets of values can also be passed as an argument.
selectByNames:
SELECT *
FROM hockeyPlayer
WHERE full_name IN ?;
playerQueries.selectByNames(listOf("Alec Strong", "Jake Wharton", "Matt Precious"))
Named parameters or indexed parameters can be used.
firstOrLastName:
SELECT *
FROM hockeyPlayer
WHERE full_name LIKE ('% ' || :name)
OR full_name LIKE (:name || ' %');
playerQueries.firstOrLastName(name = "Ryan")
SQLDelight column definitions are identical to regular SQLite column definitions but support an extra column constraint which specifies the Kotlin type of the column in the generated interface. SQLDelight natively supports Long, Double, String, ByteArray, Int, Short, Float, and Booleans.
CREATE TABLE some_types (
some_long INTEGER, -- Stored as INTEGER in db, retrieved as Long
some_double REAL, -- Stored as REAL in db, retrieved as Double
some_string TEXT, -- Stored as TEXT in db, retrieved as String
some_blob BLOB, -- Stored as BLOB in db, retrieved as ByteArray
some_int INTEGER AS Int, -- Stored as INTEGER in db, retrieved as Int
some_short INTEGER AS Short, -- Stored as INTEGER in db, retrieved as Short
some_float REAL AS Float -- Stored as REAL in db, retrieved as Float
);
Boolean columns are stored in the db as INTEGER
, and so they can be given INTEGER
column constraints. Use DEFAULT 0
to default to false, for example.
CREATE TABLE hockey_player (
injured INTEGER AS Boolean DEFAULT 0
)
If you'd like to retrieve columns as custom types you can specify a Kotlin type:
import kotlin.collections.List;
CREATE TABLE hockeyPlayer (
cup_wins TEXT AS List<String> NOT NULL
);
However, creating the Database
will require you to provide a ColumnAdapter
which knows how
to map between the database type and your custom type:
val listOfStringsAdapter = object : ColumnAdapter<List<String>, String> {
override fun decode(databaseValue: String) = databaseValue.split(",")
override fun encode(value: List<String>) = value.joinToString(separator = ",")
}
val queryWrapper: Database = Database(
driver = driver,
hockeyPlayerAdapter = hockeyPlayer.Adapter(
cup_winsAdapter = listOfStringsAdapter
)
)
As a convenience the SQLDelight runtime includes a ColumnAdapter
for storing an enum as TEXT.
import com.example.hockey.HockeyPlayer;
CREATE TABLE hockeyPlayer (
position TEXT AS HockeyPlayer.Position
)
val queryWrapper: Database = Database(
driver = driver,
hockeyPlayerAdapter = HockeyPlayer.Adapter(
positionAdapter = EnumColumnAdapter()
)
)
The .sq
file always describes how to create the latest schema in an empty database. If your database is currently on an earlier version, migration files bring those databases up-to-date.
The first version of the schema is 1. Migration files are named <version to upgrade from>.sqm
. To migrate to version 2, put migration statements in 1.sqm
:
ALTER TABLE hockeyPlayer ADD COLUMN draft_year INTEGER;
ALTER TABLE hockeyPlayer ADD COLUMN draft_order INTEGER;
Migration files go in the src/main/sqldelight
folder.
These SQL statements are run by Database.Schema.migrate()
. This is automatic for the Android and iOS drivers.
You can also place a .db
file in the src/main/sqldelight
folder of the same <version number>.db
format. If there is a .db
file present, a new verifySqlDelightMigration
task will be added to the gradle project, and it will run as part of the test
task, meaning your migrations will be verified against that .db
file. It confirms that the migrations yield a database with the latest schema.
To generate a .db
file from your latest schema, run the generateSqlDelightSchema
task. You should probably do this before you create your first migration.
To observe a query, depend on the RxJava extensions artifact and use the extension method it provides:
dependencies {
implementation "com.squareup.sqldelight:rxjava2-extensions:1.1.3"
}
val players: Observable<List<HockeyPlayer>> =
playerQueries.selectAll()
.asObservable()
.mapToList()
To use SQLDelight in Kotlin multiplatform configure the Gradle plugin with a package to generate code into.
apply plugin: "org.jetbrains.kotlin.multiplatform"
apply plugin: "com.squareup.sqldelight"
sqldelight {
packageName = "com.example.hockey"
}
Put .sq
files in the src/commonMain/sqldelight
directory, and then expect
a SqlDriver
to be provided by individual platforms when creating the Database
. Migration files should also be in the same src/commonMain/sqldelight
directory.
Multiplatform requires the gradle metadata feature, which you need to enable via the settings.gradle
file in the project root:
enableFeaturePreview('GRADLE_METADATA')
To use SQLDelight with Android's Paging Library add a dependency on the paging extension artifact.
dependencies {
implementation "com.squareup.sqldelight:android-paging-extensions:1.1.3"
}
To create a DataSource
write a query to get the number of rows and a query that takes an offset and a limit.
countPlayers:
SELECT count(*) FROM hockeyPlayer;
players:
SELECT *
FROM hockeyPlayer
LIMIT :limit OFFSET :offset;
val dataSource = QueryDataSourceFactory(
queryProvider = playerQueries::players,
countQuery = playerQueries.countPlayers()
).create()
Full support of dialect including views, triggers, indexes, FTS tables, etc. If features are missing please file an issue!
The IntelliJ plugin provides language-level features for .sq
files, including:
- Syntax highlighting
- Refactoring/Find usages
- Code autocompletion
- Generate
Queries
files after edits - Right click to copy as valid SQLite
- Compiler errors in IDE click through to file
buildscript {
repositories {
google()
mavenCentral()
}
dependencies {
classpath 'com.squareup.sqldelight:gradle-plugin:1.1.3'
}
}
apply plugin: 'com.squareup.sqldelight'
You can declare databases explicitely in gradle for tighter control of the generated schema:
sqldelight {
MyDatabase {
packageName = "com.example.db"
// By default this is ["sqldelight"], and means your sqldelight will be in
// folders like 'src/main/db' instead of 'src/main/sqldelight'
sourceFolders = ["db"]
// Defaults to file("src/main/sqldelight")
schemaOutputDirectory = file("build/dbs")
// Optionally specify schema dependencies on other gradle projects
dependency project(':OtherProject')
}
}
The IntelliJ plugin can be installed from Android Studio by navigating
Android Studio -> Preferences -> Plugins -> Browse repositories -> Search for SQLDelight
Snapshots of the development version (including the IDE plugin zip) are available in
Sonatype's snapshots
repository.
There's a separate guide for upgrading from 0.7 and other pre-1.0 versions here
Copyright 2016 Square, Inc.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.