Exposed Introduction

When you have to write something more complex than simple applications, you will definitely need some persistence. Some way of storing data that is more permanent than volatile memory. It will be very unpleasant if your data is destroyed because of a server failure, power outage or system update.

Such persistence is provided, for example, by files in a filesystem or databases. However, each database has its own characteristics, connection protocols, and ways of working with information.

The Exposed framework helps you build database applications without hard dependencies on any specific database engine.

Installation

To use Exposed, you need to include the exposed-core , exposed-dao and exposed-jdbc artifacts in the build script:

implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-dao:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-jdbc:$exposedVersion")

implementation "org.jetbrains.exposed:exposed-core:$exposedVersion"
implementation "org.jetbrains.exposed:exposed-dao:$exposedVersion"
implementation "org.jetbrains.exposed:exposed-jdbc:$exposedVersion"

In this article, we will use an H2 DBMS. This is just an example. In fact, exposed can work with many popular relational database engines such as MySQL, PostgreSQL, SQLite, Oracle, and SQL Server.

Let's include the h2 driver artifact to use H2 database engine:

implementation("com.h2database:h2:$h2DriverVersion")

implementation "com.h2database:h2:$h2DriverVersion"

Defining tables

Let's create a simple banking application. For this application, we only need one table to store user data:

A DB table is represented by an object inherited from Table class.

import org.jetbrains.exposed.v1.core.Column
import org.jetbrains.exposed.v1.core.Table

object User : Table() {
    val id: Column<Int> = integer("id").autoIncrement()
    val name: Column<String> = varchar("name", 50)
    val password: Column<String> = varchar("password", 50)
    val amount: Column<Int> = integer("amount")

    override val primaryKey = PrimaryKey(id)
}

As you can see, we use Column types to map columns from the database to object fields and apply the PRIMARY KEY constraint by overriding the primaryKey field of the Table class.

While Table is the foundational class for defining tables, we can also use IdTable subclasses from the DAO (Data Access Object) API for convenience. Tables that contain an auto-incrementing Int ID with the name id can be declared using the IntIdTable subtype:

import org.jetbrains.exposed.v1.core.Column
import org.jetbrains.exposed.v1.core.dao.id.IntIdTable

object User : IntIdTable() {
    val name: Column<String> = varchar("name", 50)
    val password: Column<String> = varchar("password", 50)
    val amount: Column<Int> = integer("amount")
}

Getting started

Every database access using Exposed is started by obtaining a connection.

First, we need to connect to the database:

import org.jetbrains.exposed.v1.jdbc.Database

Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

To see which SQL queries Exposed will execute, we will add logging to the standard output. All subsequent work with the database will be performed in one transaction:

import org.jetbrains.exposed.v1.core.StdOutSqlLogger
import org.jetbrains.exposed.v1.jdbc.transactions.transaction

transaction {
    addLogger(StdOutSqlLogger)
}

Transactions

When we work with Exposed, we should place all SQL statements inside a transaction:

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

transaction {
    // Statements here
}

A database transaction is a unit of work performed with a database and processed independently of other transactions. Transactions ensure the data consistency. If at least one read/write request to the database inside the transaction block fails, the transaction is cancelled.

For example, we want to transfer $300 from Alice's bank account to Bob's account. The sequence of actions will be:

1. Check if Alice has $300.

2. Withdraw $300 from Alice's account.

3. Add $300 to Bob's account.

What if an error occurs between step 2 and step 3? In this case, we will lose the money irrevocably. If we perform these actions within a single transaction, then in case of an error, all requests to the database will be canceled (rolled back), and we will not lose the money.

Exposed DSL

The DSL (Domain Specific Language) API of Exposed allows you to write code that looks like actual SQL statements but with the type of safety that Kotlin offers.

Once we have defined our tables, we need to create them in the database:

import org.jetbrains.exposed.v1.jdbc.SchemaUtils
// ...

transaction {
    // previous statements
    SchemaUtils.create(User)
}

SchemaUtils is an object provided by Exposed for interacting with the database schema. The database schema is a logical configuration that describes how the entities stored in the database are represented and how they are related to each other.

After creating the tables, we can perform queries to interact with them.

1. First, let's create two users, Alice and Bob:

   import org.jetbrains.exposed.v1.jdbc.insert
   // ...
   
   transaction {
       // previous statements
       User.insert {
           it[name] = "Alice"
           it[password] = "p455w0rd"
           it[amount] = 500
       }
       User.insert {
           it[name] = "Bob"
           it[password] = "s3cr3t"
           it[amount] = 10
       }
   }
   

2. After creating the users, let's make sure that they both exist in the database. We will output the name and the amount for each account:

   transaction {
       // previous statements
       var users = User.selectAll().map { it[User.name] to it[User.amount] }
       println("Users: $users")
   }
   

   In the console you will see this output: Users: [(Alice, 500), (Bob, 10)]

3. If we want to interact with specific users, we should retrieve the rows using queries:

   import org.jetbrains.exposed.v1.core.eq
   // ...
   
   transaction {
       // previous statements
       val alice = User.selectAll()
           .where { User.name eq "Alice" }
           .firstOrNull()
           ?: throw Exception("User not found!")
       val bob = User.selectAll()
           .where { User.name eq "Bob" }
           .firstOrNull()
           ?: throw Exception("User not found!")
   }
   

   The query expression expects a boolean operator ( Op<Boolean>). Allowed conditions are: eq, neq, isNull, isNotNull, and so on. You can see the full list.

4. Now we will transfer $300 from Alice to Bob:

   import org.jetbrains.exposed.v1.jdbc.update
   // ...
   
   transaction {
       // previous statements
       val transfer = 300
       if (alice[User.amount] < transfer) throw Exception("Insufficient funds!")
   
       User.update({ User.name eq "Alice" }) {
           it[amount] = alice[amount] - transfer
       }
       User.update({ User.name eq "Bob" }) {
           it[amount] = bob[amount] + transfer
       }
   }
   

5. Let's say Alice decided to withdraw all money and delete the account. We need to delete the record from the database:

   import org.jetbrains.exposed.v1.jdbc.deleteWhere
   // ...
   
   transaction {
       // previous statements
       User.deleteWhere { User.name eq "Alice" }
   }
   

6. Finally, let's check that everything is alright:

   transaction {
       // previous statements
       users = User.selectAll().map { it[User.name] to it[User.amount] }
       println("Users: $users")
   }
   

   In the console you will see this output: Users: [(Bob, 310)]

In full, working with our application will look like this:

object User : IntIdTable() {
    val name: Column<String> = varchar("name", 50)
    val password: Column<String> = varchar("password", 50)
    val amount: Column<Int> = integer("amount")
}

fun main() {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

    transaction {
        addLogger(StdOutSqlLogger)
        SchemaUtils.create(User)

        User.insert {
            it[name] = "Alice"
            it[password] = "p455w0rd"
            it[amount] = 500
        }
        User.insert {
            it[name] = "Bob"
            it[password] = "s3cr3t"
            it[amount] = 10
        }

        var users = User.selectAll().map { it[User.name] to it[User.amount] }
        println("Users: $users")

        val alice = User.selectAll()
            .where { User.name eq "Alice" }
            .firstOrNull()
            ?: throw Exception("User not found!")
        val bob = User.selectAll()
            .where { User.name eq "Bob" }
            .firstOrNull()
            ?: throw Exception("User not found!")

        val transfer = 300
        if (alice[User.amount] < transfer) throw Exception("Insufficient funds!")

        User.update({ User.name eq "Alice" }) {
            it[amount] = alice[User.amount] - transfer
        }
        User.update({ User.name eq "Bob" }) {
            it[amount] = bob[User.amount] + transfer
        }

        User.deleteWhere { name eq "Alice" }

        users = User.selectAll().map { it[User.name] to it[User.amount] }
        println("Users: $users")
    }
}

Conclusion

Databases provide data persistence. You can avoid writing long SQL queries using the Exposed framework. Using this framework, you can work with databases at a high-level DSL without diving into the subtleties of a specific DBMS. Furthermore, with the help of the transactional model, Exposed ensures data consistency.