Database and Storage in Compose Desktop: Exposed ORM and SQLite

SQLite for storage. Exposed for typed Kotlin queries. A complete contacts manager with search, CRUD, and confirmation dialogs.

Today's app stores data on disk in a real database. JetBrains' Exposed library lets you write Kotlin code against an SQLite file with type-safe queries — no string SQL, no manual serialisation. The same patterns work for PostgreSQL and MySQL when you're ready to scale.

What we are building

A contacts manager:

• Search bar with a clear button.
• List of contact cards — name, phone, email, edit and delete icons.
• Floating Add button opens a "new contact" dialog.
• Edit dialog pre-fills with the existing contact.
• Delete confirmation uses Material 3's AlertDialog.

Persisted to contacts.db in the working directory. Survives restarts.

build.gradle.kts dependencies

dependencies {
  implementation(compose.desktop.currentOs)
  implementation(compose.material3)
  implementation(compose.materialIconsExtended)
  implementation("org.jetbrains.exposed:exposed-core:0.52.0")
  implementation("org.jetbrains.exposed:exposed-dao:0.52.0")
  implementation("org.jetbrains.exposed:exposed-jdbc:0.52.0")
  implementation("org.xerial:sqlite-jdbc:3.46.0.0")
}

Three Exposed artefacts plus the SQLite JDBC driver.

Project layout

src/main/kotlin/
├── Main.kt                  # window + Database.connect + schema create
├── AppContent.kt            # state + Scaffold + dialogs
├── ContactRepository.kt     # Contacts table + getAll/search/add/update/delete
└── ...                      # ContactCard, ContactDialog, etc. (in AppContent.kt for brevity)

Defining the schema

Exposed represents tables as Kotlin objects:

import org.jetbrains.exposed.dao.id.IntIdTable

object Contacts : IntIdTable() {
  val name = varchar("name", 100)
  val phone = varchar("phone", 50).default("")
  val email = varchar("email", 100).default("")
}

IntIdTable gives you an auto-increment integer primary key called id. Three columns — name, phone, email — typed as Kotlin Column<String>. Defaults handle empty strings.

Connecting and creating the schema

import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.transactions.transaction

fun main() = application {
  Database.connect("jdbc:sqlite:contacts.db", driver = "org.sqlite.JDBC")
  transaction {
    SchemaUtils.create(Contacts)
  }

  Window(onCloseRequest = ::exitApplication, title = "Contacts") {
    MaterialTheme(colorScheme = darkColorScheme()) { AppContent() }
  }
}

Database.connect registers the connection; subsequent transaction { ... } calls use it. SchemaUtils.create(Contacts) issues the CREATE TABLE IF NOT EXISTS for the table — runs once at startup.

transaction { ... } is Exposed's connection-and-transaction lambda. Anything inside it has database access. Outside the lambda, you can't query (Exposed's DSL relies on a thread-local connection).

ContactRepository.kt: the CRUD layer

data class Contact(
  val id: Int,
  val name: String,
  val phone: String,
  val email: String,
)

fun getAllContacts(): List<Contact> = transaction {
  Contacts.selectAll().orderBy(Contacts.name).map {
    Contact(it[Contacts.id].value, it[Contacts.name], it[Contacts.phone], it[Contacts.email])
  }
}

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

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

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

fun searchContacts(query: String): List<Contact> = transaction {
  Contacts.selectAll()
    .where { Contacts.name.lowerCase() like "%${query.lowercase()}%" }
    .orderBy(Contacts.name)
    .map { Contact(it[Contacts.id].value, it[Contacts.name], it[Contacts.phone], it[Contacts.email]) }
}

Five functions covering full CRUD plus search.

Contacts.selectAll() translates to SELECT * FROM contacts. .orderBy(Contacts.name) adds ORDER BY name. .map { ... } runs once per row, using subscript syntax (it[Contacts.name]) to access columns.

Contacts.insert { it[Contacts.name] = name ... } builds an INSERT. it is a builder that takes column references; you assign values.

Contacts.update({ Contacts.id eq id }) { ... } — the first lambda is the WHERE clause, the second is the SET. eq is Exposed's equality operator (== doesn't work because column references aren't actual values).

Contacts.deleteWhere { Contacts.id eq id } — DELETE with a WHERE clause.

searchContacts uses lowerCase() and like for case-insensitive substring matching. The %${query}% pattern matches anywhere in the column.

Wrapping each function in transaction { ... } keeps the database scope tight. Each call opens a connection from the pool, runs the query, returns the result.

AppContent.kt: the UI

@Composable
fun AppContent() {
  var contacts by remember { mutableStateOf(getAllContacts()) }
  var searchQuery by remember { mutableStateOf("") }
  var showAddDialog by remember { mutableStateOf(false) }
  var editingContact by remember { mutableStateOf<Contact?>(null) }
  var deletingContact by remember { mutableStateOf<Contact?>(null) }

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

  Scaffold(floatingActionButton = {
    FloatingActionButton(onClick = { showAddDialog = true }) {
      Icon(Icons.Default.Add, "Add Contact")
    }
  }) { padding ->
    Column(Modifier.fillMaxSize().padding(padding).padding(16.dp)) {
      OutlinedTextField(
        value = searchQuery,
        onValueChange = { searchQuery = it; refresh() },
        label = { Text("Search contacts") },
        modifier = Modifier.fillMaxWidth(),
        leadingIcon = { Icon(Icons.Default.Search, null) },
        trailingIcon = {
          if (searchQuery.isNotEmpty()) {
            IconButton(onClick = { searchQuery = ""; refresh() }) {
              Icon(Icons.Default.Clear, null)
            }
          }
        },
      )
      Spacer(Modifier.height(16.dp))
      LazyColumn(verticalArrangement = Arrangement.spacedBy(8.dp)) {
        items(contacts) { contact ->
          ContactCard(contact, { editingContact = contact }, { deletingContact = contact })
        }
      }
    }
  }

  // dialogs...
}

Five state pieces: the contact list, the search query, and three "is this dialog open?" flags. Plus a refresh() function that re-fetches from the DB based on the current search query.

Scaffold is Material 3's app structure container. It manages the floatingActionButton slot for the Add button. The trailing lambda's padding parameter accounts for the FAB so content doesn't overlap.

ContactCard

@Composable
fun ContactCard(contact: Contact, onEdit: () -> Unit, onDelete: () -> Unit) {
  Card(Modifier.fillMaxWidth()) {
    Row(Modifier.padding(16.dp), verticalAlignment = Alignment.CenterVertically) {
      Column(Modifier.weight(1f)) {
        Text(contact.name, style = MaterialTheme.typography.titleMedium)
        if (contact.phone.isNotEmpty()) Text(contact.phone, style = MaterialTheme.typography.bodyMedium)
        if (contact.email.isNotEmpty()) Text(contact.email, style = MaterialTheme.typography.bodySmall)
      }
      IconButton(onClick = onEdit) { Icon(Icons.Default.Edit, "Edit") }
      IconButton(onClick = onDelete) { Icon(Icons.Default.Delete, "Delete") }
    }
  }
}

Card with name/phone/email on the left (weight 1f makes it expand) and Edit/Delete buttons on the right. Conditional if (phone.isNotEmpty()) skips empty fields cleanly.

ContactDialog

@Composable
fun ContactDialog(
  title: String,
  initialName: String = "",
  initialPhone: String = "",
  initialEmail: String = "",
  onDismiss: () -> Unit,
  onSave: (String, String, String) -> Unit,
) {
  var name by remember { mutableStateOf(initialName) }
  var phone by remember { mutableStateOf(initialPhone) }
  var email by remember { mutableStateOf(initialEmail) }

  AlertDialog(
    onDismissRequest = onDismiss,
    title = { Text(title) },
    text = {
      Column(verticalArrangement = Arrangement.spacedBy(8.dp)) {
        OutlinedTextField(name, { name = it }, Modifier.fillMaxWidth(), label = { Text("Name") })
        OutlinedTextField(phone, { phone = it }, Modifier.fillMaxWidth(), label = { Text("Phone") })
        OutlinedTextField(email, { email = it }, Modifier.fillMaxWidth(), label = { Text("Email") })
      }
    },
    confirmButton = {
      TextButton(onClick = { if (name.isNotBlank()) onSave(name, phone, email) }) { Text("Save") }
    },
    dismissButton = {
      TextButton(onClick = onDismiss) { Text("Cancel") }
    },
  )
}

Single dialog used for both Add (with empty initial values) and Edit (with the existing contact's values). onSave is the callback; the parent decides whether to call addContact or updateContact.

if (name.isNotBlank()) onSave(...) validates inline. For richer validation, use the pattern from Lesson 3.

Wiring dialogs

if (showAddDialog) {
  ContactDialog("Add Contact", onDismiss = { showAddDialog = false }) { n, p, e ->
    addContact(n, p, e); showAddDialog = false; refresh()
  }
}

editingContact?.let { c ->
  ContactDialog("Edit Contact", c.name, c.phone, c.email, { editingContact = null }) { n, p, e ->
    updateContact(c.id, n, p, e); editingContact = null; refresh()
  }
}

deletingContact?.let { c ->
  AlertDialog(
    onDismissRequest = { deletingContact = null },
    title = { Text("Delete Contact") },
    text = { Text("Delete ${c.name}?") },
    confirmButton = {
      TextButton(onClick = { deleteContact(c.id); deletingContact = null; refresh() }) {
        Text("Delete")
      }
    },
    dismissButton = {
      TextButton(onClick = { deletingContact = null }) { Text("Cancel") }
    },
  )
}

Three dialogs gated by three state flags. After every mutation we call refresh() to reload the list from the database.

Why call refresh after every mutation?

The simplest correctness model. Mutate the database, re-read the canonical state, update the UI.

For high-frequency mutations (live search-as-you-type) you'd optimise by updating the local list directly without a re-fetch, but only when correctness is provable. For a contacts manager, refresh-after-every-mutation is fine and bug-free.

Migrations

When you change the schema (add a column, rename a table) on an existing database, you need migrations. Exposed has tools for this — SchemaUtils.createMissingTablesAndColumns adds new columns automatically. For renames or destructive changes, write explicit ALTER statements.

For a multi-version app, a real migration framework (Flyway, Liquibase) is a better choice. We won't go that deep here.

Common mistakes

Database operations outside transaction { }. Throws "Database not initialized" or similar. Always wrap.

Using == instead of eq. Column references compare by reference identity, not value. Use Exposed's operators: eq, neq, less, greater, like, inList.

Holding the connection too long. Each transaction block should be tight. Wrapping a whole UI flow in one transaction blocks other queries.

Storing JSON in a TEXT column without indexing. SQLite supports JSON functions, but search performance is poor. Either flatten to columns or use a real document store.

Not closing the database on exit. Exposed manages this for you in most cases, but for desktop apps, an explicit close in your onCloseRequest is good hygiene.

What's next

Lesson 10: drag-and-drop Kanban board. Combine the Lesson 9 storage with Compose Desktop's drag-and-drop APIs for a real productivity-app feel.

Recap

Database.connect("jdbc:sqlite:...") + SchemaUtils.create(Contacts) once at startup. transaction { ... } for every query. Type-safe DSL: Contacts.selectAll().where { ... }, insert, update, deleteWhere. CRUD repository as a set of plain functions. UI calls refresh() after every mutation to stay in sync.

Next lesson: drag-and-drop Kanban board.