Library

Exposed

Overview

Exposed is a Kotlin-first ORM framework developed by JetBrains that achieves type-safe database access through a "SQL DSL-first approach". With concise syntax and complete integration of modern Kotlin features, it features Kotlinx datetime support, coroutine support, and intuitive query construction. As of 2025, it has become the most promising Kotlin ORM candidate, chosen by 60% of developers as a lightweight solution and the first choice recommended for new projects.

Details

Exposed 2025 version provides a modern database access layer that balances type safety and code readability through DSL design that maximizes Kotlin language features. With continuous development by JetBrains and deep integration with the Kotlin ecosystem, it seamlessly integrates with major frameworks like Spring Boot and Ktor. It provides two API approaches, DSL and DAO, allowing flexible implementation styles according to project requirements.

Key Features

• Kotlin DSL Design: Natural and readable SQL construction and query description
• Type Safety Guarantee: Compile-time SQL validation and type checking
• Dual API Provision: Choice between DSL (functional) and DAO (object-oriented)
• JetBrains Support: Stability and continuity through official maintenance
• Multi-Database Support: Support for H2, MySQL, PostgreSQL, SQLite, Oracle, SQL Server
• Coroutine Support: Complete integration of asynchronous processing and suspend functions

Pros and Cons

Pros

• Reliability and Kotlin-optimized design through official JetBrains development
• High development efficiency with low learning cost and intuitive DSL syntax
• Compile-time error detection and runtime safety through type safety
• Easy integration into existing projects with Spring Boot Starter
• Rich sample code and comprehensive official documentation
• Potential use in Kotlin Multiplatform projects

Cons

• Kotlin-only, unusable in other language projects
• Verbosity due to DSL limitations when constructing complex queries
• Lack of enterprise features compared to JPA/Hibernate
• Limited migration feature support
• Performance optimization challenges with large datasets
• Integration costs with legacy Java codebases

Reference Pages

• Exposed GitHub Repository
• Exposed Official Documentation
• JetBrains Kotlin Project

Code Examples

Project Setup and Dependencies

// build.gradle.kts
dependencies {
    implementation("org.jetbrains.exposed:exposed-core:1.0.0-beta-2")
    implementation("org.jetbrains.exposed:exposed-dao:1.0.0-beta-2")
    implementation("org.jetbrains.exposed:exposed-jdbc:1.0.0-beta-2")
    
    // Date-time support
    implementation("org.jetbrains.exposed:exposed-kotlin-datetime:1.0.0-beta-2")
    
    // Spring Boot integration
    implementation("org.jetbrains.exposed:exposed-spring-boot-starter:1.0.0-beta-2")
    
    // Database drivers (choose as needed)
    implementation("com.h2database:h2:2.2.224")                    // H2
    implementation("mysql:mysql-connector-java:8.0.33")           // MySQL
    implementation("org.postgresql:postgresql:42.7.1")            // PostgreSQL
    implementation("org.xerial:sqlite-jdbc:3.44.1.0")            // SQLite
}

// Basic imports in main class
import org.jetbrains.exposed.v1.*
import org.jetbrains.exposed.v1.transactions.transaction

Database Connection Configuration

import org.jetbrains.exposed.v1.core.Database
import org.jetbrains.exposed.v1.core.DatabaseConfig

// H2 in-memory database
Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

// MySQL connection
Database.connect(
    "jdbc:mysql://localhost:3306/testdb",
    driver = "com.mysql.cj.jdbc.Driver",
    user = "root",
    password = "password"
)

// PostgreSQL connection
Database.connect(
    "jdbc:postgresql://localhost:5432/testdb",
    driver = "org.postgresql.Driver",
    user = "postgres",
    password = "password"
)

// SQLite connection
Database.connect("jdbc:sqlite:data.db", "org.xerial.sqlite-jdbc.Driver")

// Advanced configuration example (using HikariCP)
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

val config = HikariConfig().apply {
    jdbcUrl = "jdbc:mysql://localhost:3306/testdb"
    driverClassName = "com.mysql.cj.jdbc.Driver"
    username = "root"
    password = "password"
    maximumPoolSize = 10
}

val dataSource = HikariDataSource(config)
Database.connect(dataSource)

// DatabaseConfig usage example
val database = Database.connect(
    datasource = dataSource,
    databaseConfig = DatabaseConfig {
        sqlLogger = Slf4jSqlDebugLogger
        useNestedTransactions = true
    }
)

Table Definition (DSL Approach)

import org.jetbrains.exposed.v1.core.Table
import org.jetbrains.exposed.v1.core.Column
import kotlinx.datetime.LocalDateTime

// Basic table definition
object Users : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 50)
    val email = varchar("email", 100).uniqueIndex()
    val age = integer("age").nullable()
    val isActive = bool("is_active").default(true)
    
    override val primaryKey = PrimaryKey(id)
}

object Cities : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 50)
    val country = varchar("country", 50)
    
    override val primaryKey = PrimaryKey(id)
}

object Posts : Table() {
    val id = integer("id").autoIncrement()
    val title = varchar("title", 200)
    val content = text("content")
    val authorId = integer("author_id") references Users.id
    val cityId = integer("city_id") references Cities.id
    val createdAt = varchar("created_at", 50) // Use datetime() when using kotlinx-datetime
    val publishedAt = varchar("published_at", 50).nullable()
    
    override val primaryKey = PrimaryKey(id)
}

// Composite primary key example
object UserRoles : Table() {
    val userId = integer("user_id") references Users.id
    val roleId = integer("role_id")
    val assignedAt = varchar("assigned_at", 50)
    
    override val primaryKey = PrimaryKey(userId, roleId)
}

// Indexes and unique constraints
object Products : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 100)
    val sku = varchar("sku", 50).uniqueIndex()
    val categoryId = integer("category_id")
    val price = decimal("price", 10, 2)
    
    override val primaryKey = PrimaryKey(id)
    
    init {
        // Composite index
        index(false, categoryId, name)
    }
}

Basic CRUD Operations (DSL)

import org.jetbrains.exposed.v1.*
import org.jetbrains.exposed.v1.transactions.transaction

fun basicCrudOperations() {
    transaction {
        // Create tables
        SchemaUtils.create(Users, Cities, Posts)
        
        // Data insertion
        val tokyoId = Cities.insert {
            it[name] = "Tokyo"
            it[country] = "Japan"
        } get Cities.id
        
        val osakaId = Cities.insert {
            it[name] = "Osaka"
            it[country] = "Japan"
        } get Cities.id
        
        // User insertion
        val userId1 = Users.insert {
            it[name] = "John Doe"
            it[email] = "[email protected]"
            it[age] = 30
            it[isActive] = true
        } get Users.id
        
        val userId2 = Users.insert {
            it[name] = "Jane Smith"
            it[email] = "[email protected]"
            it[age] = 25
            it[isActive] = true
        } get Users.id
        
        // Post creation
        Posts.insert {
            it[title] = "Getting Started with Kotlin Exposed"
            it[content] = "Let's try database access with Kotlin Exposed"
            it[authorId] = userId1
            it[cityId] = tokyoId
            it[createdAt] = "2025-06-24T10:00:00"
            it[publishedAt] = "2025-06-24T12:00:00"
        }
        
        // Batch insertion
        Users.batchInsert(listOf(
            Triple("Alice Johnson", "[email protected]", 28),
            Triple("Bob Wilson", "[email protected]", 32),
            Triple("Carol Brown", "[email protected]", 27)
        )) { (name, email, age) ->
            this[Users.name] = name
            this[Users.email] = email
            this[Users.age] = age
            this[Users.isActive] = true
        }
        
        // Data selection
        println("=== All Users ===")
        Users.selectAll().forEach { row ->
            println("${row[Users.id]}: ${row[Users.name]} (${row[Users.email]})")
        }
        
        // Conditional selection
        println("\n=== Active Users (Age 25+) ===")
        Users.select { (Users.isActive eq true) and (Users.age greaterEq 25) }
            .forEach { row ->
                println("${row[Users.name]} - Age: ${row[Users.age]}")
            }
        
        // JOIN operations
        println("\n=== User and Post JOIN ===")
        (Users innerJoin Posts innerJoin Cities)
            .select(Users.name, Posts.title, Cities.name)
            .forEach { row ->
                println("${row[Users.name]} posted from ${row[Cities.name]}: ${row[Posts.title]}")
            }
        
        // Data update
        Users.update({ Users.id eq userId1 }) {
            it[age] = 31
            it[email] = "[email protected]"
        }
        
        // Data deletion
        Posts.deleteWhere { Posts.authorId eq userId2 }
        
        // Aggregate queries
        val userCount = Users.select { Users.isActive eq true }.count()
        println("\nActive user count: $userCount")
        
        val avgAge = Users.slice(Users.age.avg()).select { Users.age.isNotNull() }
            .single()[Users.age.avg()]
        println("Average age: $avgAge")
    }
}

DAO Approach (Object-Oriented)

import org.jetbrains.exposed.v1.dao.*
import org.jetbrains.exposed.v1.dao.id.*

// DAO table definitions
object UsersTable : IntIdTable("users") {
    val name = varchar("name", 50)
    val email = varchar("email", 100).uniqueIndex()
    val age = integer("age").nullable()
    val isActive = bool("is_active").default(true)
}

object CitiesTable : IntIdTable("cities") {
    val name = varchar("name", 50)
    val country = varchar("country", 50)
}

object PostsTable : IntIdTable("posts") {
    val title = varchar("title", 200)
    val content = text("content")
    val author = reference("author_id", UsersTable)
    val city = reference("city_id", CitiesTable)
    val createdAt = varchar("created_at", 50)
    val publishedAt = varchar("published_at", 50).nullable()
}

// Entity class definitions
class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(UsersTable)
    
    var name by UsersTable.name
    var email by UsersTable.email
    var age by UsersTable.age
    var isActive by UsersTable.isActive
    
    // Related entities
    val posts by Post referrersOn PostsTable.author
    
    // Custom methods
    fun getActivePostsCount(): Long = posts.filter { it.publishedAt != null }.count()
    
    override fun toString(): String = "User(id=$id, name='$name', email='$email')"
}

class City(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<City>(CitiesTable)
    
    var name by CitiesTable.name
    var country by CitiesTable.country
    
    val posts by Post referrersOn PostsTable.city
    val users by User via PostsTable
    
    override fun toString(): String = "City(id=$id, name='$name', country='$country')"
}

class Post(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Post>(PostsTable)
    
    var title by PostsTable.title
    var content by PostsTable.content
    var author by User referencedOn PostsTable.author
    var city by City referencedOn PostsTable.city
    var createdAt by PostsTable.createdAt
    var publishedAt by PostsTable.publishedAt
    
    val isPublished: Boolean get() = publishedAt != null
    
    override fun toString(): String = "Post(id=$id, title='$title', author='${author.name}')"
}

// DAO operation examples
fun daoOperations() {
    transaction {
        // Create tables
        SchemaUtils.create(UsersTable, CitiesTable, PostsTable)
        
        // Entity creation
        val tokyo = City.new {
            name = "Tokyo"
            country = "Japan"
        }
        
        val osaka = City.new {
            name = "Osaka"
            country = "Japan"
        }
        
        val user1 = User.new {
            name = "John Doe"
            email = "[email protected]"
            age = 30
            isActive = true
        }
        
        val user2 = User.new {
            name = "Jane Smith"
            email = "[email protected]"
            age = 25
            isActive = true
        }
        
        // Post creation
        val post1 = Post.new {
            title = "Kotlin Exposed DAO Introduction"
            content = "Let's learn database access with DAO pattern"
            author = user1
            city = tokyo
            createdAt = "2025-06-24T10:00:00"
            publishedAt = "2025-06-24T12:00:00"
        }
        
        val post2 = Post.new {
            title = "Kotlin Study Group in Kansai"
            content = "We're hosting a Kotlin study group in Osaka"
            author = user2
            city = osaka
            createdAt = "2025-06-24T14:00:00"
            publishedAt = null
        }
        
        // Data search
        println("=== All Users ===")
        User.all().forEach { user ->
            println("$user - Posts: ${user.posts.count()}")
        }
        
        // Conditional search
        println("\n=== Active Users ===")
        User.find { UsersTable.isActive eq true }.forEach { user ->
            println("${user.name}: ${user.email}")
        }
        
        // Related data access
        println("\n=== User Posts ===")
        user1.posts.forEach { post ->
            println("${post.title} (${if (post.isPublished) "Published" else "Draft"})")
        }
        
        println("\n=== Posts by City ===")
        City.all().forEach { city ->
            println("${city.name}: ${city.posts.count()} posts")
        }
        
        // Data update
        user1.age = 31
        user1.email = "[email protected]"
        
        post2.publishedAt = "2025-06-24T16:00:00"
        
        // Data deletion
        post2.delete()
        
        // Aggregate operations
        val activeUserCount = User.find { UsersTable.isActive eq true }.count()
        println("\nActive user count: $activeUserCount")
        
        val publishedPostCount = Post.find { PostsTable.publishedAt.isNotNull() }.count()
        println("Published posts count: $publishedPostCount")
    }
}

Advanced Queries and Transactions

import org.jetbrains.exposed.v1.transactions.experimental.newSuspendedTransaction
import org.jetbrains.exposed.v1.core.SqlExpressionBuilder
import kotlinx.coroutines.runBlocking

// Complex query examples
fun advancedQueries() {
    transaction {
        // Subquery usage example
        val activeUsers = Users.select { Users.isActive eq true }
        val postsFromActiveUsers = Posts.select { 
            Posts.authorId inSubQuery activeUsers.slice(Users.id)
        }
        
        println("Posts from active users:")
        postsFromActiveUsers.forEach { row ->
            println("${row[Posts.title]}")
        }
        
        // Multi-table JOIN and aggregation
        val userPostStats = (Users innerJoin Posts)
            .slice(Users.name, Posts.id.count(), Posts.publishedAt.count())
            .select { Users.isActive eq true }
            .groupBy(Users.id, Users.name)
            .orderBy(Posts.id.count() to SortOrder.DESC)
        
        println("\nUser post statistics:")
        userPostStats.forEach { row ->
            println("${row[Users.name]}: ${row[Posts.id.count()]} total posts, ${row[Posts.publishedAt.count()]} published")
        }
        
        // CASE statement usage example
        val userStatusQuery = Users
            .slice(Users.name, Users.age.case()
                .When(Users.age.less(25), stringLiteral("Young"))
                .When(Users.age.between(25, 35), stringLiteral("Mid-career"))
                .Else(stringLiteral("Senior")).alias("status"))
            .selectAll()
        
        println("\nUser age classification:")
        userStatusQuery.forEach { row ->
            println("${row[Users.name]}: ${row[userStatusQuery.slice.last()]}")
        }
        
        // Window function usage example (available on PostgreSQL etc.)
        val rankedPosts = Posts
            .slice(Posts.title, Posts.createdAt, Posts.id.rank().over().orderBy(Posts.createdAt to SortOrder.DESC))
            .select { Posts.publishedAt.isNotNull() }
            .orderBy(Posts.createdAt to SortOrder.DESC)
        
        println("\nPost ranking:")
        rankedPosts.forEach { row ->
            val rank = row[rankedPosts.slice.last()]
            println("$rank: ${row[Posts.title]}")
        }
    }
}

// Asynchronous transactions (coroutine support)
suspend fun suspendedTransactionExample() = newSuspendedTransaction {
    // Asynchronous database operations
    val users = User.all().toList()
    
    users.forEach { user ->
        // Database access within asynchronous processing
        val postCount = user.posts.count()
        println("${user.name}: $postCount posts")
    }
}

// Transaction management
fun transactionManagement() {
    // Basic transaction
    transaction {
        val user = User.new {
            name = "Test User"
            email = "[email protected]"
            age = 30
        }
        
        Post.new {
            title = "Test Post"
            content = "This is a test"
            author = user
            city = City.all().first()
            createdAt = "2025-06-24T10:00:00"
        }
    }
    
    // Nested transactions
    transaction {
        val user = User.new {
            name = "Main User"
            email = "[email protected]"
            age = 35
        }
        
        try {
            transaction {
                // Inner transaction
                Post.new {
                    title = "Inner Transaction Post"
                    content = "Created within nested transaction"
                    author = user
                    city = City.all().first()
                    createdAt = "2025-06-24T11:00:00"
                }
                
                // If error occurs, inner transaction will rollback
                if (false) { // Conditionally trigger error
                    throw Exception("Inner transaction error")
                }
            }
        } catch (e: Exception) {
            println("Error in inner transaction: ${e.message}")
            // Outer transaction continues
        }
        
        // Outer transaction processing continues
        println("User ${user.name} was created")
    }
}

// Usage example
fun main() {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")
    
    basicCrudOperations()
    daoOperations()
    advancedQueries()
    transactionManagement()
    
    // Asynchronous example
    runBlocking {
        suspendedTransactionExample()
    }
}

Spring Boot Integration

// Application.kt
@SpringBootApplication
class ExposedDemoApplication

fun main(args: Array<String>) {
    runApplication<ExposedDemoApplication>(*args)
}

// application.yml
/*
spring:
  datasource:
    url: jdbc:h2:mem:testdb
    driver-class-name: org.h2.Driver
    username: sa
    password:
exposed:
  generate-ddl: true
*/

// UserService.kt
@Service
@Transactional
class UserService {
    
    fun createUser(name: String, email: String, age: Int?): Int {
        return transaction {
            Users.insert {
                it[Users.name] = name
                it[Users.email] = email
                it[Users.age] = age
                it[isActive] = true
            } get Users.id
        }
    }
    
    fun findUserById(id: Int): User? {
        return transaction {
            User.findById(id)
        }
    }
    
    fun getAllActiveUsers(): List<User> {
        return transaction {
            User.find { UsersTable.isActive eq true }.toList()
        }
    }
    
    fun updateUser(id: Int, name: String?, email: String?): Boolean {
        return transaction {
            val updated = Users.update({ Users.id eq id }) {
                name?.let { newName -> it[Users.name] = newName }
                email?.let { newEmail -> it[Users.email] = newEmail }
            }
            updated > 0
        }
    }
    
    fun deleteUser(id: Int): Boolean {
        return transaction {
            val deleted = Users.deleteWhere { Users.id eq id }
            deleted > 0
        }
    }
}

// UserController.kt
@RestController
@RequestMapping("/api/users")
class UserController(private val userService: UserService) {
    
    @PostMapping
    fun createUser(@RequestBody request: CreateUserRequest): ResponseEntity<Map<String, Any>> {
        val userId = userService.createUser(request.name, request.email, request.age)
        return ResponseEntity.ok(mapOf("id" to userId, "message" to "User created successfully"))
    }
    
    @GetMapping("/{id}")
    fun getUser(@PathVariable id: Int): ResponseEntity<User?> {
        val user = userService.findUserById(id)
        return if (user != null) {
            ResponseEntity.ok(user)
        } else {
            ResponseEntity.notFound().build()
        }
    }
    
    @GetMapping
    fun getAllUsers(): List<User> {
        return userService.getAllActiveUsers()
    }
    
    @PutMapping("/{id}")
    fun updateUser(
        @PathVariable id: Int,
        @RequestBody request: UpdateUserRequest
    ): ResponseEntity<Map<String, String>> {
        val updated = userService.updateUser(id, request.name, request.email)
        return if (updated) {
            ResponseEntity.ok(mapOf("message" to "User updated successfully"))
        } else {
            ResponseEntity.notFound().build()
        }
    }
    
    @DeleteMapping("/{id}")
    fun deleteUser(@PathVariable id: Int): ResponseEntity<Map<String, String>> {
        val deleted = userService.deleteUser(id)
        return if (deleted) {
            ResponseEntity.ok(mapOf("message" to "User deleted successfully"))
        } else {
            ResponseEntity.notFound().build()
        }
    }
}

data class CreateUserRequest(val name: String, val email: String, val age: Int?)
data class UpdateUserRequest(val name: String?, val email: String?)