### 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:

- The latest Gradle distribution.

- A Java Development Kit (JDK), version 8 or higher.

- An integrated development environment (IDE), such as IntelliJ IDEA Ultimate.

  We recommend that you install IntelliJ IDEA Ultimate which comes with built-in database tools and the Exposed plugin for code completion and inspections. However, you can use another IDE of your choice.

## 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 () 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.

   Note that invoking `Database.connect()` only configures the connection settings, but it does not immediately establish a connection with the database. The actual connection to the database will be established later when a database operation is performed.

   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 () 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 () 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

It might be useful to know that if a second `transaction` is opened after the first one, you will find that the table and its data has been lost even though the app hasn't stopped. This is expected behavior in H2 databases when managing connections and transactions.

To keep the database open, add `;DB_CLOSE_DELAY=-1` to the database URL:

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

## 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