Working with tables

Defining tables

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

object StarWarsFilms : Table() {}

Exposed supports a variety of column types, including integer, varchar, bool, and more. Each column is defined by calling the appropriate method on the Table object.

The following example defines a table with an auto-incrementing integer id column and custom columns sequel_id, name, and director:

import org.jetbrains.exposed.sql.Table

const val MAX_VARCHAR_LENGTH = 50

object StarWarsFilmsTable : Table() {
    val id = integer("id").autoIncrement()
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_VARCHAR_LENGTH)
    val director = varchar("director", MAX_VARCHAR_LENGTH)
}

The IntIdTable class automatically generates an auto-incrementing integer id column, which serves as the primary key for the table. Therefore, there is no need to explicitly define the id column in the StarWarsFilmsTable object.

CREATE TABLE IF NOT EXISTS STARWARSFILMS
(ID INT AUTO_INCREMENT NOT NULL,
 SEQUEL_ID INT NOT NULL,
 "name" VARCHAR(50) NOT NULL,
 DIRECTOR VARCHAR(50) NOT NULL)

Configuring a custom table name

object StarWarsFilmsTable : Table() {
    val id = integer("id").autoIncrement()
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_VARCHAR_LENGTH)
    val director = varchar("director", MAX_VARCHAR_LENGTH)
}

CREATE TABLE IF NOT EXISTS STARWARSFILMS
(ID INT AUTO_INCREMENT NOT NULL,
 SEQUEL_ID INT NOT NULL,
 "name" VARCHAR(50) NOT NULL,
 DIRECTOR VARCHAR(50) NOT NULL)

To configure a custom name for a table, which will be used in actual SQL queries, pass it to the name parameter of the Table constructor.

object CustomStarWarsFilmsTable : Table("all_star_wars_films") {
    val id = integer("id").autoIncrement()
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_VARCHAR_LENGTH)
    val director = varchar("director", MAX_VARCHAR_LENGTH)
}

CREATE TABLE IF NOT EXISTS ALL_STAR_WARS_FILMS
(ID INT AUTO_INCREMENT NOT NULL,
SEQUEL_ID INT NOT NULL,
"name" VARCHAR(50) NOT NULL,
DIRECTOR VARCHAR(50) NOT NULL)

object StarWarsFilms : Table("\"all_star_wars_films\"") {
    val id = integer("id").autoIncrement()
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_VARCHAR_LENGTH)
    val director = varchar("director", MAX_VARCHAR_LENGTH)
}

CREATE TABLE IF NOT EXISTS "all_star_wars_films"
(ID INT AUTO_INCREMENT NOT NULL,
SEQUEL_ID INT NOT NULL,
"name" VARCHAR(50) NOT NULL,
DIRECTOR VARCHAR(50) NOT NULL)

Constraints

Nullable

The NOT NULL SQL constraint restricts the column to accept the null value. By default, Exposed applies this constraint to all the columns. To allow the column to be nullable, apply the .nullable() method to a definition of an appropriate column.

// SQL: POPULATION INT NULL
val population: Column<Int?> = integer("population").nullable()

Default

The DEFAULT SQL constraint provides the default value for the column. Exposed supports three methods for configuring default values:

For example, to configure the default value for the name column, use the following code:

// SQL: "NAME" VARCHAR(50) DEFAULT 'Unknown'
val name: Column<String> = varchar("name", 50).default("Unknown")

Exposed also supports marking a column as databaseGenerated if the default value of the column is not known at the time of table creation and/or if it depends on other columns. It makes it possible to omit setting a value for the column when inserting a new record, without getting an error. The value for the column can be set by creating a TRIGGER or with a DEFAULT clause, for example.

val name: Column<String> = varchar("name", 50).databaseGenerated()

Index

The INDEX SQL constraint makes traversing through tables quicker. Exposed supports the .index() method. It has six parameters, most of which are optional:

The simplest way to create an index is to use an extension function directly on a column. For example, to apply a non-unique INDEX constraint to the name column, use the following code:

val name = varchar("name", 50).index()

If the customIndexName parameter is not set, the name of the index is determined by the table and column names.

Complex indexes

If you have a frequent query for two columns, Exposed can perform it more efficiently. It creates a tree from the first column with the references to the second one. For example, to create a non-unique complex index on the name and population columns, paste the following code:

val indexName = index("indexName", false, *arrayOf(name, population))
// or inside an init block within the table object
init {
    index("indexName", isUnique = false, name, population)
}

Index with a custom type

Exposed also supports creating an index with a custom type. For example, to retrieve data from the name column faster with a hash function for traversing, use the following code:

val indexName = index("indexName", false, *arrayOf(name), indexType = "HASH")

init {
    index(functions = listOf(name.lowerCase(), address.substring(1, 5)))
    uniqueIndex(
        columns = arrayOf(name),
        functions = listOf(Coalesce(address, stringLiteral("*")))
    )
}

Operator expressions, like plus(), are also accepted by the functions parameter.

init {
    index(columns = arrayOf(name, flag)) { flag eq true }
    index(
        columns = arrayOf(
            name,
            population
        )
    ) { (name like "A%") and (population greaterEq 10) }
}

Access indices

Once a table has been created, the list of its indices can be accessed using the property Table.indices. Table indices are represented by the data class Index, so its properties can be checked in the following way:

Table.indices.map { it.indexName to it.createStatement().first() }

Unique

The UNIQUE SQL constraint restricts duplicates within this column. Exposed supports the .uniqueIndex() method which creates a unique index for the column. This method is the composition of UNIQUE and INDEX constraint, the quicker modification of UNIQUE constraint.

For example, to apply UNIQUE and INDEX constraint to the name column, use the following code:

val name = varchar("name", 50).uniqueIndex()

Primary Key

The PRIMARY KEY SQL constraint applied to a column means each value in that column identifies the row. This constraint is the composition of NOT NULL and UNIQUE constraints. To change the column set, add columns, or change the primary key name to a custom one, override this field of the table class.

For example, to define the name column as the primary key, use the following code. The "Cities_name" string will be used as the constraint name in the actual SQL query, if provided; otherwise a name will be generated based on the table's name.

override val primaryKey = PrimaryKey(name, name = "Cities_name")

CONSTRAINT Cities_name PRIMARY KEY ("name")

override val primaryKey = PrimaryKey(id, name)

CONSTRAINT pk_Cities PRIMARY KEY (ID, "name")

Except for CompositeIdTable, each available class in Exposed that inherits from IdTable has the primaryKey field automatically defined. For example, the IntIdTable by default has an auto-incrementing integer column, id, which is defined as the primary key.

An IdTable that requires a primary key with multiple columns can be defined using CompositeIdTable. In this case, each column that is a component of the table's ID should be identified by .entityId():

object Towns : CompositeIdTable("towns") {
    val areaCode = integer("area_code").autoIncrement().entityId()
    val latitude = decimal("latitude", 9, 6).entityId()
    val longitude = decimal("longitude", 9, 6).entityId()
    val name = varchar("name", 32)

    override val primaryKey = PrimaryKey(areaCode, latitude, longitude)
}

If any of the key component columns have already been marked by .entityId() in another table, they can still be identified using addIdColumn(). This might be useful for key columns that reference another IdTable:

object AreaCodes : IdTable<Int>("area_codes") {
    override val id = integer("code").entityId()
    override val primaryKey = PrimaryKey(id)
}

object Towns : CompositeIdTable("towns") {
    val areaCode = reference("area_code", AreaCodes)
    val latitude = decimal("latitude", 9, 6).entityId()
    val longitude = decimal("longitude", 9, 6).entityId()
    val name = varchar("name", 32)

    init {
        addIdColumn(areaCode)
    }

    override val primaryKey = PrimaryKey(areaCode, latitude, longitude)
}

Foreign Key

The FOREIGN KEY SQL constraint links two tables. A foreign key is a column from one table that refers to the primary key or columns with a unique index from another table. To configure a foreign key on a column, use reference() or optReference() methods. The latter lets the foreign key accept a null value. To configure a foreign key on multiple columns, use the foreignKey() function directly within an init block.

reference() and optReference() methods have several parameters:

Consider the following Citizens table. This table has the name and city columns. If the Cities table has configured the name column as the primary key, the Citizens table can refer to it by its city column, which is a foreign key. To configure such reference and make it nullable, use the optReference() method:

object Citizens : IntIdTable() {
    val name = varchar("name", 50)
    val city = optReference("city", Cities.name, onDelete = ReferenceOption.CASCADE)
}

If any Cities row will be deleted, the appropriate Citizens row will be deleted too.

If instead the Cities table has configured multiple columns as the primary key (for example, both id and name columns as in the above section), the Citizens table can refer to it by using a table-level foreign key constraint. In this case, the Citizens table must have defined matching columns to store each component value of the Cities table's primary key:

object Citizens : IntIdTable() {
    val name = varchar("name", 50)
    val cityId = integer("city_id")
    val cityName = varchar("city_name", 50)

    init {
        foreignKey(cityId, cityName, target = Cities.primaryKey)
    }
}

In the above example, the order of the referencing columns in foreignKey() must match the order of columns defined in the target primary key. If this order is uncertain, the foreign key can be defined with explicit column associations instead:

init {
    foreignKey(cityId to Cities.id, cityName to Cities.name)
}

Check

The CHECK SQL constraint checks that all values in a column match some condition. Exposed supports the .check() method. You apply this method to a column and pass the appropriate condition to it.

For example, to check that the name column contains strings that begin with a capital letter, use the following code:

// SQL: CONSTRAINT check_Cities_0 CHECK (REGEXP_LIKE("NAME", '^[A-Z].*', 'c')))
val name = varchar("name", 50).check { it regexp "^[A-Z].*" }

Some databases, like older MySQL versions, may not support CHECK constraints. For more information, consult the relevant documentation.

Creating tables

transaction {
    SchemaUtils.create(StarWarsFilms)
    //...
}

Working with tables﻿

Table Types﻿

Table﻿

IdTable﻿

Defining tables﻿

Configuring a custom table name﻿

Constraints﻿

Nullable﻿

Default﻿

Index﻿

Complex indexes﻿

Index with a custom type﻿

Access indices﻿

note

Unique﻿

Primary Key﻿

tip

Foreign Key﻿

Check﻿

Creating tables﻿