0
# Query System
1

2
The SQLDelight query system provides type-safe SQL query execution with reactive result updates and comprehensive lifecycle management. It enables compile-time verified database operations with runtime change notifications.
3

4
## Capabilities
5

6
### Query Creation and Execution
7

8
Create and execute type-safe queries with automatic result set mapping.
9

10
```kotlin { .api }
11
/**
12
 * Creates a listenable, typed query generated by SQLDelight
13
 * @param identifier Unique identifier for driver-side caching
14
 * @param queryKeys Array of table names this query depends on for change notifications
15
 * @param driver Database driver for execution
16
 * @param query SQL query string
17
 * @param mapper Function to convert cursor rows to typed objects
18
 * @returns Query instance that supports listener registration
19
 */
20
fun <RowType : Any> Query(
21
    identifier: Int,
22
    queryKeys: Array<out String>,
23
    driver: SqlDriver,
24
    query: String,
25
    mapper: (SqlCursor) -> RowType
26
): Query<RowType>
27

28
/**
29
 * Creates a listenable, typed query with file and label information
30
 * @param identifier Unique identifier for driver-side caching
31
 * @param queryKeys Array of table names this query depends on
32
 * @param driver Database driver for execution
33
 * @param fileName Source file name for debugging
34
 * @param label Query label for debugging
35
 * @param query SQL query string
36
 * @param mapper Function to convert cursor rows to typed objects
37
 * @returns Query instance that supports listener registration
38
 */
39
fun <RowType : Any> Query(
40
    identifier: Int,
41
    queryKeys: Array<out String>,
42
    driver: SqlDriver,
43
    fileName: String,
44
    label: String,
45
    query: String,
46
    mapper: (SqlCursor) -> RowType
47
): Query<RowType>
48

49
/**
50
 * Creates an executable query without listener support
51
 * @param identifier Unique identifier for driver-side caching
52
 * @param driver Database driver for execution
53
 * @param query SQL query string
54
 * @param mapper Function to convert cursor rows to typed objects
55
 * @returns ExecutableQuery instance for one-time execution
56
 */
57
fun <RowType : Any> Query(
58
    identifier: Int,
59
    driver: SqlDriver,
60
    query: String,
61
    mapper: (SqlCursor) -> RowType
62
): ExecutableQuery<RowType>
63

64
/**
65
 * Creates an executable query with debugging information but without listener support
66
 * @param identifier Unique identifier for driver-side caching
67
 * @param driver Database driver for execution
68
 * @param fileName Source file name for debugging
69
 * @param label Query label for debugging
70
 * @param query SQL query string
71
 * @param mapper Function to convert cursor rows to typed objects
72
 * @returns ExecutableQuery instance for one-time execution
73
 */
74
fun <RowType : Any> Query(
75
    identifier: Int,
76
    driver: SqlDriver,
77
    fileName: String,
78
    label: String,
79
    query: String,
80
    mapper: (SqlCursor) -> RowType
81
): ExecutableQuery<RowType>
82
```
83

84
**Usage Examples:**
85

86
```kotlin
87
import app.cash.sqldelight.Query
88
import app.cash.sqldelight.db.SqlDriver
89

90
// Create a listenable query
91
val userQuery = Query(
92
    identifier = 1,
93
    queryKeys = arrayOf("users"),
94
    driver = database.driver,
95
    query = "SELECT * FROM users WHERE active = 1",
96
    mapper = { cursor ->
97
        User(
98
            id = cursor.getLong(0)!!,
99
            name = cursor.getString(1)!!,
100
            email = cursor.getString(2)!!
101
        )
102
    }
103
)
104

105
// Execute and get all results
106
val activeUsers: List<User> = userQuery.executeAsList()
107

108
// Execute and get single result
109
val firstUser: User? = userQuery.executeAsOneOrNull()
110
```
111

112
### Query Abstract Classes
113

114
Base classes providing query execution and result handling functionality.
115

116
```kotlin { .api }
117
/**
118
 * A listenable, typed query generated by SQLDelight
119
 * @param RowType the type that this query can map its result set to
120
 * @property mapper The mapper this Query was created with
121
 */
122
abstract class Query<out RowType : Any>(
123
    mapper: (SqlCursor) -> RowType
124
) : ExecutableQuery<RowType>(mapper) {
125
    /**
126
     * Register a listener to be notified of future changes in the result set
127
     */
128
    abstract fun addListener(listener: Listener)
129
    
130
    /**
131
     * Remove a listener to no longer be notified of future changes in the result set
132
     */
133
    abstract fun removeListener(listener: Listener)
134
    
135
    /**
136
     * An interface for listening to changes in the result set of a query
137
     */
138
    fun interface Listener {
139
        /**
140
         * Called whenever the query this listener was attached to is dirtied.
141
         * Calls are made synchronously on the thread where the updated occurred, 
142
         * after the update applied successfully.
143
         */
144
        fun queryResultsChanged()
145
    }
146
}
147

148
/**
149
 * Base class for queries that can be executed
150
 * @param RowType the type that this query can map its result set to
151
 * @property mapper Function to convert cursor rows to typed objects
152
 */
153
abstract class ExecutableQuery<out RowType : Any>(
154
    val mapper: (SqlCursor) -> RowType
155
) {
156
    /**
157
     * Execute the underlying statement. The resulting cursor is passed to the given block.
158
     * The cursor is closed automatically after the block returns.
159
     */
160
    abstract fun <R> execute(mapper: (SqlCursor) -> QueryResult<R>): QueryResult<R>
161
    
162
    /**
163
     * @return The result set of the underlying SQL statement as a list of [RowType]
164
     */
165
    fun executeAsList(): List<RowType>
166
    
167
    /**
168
     * @return The only row of the result set for the underlying SQL statement as a non null [RowType]
169
     * @throws NullPointerException if when executed this query has no rows in its result set
170
     * @throws IllegalStateException if when executed this query has multiple rows in its result set
171
     */
172
    fun executeAsOne(): RowType
173
    
174
    /**
175
     * @return The first row of the result set for the underlying SQL statement as a non null 
176
     *   [RowType] or null if the result set has no rows
177
     * @throws IllegalStateException if when executed this query has multiple rows in its result set
178
     */
179
    fun executeAsOneOrNull(): RowType?
180
}
181
```
182

183
### Query Result Management
184

185
Execute queries and handle results with type safety and error handling.
186

187
**Usage Examples:**
188

189
```kotlin
190
import app.cash.sqldelight.Query
191

192
// Multiple result handling
193
val users: List<User> = userQueries.selectAll().executeAsList()
194
println("Found ${users.size} users")
195

196
// Single result handling
197
try {
198
    val user: User = userQueries.selectById(123).executeAsOne()
199
    println("User: ${user.name}")
200
} catch (e: NullPointerException) {
201
    println("User not found")
202
} catch (e: IllegalStateException) {
203
    println("Multiple users found - data integrity issue")
204
}
205

206
// Optional single result
207
val user: User? = userQueries.selectById(123).executeAsOneOrNull()
208
if (user != null) {
209
    println("User: ${user.name}")
210
} else {
211
    println("User not found")
212
}
213

214
// Custom result processing
215
val userCount: Int = userQueries.selectAll().execute { cursor ->
216
    var count = 0
217
    while (cursor.next().value) {
218
        count++
219
    }
220
    QueryResult.Value(count)
221
}.value
222
```
223

224
### Query Change Listening
225

226
Register for reactive updates when query result sets change.
227

228
```kotlin { .api }
229
/**
230
 * An interface for listening to changes in the result set of a query
231
 */
232
fun interface Query.Listener {
233
    /**
234
     * Called whenever the query this listener was attached to is dirtied.
235
     * Calls are made synchronously on the thread where the updated occurred, 
236
     * after the update applied successfully.
237
     */
238
    fun queryResultsChanged()
239
}
240
```
241

242
**Usage Examples:**
243

244
```kotlin
245
import app.cash.sqldelight.Query
246

247
// Simple listener implementation
248
val userQuery = userQueries.selectAll()
249
val listener = Query.Listener {
250
    println("User data has changed, refreshing UI...")
251
    refreshUserList()
252
}
253

254
// Register listener
255
userQuery.addListener(listener)
256

257
// Make changes that trigger notifications
258
database.transaction {
259
    userQueries.insertUser("New User", "new@example.com")
260
    // Listener will be called after transaction commits
261
}
262

263
// Remove listener when no longer needed
264
userQuery.removeListener(listener)
265

266
// Anonymous listener for one-time setup
267
userQueries.selectByStatus("active").addListener {
268
    updateActiveUserCount()
269
}
270
```
271

272
### Custom Query Execution
273

274
Execute queries with custom result processing and cursor handling.
275

276
**Usage Examples:**
277

278
```kotlin
279
import app.cash.sqldelight.db.QueryResult
280

281
// Custom aggregation
282
val avgAge: Double = userQueries.selectAll().execute { cursor ->
283
    var total = 0.0
284
    var count = 0
285
    while (cursor.next().value) {
286
        total += cursor.getLong(2)?.toDouble() ?: 0.0  // age column
287
        count++
288
    }
289
    QueryResult.Value(if (count > 0) total / count else 0.0)
290
}.value
291

292
// Streaming processing for large result sets
293
userQueries.selectAll().execute { cursor ->
294
    val results = mutableListOf<String>()
295
    while (cursor.next().value && results.size < 10) {
296
        val name = cursor.getString(1) // name column
297
        if (name?.startsWith("A") == true) {
298
            results.add(name)
299
        }
300
    }
301
    QueryResult.Value(results)
302
}.value
303

304
// Error handling during execution
305
try {
306
    val result = complexQuery.execute { cursor ->
307
        // Custom processing that might throw
308
        if (!cursor.next().value) {
309
            throw IllegalStateException("Expected at least one result")
310
        }
311
        QueryResult.Value(processComplexData(cursor))
312
    }.value
313
} catch (e: IllegalStateException) {
314
    // Handle custom execution errors
315
    handleQueryError(e)
316
}
317
```