0
# Database Driver Interface
1

2
Low-level database driver abstraction providing platform-agnostic SQL execution, connection management, and prepared statement handling with support for both synchronous and asynchronous operations.
3

4
## Capabilities
5

6
### SqlDriver Interface
7

8
Core interface for database connections providing SQL execution and transaction management.
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 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 Lambda called with the cursor when the statement is executed successfully. The cursor must not escape the block scope
21
     * @param parameters The number of bindable parameters sql contains
22
     * @param binders Lambda called before execution to bind any parameters to the SQL statement
23
     * @return 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 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 Lambda called before execution to bind any parameters to the SQL statement
39
     * @return The number of rows updated for an 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
     * @return QueryResult containing the new Transaction instance
51
     */
52
    fun newTransaction(): QueryResult<Transacter.Transaction>
53
    
54
    /**
55
     * The currently open Transaction on the database
56
     * @return Current transaction or null if no transaction is active
57
     */
58
    fun currentTransaction(): Transacter.Transaction?
59
    
60
    /**
61
     * Register a listener for changes to specific query keys
62
     * @param queryKeys Table/view names to listen for changes
63
     * @param listener Listener to notify when changes occur
64
     */
65
    fun addListener(vararg queryKeys: String, listener: Query.Listener)
66
    
67
    /**
68
     * Remove a listener for specific query keys
69
     * @param queryKeys Table/view names to stop listening for changes
70
     * @param listener Listener to remove
71
     */
72
    fun removeListener(vararg queryKeys: String, listener: Query.Listener)
73
    
74
    /**
75
     * Notify all listeners registered for the specified query keys
76
     * @param queryKeys Table/view names that have changed
77
     */
78
    fun notifyListeners(vararg queryKeys: String)
79
}
80
```
81

82
### SqlCursor Interface
83

84
Interface representing a SQL result set that can be iterated and provides typed column access.
85

86
```kotlin { .api }
87
/**
88
 * Represents a SQL result set which can be iterated through with next().
89
 * Initially the cursor will not point to any row, and calling next() once will iterate to the first row
90
 */
91
interface SqlCursor {
92
    /**
93
     * Move to the next row in the result set
94
     * @return QueryResult containing true if the cursor successfully moved to a new row, false if there was no row to iterate to
95
     */
96
    fun next(): QueryResult<Boolean>
97
    
98
    /**
99
     * Get the string or null value of column index for the current row of the result set
100
     * @param index Zero-based column index
101
     * @return String value or null
102
     */
103
    fun getString(index: Int): String?
104
    
105
    /**
106
     * Get the long or null value of column index for the current row of the result set
107
     * @param index Zero-based column index
108
     * @return Long value or null
109
     */
110
    fun getLong(index: Int): Long?
111
    
112
    /**
113
     * Get the bytes or null value of column index for the current row of the result set
114
     * @param index Zero-based column index
115
     * @return ByteArray value or null
116
     */
117
    fun getBytes(index: Int): ByteArray?
118
    
119
    /**
120
     * Get the double or null value of column index for the current row of the result set
121
     * @param index Zero-based column index
122
     * @return Double value or null
123
     */
124
    fun getDouble(index: Int): Double?
125
    
126
    /**
127
     * Get the boolean or null value of column index for the current row of the result set
128
     * @param index Zero-based column index
129
     * @return Boolean value or null
130
     */
131
    fun getBoolean(index: Int): Boolean?
132
}
133
```
134

135
### SqlPreparedStatement Interface
136

137
Interface for binding parameters to prepared SQL statements with type-safe binding methods.
138

139
```kotlin { .api }
140
/**
141
 * Represents a SQL statement that has been prepared by a driver to be executed.
142
 * This type is not thread safe unless otherwise specified by the driver.
143
 * Prepared statements should not be cached by client code. Drivers can implement 
144
 * caching by using the integer identifier passed to SqlDriver.execute or SqlDriver.executeQuery
145
 */
146
interface SqlPreparedStatement {
147
    /**
148
     * Bind bytes to the underlying statement at index
149
     * @param index One-based parameter index
150
     * @param bytes ByteArray value or null to bind
151
     */
152
    fun bindBytes(index: Int, bytes: ByteArray?)
153
    
154
    /**
155
     * Bind long to the underlying statement at index
156
     * @param index One-based parameter index
157
     * @param long Long value or null to bind
158
     */
159
    fun bindLong(index: Int, long: Long?)
160
    
161
    /**
162
     * Bind double to the underlying statement at index
163
     * @param index One-based parameter index
164
     * @param double Double value or null to bind
165
     */
166
    fun bindDouble(index: Int, double: Double?)
167
    
168
    /**
169
     * Bind string to the underlying statement at index
170
     * @param index One-based parameter index
171
     * @param string String value or null to bind
172
     */
173
    fun bindString(index: Int, string: String?)
174
    
175
    /**
176
     * Bind boolean to the underlying statement at index
177
     * @param index One-based parameter index
178
     * @param boolean Boolean value or null to bind
179
     */
180
    fun bindBoolean(index: Int, boolean: Boolean?)
181
}
182
```
183

184
### QueryResult System
185

186
Unified result handling supporting both immediate values and suspending operations.
187

188
```kotlin { .api }
189
/**
190
 * The returned value is the result of a database query or other database operation.
191
 * This interface enables drivers to be based on non-blocking APIs where the result 
192
 * can be obtained using the suspending await method
193
 */
194
sealed interface QueryResult<T> {
195
    /**
196
     * Immediate access to the result value. 
197
     * Throws IllegalStateException if the driver is asynchronous and generateAsync is not configured
198
     */
199
    val value: T
200
    
201
    /**
202
     * Suspending access to the result value, works with both sync and async drivers
203
     * @return The result value
204
     */
205
    suspend fun await(): T
206
    
207
    /**
208
     * Immediate result wrapper for synchronous operations
209
     */
210
    @JvmInline
211
    value class Value<T>(override val value: T) : QueryResult<T> {
212
        override suspend fun await() = value
213
    }
214
    
215
    /**
216
     * Asynchronous result wrapper for suspending operations
217
     */
218
    @JvmInline
219
    value class AsyncValue<T>(private val getter: suspend () -> T) : QueryResult<T> {
220
        override suspend fun await() = getter()
221
    }
222
    
223
    companion object {
224
        /**
225
         * A QueryResult representation of a Kotlin Unit for convenience.
226
         * Equivalent to QueryResult.Value(Unit)
227
         */
228
        val Unit = Value(kotlin.Unit)
229
    }
230
}
231
```
232

233
**Usage Examples:**
234

235
```kotlin
236
import app.cash.sqldelight.db.*
237

238
// Implementing a custom SqlDriver
239
class MyCustomDriver : SqlDriver {
240
    private val connection: DatabaseConnection = createConnection()
241
    private val listeners = mutableMapOf<String, MutableList<Query.Listener>>()
242
    private var currentTx: Transacter.Transaction? = null
243
    
244
    override fun <R> executeQuery(
245
        identifier: Int?,
246
        sql: String,
247
        mapper: (SqlCursor) -> QueryResult<R>,
248
        parameters: Int,
249
        binders: (SqlPreparedStatement.() -> Unit)?
250
    ): QueryResult<R> {
251
        val preparedStatement = connection.prepareStatement(sql)
252
        binders?.invoke(preparedStatement)
253
        
254
        val cursor = preparedStatement.executeQuery()
255
        return mapper(cursor)
256
    }
257
    
258
    override fun execute(
259
        identifier: Int?,
260
        sql: String,
261
        parameters: Int,
262
        binders: (SqlPreparedStatement.() -> Unit)?
263
    ): QueryResult<Long> {
264
        val preparedStatement = connection.prepareStatement(sql)
265
        binders?.invoke(preparedStatement)
266
        
267
        val rowsAffected = preparedStatement.executeUpdate()
268
        return QueryResult.Value(rowsAffected.toLong())
269
    }
270
    
271
    override fun newTransaction(): QueryResult<Transacter.Transaction> {
272
        val transaction = MyTransaction(connection, currentTx)
273
        currentTx = transaction
274
        return QueryResult.Value(transaction)
275
    }
276
    
277
    override fun currentTransaction(): Transacter.Transaction? = currentTx
278
    
279
    override fun addListener(vararg queryKeys: String, listener: Query.Listener) {
280
        queryKeys.forEach { key ->
281
            listeners.getOrPut(key) { mutableListOf() }.add(listener)
282
        }
283
    }
284
    
285
    override fun removeListener(vararg queryKeys: String, listener: Query.Listener) {
286
        queryKeys.forEach { key ->
287
            listeners[key]?.remove(listener)
288
        }
289
    }
290
    
291
    override fun notifyListeners(vararg queryKeys: String) {
292
        queryKeys.forEach { key ->
293
            listeners[key]?.forEach { listener ->
294
                listener.queryResultsChanged()
295
            }
296
        }
297
    }
298
    
299
    override fun close() {
300
        connection.close()
301
    }
302
}
303

304
// Using SqlDriver for raw database operations
305
class DatabaseManager(private val driver: SqlDriver) {
306
    
307
    fun createUser(name: String, email: String): Long {
308
        return driver.execute(
309
            identifier = 1,
310
            sql = "INSERT INTO users (name, email) VALUES (?, ?)",
311
            parameters = 2
312
        ) { statement ->
313
            statement.bindString(1, name)
314
            statement.bindString(2, email)
315
        }.value
316
    }
317
    
318
    fun findUsersByStatus(active: Boolean): List<User> {
319
        return driver.executeQuery(
320
            identifier = 2,
321
            sql = "SELECT id, name, email, active FROM users WHERE active = ?",
322
            mapper = { cursor ->
323
                val users = mutableListOf<User>()
324
                while (cursor.next().value) {
325
                    users.add(
326
                        User(
327
                            id = cursor.getLong(0)!!,
328
                            name = cursor.getString(1)!!,
329
                            email = cursor.getString(2)!!,
330
                            active = cursor.getBoolean(3)!!
331
                        )
332
                    )
333
                }
334
                QueryResult.Value(users)
335
            },
336
            parameters = 1
337
        ) { statement ->
338
            statement.bindBoolean(1, active)
339
        }.value
340
    }
341
    
342
    fun getUserCount(): Int {
343
        return driver.executeQuery(
344
            identifier = 3,
345
            sql = "SELECT COUNT(*) FROM users",
346
            mapper = { cursor ->
347
                cursor.next()
348
                val count = cursor.getLong(0)?.toInt() ?: 0
349
                QueryResult.Value(count)
350
            },
351
            parameters = 0
352
        ).value
353
    }
354
    
355
    // Working with transactions at the driver level
356
    fun transferUserData(fromUserId: Long, toUserId: Long) {
357
        val transaction = driver.newTransaction().value
358
        try {
359
            // Perform operations within transaction
360
            val userData = getUserData(fromUserId)
361
            deleteUser(fromUserId)
362
            createUserWithData(toUserId, userData)
363
            
364
            // Commit is handled by transaction lifecycle
365
        } catch (e: Exception) {
366
            // Rollback is handled automatically
367
            throw e
368
        }
369
    }
370
}
371

372
// Async driver example
373
class AsyncDatabaseManager(private val driver: SqlDriver) {
374
    
375
    suspend fun createUserAsync(name: String, email: String): Long {
376
        return driver.execute(
377
            identifier = 1,
378
            sql = "INSERT INTO users (name, email) VALUES (?, ?)",
379
            parameters = 2
380
        ) { statement ->
381
            statement.bindString(1, name)
382
            statement.bindString(2, email)
383
        }.await()
384
    }
385
    
386
    suspend fun findUsersAsync(active: Boolean): List<User> {
387
        return driver.executeQuery(
388
            identifier = 2,
389
            sql = "SELECT id, name, email, active FROM users WHERE active = ?",
390
            mapper = { cursor ->
391
                QueryResult.AsyncValue {
392
                    val users = mutableListOf<User>()
393
                    while (cursor.next().await()) {
394
                        users.add(
395
                            User(
396
                                id = cursor.getLong(0)!!,
397
                                name = cursor.getString(1)!!,
398
                                email = cursor.getString(2)!!,
399
                                active = cursor.getBoolean(3)!!
400
                            )
401
                        )
402
                    }
403
                    users
404
                }
405
            },
406
            parameters = 1
407
        ) { statement ->
408
            statement.bindBoolean(1, active)
409
        }.await()
410
    }
411
}
412
```
413

414
### Platform Considerations
415

416
- **Prepared Statement Caching**: Drivers may implement caching using the identifier parameter
417
- **Thread Safety**: SqlPreparedStatement is not thread-safe unless specified by the driver
418
- **Cursor Lifecycle**: SqlCursor instances must not escape the mapper lambda scope
419
- **Connection Management**: SqlDriver implementations handle connection pooling and lifecycle
420
- **Error Handling**: Drivers should propagate SQL exceptions appropriately for the platform