How do I actually structure my database tables for something like transactions?

When you're building a finance tracker, the temptation is to use a Double for the amount. Don't do that. I've seen too many projects ruined by floating-point rounding errors where a user's balance is off by 0.0000001. Instead, store everything as a Long representing the smallest currency unit (like cents). It's safer and makes the math predictable.

In Exposed, you'll want to define your schema as an object that extends Table. Here is how I'd set up a basic transactions table:

object Transactions : Table("transactions") {
    val id = integer("id").autoIncrement()
    val amountCents = long("amount_cents")
    val category = varchar("category", 50)
    val description = varchar("description", 255)
    val createdAt = datetime("created_at").defaultExpression(CurrentDateTime)

    override val primaryKey = PrimaryKey(id)
}

Keep it simple. You can add more complex relationships later, but for a tracker, starting with a flat table for transactions gets you moving quickly.

How do I run these database queries inside a Ktor route without freezing the server?

This is where most people get stuck. Ktor is asynchronous, but standard JDBC (which Exposed uses under the hood) is blocking. If you just wrap your code in a standard transaction { ... } block, you're blocking the thread handling the request. On a small project, you won't notice. On a real app, your performance will tank.

You should use newSuspendedTransaction. It offloads the blocking database work to a separate thread pool (the Dispatchers.IO pool), keeping your Ktor engine snappy. Here is how that looks in a POST route:

post("/transactions") {
    val tx = call.receive<TransactionRequest>
    
    val id = newSuspendedTransaction(Dispatchers.IO) {
        Transactions.insert {
            it[amountCents] = tx.amount
            it[category] = tx.category
            it[description] = tx.description
        } get Transactions.id
    }
    
    call.respond(HttpStatusCode.Created, mapOf("id" to id))
}

How do I handle the gap between the Database row and the JSON I send back?

You'll notice that Exposed returns a ResultRow, which is basically a fancy map. You definitely don't want to send that raw object back to your frontend; it's leaky and ugly. The professional way to handle this is with a DTO (Data Transfer Object).

I always create a simple data class for the response. Then, I use a mapping function to convert the row. It feels like extra boilerplate, but it saves you a massive headache when you decide to rename a database column without wanting to break your API contract.

data class TransactionResponse(
    val id: Int,
    val amount: Long,
    val category: String,
    val description: String
)

// In your route:
val results = newSuspendedTransaction(Dispatchers.IO) {
    Transactions.selectAll().map { row ->
        TransactionResponse(
            id = row[Transactions.id],
            amount = row[Transactions.amountCents],
            category = row[Transactions.category],
            description = row[Transactions.description]
        )
    }
}
call.respond(results)

📋 Practical Task

Implement a Category-Based Filter for Transactions

Right now, the API returns every single transaction in the database. Your task is to modify the GET route to support an optional query parameter called category.

• Update the route to check for a category parameter (e.g., /transactions?category=Food).
• If the parameter is present, use Transactions.select { Transactions.category eq categoryParam } to filter the results.
• If the parameter is missing, continue returning all transactions.
• Ensure the database call remains wrapped in newSuspendedTransaction to avoid blocking the main thread.