When people first encounter Exposed, they usually bring a lot of baggage from other languages. If you've used Hibernate in Java or Entity Framework in C#, you likely have a specific mental model of how a database library should work: you define a class, you annotate it, and the library magically syncs that class with a table. Because of this, the most common misconception I see is that Exposed is just another traditional ORM where the DAO (Data Access Object) layer is the primary way to interact with the database.

Here is why that's a dangerous way to think about it. If you treat Exposed strictly as a DAO-based ORM, you'll eventually hit a wall where you're fighting the framework to write a complex JOIN or a specific aggregation. You'll find yourself wondering why a simple SQL query feels like it requires a PhD in framework internals to implement. The truth is, the DAO layer in Exposed is actually a thin, optional wrapper sitting on top of something much more powerful: the DSL (Domain Specific Language).

The Myth: You must map everything to Entity classes

In a traditional ORM, you might be tempted to create an Entity for every single table and perform all your logic through those objects. While Exposed provides this via IntEntity and IntEntityClass, relying on them exclusively makes your code brittle. For example, if you want to find the total number of high-priority tasks across all projects, using the DAO approach often leads to "N+1" query problems—where you fetch all projects, then loop through them to fetch tasks, firing off dozens of unnecessary queries to the database.

// The "ORM-style" trap (DAO)
val projects = ProjectEntity.all().toList() 
val totalHighPriority = projects.sumOf { project -> 
    project.tasks.count { it.priority == Priority.HIGH } 
}
// This is an efficiency nightmare. Your logs will be screaming.

The Reality: DSL first, Entities second

The "real" Exposed is the DSL. The DSL allows you to write Kotlin code that looks and behaves like SQL, but with full type safety. Instead of thinking in terms of "Objects," you think in terms of "Tables" and "Columns." I always tell my juniors: start with the DSL. Only reach for the DAO when you are doing simple CRUD operations where the overhead of mapping is actually helpful.

Let's look at how we actually set this up. First, we define the schema as singleton objects. This isn't just a configuration; these objects are the representation of your database schema in Kotlin.

object Projects : Table("projects") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 100)
    override val primaryKey = PrimaryKey(id)
}

object Tasks : Table("tasks") {
    val id = integer("id").autoIncrement()
    val projectId = reference("project_id", Projects.id)
    val description = text("description")
    val priority = integer("priority") // 1 for High, 2 for Medium, 3 for Low
    override val primaryKey = PrimaryKey(id)
}

Now, remember that in Exposed, nothing happens outside of a transaction block. This is non-negotiable. The transaction manages the connection and the unit of work. If you try to query a table outside of one, Exposed will throw an exception immediately. It's a bit strict, but it prevents the kind of connection-leak nightmares I've spent far too many weekends fixing in older projects.

To solve our "high priority" problem from earlier, we use the DSL. This generates a single, efficient SQL query:

transaction {
    val highPriorityCount = Tasks
        .select { Tasks.priority eq 1 }
        .count()
    
    println("Total high priority tasks: $highPriorityCount")
}


If you need to join tables—say, to find the names of projects that have overdue tasks—the DSL makes it explicit. You aren't guessing what the ORM is doing under the hood; you are telling it exactly how to join:

transaction {
    (Projects innerJoin Tasks)
        .slice(Projects.name)
        .select { Tasks.priority eq 1 }
        .map { it[Projects.name] }
}


I prefer this approach because it keeps the database logic visible. When the query gets slow, you can take the generated SQL, run an EXPLAIN ANALYZE on it in your DB console, and optimize your indices. You aren't fighting a "black box" that decides how to fetch your data.

📋 Practical Task

Build a Project Budget Auditor

You need to create a small auditing tool using the Exposed DSL. Your goal is to calculate the total cost of all tasks associated with a specific project, but only for tasks that are marked as "Completed".

Requirements:

• Define a Projects table with id and name.
• Define a Tasks table with id, projectId (FK), cost (decimal), and isCompleted (boolean).
• Write a function calculateProjectSpend(projectId: Int): BigDecimal that:
  • Opens a transaction.
  • Uses the Tasks table DSL to filter by the given projectId AND where isCompleted is true.
  • Uses the sum() aggregation function on the cost column.
  • Returns the total sum.

Hint: You will need to import org.jetbrains.exposed.sql.* and use Tasks.cost.sum() within your select statement.