Brett Tucker

Getting Exposed

Brett Tucker -

I recently came across the Jetbrains Exposed ORM project on github and thought it looked like an interesting little ORM for kotlin projects. I was surprised to see the project was several years old at this point but that I had never heard of it. Let's take it for a test drive today to see how it works!

Project Setup

Let's get started by adding the dependencies we'll need to run some code and test out the ORM.

First we'll want to the ORM itself. We'll start with just the base modules and then look at a few more additional ones that could be useful in a real project toward the end of the post.

In our gradle.properties file we'll add a variable call exposedVersion to store the version of the library we're using in the project

exposedVersion=0.54.0

We'll then add the core, dao, and jdbc modules to our project dependencies in our build.gradle.kts file and use our exposedVersion variable to keep them in sync

val exposedVersion: String by project

dependencies {
    testImplementation(kotlin("test"))
    implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-dao:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-jdbc:$exposedVersion")
}

Next we'll add the h2 in memory database as a dependency. This is just to make our life easier as we explore the library. We don't want to have to go setup or connect to a real DB instance running somewhere just to explore this ORM.

implementation("com.h2database:h2:2.3.232")

Lastly we'll go ahead and add the logback dependency to the project as well. Exposed by default makes use of the SLF4J logging facade so by adding this concrete implementation we'll be able to see logs from exposed as we run our code to give us a clearer picture of what its doing as we execute statements.

implementation("ch.qos.logback:logback-classic:1.4.5")

Setting the table(s)

The next thing we need to do is dive in to the Main.kt file and write some code! Throughout this blog post I'll use a small schema representing a book store to exercise Exposed and so for our first table lets create the Genre table. It will represent all the different genres of books our bookstore has to offer.


object Genres : IntIdTable(){
    val name = varchar("name", 255).uniqueIndex()
    val description = varchar("description", 255).nullable()
}

To create a table in Exposed to represent our genre data we declare an object Called Genres and let it inherit from IntIdTable. This will give us a table that uses an integer as its primary key. This column will auto increment and the column name will be id.

If you're not a fan of auto incrementing integer primary keys, don't worry. Exposed also offers UUIDTable which will generate a column named id and auto insert a UUID for each row that you insert into the table.

The next two lines of code declare the columns for our genre table that we want to store. The first one name sets up a varchar with a max length of 255 and then ensures that this column has a unique index created for it.

After that we declare another varchar column for storing the description of the genre with a max length of 255 and allow it to be nullable.

CRUD

Now that we have a table let's make use of it! In our main function we'll go ahead and connect to our in memory h2 database instance that is conveniently bundled into our project

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

Then let's write some code that will create, read, update, and delete genre rows from our table.


package dev.bltucker

import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.Query
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq

object Genres : IntIdTable(){
    val name = varchar("name", 255).uniqueIndex()
    val description = varchar("description", 255).nullable()
}

fun main() {
    println("Hello Exposed!")

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

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

        //CREATE
        val fantasyGenreId = Genres.insert {
            it[name] = "Fantasy"
            it[description] = "Magic, Sorcery, Elves and Stuff"
        } get Genres.id

        //Note the lack of a description here. We allow it to be nullable so that's ok!
        val scienceFictionGenreId = Genres.insert {
            it[name] = "Science Fiction"
        } get Genres.id

        println("Inserted Fantasy with ID: $fantasyGenreId")
        println("Inserted Science Fiction with ID: $scienceFictionGenreId")

        //READ
        val allGenres: Query = Genres.selectAll()

        allGenres.forEach{
            println("${it[Genres.id]}: ${it[Genres.name]} - ${it[Genres.description]}")
        }

        val fantasy = Genres.select(Genres.name, Genres.description).where { Genres.name eq "Fantasy" }.single()
        println("Fantasy genre: ${fantasy[Genres.name]} - ${fantasy[Genres.description]}")


        //UPDATE
        Genres.update({ Genres.id eq scienceFictionGenreId }) {
            it[description] = "A genre of speculative fiction dealing with imaginative and futuristic concepts"
        }

        val updatedSciFi = Genres.select(Genres.name, Genres.description).where { Genres.id eq scienceFictionGenreId }.single()
        println("Updated Sci-Fi: ${updatedSciFi[Genres.name]} - ${updatedSciFi[Genres.description]}")


        //DELETE
        val knitting: EntityID<Int> = Genres.insert {
            it[name] = "Cyberpunk Knitting"
            it[description] = "A genre doomed to never catch on"
        } get Genres.id

        val genreCountBefore = Genres.selectAll().count()
        println("There are $genreCountBefore genres")

        Genres.deleteWhere { Genres.id eq knitting }

        val genreCountAfter = Genres.selectAll().count()
        println("There are $genreCountAfter genres")

    }

}

Let's break this code down into chunks. First i want to call out a particular import that gave me some trouble and sent me to the issue list for the Exposed project

import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq

The deleteWhere call would not work until that was manually imported, the other clauses making use of eq were fine but it seems there's some import conflicts/differences between the deleteWhere clause and the rest of the api that leads to some issues.

With that out of the way let's dive into the more interesting stuff

//Connect to our in memory h2 database
Database.connect("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")

//begin a transaction
  transaction {
    //log everything Exposed does out to the console
        addLogger(StdOutSqlLogger)
        
        //Create the schema in our database
         SchemaUtils.create(Genres)
  }

The Exposed dsl gives us a transaction block that will automatically wrap our SQL operations in a transaction. Once we've started our transaction we add a logger. This logger will print out all of the SQL being executed in our transaction to your standard out. This is really handy for being able to see exactly what Exposed is doing and can help you debug issues you might run into as you use the Exposed library in a real project.

The last line in this snippet simply creates our Schema which currently just includes our genre table.

Here's a little sample of the logging showing our scehma being made in SQL

SQL: SELECT SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'MODE'
SQL: CREATE TABLE IF NOT EXISTS GENRES (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(255) NULL)
SQL: ALTER TABLE GENRES ADD CONSTRAINT GENRES_NAME_UNIQUE UNIQUE ("name")

After that we create some entries in our genres table

//CREATE
        val fantasyGenreId = Genres.insert {
            it[name] = "Fantasy"
            it[description] = "Magic, Sorcery, Elves and Stuff"
        } get Genres.id

        //Note the lack of a description here. We allow it to be nullable so that's ok!
        val scienceFictionGenreId = Genres.insert {
            it[name] = "Science Fiction"
        } get Genres.id

        println("Inserted Fantasy with ID: $fantasyGenreId")
        println("Inserted Science Fiction with ID: $scienceFictionGenreId")

You can see that since we left description as nullable we dont need to provide a value when defining our Science Fiction genre. The Exposed DSL also let's us automatically grab the ID of the row we just inserted for use in other queries later.

//READ
        val allGenres: Query = Genres.selectAll()

        allGenres.forEach{
            println("${it[Genres.id]}: ${it[Genres.name]} - ${it[Genres.description]}")
        }

        val fantasy = Genres.select(Genres.name, Genres.description).where { Genres.name eq "Fantasy" }.single()
        println("Fantasy genre: ${fantasy[Genres.name]} - ${fantasy[Genres.description]}")

Reading from the table using Exposed is pretty straight forward. You can easily do a selectAll() to grab everything, or you can be more precise like we do in our second query.

.select() will take a list of columns you want to grab, and the where clause will let you define what conditions you want met for the data being returned.

Finally we call .single() because we expect this query to return exactly 1 result and just want to deal with it. Exposed also gives you a .singleOrNull which is generally safer.

You can also leave off the single or singleOrNull and deal with the raw query result as well.

Genres.update({ Genres.id eq scienceFictionGenreId }) {
            it[description] = "Laser guns & Spaceships, maybe some sand worms too"
        }

        val updatedSciFi = Genres.select(Genres.name, Genres.description).where { Genres.id eq scienceFictionGenreId }.single()
        println("Updated Sci-Fi: ${updatedSciFi[Genres.name]} - ${updatedSciFi[Genres.description]}")

We can add a description to our Science Fiction Genre using a simple update clause and a lambda to change the properties that we wish to change.

//DELETE
        val knitting: EntityID<Int> = Genres.insert {
            it[name] = "Cyberpunk Knitting"
            it[description] = "A genre doomed to never catch on"
        } get Genres.id

        val genreCountBefore = Genres.selectAll().count()
        println("There are $genreCountBefore genres")

        Genres.deleteWhere { Genres.id eq knitting }

        val genreCountAfter = Genres.selectAll().count()
        println("There are $genreCountAfter genres")

Lastly delete's can be accomplished using the .deleteWhere function.
As you can see from these examples Exposed's DSL and api make most operations you might want to do to be pretty easy and since its all typed you get really nice auto complete within the IDE.

More tables, More Queries

Let's expand our schema now and do some more complex queries and operations.
First we'll need to add another Exposed module to our project because we're going to begin storing dates in our database. Exposed gives you several options for doing this. You can choose from a module that uses java's time package, jodatime, or the kotlinx-datetime package. For this post we'll go with the module that works with kotlinx-datetime


 implementation("org.jetbrains.exposed:exposed-kotlin-datetime:$exposedVersion")

Once we've done that let's create a new file that we'll call Schema.kt and store all of our table declarations there

package dev.bltucker

import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.ReferenceOption
import org.jetbrains.exposed.sql.kotlin.datetime.date

object Books : IntIdTable() {
    val title = varchar("title", 255)
    val isbn = varchar("isbn", 13).uniqueIndex()
    val price = decimal("price", precision = 10, scale = 2)
    val publishDate = date("publish_date")
    val authorId = reference("author_id", Authors)
    val genreId = reference("genre_id", Genres)
}

object Authors : IntIdTable() {
    val name = varchar("name", 100)
    val biography = text("biography").nullable()
}

object Genres : IntIdTable() {
    val name = varchar("name", 50).uniqueIndex()
    val description = text("description").nullable()
}

object Users : IntIdTable() {
    val username = varchar("username", 50).uniqueIndex()
    val email = varchar("email", 100).uniqueIndex()
    val registrationDate = date("registration_date")
}

object UserBooks : IntIdTable() {
    val userId = reference("user_id", Users, onDelete = ReferenceOption.CASCADE)
    val bookId = reference("book_id", Books, onDelete = ReferenceOption.CASCADE)
    val purchaseDate = date("purchase_date")
}

Let's look at a couple of the these declarations to call out some of the new things we are seeing.

val publishDate = date("publish_date")

Our new package is what enables us to store a date in a column declaration.

//Books
val authorId = reference("author_id", Authors)
val genreId = reference("genre_id", Genres)


//UserBooks
 val userId = reference("user_id", Users, onDelete = ReferenceOption.CASCADE)
 val bookId = reference("book_id", Books, onDelete = ReferenceOption.CASCADE)

reference let's us setup foreign keys and relationships to other tables in our schema and in the UserBooks declaration you can see that a reference also gives you options for cascading on deletes and updates. All pretty normal stuff that you would expect from a robust and complete ORM.

Let's look at how we might accomplish a join in Exposed's DSL by getting all books along with their authors and genres

val booksWithDetails = (Books innerJoin Authors innerJoin Genres)
            .select(Books.title, Authors.name, Genres.name, Books.price)


        println("Books with details:")
        booksWithDetails.forEach {
            println("${it[Books.title]} by ${it[Authors.name]} (${it[Genres.name]}) - $${it[Books.price]}")
        }

Pretty straight forward and the select function lets us grab just the columns that we care about all in an easy and typesafe manner.

In a similar manner we could easily ask for all the books purchased by a specific user like this:


val userPurchases = (UserBooks innerJoin Books innerJoin Users)
            .select(Users.username, Books.title, UserBooks.purchaseDate)
            .where { Users.id eq userId }

If we want to run some kind of aggregate query, like finding the average price of a book in our bookstore we can do this:


val avgPrice = Books.select(Books.price.avg()).single()[Books.price.avg()]
        println("\nAverage book price: $${avgPrice?.setScale(2)}")

Finally, thanks to our reference and our cascading options, if we delete a User Exposed will make sure to also delete their purchases from our book store UserBooks table.


//Delete a user (cascading delete will remove their purchases)
Users.deleteWhere { Users.id eq userId }

val remainingUsers = Users.selectAll().count()
println("\nRemaining users after deletion: $remainingUsers")

Raw Queries

So far everything we've done has been with the Exposed DSL and its apis, but sometimes you need to just write some raw sql and this is where some ORMs will fumble the ball and why alot of devs will say that they hate ORMs.
So let's take a look at what exposed gives us in this arena.

Here's an example of how we can write a raw sql query using Exposed when we need to do something particularlly nasty or address a performance issue manually.


val userInput = "Fantasy" 
        
val query = """
    SELECT b.title, a.name 
    FROM Books b 
    JOIN Authors a ON b.author_id = a.id 
    WHERE b.genre = ?
"""

val result = Table.rawQuery(query, userInput)

result.forEach {
    println("Book: ${it[0]}, Author: ${it[1]}")
}

You'll want to make use of the ? placeholder when you perform raw queries to protect yourself against sql injection. Failure to use Exposed's placeholder functionality could leave you vulnerable, but on a whole this is a pretty easy api to use that let's you write the SQL that you need without much fuss.

Exposed will also let us define custom sql functions that we can take advanatge of when writing queries. Let's take a look at an example of that


class YEAR(expr: Expression<*>): CustomFunction<Int>("YEAR", IntegerColumnType(), expr)

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

    transaction {
        Books.selectAll().where { YEAR(Books.publishDate) eq 2022 }.forEach {
            println("Book published in 2022: ${it[Books.title]}")
        }
    }
}

Here we define a custom fucntion that we call year which will take an integer as an argument and then use it to find all of the books that were published in the year 2022.

Speaking of custom SQL Functions, what about stored procedures? Exposed provides us with a way to execute those too. Its not as pretty as what we get with the DSL but its still usable.
In this example we have a stored procedure named get_author_books which will take an author ID and then return the names of the books they've written.

fun main() {
    Database.connect("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
    
    transaction{
      //create our stored procedure
        exec("""
            CREATE ALIAS get_author_books AS $$
            ResultSet getAuthorBooks(Connection conn, int authorId) throws SQLException {
                PreparedStatement stmt = conn.prepareStatement(
                    "SELECT title FROM Books WHERE author_id = ?"
                );
                stmt.setInt(1, authorId);
                return stmt.executeQuery();
            }
            $$
        """) 
    }

    transaction {
      
      //use it
        val authorId = 1
        val connection = connection()
        val statement = connection.prepareCall("{call get_author_books(?)}")
        statement.setInt(1, authorId)

        val results = statement.executeQuery()

        println("Books by author $authorId:")
        while (results.next()) {
            println("- ${results.getString("title")}")
        }

        results.close()
        statement.close()
    }
}

Json Support

Let's pretend we've decided to add a json column to our books table for storing a bunch of meta data about our book. Can Exposed help us handle that? The answer is yes!

First add the json module as a dependency along with kotlin serialization

implementation("org.jetbrains.exposed:exposed-json:$exposedVersion")
implementation("org.jetbrains.kotlinx:kotlinx-serialization-json:1.5.0")

Then add the serialization plugin to our gradle plugins

kotlin("plugin.serialization") version "1.8.20"

Once we've done that we can use it in a table declaration like this:

val format = Json { prettyPrint = true }

@Serializable
data class BookMetadata(val tags: List<String>, val editions: List<String>, val translations: List<String>)


object Books : IntIdTable() {
    val title = varchar("title", 255)
    val isbn = varchar("isbn", 13).uniqueIndex()
    val price = decimal("price", precision = 10, scale = 2)
    val publishDate = date("publish_date")
    val authorId = reference("author_id", Authors)
    val genreId = reference("genre_id", Genres)
    val metadata = json<BookMetadata>("metadata", format)
}

We've added a metadata column which will contain serialized json matching our BookMetadata class. We can use it when we insert data into our books table as so:

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

    transaction {
        addLogger(StdOutSqlLogger)
        // Create tables
        SchemaUtils.create(Books, Authors, Genres, Users, UserBooks)


        val fantasyId = Genres.insert {
            it[name] = "Fantasy"
            it[description] = "Fantasy literature"
        } get Genres.id

        val authorId = Authors.insert {
            it[name] = "J.R.R. Tolkien"
            it[biography] = "English writer and philologist"
        } get Authors.id

        val bookId = Books.insert {
            it[title] = "The Lord of the Rings"
            it[isbn] = "9780618640157"
            it[price] = BigDecimal("29.99")
            it[publishDate] = LocalDate(1954, 7, 29)
            it[this.authorId] = authorId
            it[genreId] = fantasyId
            it[metadata] = BookMetadata(
                tags = listOf("awesome", "classic"),
                editions = listOf(),
                translations = listOf()
            )
        } get Books.id

        val book = Books.selectAll().where { Books.id eq bookId }.single()
        val metaData = book[Books.metadata]

        println("Book Meta Data: $metaData")
    }

Closing thoughts

That's it for our test drive of Exposed. I'm still surprised I had not heard of this little ORM sooner but now that I've got to play with it I'm pretty impressed with it. I'll definitely consider it for my next random web project and I hope jetbrains continues to work on it and evolve it.

If you want to play around with it or use it in your next project take a look at the links below to give you some more info and examples that may be helpful!

You can find all the code from the blog post at this Github project here

Useful Links

Exposed Github

Exposed Documentation

Exposed Sample Projects

Exposed IDE Plugin

Blog Post Code