SQLDelight

https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/
Admin
SQLDelight is a Kotlin SQL database library that generates typesafe Kotlin APIs from SQL statements,...
Tokens:7,811
Snippets:124
Trust Score:9.7
Update:2 months ago
Show doc for...
Tokens:
Raw
### Executing a Query with a Bind Argument

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/query_arguments

This example shows how to execute a query that uses a bind argument, passing the value for `player_number`.

```kotlin
val selectNumber10 = playerQueries.selectByNumber(player_number = 10)
println(selectNumber10.executeAsOne())
// Prints "Corey Perry"
```

--------------------------------

### Executing a Query with a Named Argument

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/query_arguments

This example demonstrates calling a query that uses a named argument, providing the value for `:name`.

```kotlin
playerQueries.firstOrLastName(name = "Ryan")
```

--------------------------------

### Executing a Query with Variable Arguments

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/query_arguments

This example shows how to pass a list of names as a variable argument to the `selectByNames` query.

```kotlin
playerQueries.selectByNames(listOf("Alec", "Jake", "Matt"))
```

--------------------------------

### Example Migration SQL Statements

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/migrations

SQL statements to add new columns to an existing table for schema version upgrade.

```sql
ALTER TABLE hockeyPlayer ADD COLUMN draft_year INTEGER;
ALTER TABLE hockeyPlayer ADD COLUMN draft_order INTEGER;

```

--------------------------------

### Inserting a Record using a Data Class

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/query_arguments

This example demonstrates how to insert a new player record by binding a `HockeyPlayer` data class instance to the `insertPlayer` query.

```kotlin
val rickardRakell = HockeyPlayer(
  full_name = "Rickard Rakell",
  number = 67
)
playerQueries.insertPlayer(rickardRakell)
```

--------------------------------

### Consume Query as Flow

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/coroutines

Use the .asFlow() extension method on a query builder to get a Flow that emits query results. Map to a list using .mapToList() with a specified dispatcher.

```kotlin
val players: Flow<List<HockeyPlayer>> = 
  playerQueries.selectAll()
    .asFlow()
    .mapToList(Dispatchers.IO)
```

--------------------------------

### Custom Projection with Mapper

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/custom_projections

Override the default data class projection by providing a custom mapper function to the query. This example maps the full name to uppercase.

```kotlin
val selectAllNames = playerQueries.selectAll(
  mapper = { player_number, full_name -> full_name.toUppercase() }
)
println(selectAllNames.executeAsList())
// Prints ["RYAN GETZLAF", "COREY PERRY"]

```

--------------------------------

### Configure Source Directories for SQL Files

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Define the directories where SQLDelight should look for `.sq` and `.sqm` files. Defaults to `src/[prefix]main/sqldelight`.

```kotlin
srcDirs.setFrom("src/main/sqldelight")

```

```kotlin
srcDirs = ['src/main/sqldelight']

```

--------------------------------

### Create Keyset PagingSource

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/androidx_paging

Instantiate a QueryPagingSource for keyset-based paging using page boundary and core query providers.

```kotlin
import app.cash.sqldelight.android.paging3.QueryPagingSource

val keyedSource = QueryPagingSource(
  transacter = playerQueries,
  context = Dispatchers.IO,
  pageBoundariesProvider = playerQueries::pageBoundaries,
  queryProvider = playerQueries::keyedQuery,
)

```

--------------------------------

### Instantiate Database with Custom ColumnAdapter

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Provide the custom `ColumnAdapter` for your custom column type when creating the SQLDelight `Database` instance.

```kotlin
val queryWrapper: Database = Database(
  driver = driver,
  hockeyPlayerAdapter = hockeyPlayer.Adapter(
    cup_winsAdapter = listOfStringsAdapter
  )
)

```

--------------------------------

### Add Multiple Source Directories

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Specify multiple directories for SQLDelight to scan for SQL files. Useful for organizing different sets of SQL definitions.

```kotlin
srcDirs("src/main/sqldelight", "main/sqldelight")

```

```kotlin
srcDirs('src/main/sqldelight', 'main/sqldelight')

```

--------------------------------

### Apply SQLDelight Gradle Plugin

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Apply the SQLDelight Gradle plugin and configure database registration in your project's build script.

```kotlin
plugins {
  id("app.cash.sqldelight") version "2.3.2"
}

repositories {
  google()
  mavenCentral()
}

sqldelight {
  databases {
    register("Database") {
      packageName.set("com.example")
    }
  }
}

```

```groovy
plugins {
  id "app.cash.sqldelight" version "2.3.2"
}

repositories {
  google()
  mavenCentral()
}

sqldelight {
  databases {
    register("Database") { // This will be the name of the generated database class.
      packageName = "com.example"
    }
  }
}

```

--------------------------------

### Programmatic Database Migration with Callbacks

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/migrations

Use the `Database.Schema.migrate` API to run migrations programmatically. The `AfterVersion` callback allows executing custom SQL after a specific migration version completes, before subsequent migrations are applied.

```kotlin
Database.Schema.migrate(
    driver = database,
    oldVersion = 0,
    newVersion = Database.Schema.version,
    AfterVersion(3) { driver -> driver.execute(null, "INSERT INTO test (value) VALUES('hello')", 0) },
)

```

--------------------------------

### Create SQLDelight Database with Name

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Declare a new SQLDelight database with a specific name. Use this when you need to define a database instance with a given identifier.

```kotlin
sqldelight {
  databases {
    create("MyDatabase") {
      // Database configuration here.
    }
  }
}

```

```kotlin
sqldelight {
  databases {
    MyDatabase {
      // Database configuration here.
    }
  }
}

```

--------------------------------

### Implement Native Driver Factory

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Implement the DriverFactory for native platforms using NativeSqliteDriver.

```kotlin
actual class DriverFactory {
  actual fun createDriver(): SqlDriver {
    return NativeSqliteDriver(Database.Schema, "test.db")
  }
}

```

--------------------------------

### Set Schema Output Directory

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Configure the directory where `.db` schema files are stored. These are used for migration verification. Defaults to `null`.

```kotlin
schemaOutputDirectory.set(file("src/main/sqldelight/databases"))

```

```kotlin
schemaOutputDirectory = file("src/main/sqldelight/databases")

```

--------------------------------

### Instantiate Database with EnumColumnAdapter

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Configure the `Database` to use the `EnumColumnAdapter` for enum columns. This adapter handles String-to-enum conversion.

```kotlin
val queryWrapper: Database = Database(
  driver = driver,
  hockeyPlayerAdapter = HockeyPlayer.Adapter(
    positionAdapter = EnumColumnAdapter()
  )
)

```

--------------------------------

### Enable Migration Verification

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Set to true to fail the build if there are errors in migration files. Defaults to false.

```kotlin
verifyMigrations.set(true)

```

```kotlin
verifyMigrations = true

```

--------------------------------

### Create Offset Based PagingSource

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/androidx_paging

Instantiate a QueryPagingSource for offset-based paging using count and paged queries.

```kotlin
import app.cash.sqldelight.android.paging3.QueryPagingSource

val pagingSource: PagingSource = QueryPagingSource(
  countQuery = playerQueries.countPlayers(),
  transacter = playerQueries,
  context = Dispatchers.IO,
  queryProvider = playerQueries::players,
)

```

--------------------------------

### Add SQLDelight Driver Dependencies

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Add the appropriate SQLDelight driver dependency for each target platform in your project's build script.

```kotlin
kotlin {
  sourceSets.androidMain.dependencies {
    implementation("app.cash.sqldelight:android-driver:2.3.2")
  }

  // or iosMain, windowsMain, etc.
  sourceSets.nativeMain.dependencies {
    implementation("app.cash.sqldelight:native-driver:2.3.2")
  }

  sourceSets.jvmMain.dependencies {
    implementation("app.cash.sqldelight:sqlite-driver:2.3.2")
  }
}

```

```groovy
kotlin {
  sourceSets.androidMain.dependencies {
    implementation "app.cash.sqldelight:android-driver:2.3.2"
  }

  // or iosMain, windowsMain, etc.
  sourceSets.nativeMain.dependencies {
    implementation "app.cash.sqldelight:native-driver:2.3.2"
  }

  sourceSets.jvmMain.dependencies {
    implementation "app.cash.sqldelight:sqlite-driver:2.3.2"
  }
}

```

--------------------------------

### Create Common Driver Factory

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Define an expect class for a common factory to create SqlDriver instances, which will be implemented for each platform.

```kotlin
import com.example.Database

expect class DriverFactory {
  fun createDriver(): SqlDriver
}

fun createDatabase(driverFactory: DriverFactory): Database {
  val driver = driverFactory.createDriver()
  val database = Database(driver)

  // Do more work with the database (see below).
}

```

--------------------------------

### Define SQL Schema for Players

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Define your SQL schema in a .sq file, including table creation, index definition, and initial data insertion.

```sql
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');

```

--------------------------------

### Implement JDBC Driver Factory

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Implement the DriverFactory for JVM using JdbcSqliteDriver.

```kotlin
actual class DriverFactory {
  actual fun createDriver(): SqlDriver {
    val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:test.db", Properties(), Database.Schema)
    return driver
  }
}

```

--------------------------------

### Add AndroidX Paging Dependency (Kotlin)

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/androidx_paging

Add the SQLDelight AndroidX Paging 3 extension artifact to your commonMain dependencies.

```kotlin
kotlin {
  sourceSets.commonMain.dependencies {
    implementation("app.cash.sqldelight:androidx-paging3-extensions:2.3.2")
  }
}

```

```kotlin
kotlin {
  sourceSets.commonMain.dependencies {
    implementation "app.cash.sqldelight:androidx-paging3-extensions:2.3.2"
  }
}

```

--------------------------------

### Implement Android Driver Factory

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Implement the DriverFactory for Android using AndroidSqliteDriver.

```kotlin
actual class DriverFactory(private val context: Context) {
  actual fun createDriver(): SqlDriver {
    return AndroidSqliteDriver(Database.Schema, context, "test.db")
  }
}

```

--------------------------------

### Set Database Package Name

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Specify the package name for the generated database class. This ensures proper organization of your database code.

```kotlin
packageName.set("com.example.db")

```

```kotlin
packageName = "com.example.db"

```

--------------------------------

### Enable Asynchronous Query Generation

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

If true, SQLDelight generates suspending query methods for asynchronous drivers. Defaults to false.

```kotlin
generateAsync.set(true)

```

```kotlin
generateAsync = true

```

--------------------------------

### Use Generated Typesafe Queries

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Interact with the database using the generated typesafe query functions from the Database and PlayerQueries objects.

```kotlin
fun doDatabaseThings(driver: SqlDriver) {
  val database = Database(driver)
  val playerQueries: PlayerQueries = database.playerQueries

  println(playerQueries.selectAll().executeAsList()) 
  // [HockeyPlayer(15, "Ryan Getzlaf")]

  playerQueries.insert(player_number = 10, full_name = "Corey Perry")
  println(playerQueries.selectAll().executeAsList()) 
  // [HockeyPlayer(15, "Ryan Getzlaf"), HockeyPlayer(10, "Corey Perry")]

  val player = HockeyPlayer(10, "Ronald McDonald")
  playerQueries.insertFullPlayerObject(player)
}

```

--------------------------------

### SQL Query with Named Argument

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/query_arguments

SQLDelight supports named parameters, which can be used in place of indexed parameters for clarity.

```sqlite
firstOrLastName:
SELECT *
FROM hockeyPlayer
WHERE full_name LIKE ('% ' || :name)
OR full_name LIKE (:name || ' %');
```

--------------------------------

### Implement Custom ColumnAdapter for List<String>

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Create a `ColumnAdapter` to handle the conversion between the database's String representation and Kotlin's `List<String>`.

```kotlin
val listOfStringsAdapter = object : ColumnAdapter<List<String>, String> {
  override fun decode(databaseValue: String) =
    if (databaseValue.isEmpty()) {
      listOf()
    } else {
      databaseValue.split(",")
    }
  override fun encode(value: List<String>) = value.joinToString(separator = ",")
}

```

--------------------------------

### SQL Query with Bind Argument

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/query_arguments

SQLDelight uses standard SQLite bind arguments. The associated method will require arguments corresponding to the bind args in the statement.

```sqlite
selectByNumber:
SELECT *
FROM hockeyPlayer
WHERE player_number = ?;
```

--------------------------------

### Keyset Paging Boundaries Query

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/androidx_paging

Define the SQL query for pre-calculating page boundaries in keyset paging, often requiring window functions.

```sql
pageBoundaries:
SELECT id 
FROM (
  SELECT
    id,
    CASE
      WHEN ((row_number() OVER(ORDER BY id ASC) - 0) % :limit) = 0 THEN 1
      WHEN id = :anchor THEN 1
      ELSE 0
    END page_boundary;
  FROM hockeyPlayer
  ORDER BY id ASC
)
WHERE page_boundary = 1;

```

--------------------------------

### Executing SQL Projection Query

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/custom_projections

Execute a query that uses an SQL projection. The result will be a list of the projected values (uppercase names in this case).

```kotlin
val selectAllNames = playerQueries.selectNames()
println(selectAllNames.executeAsList())
// Prints ["RYAN GETZLAF", "COREY PERRY"]

```

--------------------------------

### Define Custom Column Type with SQLDelight

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Specify a custom Kotlin type for a column using the `AS` keyword. Ensure you provide a corresponding `ColumnAdapter` during database creation.

```sql
import kotlin.String;
import kotlin.collections.List;

CREATE TABLE hockeyPlayer (
  cup_wins TEXT AS List<String> NOT NULL
);

```

--------------------------------

### Derive Schema from Migrations

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

If true, the database schema is derived from `.sqm` files as if migrations were applied. If false, schema is defined in `.sq` files. Defaults to false.

```kotlin
deriveSchemaFromMigrations.set(true)

```

```kotlin
deriveSchemaFromMigrations = true

```

--------------------------------

### Define Typesafe Queries

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite

Define labeled SQL statements in a .sq file to generate typesafe query functions.

```sql
selectAll:
SELECT *
FROM hockeyPlayer;

insert:
INSERT INTO hockeyPlayer(player_number, full_name)
VALUES (?, ?);

insertFullPlayerObject:
INSERT INTO hockeyPlayer(player_number, full_name)
VALUES ?;

```

--------------------------------

### SQL Query for Inserting with Data Class

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/query_arguments

Arguments for `INSERT VALUES` statements can be bound using the table's data class.

```sqlite
insertPlayer:
INSERT INTO hockeyPlayer
VALUES ?;
```

--------------------------------

### Configure Automatic SQLite Linking

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Control whether SQLite is automatically linked for native targets. This is useful for dynamic frameworks in KMP. Defaults to true.

```kotlin
linkSqlite.set(true)

```

```kotlin
linkSqlite = true

```

--------------------------------

### Add Primitive Adapters Dependency (Kotlin)

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Include the primitive-adapters module in your Kotlin project to use convenience adapters for Float, Int, and Short.

```kotlin
dependencies {
  implementation("app.cash.sqldelight:primitive-adapters:2.3.2")
}

```

--------------------------------

### Add Primitive Adapters Dependency (Groovy)

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Include the primitive-adapters module in your Groovy project to use convenience adapters for Float, Int, and Short.

```groovy
dependencies {
  implementation "app.cash.sqldelight:primitive-adapters:2.3.2"
}

```

--------------------------------

### Enable Foreign Keys on Native SQLite

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/foreign_keys

Enable foreign key constraints for the Native SQLite driver by setting `foreignKeyConstraints` to `true` in the `extendedConfig` of the database configuration.

```kotlin
NativeSqliteDriver(
  schema = Database.Schema,
  onConfiguration = { config: DatabaseConfiguration ->
    config.copy(
      extendedConfig = DatabaseConfiguration.Extended(foreignKeyConstraints = true)
    )
  }
)

```

--------------------------------

### SQL Query with Variable Arguments

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/query_arguments

Sets of values can be passed as a single argument to a query, typically used with the `IN` clause.

```sqlite
selectByNames:
SELECT *
FROM hockeyPlayer
WHERE full_name IN ?;
```

--------------------------------

### Keyset Paging Core Query

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/androidx_paging

Define the core SQL query for keyset paging, using a unique column (e.g., 'id') to restrict query bounds.

```sql
keyedQuery:
SELECT * FROM hockeyPlayer
WHERE id >= :beginInclusive AND (id < :endExclusive OR :endExclusive IS NULL)
ORDER BY id ASC;

```

--------------------------------

### Enable Foreign Keys on Android SQLite

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/foreign_keys

Enable foreign key constraints for the Android SQLite driver by using the `onOpen` callback to call `setForeignKeyConstraintsEnabled(true)`.

```kotlin
AndroidSqliteDriver(
  schema = Database.Schema,
  callback = object : AndroidSqliteDriver.Callback(Database.Schema) {
    override fun onOpen(db: SupportSQLiteDatabase) {
      db.setForeignKeyConstraintsEnabled(true)
    }
  }
)

```

--------------------------------

### Define SQLite Column Types

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Define columns with standard SQLite types. SQLDelight maps these to corresponding Kotlin types (INTEGER to Long, REAL to Double, TEXT to String, BLOB to ByteArray).

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

```

--------------------------------

### Offset Based Paging Queries

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/androidx_paging

Define SQL queries for offset-based paging, including a count query and a paged query with LIMIT and OFFSET.

```sql
countPlayers:
SELECT count(*) FROM hockeyPlayer;

players:
SELECT *
FROM hockeyPlayer
LIMIT :limit OFFSET :offset;

```

--------------------------------

### Enable Foreign Keys on JVM SQLite

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/foreign_keys

Enable foreign key constraints for the JVM SQLite driver by passing the `foreign_keys` property set to `"true"` in the driver's properties.

```kotlin
JdbcSqliteDriver(
  url = "...", 
  properties = Properties().apply { put("foreign_keys", "true") }
)

```

--------------------------------

### Configure SQLDelight Dialect

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Specify the SQL dialect to target, using a Gradle dependency. For Android, SQLite version is based on `minSdk`. Defaults to SQLite 3.18.

```kotlin
dialect("app.cash.sqldelight:sqlite-3-24-dialect:2.3.2")

```

```kotlin
dialect 'app.cash.sqldelight:sqlite-3-24-dialect:2.3.2'

```

--------------------------------

### Define Enum Column Type with SQLDelight

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Map a TEXT column to an enum type using the `AS` keyword. SQLDelight will use an `EnumColumnAdapter` by default if the enum is defined in the correct package.

```sql
import com.example.hockey.HockeyPlayer;

CREATE TABLE hockeyPlayer (
  position TEXT AS HockeyPlayer.Position
)

```

--------------------------------

### Configure Null Handling for Equality

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

When true, SQLDelight will not replace equality comparisons with nullable typed values using `IS`. Defaults to false.

```kotlin
treatNullAsUnknownForEquality.set(true)

```

```kotlin
treatNullAsUnknownForEquality = true

```

--------------------------------

### Register Callbacks for Transaction Events

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/transactions

Use `afterRollback` and `afterCommit` to register functions that execute after a transaction has been rolled back or successfully committed, respectively.

```kotlin
database.playerQueries.transaction {
  afterRollback { log("No players were inserted.") }
  afterCommit { log("${players.size} players were inserted.") }

  players.forEach { player ->
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}
```

--------------------------------

### Configure Dependent Module Schema in Kotlin DSL

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Configure the dependent module's database with the same name but a different package. This is necessary when specifying schema dependencies between modules.

```kotlin
// project-b/build.gradle.kts

sqldelight {
  databases {
    // Same database name
    create("MyDatabase") {
      package = "com.example.projectb"
    }
  }
}

```

--------------------------------

### Execute Multiple Statements in a Transaction

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/transactions

Use the `transaction` function to group multiple database operations. This ensures atomicity, where all operations succeed or none do.

```kotlin
val players = listOf<Player>()
database.playerQueries.transaction {
  players.forEach { player ->
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}
```

--------------------------------

### SQL Projection for Uppercase Names

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/custom_projections

Leverage SQL to perform custom projections, such as converting names to uppercase directly within the database query. This is generally the preferred approach for performance.

```sql
selectNames:
SELECT upper(full_name)
FROM hockeyPlayer;
```

--------------------------------

### Manually Roll Back and Return a Value

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/transactions

When using `transactionWithResult`, you can specify a return value for the rollback case using `rollback(value)`. This is useful for indicating the outcome of a partial operation.

```kotlin
val numberInserted: Int = database.playerQueries.transactionWithResult {
  players.forEach { player ->
    if (player.number == 0) rollback(0)
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
  players.size
}
```

--------------------------------

### Specify Schema Dependency on Other Projects

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Declare schema dependencies on other Gradle projects. This allows sharing schema definitions across modules.

```kotlin
dependency(project(":other-project"))

```

```kotlin
dependency project(":other-project")

```

--------------------------------

### Configure Dependent Module Schema in Groovy DSL

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Configure the dependent module's database with the same name but a different package. This is necessary when specifying schema dependencies between modules.

```groovy
// project-b/build.gradle

sqldelight {
  databases {
    // Same database name
    MyDatabase {
      package = "com.example.projectb"
    }
  }
}

```

--------------------------------

### Add Coroutines Extensions Dependency (Groovy DSL)

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/coroutines

Include the coroutines-extensions artifact in your commonMain source set to enable Flow support.

```groovy
kotlin {
  sourceSets.commonMain.dependencies {
    implementation "app.cash.sqldelight:coroutines-extensions:2.3.2"
  }
}
```

--------------------------------

### Enable expandSelectStar in Groovy DSL

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Set `expandSelectStar` to true to rewrite `SELECT *` statements to explicitly reference all columns. This is enabled by default.

```groovy
expandSelectStar = true

```

--------------------------------

### Enable expandSelectStar in Kotlin DSL

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Set `expandSelectStar` to true to rewrite `SELECT *` statements to explicitly reference all columns. This is enabled by default.

```kotlin
expandSelectStar.set(true)

```

--------------------------------

### Add Coroutines Extensions Dependency (Kotlin DSL)

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/coroutines

Include the coroutines-extensions artifact in your commonMain source set to enable Flow support.

```kotlin
kotlin {
  sourceSets.commonMain.dependencies {
    implementation("app.cash.sqldelight:coroutines-extensions:2.3.2")
  }
}
```

--------------------------------

### Specify Schema Dependency in Kotlin DSL

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Declare a schema dependency on another module to include its schema during compilation. Ensure the dependent module has a database with the same name but a different package.

```kotlin
// project-a/build.gradle.kts

sqldelight {
  databases {
    create("MyDatabase") {
      packageName.set("com.example.projecta")
      dependency(project(":ProjectB"))
    }
  }
}

```

--------------------------------

### Grouped SQL Statements within a Transaction

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/grouping_statements

Use the `upsert` block to execute multiple SQL statements atomically. This is useful for operations that require consistency, such as updating and inserting related data.

```kotlin
upsert {
  UPDATE myTable
  SET column1 = :column1,
      column2 = :column2
  WHERE id = :id;

  INSERT OR IGNORE INTO myTable (id, column1, column2)
  VALUES (:id, :column1, :column2);
}
```

--------------------------------

### Define Value Type Column

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/types

Generate a Kotlin value type wrapper for a column by specifying `AS VALUE`. This creates a distinct type in Kotlin for the column's data.

```sql
CREATE TABLE hockeyPlayer (
  id INT AS VALUE
);

```

--------------------------------

### Return a Value from a Transaction

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/transactions

Use `transactionWithResult` to execute statements within a transaction and return a computed value. The transaction commits if no exceptions occur.

```kotlin
val players: List<Player> = database.playerQueries.transactionWithResult {
  database.playerQueries.selectAll().executeAsList()
}
```

--------------------------------

### Specify Schema Dependency in Groovy DSL

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/gradle

Declare a schema dependency on another module to include its schema during compilation. Ensure the dependent module has a database with the same name but a different package.

```groovy
// project-a/build.gradle

sqldelight {
  databases {
    MyDatabase {
      packageName = "com.example.projecta"
      dependency project(":ProjectB")
    }
  }
}

```

--------------------------------

### Manually Roll Back a Transaction

Source: https://sqldelight.github.io/sqldelight/latest/multiplatform_sqlite/transactions

Transactions automatically roll back on exceptions. You can also manually trigger a rollback using `rollback()` within the transaction block.

```kotlin
database.playerQueries.transaction {
  players.forEach { player ->
    if (player.number == 0) rollback()
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}
```
Tokens:
Raw