0
# Logging and Debugging
1

2
Comprehensive logging support for debugging database operations with query and transaction visibility, parameter inspection, and connection lifecycle monitoring.
3

4
## Capabilities
5

6
### LogSqliteDriver Class
7

8
Decorating SqlDriver implementation that logs all database operations for debugging purposes.
9

10
```kotlin { .api }
11
/**
12
 * A SqlDriver decorator that logs all database operations using a provided logger function
13
 * @param sqlDriver The underlying SqlDriver to wrap with logging
14
 * @param logger Function to handle log messages
15
 */
16
class LogSqliteDriver(
17
    private val sqlDriver: SqlDriver,
18
    private val logger: (String) -> Unit
19
) : SqlDriver {
20
    
21
    /**
22
     * Get the currently open Transaction on the database
23
     * @return Current transaction or null if no transaction is active
24
     */
25
    override fun currentTransaction(): Transacter.Transaction?
26
    
27
    /**
28
     * Execute a SQL statement with logging
29
     * @param identifier Opaque identifier for prepared statement caching
30
     * @param sql SQL string to execute
31
     * @param parameters Number of bindable parameters
32
     * @param binders Lambda to bind parameters to the statement
33
     * @return Number of rows affected
34
     */
35
    override fun execute(
36
        identifier: Int?,
37
        sql: String,
38
        parameters: Int,
39
        binders: (SqlPreparedStatement.() -> Unit)?
40
    ): QueryResult<Long>
41
    
42
    /**
43
     * Execute a SQL query with logging
44
     * @param identifier Opaque identifier for prepared statement caching
45
     * @param sql SQL string to execute
46
     * @param mapper Function to process the result cursor
47
     * @param parameters Number of bindable parameters
48
     * @param binders Lambda to bind parameters to the statement
49
     * @return Query result from the mapper function
50
     */
51
    override fun <R> executeQuery(
52
        identifier: Int?,
53
        sql: String,
54
        mapper: (SqlCursor) -> QueryResult<R>,
55
        parameters: Int,
56
        binders: (SqlPreparedStatement.() -> Unit)?
57
    ): QueryResult<R>
58
    
59
    /**
60
     * Start a new Transaction with logging
61
     * @return QueryResult containing the new Transaction with attached log hooks
62
     */
63
    override fun newTransaction(): QueryResult<Transacter.Transaction>
64
    
65
    /**
66
     * Close the connection with logging
67
     */
68
    override fun close()
69
    
70
    /**
71
     * Add a listener with logging
72
     * @param queryKeys Table/view names to listen for changes
73
     * @param listener Listener to notify when changes occur
74
     */
75
    override fun addListener(vararg queryKeys: String, listener: Query.Listener)
76
    
77
    /**
78
     * Remove a listener with logging
79
     * @param queryKeys Table/view names to stop listening for changes
80
     * @param listener Listener to remove
81
     */
82
    override fun removeListener(vararg queryKeys: String, listener: Query.Listener)
83
    
84
    /**
85
     * Notify listeners with logging
86
     * @param queryKeys Table/view names that have changed
87
     */
88
    override fun notifyListeners(vararg queryKeys: String)
89
}
90
```
91

92
### StatementParameterInterceptor Class
93

94
Helper class for intercepting and logging prepared statement parameters.
95

96
```kotlin { .api }
97
/**
98
 * Helper class that intercepts SqlPreparedStatement parameter binding 
99
 * to capture values for logging purposes
100
 */
101
class StatementParameterInterceptor : SqlPreparedStatement {
102
    /**
103
     * Bind bytes parameter and capture for logging
104
     * @param index Parameter index
105
     * @param bytes ByteArray value to bind
106
     */
107
    override fun bindBytes(index: Int, bytes: ByteArray?)
108
    
109
    /**
110
     * Bind double parameter and capture for logging
111
     * @param index Parameter index
112
     * @param double Double value to bind
113
     */
114
    override fun bindDouble(index: Int, double: Double?)
115
    
116
    /**
117
     * Bind long parameter and capture for logging
118
     * @param index Parameter index
119
     * @param long Long value to bind
120
     */
121
    override fun bindLong(index: Int, long: Long?)
122
    
123
    /**
124
     * Bind string parameter and capture for logging
125
     * @param index Parameter index
126
     * @param string String value to bind
127
     */
128
    override fun bindString(index: Int, string: String?)
129
    
130
    /**
131
     * Bind boolean parameter and capture for logging
132
     * @param index Parameter index
133
     * @param boolean Boolean value to bind
134
     */
135
    override fun bindBoolean(index: Int, boolean: Boolean?)
136
    
137
    /**
138
     * Get captured parameters and clear the internal list
139
     * @return List of all captured parameter values
140
     */
141
    fun getAndClearParameters(): List<Any?>
142
}
143
```
144

145
**Usage Examples:**
146

147
```kotlin
148
import app.cash.sqldelight.logs.LogSqliteDriver
149
import app.cash.sqldelight.db.SqlDriver
150

151
// Basic logging setup
152
class DatabaseManager {
153
    private val baseDriver: SqlDriver = createSqliteDriver()
154
    
155
    // Create logging driver with simple console output
156
    private val loggingDriver = LogSqliteDriver(baseDriver) { message ->
157
        println("[DB] $message")
158
    }
159
    
160
    // Use the logging driver like any other SqlDriver
161
    fun setupDatabase() {
162
        val database = MyDatabase(loggingDriver)
163
        
164
        // All operations will be logged
165
        database.userQueries.insertUser("John Doe", "john@example.com")
166
        val users = database.userQueries.selectAllUsers().executeAsList()
167
    }
168
}
169

170
// Structured logging with different log levels
171
class StructuredDatabaseLogger {
172
    private val logger = LoggerFactory.getLogger(StructuredDatabaseLogger::class.java)
173
    
174
    fun createLoggingDriver(baseDriver: SqlDriver): SqlDriver {
175
        return LogSqliteDriver(baseDriver) { message ->
176
            when {
177
                message.startsWith("EXECUTE") -> logger.debug("SQL Execute: {}", message)
178
                message.startsWith("QUERY") -> logger.debug("SQL Query: {}", message)
179
                message.startsWith("TRANSACTION") -> logger.info("SQL Transaction: {}", message)
180
                message.startsWith("CLOSE") -> logger.info("SQL Connection: {}", message)
181
                message.contains("LISTENING") -> logger.trace("SQL Listener: {}", message)
182
                else -> logger.debug("SQL: {}", message)
183
            }
184
        }
185
    }
186
}
187

188
// File-based logging
189
class FileDatabaseLogger {
190
    private val logFile = File("database.log")
191
    private val logWriter = logFile.bufferedWriter()
192
    
193
    fun createLoggingDriver(baseDriver: SqlDriver): SqlDriver {
194
        return LogSqliteDriver(baseDriver) { message ->
195
            synchronized(logWriter) {
196
                val timestamp = LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_DATE_TIME)
197
                logWriter.write("[$timestamp] $message")
198
                logWriter.newLine()
199
                logWriter.flush()
200
            }
201
        }
202
    }
203
    
204
    fun close() {
205
        logWriter.close()
206
    }
207
}
208

209
// Conditional logging based on configuration
210
class ConfigurableLogging {
211
    private val isDebugEnabled = System.getProperty("sqldelight.debug", "false").toBoolean()
212
    private val logQueries = System.getProperty("sqldelight.log.queries", "true").toBoolean()
213
    private val logTransactions = System.getProperty("sqldelight.log.transactions", "true").toBoolean()
214
    
215
    fun createLoggingDriver(baseDriver: SqlDriver): SqlDriver {
216
        return if (isDebugEnabled) {
217
            LogSqliteDriver(baseDriver) { message ->
218
                val shouldLog = when {
219
                    message.startsWith("QUERY") || message.startsWith("EXECUTE") -> logQueries
220
                    message.startsWith("TRANSACTION") -> logTransactions
221
                    else -> true
222
                }
223
                
224
                if (shouldLog) {
225
                    System.err.println("[SQL] $message")
226
                }
227
            }
228
        } else {
229
            baseDriver
230
        }
231
    }
232
}
233

234
// Performance monitoring with logging
235
class PerformanceLoggingDriver(
236
    baseDriver: SqlDriver
237
) : SqlDriver by LogSqliteDriver(baseDriver, ::logWithTiming) {
238
    
239
    companion object {
240
        private fun logWithTiming(message: String) {
241
            val timestamp = System.currentTimeMillis()
242
            val threadName = Thread.currentThread().name
243
            println("[$timestamp][$threadName] $message")
244
        }
245
    }
246
}
247

248
// Custom logging with metrics collection
249
class MetricsCollectingLogger {
250
    private val queryCount = AtomicLong(0)
251
    private val transactionCount = AtomicLong(0)
252
    private val errorCount = AtomicLong(0)
253
    
254
    fun createLoggingDriver(baseDriver: SqlDriver): SqlDriver {
255
        return LogSqliteDriver(baseDriver) { message ->
256
            // Collect metrics
257
            when {
258
                message.startsWith("QUERY") || message.startsWith("EXECUTE") -> 
259
                    queryCount.incrementAndGet()
260
                message.startsWith("TRANSACTION BEGIN") -> 
261
                    transactionCount.incrementAndGet()
262
                message.contains("ERROR") || message.contains("ROLLBACK") -> 
263
                    errorCount.incrementAndGet()
264
            }
265
            
266
            // Log the message
267
            println("[METRICS] Queries: ${queryCount.get()}, Transactions: ${transactionCount.get()}, Errors: ${errorCount.get()}")
268
            println("[SQL] $message")
269
        }
270
    }
271
    
272
    fun getMetrics(): DatabaseMetrics {
273
        return DatabaseMetrics(
274
            totalQueries = queryCount.get(),
275
            totalTransactions = transactionCount.get(),
276
            totalErrors = errorCount.get()
277
        )
278
    }
279
}
280

281
// Log filtering and formatting
282
class FilteredLoggingDriver {
283
    private val sensitivePatterns = listOf(
284
        Regex("password\\s*=\\s*'[^']*'", RegexOption.IGNORE_CASE),
285
        Regex("token\\s*=\\s*'[^']*'", RegexOption.IGNORE_CASE)
286
    )
287
    
288
    fun createLoggingDriver(baseDriver: SqlDriver): SqlDriver {
289
        return LogSqliteDriver(baseDriver) { message ->
290
            val sanitized = sanitizeMessage(message)
291
            val formatted = formatMessage(sanitized)
292
            println(formatted)
293
        }
294
    }
295
    
296
    private fun sanitizeMessage(message: String): String {
297
        var sanitized = message
298
        sensitivePatterns.forEach { pattern ->
299
            sanitized = pattern.replace(sanitized, "[REDACTED]")
300
        }
301
        return sanitized
302
    }
303
    
304
    private fun formatMessage(message: String): String {
305
        val timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("HH:mm:ss.SSS"))
306
        return "[$timestamp] $message"
307
    }
308
}
309

310
// Testing with captured logs
311
class TestLoggingDriver {
312
    private val capturedLogs = mutableListOf<String>()
313
    
314
    fun createLoggingDriver(baseDriver: SqlDriver): SqlDriver {
315
        return LogSqliteDriver(baseDriver) { message ->
316
            synchronized(capturedLogs) {
317
                capturedLogs.add(message)
318
            }
319
        }
320
    }
321
    
322
    fun getCapturedLogs(): List<String> {
323
        return synchronized(capturedLogs) {
324
            capturedLogs.toList()
325
        }
326
    }
327
    
328
    fun clearLogs() {
329
        synchronized(capturedLogs) {
330
            capturedLogs.clear()
331
        }
332
    }
333
    
334
    fun assertQueryLogged(expectedSql: String) {
335
        val found = capturedLogs.any { it.contains(expectedSql) }
336
        assert(found) { "Expected SQL not found in logs: $expectedSql" }
337
    }
338
}
339

340
// Usage in test
341
@Test
342
fun testUserInsertion() {
343
    val testLogger = TestLoggingDriver()
344
    val database = MyDatabase(testLogger.createLoggingDriver(testDriver))
345
    
346
    database.userQueries.insertUser("Alice", "alice@example.com")
347
    
348
    testLogger.assertQueryLogged("INSERT INTO users")
349
    val logs = testLogger.getCapturedLogs()
350
    assert(logs.any { it.contains("alice@example.com") })
351
}
352
```
353

354
### Log Message Formats
355

356
The LogSqliteDriver produces structured log messages in the following formats:
357

358
- **Query Execution**: `QUERY\n [SQL statement]` followed by parameter list if present
359
- **Statement Execution**: `EXECUTE\n [SQL statement]` followed by parameter list if present  
360
- **Transaction Events**: `TRANSACTION BEGIN`, `TRANSACTION COMMIT`, `TRANSACTION ROLLBACK`
361
- **Connection Events**: `CLOSE CONNECTION`
362
- **Listener Events**: `BEGIN [listener] LISTENING TO [table1, table2]`, `END [listener] LISTENING TO [table1, table2]`, `NOTIFYING LISTENERS OF [table1, table2]`
363
- **Parameters**: ` [param1, param2, param3]` when parameters are bound to statements
364

365
### Performance Considerations
366

367
- **Logging Overhead**: Each database operation incurs additional logging overhead
368
- **String Formatting**: Parameter serialization and message formatting add CPU cost
369
- **I/O Operations**: File or network logging can impact database performance
370
- **Memory Usage**: Parameter capture temporarily holds values in memory
371
- **Thread Safety**: Logger functions should be thread-safe for concurrent database access
372

373
### Security Considerations
374

375
- **Sensitive Data**: Be careful not to log sensitive information like passwords or tokens
376
- **Parameter Sanitization**: Consider sanitizing or redacting sensitive parameters
377
- **Log Storage**: Ensure log files are properly secured and access-controlled
378
- **Data Retention**: Implement appropriate log rotation and retention policies