Database & Storage in Compose Desktop: Exposed ORM & SQLite | Kotlin Desktop #9

Database & Storage in Compose Desktop: Exposed ORM & SQLite

In this example, we build a simple Contact Manager desktop app using Kotlin Compose with local persistence powered by SQLite and the Exposed ORM. The app demonstrates defining a database schema, performing CRUD operations within transactions, and implementing search functionality using SQL LIKE queries, all wrapped in a clean Material Design layout with dialogs for data entry.

Code

import androidx.compose.material.*
import androidx.compose.runtime.*
import androidx.compose.ui.window.singleWindowApplication
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.SqlExpressionBuilder.like

// Define the Contacts table schema with Exposed IntIdTable
object Contacts : IntIdTable() {
  val name = varchar("name", 50)
  val phone = varchar("phone", 20)
}

// Initialize SQLite database connection and create table
fun initDatabase() {
  Database.connect("jdbc:sqlite:contacts.db", driver = "org.sqlite.JDBC")
  transaction {
    SchemaUtils.create(Contacts)
  }
}

// Repository with CRUD operations wrapped in transactions
object ContactRepository {
  fun allContacts(): List = transaction {
    Contacts.selectAll().toList()
  }

  fun searchContacts(query: String): List = transaction {
    Contacts.select { Contacts.name.lowerCase() like "%${query.lowercase()}%" }.toList()
  }

  fun addContact(name: String, phone: String) = transaction {
    Contacts.insert {
      it[Contacts.name] = name
      it[Contacts.phone] = phone
    }
  }

  fun updateContact(id: Int, name: String, phone: String) = transaction {
    Contacts.update({ Contacts.id eq id }) {
      it[Contacts.name] = name
      it[Contacts.phone] = phone
    }
  }

  fun deleteContact(id: Int) = transaction {
    Contacts.deleteWhere { Contacts.id eq id }
  }
}

@Composable
fun ContactManagerApp() {
  var contacts by remember { mutableStateOf(ContactRepository.allContacts()) }
  var searchQuery by remember { mutableStateOf("") }
  var dialogOpen by remember { mutableStateOf(false) }
  var editingContact by remember { mutableStateOf(null) }

  fun refresh() {
    contacts = if (searchQuery.isBlank()) ContactRepository.allContacts()
    else ContactRepository.searchContacts(searchQuery)
  }

  Scaffold(
    floatingActionButton = {
      FloatingActionButton(onClick = {
        editingContact = null
        dialogOpen = true
      }) {
        Text("+")
      }
    }
  ) {
    Column {
      OutlinedTextField(
        value = searchQuery,
        onValueChange = {
          searchQuery = it
          refresh()
        },
        label = { Text("Search") }
      )
      LazyColumn {
        items(contacts) { contact ->
          val id = contact[Contacts.id].value
          val name = contact[Contacts.name]
          val phone = contact[Contacts.phone]
          ListItem(
            text = { Text(name) },
            secondaryText = { Text(phone) },
            trailing = {
              Row {
                IconButton(onClick = {
                  editingContact = contact
                  dialogOpen = true
                }) {
                  Text("Edit")
                }
                IconButton(onClick = {
                  ContactRepository.deleteContact(id)
                  refresh()
                }) {
                  Text("Delete")
                }
              }
            }
          )
        }
      }
    }
  }

  if (dialogOpen) {
    var name by remember { mutableStateOf(editingContact?.get(Contacts.name) ?: "") }
    var phone by remember { mutableStateOf(editingContact?.get(Contacts.phone) ?: "") }

    AlertDialog(
      onDismissRequest = { dialogOpen = false },
      title = { Text(if (editingContact == null) "Add Contact" else "Edit Contact") },
      text = {
        Column {
          OutlinedTextField(value = name, onValueChange = { name = it }, label = { Text("Name") })
          OutlinedTextField(value = phone, onValueChange = { phone = it }, label = { Text("Phone") })
        }
      },
      confirmButton = {
        Button(onClick = {
          if (editingContact == null) {
            ContactRepository.addContact(name, phone)
          } else {
            ContactRepository.updateContact(editingContact!!.get(Contacts.id).value, name, phone)
          }
          dialogOpen = false
          refresh()
        }) {
          Text("Save")
        }
      },
      dismissButton = {
        Button(onClick = { dialogOpen = false }) {
          Text("Cancel")
        }
      }
    )
  }
}

fun main() {
  initDatabase()
  singleWindowApplication(title = "Contact Manager") {
    ContactManagerApp()
  }
}

Key Points

• Exposed's IntIdTable lets you define typed database schemas in Kotlin with ease.
• Database.connect() with the SQLite JDBC driver establishes a local database connection.
• All database operations (CRUD) are safely wrapped inside transaction blocks for consistency.
• SQL LIKE queries enable simple search functionality directly in the database layer.
• Compose Desktop's Scaffold, FloatingActionButton, LazyColumn, and AlertDialog provide a smooth Material UI experience.