Defining tables with Kotlin's Exposed

Originally written in Japanese. This is a translation of the same piece.

Introduction

When using Kotlin on the server, Spring and Ktor are the frameworks that come up as candidates. Spring is a web framework people have been comfortable with since the Java days. Ktor, by contrast, is a newer Kotlin-based web framework that makes use of Kotlin features such as coroutines.

Doma2 and others are well known as the ORM to use with Spring, but with Ktor, Exposed seems to be the popular choice. I have been building something with Ktor + Exposed recently and had occasion to define tables with Exposed, so here are my notes.

What this covers

• Defining tables with Exposed

What it does not cover

• How to install Exposed
• How to connect to a database
• Note: there are already several articles on those, so please refer to them. The tutorial is clear as well.

Environment

• Exposed 0.23.1

The content

Libraries used

• org.jetbrains.exposed: exposed-core
• org.jetbrains.exposed: exposed-dao
• org.jetbrains.exposed: exposed-jdbc
• org.jetbrains.exposed: exposed-java-time (*1)

*1 Exposed’s getting started guide only lists core, dao and jdbc. As covered below, if you want columns for things like created and updated timestamps, you will want to specify datetime, date or timestamp — and those are not included in core, dao or jdbc. So you need to add an implementation dependency on java-time, jodatime or similar separately.

An example table definition

UML

Here is a simple example. There is a companies table, and a company has one or more departments. A department has zero or more employees, and an employee may belong to several departments. Below is an ER diagram I put together quickly in PlantUML. Note that the primary keys are surrogate keys rather than natural keys, and since the relationship between employees and departments is many-to-many, there is a join table.

Note, tangentially: PlantUML was extremely handy for drawing ER diagrams and other UML, so if you have not come across it, this is a good excuse to look it up. The diagram above took about five minutes in PlantUML.

The implementation

Let us actually implement the tables above. First, the Companies table, with an overview in the comments.

// Tables are defined as objects. The name in Table("name") is the table's name in the DB.
object Companies: Table("companies") {
  // The name in type("name") is the column name in the table.
  // Using .autoincrement() increments by 1 automatically on creation.
  val id = long("id").autoIncrement()
  // For strings you can use char, varchar or text.
  // varchar requires you to specify the length.
  val name = varchar("name", 255)
  // datetime is used for the creation date. date and timestamp are also available.
  // date holds only the date, as yyyy-MM-dd; datetime holds yyyy-MM-dd HH:mm:ssSSSSSS.
  // .default() sets the default used when no value is explicitly supplied. Below, that is the current time.
  val createdAt = datetime("created_at").default(LocalDateTime.now())
  val updatedAt = datetime("updated_at").default(LocalDateTime.now())
  // The date of a logical delete.
  // Columns are not null unless specified otherwise; adding nullable() allows null.
  val deletedAt = datetime("deleted_at").nullable()

  // Overriding primaryKey lets you decide the table's primary key.
  override val primaryKey = PrimaryKey(id, name = "pk_company_id")
}

Defining something as long("id") produces a variable of type Column<T>. That corresponds to a column in the table.

Here I use Table and define primaryKey myself, but inheriting from IntIdTable or LongIdTable gives you a table that has an EntityID<Int> or EntityID<Long> as its id, already designated as the primary key.

Here are the other table definitions. created_at, updated_at and deleted_at are omitted as redundant.

object DepartmentsEmployees: Table("departments_employees") {
  val id = long("id").autoIncrement()
  // references() sets a foreign key. fkName = "" lets you give the foreign key any name you like.
  // You can explicitly attach constraints for update and delete of the foreign key.
  // The default is ReferenceOption.RESTRICT.
  // These govern the behaviour when a record holding a key referenced as a foreign key is updated or deleted.
  // For example, with RESTRICT a referenced record cannot be deleted until every referencing record is gone.
  val departmentsId = long("department_id").index("idx_department_id").references(Departments.id, fkName = "fk_department_id", onUpdate = ReferenceOption.CASCADE, onDelete = ReferenceOption.RESTRICT)
  val employeesId = long("employee_id").index().references(Employees.id, fkName = "fk_emploee_id")

  override val primaryKey = PrimaryKey(id, name = "pk_departments_employees_id")
}

object Departments: Table("departments") {
  val id = long("id").autoIncrement()
  // index creates an index. Supplying the name in index(name) gives it a name of your choosing.
  // Records are often pulled by following foreign keys, so indexing foreign keys seems to be common practice.
  // (Corrections welcome if I have this wrong.)
  val companyId = long("company_id").index("idx_company_id").references(Comapnies.id)

  val name = varchar("name", 255)
  override val primaryKey = PrimaryKey(id, name = "pk_department_id")
}

object Employees: Table("emploees") {
  val id = long("id").autoIncrement()

  val familyName = varchar("family_name", 255)
  val givenName = varchar("given_name", 255)
  override val primaryKey = PrimaryKey(id, name = "pk_employee_id")
}

A summary, plus a few things not covered above:

• references attaches a foreign key. onUpdated and onDeleted let you specify the foreign constraint explicitly (CASCADE, SET_NULL, RESTRICT, NO_ACTION).
• index creates an index. uniqueIndex() adds a unique constraint.
• (long("fkId").references("FkEmtity.id")).nullable() produces a nullable column that still carries a foreign constraint.

Those are my somewhat scattered notes on what I have learned so far about creating tables.

Summary

I did consider using Doma2 as the ORM, with Spring + Doma2, but its Kotlin support being experimental gave me pause. It also appears you have to define the DAO interfaces in Java.

Exposed is written 100% in Kotlin, which looked very attractive given that I wanted the whole project unified on Kotlin. My understanding of the DML side has not caught up yet, but so far it has been very intuitive to write, which I like. If you are doing server-side development on a JVM language, Ktor + Exposed strikes me as a reasonable option.

References

• Exposed - wiki
  • Exposed’s wiki. You will largely be developing with this page open.
• Exposed - GitHub
  • Exposed’s GitHub repository.
• PlantUML
  • PlantUML’s (presumably) official site.
• Doma2