0
# Database Driver Interface
1

2
The SQLDelight database driver interface provides a platform-agnostic abstraction for database connectivity, SQL execution, and result handling. It supports both synchronous and asynchronous operation modes with comprehensive resource management.
3

4
## Capabilities
5

6
### Core Database Driver Interface
7

8
Main interface for database connectivity and SQL statement execution.
9

10
```kotlin { .api }
11
/**
12
 * Maintains connections to an underlying SQL database and provides APIs for managing 
13
 * transactions and executing SQL statements
14
 */
15
interface SqlDriver : Closeable {
16
    /**
17
     * Execute a SQL statement and evaluate its result set using the given block
18
     * @param identifier An opaque, unique value for driver-side caching of prepared statements. If null, a fresh statement is required
19
     * @param sql The SQL string to be executed
20
     * @param mapper A lambda called with the cursor when the statement is executed successfully
21
     * @param parameters The number of bindable parameters sql contains
22
     * @param binders A lambda called before execution to bind any parameters to the SQL statement
23
     * @returns The generic result of the mapper lambda
24
     */
25
    fun <R> executeQuery(
26
        identifier: Int?,
27
        sql: String,
28
        mapper: (SqlCursor) -> QueryResult<R>,
29
        parameters: Int,
30
        binders: (SqlPreparedStatement.() -> Unit)? = null
31
    ): QueryResult<R>
32
    
33
    /**
34
     * Execute a SQL statement
35
     * @param identifier An opaque, unique value for driver-side caching of prepared statements. If null, a fresh statement is required
36
     * @param sql The SQL string to be executed
37
     * @param parameters The number of bindable parameters sql contains
38
     * @param binders A lambda called before execution to bind any parameters to the SQL statement
39
     * @returns The number of rows updated for INSERT/DELETE/UPDATE, or 0 for other SQL statements
40
     */
41
    fun execute(
42
        identifier: Int?,
43
        sql: String,
44
        parameters: Int,
45
        binders: (SqlPreparedStatement.() -> Unit)? = null
46
    ): QueryResult<Long>
47
    
48
    /**
49
     * Start a new Transaction on the database
50
     */
51
    fun newTransaction(): QueryResult<Transacter.Transaction>
52
    
53
    /**
54
     * The currently open Transaction on the database
55
     */
56
    fun currentTransaction(): Transacter.Transaction?
57
    
58
    /**
59
     * Register a listener for query result changes
60
     */
61
    fun addListener(vararg queryKeys: String, listener: Query.Listener)
62
    
63
    /**
64
     * Remove a listener for query result changes
65
     */
66
    fun removeListener(vararg queryKeys: String, listener: Query.Listener)
67
    
68
    /**
69
     * Notify listeners that query results have changed
70
     */
71
    fun notifyListeners(vararg queryKeys: String)
72
}
73
```
74

75
**Usage Examples:**
76

77
```kotlin
78
import app.cash.sqldelight.db.SqlDriver
79
import app.cash.sqldelight.db.QueryResult
80

81
// Execute a query with result mapping
82
val users: List<User> = driver.executeQuery(
83
    identifier = 1,
84
    sql = "SELECT id, name, email FROM users WHERE active = ?",
85
    mapper = { cursor ->
86
        val results = mutableListOf<User>()
87
        while (cursor.next().value) {
88
            results.add(User(
89
                id = cursor.getLong(0)!!,
90
                name = cursor.getString(1)!!,
91
                email = cursor.getString(2)!!
92
            ))
93
        }
94
        QueryResult.Value(results)
95
    },
96
    parameters = 1
97
) { bindBoolean(1, true) }.value
98

99
// Execute an update statement
100
val rowsAffected: Long = driver.execute(
101
    identifier = 2,
102
    sql = "UPDATE users SET last_login = ? WHERE id = ?",
103
    parameters = 2
104
) {
105
    bindLong(1, System.currentTimeMillis())
106
    bindLong(2, userId)
107
}.value
108

109
// Transaction management
110
val transaction = driver.newTransaction().value
111
try {
112
    // Perform operations within transaction
113
    driver.execute(null, "INSERT INTO users (name) VALUES (?)", 1) {
114
        bindString(1, "New User")
115
    }
116
    transaction.endTransaction(successful = true)
117
} catch (e: Exception) {
118
    transaction.endTransaction(successful = false)
119
    throw e
120
}
121
```
122

123
### SQL Cursor Interface
124

125
Interface for navigating and reading SQL result sets.
126

127
```kotlin { .api }
128
/**
129
 * Represents a SQL result set which can be iterated through with next(). 
130
 * Initially the cursor will not point to any row, and calling next() once will iterate to the first row.
131
 */
132
interface SqlCursor {
133
    /**
134
     * Move to the next row in the result set
135
     * @returns true if the cursor successfully moved to a new row, false if there was no row to iterate to
136
     */
137
    fun next(): QueryResult<Boolean>
138
    
139
    /**
140
     * @param index Column index (0-based)
141
     * @returns The string or null value of column index for the current row of the result set
142
     */
143
    fun getString(index: Int): String?
144
    
145
    /**
146
     * @param index Column index (0-based)
147
     * @returns The long or null value of column index for the current row of the result set
148
     */
149
    fun getLong(index: Int): Long?
150
    
151
    /**
152
     * @param index Column index (0-based)
153
     * @returns The bytes or null value of column index for the current row of the result set
154
     */
155
    fun getBytes(index: Int): ByteArray?
156
    
157
    /**
158
     * @param index Column index (0-based)
159
     * @returns The double or null value of column index for the current row of the result set
160
     */
161
    fun getDouble(index: Int): Double?
162
    
163
    /**
164
     * @param index Column index (0-based)
165
     * @returns The boolean or null value of column index for the current row of the result set
166
     */
167
    fun getBoolean(index: Int): Boolean?
168
}
169
```
170

171
**Usage Examples:**
172

173
```kotlin
174
import app.cash.sqldelight.db.SqlCursor
175
import app.cash.sqldelight.db.QueryResult
176

177
// Manual cursor navigation
178
val result = driver.executeQuery(
179
    identifier = null,
180
    sql = "SELECT id, name, age, active FROM users",
181
    mapper = { cursor ->
182
        val users = mutableListOf<User>()
183
        while (cursor.next().value) {
184
            val user = User(
185
                id = cursor.getLong(0) ?: 0L,
186
                name = cursor.getString(1) ?: "",
187
                age = cursor.getLong(2)?.toInt() ?: 0,
188
                active = cursor.getBoolean(3) ?: false
189
            )
190
            users.add(user)
191
        }
192
        QueryResult.Value(users)
193
    },
194
    parameters = 0
195
).value
196

197
// Single row processing
198
val user = driver.executeQuery(
199
    identifier = null,
200
    sql = "SELECT * FROM users WHERE id = ?",
201
    mapper = { cursor ->
202
        if (cursor.next().value) {
203
            QueryResult.Value(User(
204
                id = cursor.getLong(0)!!,
205
                name = cursor.getString(1)!!,
206
                email = cursor.getString(2)!!
207
            ))
208
        } else {
209
            QueryResult.Value(null)
210
        }
211
    },
212
    parameters = 1
213
) { bindLong(1, userId) }.value
214

215
// Aggregation using cursor
216
val statistics = driver.executeQuery(
217
    identifier = null,
218
    sql = "SELECT COUNT(*), AVG(age), MAX(age) FROM users",
219
    mapper = { cursor ->
220
        if (cursor.next().value) {
221
            QueryResult.Value(UserStatistics(
222
                count = cursor.getLong(0) ?: 0,
223
                averageAge = cursor.getDouble(1) ?: 0.0,
224
                maxAge = cursor.getLong(2) ?: 0
225
            ))
226
        } else {
227
            QueryResult.Value(UserStatistics(0, 0.0, 0))
228
        }
229
    },
230
    parameters = 0
231
).value
232
```
233

234
### Prepared Statement Interface
235

236
Interface for binding parameters to prepared SQL statements.
237

238
```kotlin { .api }
239
/**
240
 * Represents a SQL statement that has been prepared by a driver to be executed.
241
 * This type is not thread safe unless otherwise specified by the driver emitting these.
242
 * Prepared statements should not be cached by client code. Drivers can implement caching 
243
 * by using the integer identifier passed to SqlDriver.execute or SqlDriver.executeQuery.
244
 */
245
interface SqlPreparedStatement {
246
    /**
247
     * Bind bytes to the underlying statement at index
248
     */
249
    fun bindBytes(index: Int, bytes: ByteArray?)
250
    
251
    /**
252
     * Bind long to the underlying statement at index
253
     */
254
    fun bindLong(index: Int, long: Long?)
255
    
256
    /**
257
     * Bind double to the underlying statement at index
258
     */
259
    fun bindDouble(index: Int, double: Double?)
260
    
261
    /**
262
     * Bind string to the underlying statement at index
263
     */
264
    fun bindString(index: Int, string: String?)
265
    
266
    /**
267
     * Bind boolean to the underlying statement at index
268
     */
269
    fun bindBoolean(index: Int, boolean: Boolean?)
270
}
271
```
272

273
**Usage Examples:**
274

275
```kotlin
276
import app.cash.sqldelight.db.SqlPreparedStatement
277

278
// Complex parameter binding
279
val rowsUpdated = driver.execute(
280
    identifier = 10,
281
    sql = """
282
        UPDATE users 
283
        SET name = ?, email = ?, age = ?, active = ?, profile_data = ?, updated_at = ?
284
        WHERE id = ?
285
    """.trimIndent(),
286
    parameters = 7
287
) {
288
    bindString(1, user.name)
289
    bindString(2, user.email) 
290
    bindLong(3, user.age.toLong())
291
    bindBoolean(4, user.active)
292
    bindBytes(5, user.profileData)
293
    bindLong(6, System.currentTimeMillis())
294
    bindLong(7, user.id)
295
}.value
296

297
// Handling null values
298
driver.execute(
299
    identifier = 11,
300
    sql = "INSERT INTO profiles (user_id, bio, avatar_url) VALUES (?, ?, ?)",
301
    parameters = 3
302
) {
303
    bindLong(1, userId)
304
    bindString(2, profile.bio)  // Can be null
305
    bindString(3, profile.avatarUrl)  // Can be null
306
}
307

308
// Batch operations with different parameters
309
val userIds = listOf(1L, 2L, 3L)
310
userIds.forEach { userId ->
311
    driver.execute(
312
        identifier = 12,
313
        sql = "UPDATE users SET last_seen = ? WHERE id = ?",
314
        parameters = 2
315
    ) {
316
        bindLong(1, System.currentTimeMillis())
317
        bindLong(2, userId)
318
    }
319
}
320
```
321

322
### Query Result Types
323

324
Sealed interface supporting both synchronous and asynchronous database operations.
325

326
```kotlin { .api }
327
/**
328
 * The returned value is the result of a database query or other database operation.
329
 * This interface enables drivers to be based on non-blocking APIs where the result 
330
 * can be obtained using the suspending await method.
331
 */
332
sealed interface QueryResult<T> {
333
    /**
334
     * Get the result value immediately. Throws IllegalStateException for async drivers.
335
     */
336
    val value: T
337
    
338
    /**
339
     * Get the result value using suspension (works for both sync and async drivers)
340
     */
341
    suspend fun await(): T
342
    
343
    /**
344
     * Immediate/synchronous result
345
     */
346
    value class Value<T>(override val value: T) : QueryResult<T> {
347
        override suspend fun await() = value
348
    }
349
    
350
    /**
351
     * Asynchronous result requiring await()
352
     * Note: Accessing the .value property throws IllegalStateException for async results
353
     */
354
    value class AsyncValue<T>(private val getter: suspend () -> T) : QueryResult<T> {
355
        override val value: T get() = throw IllegalStateException("Cannot get async value synchronously, use await() instead")
356
        override suspend fun await() = getter()
357
    }
358
    
359
    companion object {
360
        /**
361
         * A QueryResult representation of a Kotlin Unit for convenience
362
         * Equivalent to QueryResult.Value(Unit)
363
         */
364
        val Unit = Value(kotlin.Unit)
365
    }
366
}
367
```
368

369
**Usage Examples:**
370

371
```kotlin
372
import app.cash.sqldelight.db.QueryResult
373
import kotlinx.coroutines.runBlocking
374

375
// Working with synchronous results
376
val syncResult: QueryResult<List<User>> = syncDriver.executeQuery(...)
377
val users: List<User> = syncResult.value  // Immediate access
378

379
// Working with asynchronous results
380
val asyncResult: QueryResult<List<User>> = asyncDriver.executeQuery(...)
381
val users: List<User> = runBlocking { asyncResult.await() }  // Suspended access
382

383
// Generic handling that works with both
384
suspend fun handleResult(result: QueryResult<List<User>>): List<User> {
385
    return result.await()  // Works for both sync and async
386
}
387

388
// Creating custom results
389
fun createSuccessResult(): QueryResult<String> {
390
    return QueryResult.Value("Operation completed")
391
}
392

393
fun createAsyncResult(): QueryResult<String> {
394
    return QueryResult.AsyncValue {
395
        delay(100)
396
        "Async operation completed"
397
    }
398
}
399

400
// Using the Unit convenience constant
401
fun executeVoidOperation(): QueryResult<Unit> {
402
    driver.execute(...)
403
    return QueryResult.Unit
404
}
405
```
406

407
### Query Listener Management
408

409
Register and manage listeners for reactive query result updates.
410

411
**Usage Examples:**
412

413
```kotlin
414
import app.cash.sqldelight.Query
415

416
// Register listeners for table changes
417
val userListener = Query.Listener { 
418
    println("User data changed")
419
    refreshUserUI()
420
}
421

422
val profileListener = Query.Listener {
423
    println("Profile data changed") 
424
    refreshProfileUI()
425
}
426

427
// Register for specific tables
428
driver.addListener("users", listener = userListener)
429
driver.addListener("profiles", "user_settings", listener = profileListener)
430

431
// Make changes that trigger notifications
432
driver.execute(
433
    identifier = null,
434
    sql = "INSERT INTO users (name) VALUES (?)",
435
    parameters = 1
436
) { bindString(1, "New User") }
437

438
// This will trigger userListener
439
driver.notifyListeners("users")
440

441
// Remove listeners when no longer needed
442
driver.removeListener("users", listener = userListener)
443
driver.removeListener("profiles", "user_settings", listener = profileListener)
444

445
// Listener for multiple tables
446
val multiTableListener = Query.Listener {
447
    refreshEntireUI()
448
}
449

450
driver.addListener("users", "profiles", "settings", listener = multiTableListener)
451
```
452

453
### Exception Handling
454

455
SQLDelight runtime exceptions for error handling and transaction management.
456

457
```kotlin { .api }
458
/**
459
 * Exception thrown when an optimistic lock fails during database operations
460
 * Typically used in scenarios where concurrent modifications are detected
461
 */
462
class OptimisticLockException(
463
    message: String?, 
464
    cause: Throwable? = null
465
) : IllegalStateException(message, cause)
466
```
467

468
**Usage Examples:**
469

470
```kotlin
471
import app.cash.sqldelight.db.OptimisticLockException
472

473
// Handle optimistic locking in concurrent scenarios
474
try {
475
    database.transaction {
476
        val user = userQueries.selectById(userId).executeAsOne()
477
        val updatedUser = user.copy(
478
            version = user.version + 1,
479
            lastModified = System.currentTimeMillis()
480
        )
481
        
482
        val rowsAffected = userQueries.updateWithVersion(
483
            id = userId,
484
            version = user.version, // Check old version
485
            newVersion = updatedUser.version,
486
            lastModified = updatedUser.lastModified
487
        ).executeAsOne()
488
        
489
        if (rowsAffected == 0L) {
490
            throw OptimisticLockException("User was modified by another transaction")
491
        }
492
    }
493
} catch (e: OptimisticLockException) {
494
    // Handle concurrent modification
495
    println("Update failed due to concurrent modification: ${e.message}")
496
    // Retry logic or user notification
497
}
498
```
499

500
### Resource Management
501

502
Proper cleanup and resource management using the Closeable pattern.
503

504
```kotlin { .api }
505
/**
506
 * A type that can be closed
507
 * Platform-specific implementations:
508
 * - JVM: actual typealias Closeable = java.io.Closeable
509
 * - Native/JS: Platform-specific implementations
510
 */
511
expect interface Closeable {
512
    /**
513
     * Close any resources backed by this object
514
     */
515
    fun close()
516
}
517

518
/**
519
 * Run body on the receiver and call Closeable.close before returning or throwing
520
 * Ensures proper resource cleanup using try-with-resources pattern
521
 */
522
expect inline fun <T : Closeable?, R> T.use(body: (T) -> R): R
523
```
524

525
**Usage Examples:**
526

527
```kotlin
528
import app.cash.sqldelight.db.Closeable
529

530
// Automatic resource cleanup
531
driver.use { database ->
532
    val users = database.executeQuery(
533
        identifier = null,
534
        sql = "SELECT * FROM users",
535
        mapper = { cursor -> /* ... */ },
536
        parameters = 0
537
    ).value
538
    
539
    processUsers(users)
540
    // driver.close() is called automatically even if an exception occurs
541
}
542

543
// Manual resource management
544
val driver = createSqlDriver()
545
try {
546
    val result = driver.executeQuery(...)
547
    processResult(result)
548
} finally {
549
    driver.close()
550
}
551

552
// Connection pooling scenario
553
class DatabaseManager : Closeable {
554
    private val drivers = mutableListOf<SqlDriver>()
555
    
556
    fun getDriver(): SqlDriver {
557
        return drivers.firstOrNull() ?: createNewDriver().also { drivers.add(it) }
558
    }
559
    
560
    override fun close() {
561
        drivers.forEach { it.close() }
562
        drivers.clear()
563
    }
564
}
565

566
// Using with connection pooling
567
DatabaseManager().use { manager ->
568
    val driver = manager.getDriver()
569
    val users = driver.executeQuery(...)
570
    // All drivers are closed when manager is closed
571
}
572
```