Print contents of a SqlDelight (https://cashapp.github.io/sqldelight/) database. Formatted using picnic (https://github.com/JakeWharton/picnic)

DatabaseContents.kt

import app.cash.sqldelight.async.coroutines.awaitQuery
import app.cash.sqldelight.db.SqlDriver
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.withContext

/**
 * Describes contents of all tables in a database.
 */
data class DatabaseContents(
  val tables: List<TableContents>,
) {

  /**
   * Describes contents of a table.
   *
   * @property columnNames - list of all column names for the table.
   * @property rowValues - map of row values where key is the name of a column, and the value is a
   * list of values in all rows for that column.
   */
  data class TableContents(
    val tableName: String,
    val columnNames: List<String>,
    val rowValues: Map<String, List<String>>,
  )
}

suspend fun SqlDriver.databaseContents(): DatabaseContents {
  val tables = tableNames().map { tableName ->
    tableContents(tableName)
  }
  return DatabaseContents(tables)
}

private suspend fun SqlDriver.tableNames(): List<String> {
  return withContext(Dispatchers.Default) {
    buildList {
      awaitQuery(
        identifier = null,
        sql = "SELECT name FROM sqlite_master WHERE type='table'",
        mapper = {
          while (it.next()) {
            val tableName = requireNotNull(it.getString(0))
            add(tableName)
          }
        },
        parameters = 0,
        binders = null
      )
    }
  }
}

/**
 * Get [TableContents] with column names and row values from the given table.
 */
suspend fun SqlDriver.tableContents(
  tableName: String,
): TableContents {
  val columnNames = columnNames(tableName)
  val rowValues = buildMap<String, MutableList<String>> {
    columnNames.forEach { columnName ->
      // Get all values for the given column.
      awaitQuery(
        identifier = null,
        sql = "SELECT $columnName FROM $tableName",
        mapper = {
          while (it.next()) {
            val value = it.getString(0)
            val rowValues = get(columnName) ?: mutableListOf()
            rowValues.add(value.toString())
            put(columnName, rowValues)
          }
        },
        parameters = 0,
        binders = null
      )
    }
  }

  return TableContents(
    tableName = tableName,
    columnNames = columnNames,
    rowValues = rowValues
  )
}

/**
 * Get list of column names for the given table.
 */
private suspend fun SqlDriver.columnNames(
  tableName: String,
): List<String> {
  return withContext(Dispatchers.Default) {
    buildList {
      awaitQuery(
        identifier = null,
        sql = "PRAGMA table_info($tableName)",
        mapper = {
          while (it.next()) {
            val columnName = requireNotNull(it.getString(1))
            add(columnName)
          }
        },
        parameters = 0,
        binders = null
      )
    }
  }
}

DatabaseContentsRenderer.kt

import com.jakewharton.picnic.TextAlignment.MiddleCenter
import com.jakewharton.picnic.TextBorder
import com.jakewharton.picnic.renderText
import com.jakewharton.picnic.table

/**
 * Simply render each individual table.
 */
fun DatabaseContents.renderText(): String {
  return buildString {
    tables.forEach { table ->
      append(table.renderText())
      appendLine()
    }
  }
}

/**
 * Render table contents as a nicely formatted table of contents.
 */
fun TableContents.renderText(): String {
  // Naive way to count how many rows there are in the table.
  val rowCount = rowValues.values.firstOrNull()?.size ?: 0

  return table {
    cellStyle {
      border = true
      alignment = MiddleCenter
    }
    header {
      row {
        cell(tableName) {
          columnSpan = columnNames.size
        }
      }
      // Render row with all column names.
      row(*columnNames.toTypedArray())
    }

    // Render each row and its values for each column.
    if (rowCount > 0) {
      repeat(rowCount) { rowIndex ->
        row {
          columnNames.forEach { column ->
            val rowValues = rowValues[column]
            val value = requireNotNull(rowValues)[rowIndex]
            cell(value)
          }
        }
      }
    } else {
      row {
        cell("Empty") {
          columnSpan = columnNames.size
        }
      }
    }
  }.renderText(border = TextBorder.ROUNDED)
}

Sample.kt

// given SQL schema:
//
// CREATE TABLE myTable(
//   id       INTEGER NOT NULL PRIMARY KEY,
//   columnA  TEXT
// );

// print contents of the database associated with the SqlDriver
suspend fun sample(sqlDriver: SqlDriver) {
  println(sqlDriver.databaseContents().renderText()
}

// ┌──────────────┐
// │   myTable    │
// ├──────────────┤
// │  id  │columnA│
// ├──────┼───────┤
// |  1   │  foo  │
// └──────┴───────┘