👷♀️ IN DEVELOPMENT
PostgreSQL is still incubating, and pieces of the dialect are missing. If you are using it and encounter parts of the dialect which are unsupported, please report at sql-psi
Getting Started with PostgreSQL
First apply the gradle plugin in your project.
plugins {
id("app.cash.sqldelight") version "2.0.0-alpha05"
}
repositories {
google()
mavenCentral()
}
sqldelight {
databases {
create("Database") {
packageName.set("com.example")
dialect = "app.cash.sqldeight:postgres-dialect:2.0.0-alpha05"
}
}
}
plugins {
id "app.cash.sqldelight" version "2.0.0-alpha05"
}
repositories {
google()
mavenCentral()
}
sqldelight {
databases {
Database { // This will be the name of the generated database class.
packageName = "com.example"
dialect = "app.cash.sqldeight:postgres-dialect:2.0.0-alpha05"
}
}
}
SQLDelight needs to know the schema of your database. There are typically two approaches to setting up your database's schema. The "Fresh Schema" approach assumes that you are starting with an empty database, and that all the statements necessary to bring it to the desired state will be applied all at once. The "Migration Schema" approach on the other hand assumes that you already have a database and schema set up (e.g. an existing production database), and that you'll progressively apply migrations over time to update your database's schema.
In SQLDelight, these approaches translate to either writing your table definitions in .sq
files for a
"Fresh Schema", or by writing migration statements in .sqm
files for a "Migration Schema".
In both cases, your SQL queries will be written in .sq
files (as shown here).
Fresh Schema
Put your SQL statements in a .sq
file under 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 PRIMARY KEY 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');
In the same .sq
files you can start placing your sql statements to be executed at runtime.
Migration Schema
First, configure gradle to use migrations to assemble the schema:
sqldelight {
databases {
create("Database") {
...
sourceFolders = listOf("sqldelight")
deriveSchemaFromMigrations = true
}
}
}
sqldelight {
databases {
Database {
...
sourceFolders = ["sqldelight"]
deriveSchemaFromMigrations = true
}
}
}
Migration files have the extension .sqm
, and must have a number in their file name indicating what
order the migration file runs in. For example, given this hierarchy:
src
`-- main
`-- sqldelight
|-- v1__backend.sqm
`-- v2__backend.sqm
SQLDelight will create the schema by applying v1__backend.sqm
and then v2__backend.sqm
. Place
your normal SQL CREATE
/ALTER
statements in these files. If another service reads from your
migrations files (like flyway), make sure to read the info on migrations and how to
output valid SQL.
Typesafe SQL
Before you're able to execute SQL statements at runtime, you need to create a SqlDriver
to connect
to your database. The easiest way is off of a DataSource
that you would get from hikari or other
connection managers.
dependencies {
implementation("app.cash.sqldelight:jdbc-driver:2.0.0-alpha05")
}
dependencies {
implementation "app.cash.sqldelight:jdbc-driver:2.0.0-alpha05"
}
val driver: SqlDriver = dataSource.asJdbcDriver()
Regardless of if you specify the schema as fresh create table statements or through migrations,
runtime SQL goes in .sq
files.
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 (?, ?);
insertFullPlayerObject:
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(15, "Ryan Getzlaf")]
playerQueries.insert(player_number = 10, full_name = "Corey Perry")
println(playerQueries.selectAll().executeAsList())
// Prints [HockeyPlayer(15, "Ryan Getzlaf"), HockeyPlayer(10, "Corey Perry")]
val player = HockeyPlayer(10, "Ronald McDonald")
playerQueries.insertFullPlayerObject(player)
And that's it! Check out the other pages on the sidebar for other functionality.