Exposed 0.56.0 Help

Get started with Exposed, an ORM framework for Kotlin

In this tutorial, you’ll learn how to use Exposed’s Domain-Specific Language (DSL) API to store and retrieve data in a relational database by building a simple console application.

By the end of this tutorial, you’ll be able to do the following:

  • Configure database connections using an in-memory database.

  • Define database tables using Exposed’s DSL.

  • Perform basic CRUD (Create, Read, Update, and Delete) operations on the database.

Prerequisites

Before starting this tutorial, ensure that you have the following installed on your machine:

Create a new project

First, you will need a basic Kotlin project setup to build upon. You can download a pre-initialized project or follow the steps below to generate a new project with Gradle.

  1. In a terminal window, navigate to the destination where you want to create your project and run the following commands to create a new folder and change directory into it:

    mkdir exposed-kotlin-app cd exposed-kotlin-app

  2. Run the gradle init task to initialize a new Gradle project:

    gradle init

    When prompted, select the following options:

    • 1: Application project type.

    • 2: Kotlin implementation language.

    For the other questions, press enter to use the default values. The output will look like the following:

    Select type of build to generate: 1: Application 2: Library 3: Gradle plugin 4: Basic (build structure only) Enter selection (default: Application) [1..4] Select implementation language: 1: Java 2: Kotlin 3: Groovy 4: Scala 5: C++ 6: Swift Enter selection (default: Java) [1..6] 2 Enter target Java version (min: 7, default: 21): Project name (default: exposed-kotlin-app): Select application structure: 1: Single application project 2: Application and library project Enter selection (default: Single application project) [1..2] Select build script DSL: 1: Kotlin 2: Groovy Enter selection (default: Kotlin) [1..2] Select test framework: 1: kotlin.test 2: JUnit Jupiter Enter selection (default: kotlin.test) [1..2] Generate build using new APIs and behavior (some features may change in the next minor release)? (default: no) [yes, no] > Task :init To learn more about Gradle by exploring our Samples at https://docs.gradle.org/8.8/samples/sample_building_kotlin_applications.html BUILD SUCCESSFUL in 28s 1 actionable task: 1 executed

  3. Once the project has been initialized, open the project folder in your IDE. To open the project in IntelliJ IDEA, use the following command:

    idea .

Add dependencies

Before you start using Exposed, you need to provide dependencies to your project.

  1. Navigate to the gradle/libs.versions.toml file and define the Exposed version and libraries:

    [versions] //... exposed = "0.56.0" [libraries] //... exposed-core = { module = "org.jetbrains.exposed:exposed-core", version.ref = "exposed" } exposed-jdbc = { module = "org.jetbrains.exposed:exposed-jdbc", version.ref = "exposed" }

    • The exposed-core module provides the foundational components and abstractions needed to work with databases in a type-safe manner and includes the DSL API.

    • The exposed-jdbc module is an extension of the exposed-core module that adds support for Java Database Connectivity (JDBC).

  2. Navigate to the app/build.gradle.kts file and add the Exposed and H2 database modules into the dependencies block:

    dependencies { //... implementation(libs.exposed.core) implementation(libs.exposed.jdbc) implementation("com.h2database:h2:2.2.224") //... }

  3. In intelliJ IDEA, click on the notification Gradle icon (intelliJ IDEA gradle icon) on the right side of the editor to load Gradle changes.

Configure a database connection

Every database access using Exposed is started by obtaining a connection and creating a transaction. To configure the database connection, use the Database.connect() function.

  1. Navigate to app/src/main/kotlin/org/example/ and open the App.kt file.

  2. Replace the contents of the App.kt file with the following implementation:

    package org.example import org.jetbrains.exposed.sql.* fun main() { Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver") }

    The Database.connect() function creates an instance of a class that represents the database and takes two or more parameters. In this case, the connection URL and the driver.

    • jdbc:h2:mem:test is the database URL to connect to:

      • jdbc specifies that this is a JDBC connection.

      • h2 indicates that the database is an H2 database.

      • mem specifies that the database is in-memory, meaning the data will only exist in memory and will be lost when the application stops.

      • test is the name of the database.

    • org.h2.Driver specifies the H2 JDBC driver to be used for establishing the connection.

    With this, you've added Exposed to your Kotlin project and configured a database connection. You're now ready to define your data model and engage with the database using Exposed's DSL API.

Define a table object

In Exposed, a database table is represented by an object inherited from the Table class. To define the table object, follow the steps below.

  1. In the app/src/main/kotlin/org/example/ folder, create a new Task.kt file.

  2. Open Task.kt and add the following table definition:

    import org.jetbrains.exposed.sql.Table const val MAX_VARCHAR_LENGTH = 128 object Tasks : Table("tasks") { val id = integer("id").autoIncrement() val title = varchar("name", MAX_VARCHAR_LENGTH) val description = varchar("description", MAX_VARCHAR_LENGTH) val isCompleted = bool("completed").default(false) }

    In the Table constructor, passing the name tasks configures a custom name for the table. Keep in mind that if no custom name is specified, Exposed will generate one from the class name, which might lead to unexpected results.

    Within the Tasks object, four columns are defined:

    • id of type Int is defined with the integer() method. The autoIncrement() function indicates that this column will be an auto-incrementing integer, typically used for primary keys.

    • title and description of type String are defined with the varchar() method.

    • isCompleted of type Boolean is defined with the bool() method. Using the default() function, you configure the default value to false.

    At this point, you have defined a table with columns, which essentially creates the blueprint for the Tasks table.

    To now create and populate the table within the database, you need to open a transaction.

Create and query a table

With Exposed’s DSL API, you can interact with a database using a type-safe syntax similar to SQL. Before you start executing database operations, you must open a transaction.

A transaction is represented by an instance of the Transaction class, within which you can define and manipulate data using its lambda function. Exposed will automatically manage the opening and closing of the transaction in the background, ensuring seamless operation.

  • Navigate back to the App.kt file and add the following transaction function:

    package org.example import Tasks import org.jetbrains.exposed.sql.* import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq import org.jetbrains.exposed.sql.transactions.transaction fun main() { Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver") transaction { SchemaUtils.create(Tasks) val taskId = Tasks.insert { it[title] = "Learn Exposed" it[description] = "Go through the Get started with Exposed tutorial" } get Tasks.id val secondTaskId = Tasks.insert { it[title] = "Read The Hobbit" it[description] = "Read the first two chapters of The Hobbit" it[isCompleted] = true } get Tasks.id println("Created new tasks with ids $taskId and $secondTaskId.") Tasks.select(Tasks.id.count(), Tasks.isCompleted).groupBy(Tasks.isCompleted).forEach { println("${it[Tasks.isCompleted]}: ${it[Tasks.id.count()]} ") } } }

    Let's break down the code and go over each section.

    First, you create the tasks table using the create() method from SchemaUtils. The SchemaUtils object holds utility methods for creating, altering, and dropping database objects.

    Once the table has been created, you use the Table extension method insert() to add two new Task records.

    val taskId = Tasks.insert { it[title] = "Learn Exposed" it[description] = "Go through the Get started with Exposed tutorial" } get Tasks.id val secondTaskId = Tasks.insert { it[title] = "Read The Hobbit" it[description] = "Read the first two chapters of The Hobbit" it[isCompleted] = true } get Tasks.id

    Within the insert block, you set the values for each column by using the it parameter. Exposed will translate the functions into the following SQL queries:

    INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (FALSE, 'Go through the Get started with Exposed tutorial', 'Learn Exposed') INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (TRUE, 'Read the first two chapters of The Hobbit', 'Read The Hobbit')

    Because the insert() function returns an InsertStatement, by using the get() method after the insert operation you retrieve the autoincremented id value of the newly added row.

    With the select() extension function you then create a query to count the number of rows and to retrieve the isCompleted value for each row in the table.

    Tasks.select(Tasks.id.count(), Tasks.isCompleted).groupBy(Tasks.isCompleted).forEach { println("${it[Tasks.isCompleted]}: ${it[Tasks.id.count()]} ") }

    Using groupBy() groups the results of the query by the isCompleted column, which means it will aggregate the rows based on whether they are completed or not. The expected SQL query looks like this:

    SELECT COUNT(TASKS.ID), TASKS.COMPLETED FROM TASKS GROUP BY TASKS.COMPLETED

    It is important to note that the query will not be executed until you call a function that iterates through the result, such as forEach(). In this example, for each group we print out the isCompleted status and the corresponding count of tasks.

Before you test the code, it would be handy to be able to inspect the SQL statements and queries Exposed sends to the database. For this, you need to add a logger.

Enable logging

At the beginning of your transaction block, add the following line to enable SQL query logging:

transaction { // print sql to std-out addLogger(StdOutSqlLogger) // ... }

Run the application

In IntelliJ IDEA, click on the run button (intelliJ IDEA run icon) to start the application.

The application will start in the Run tool window at the bottom of the IDE. There you will be able to see the SQL logs along with the printed results:

SQL: SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'MODE' SQL: CREATE TABLE IF NOT EXISTS TASKS (ID INT AUTO_INCREMENT NOT NULL, "name" VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(128) NOT NULL, COMPLETED BOOLEAN DEFAULT FALSE NOT NULL) SQL: INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (FALSE, 'Go through the Get started with Exposed tutorial', 'Learn Exposed') SQL: INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (TRUE, 'Read the first two chapters of The Hobbit', 'Read The Hobbit') Created new tasks with ids 1 and 2. SQL: SELECT COUNT(TASKS.ID), TASKS.COMPLETED FROM TASKS GROUP BY TASKS.COMPLETED false: 1 true: 1 Process finished with exit code 0

Update and delete a task

Let’s extend the app’s functionality by updating and deleting the same task.

  1. In the same transaction() function, add the following code to your implementation:

    transaction { // ... // Update a task Tasks.update({ Tasks.id eq taskId }) { it[isCompleted] = true } val updatedTask = Tasks.select(Tasks.isCompleted).where(Tasks.id eq taskId).single() println("Updated task details: $updatedTask") // Delete a task Tasks.deleteWhere { id eq secondTaskId } println("Remaining tasks: ${Tasks.selectAll().toList()}") }

    Here's the breakdown:

    In the Tasks.update() function, you specify the condition to find the task with id equal to the one of the previously inserted task. If the condition is met, the isCompleted field of the found task is set to true.

    Tasks.update({ Tasks.id eq taskId }) { it[isCompleted] = true }

    Unlike the insert() function, update() returns the number of updated rows. To then retrieve the updated task, you use the select() function with the where condition to only select the tasks with id equal to taskId.

    val updatedTask = Tasks.select(Tasks.isCompleted).where(Tasks.id eq taskId).single()

    Using the single() extension function initiates the statement and retrieves the first result found.

    The deleteWhere() function, on the other hand, deletes the task with the specified condition.

    Tasks.deleteWhere { id eq secondTaskId }

    Similarly to update(), it returns the number of rows that have been deleted.

  2. In IntelliJ IDEA, click the rerun button (intelliJ IDEA rerun icon) to restart the application.

    You should now see the following result:

    SQL: SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'MODE' SQL: CREATE TABLE IF NOT EXISTS TASKS (ID INT AUTO_INCREMENT NOT NULL, "name" VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(128) NOT NULL, COMPLETED BOOLEAN DEFAULT FALSE NOT NULL) SQL: INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (FALSE, 'Go through the Get started with Exposed tutorial', 'Learn Exposed') SQL: INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (TRUE, 'Read the first two chapters of The Hobbit', 'Read The Hobbit') Created new tasks with ids 1 and 2. SQL: SELECT COUNT(TASKS.ID), TASKS.COMPLETED FROM TASKS GROUP BY TASKS.COMPLETED false: 1 true: 1 SQL: UPDATE TASKS SET COMPLETED=TRUE WHERE TASKS.ID = 1 SQL: SELECT TASKS.COMPLETED FROM TASKS WHERE TASKS.ID = 1 Updated task details: Tasks.completed=true SQL: DELETE FROM TASKS WHERE TASKS.ID = 2 SQL: SELECT TASKS.ID, TASKS."name", TASKS.DESCRIPTION, TASKS.COMPLETED FROM TASKS Remaining tasks: [Tasks.id=1, Tasks.name=Learn Exposed, Tasks.description=Go through the Get started with Exposed tutorial, Tasks.completed=true] Process finished with exit code 0

Next steps

Great job! You have now implemented a simple console application that uses Exposed to fetch and modify task data from an in-memory database. Now that you’ve covered the basics, you are ready to dive deep into the DSL API.

Last modified: 30 October 2024
Exposed, an ORM framework for KotlinModules