0Pricing
Kotlin Academy · Lesson

Exposed Setup: Database Connection and Transaction DSL

Connect to a database, configure Exposed, and execute code inside transactions.

Exposed Setup: Database Connection and Transaction DSL is a free Kotlin Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Kotlin Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is Exposed?

Exposed is JetBrains' lightweight SQL framework for Kotlin. It offers two levels: a Query DSL (typesafe SQL expressions) and a DAO layer (Active Record-style entities). Both run inside explicit transactions.

Adding Exposed Dependencies

Add the core, JDBC driver bridge, and your database driver:

dependencies {
    implementation("org.jetbrains.exposed:exposed-core:0.52.0")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.52.0")
    implementation("org.jetbrains.exposed:exposed-dao:0.52.0")
    implementation("com.h2database:h2:2.2.224") // or postgresql driver
    implementation("com.zaxxer:HikariCP:5.1.0")  // connection pooling
}

Connecting to the Database

Call Database.connect() once at startup. For production, pass a HikariDataSource; for tests, a simple JDBC URL is fine:

import org.jetbrains.exposed.sql.Database

// Simple (dev/test)
Database.connect(
    url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
    driver = "org.h2.Driver"
)

// Production with HikariCP
Database.connect(HikariDataSource(HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://localhost/mydb"
    username = "user"
    password = "pass"
    maximumPoolSize = 10
}))

The transaction { } Block

All Exposed operations must run inside a transaction { } block. Exposed commits on success and rolls back on any uncaught exception:

transaction {
    // all DB operations here
    SchemaUtils.create(Users)
}

Coroutine-Friendly Transactions

Use newSuspendedTransaction { } from exposed-r2dbc or the exposed-spring-boot-starter for coroutine-compatible transactions. In Ktor, wrap blocking Exposed calls in withContext(Dispatchers.IO) { transaction { ... } }:

suspend fun findUser(id: Long): User? = withContext(Dispatchers.IO) {
    transaction {
        UserEntity.findById(id)?.toUser()
    }
}

SchemaUtils: Creating and Dropping Tables

Use SchemaUtils.create(Table1, Table2) to create tables if they don't exist, and SchemaUtils.drop() to drop them. Call this inside a transaction { } at application startup:

transaction {
    SchemaUtils.create(Users, Posts, Comments)
}

Configuring a Connection in Ktor

Initialize the database in an Application module so it runs once when the server starts:

fun Application.configureDatabase() {
    val dbUrl = environment.config.property("ktor.database.url").getString()
    Database.connect(dbUrl, driver = "org.postgresql.Driver",
        user = "...", password = "...")
    transaction { SchemaUtils.create(Users) }
}

Transaction Isolation Levels

Pass an isolation level to transaction() when you need stronger or weaker consistency guarantees:

transaction(Connection.TRANSACTION_SERIALIZABLE) {
    // serializable isolation
}

Nested Transactions

By default, nested transaction { } calls join the outer transaction. To get an independent transaction, use transaction(db, outerTransactionIsEmpty = false) { } or a savepoint.

Logging SQL

Enable SQL logging to see generated queries during development. Set a StdOutSqlLogger or a custom logger inside the transaction block:

transaction {
    addLogger(StdOutSqlLogger)
    // ...
}

Connection Pooling Best Practices

Always use HikariCP (or similar) in production. Set maximumPoolSize to match your server thread count and connectionTimeout to fail fast when the pool is exhausted. Monitor pool utilization in production.

Quick Check

What happens if an uncaught exception is thrown inside a transaction { } block in Exposed?

Recap: Exposed Setup and Transaction DSL

Key takeaways:

  • Connect with Database.connect() once at startup (use HikariCP in production)
  • All DB operations run inside transaction { } — commits on success, rolls back on exception
  • Create/drop tables with SchemaUtils.create() inside a transaction
  • Wrap blocking Exposed calls in withContext(Dispatchers.IO) in coroutine contexts
  • Enable StdOutSqlLogger during development to inspect generated SQL

Frequently asked questions

Is the “Exposed Setup: Database Connection and Transaction DSL” lesson free?

Yes — the full text of “Exposed Setup: Database Connection and Transaction DSL” is free to read here on the web, and the Kotlin Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Kotlin Academy course, upgrade to CoddyKit PRO.

What will I learn in “Exposed Setup: Database Connection and Transaction DSL”?

Connect to a database, configure Exposed, and execute code inside transactions. You practise Kotlin Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Kotlin Academy?

No prior experience is required. Kotlin Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Exposed Setup: Database Connection and Transaction DSL” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Kotlin Academy lesson?

Yes. Every Kotlin Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Exposed Setup: Database Connection and Transaction DSL
  2. Defining Tables with the Table DSL
  3. CRUD with the Query DSL: insert, select, update, delete
  4. Exposed DAO: Entity Classes and Relationships
← Back to Kotlin Academy