Working with tables

In this topic, we will explain how to define, configure, and create tables. All examples use the H2 database to generate SQL.

Table Types

Table

In Exposed, the Table class is the core abstraction for defining database tables. This class provides methods to define various column types, constraints, and other table-specific properties.

Table is located in the org.jetbrains.exposed.sql package of the exposed-core module.

IdTable

Apart from the core Table class, Exposed provides the base IdTable class and its subclasses through the DAO API.

The IdTable class extends Table and is designed to simplify the definition of tables that use a standard id column as the primary key. These tables can be declared without explicitly including the id column, as IDs of the appropriate type are automatically generated when creating new table rows.

IdTable and its subclasses are located in the org.jetbrains.exposed.dao.id package of the exposed-core module.

For more information on IdTable types, see DAO Table Types.

Defining tables

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

object Cities : 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 a string name column:

        import org.jetbrains.exposed.sql.Table

        object Cities : Table("cities") {
            val id = integer("id").autoIncrement()
            val name = varchar("name", 50)
        }
    

                    import org.jetbrains.exposed.dao.id.IntIdTable

                    object Cities : IntIdTable("cities") {
                        val name = varchar("name", 50)
                    }
                

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 Cities object.

Creating the table with the above definition will result in the following SQL equivalent:

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

                    CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(50) NOT NULL)
                

Configuring a custom table name

By default, Exposed generates the table name from the full class name.

If the object name contains a 'Table' suffix, Exposed will omit the suffix from the generated table name:

                object CitiesTable : Table() {
                    val id = integer("id")
                    val name = varchar("name", 50)
                }
            

                CREATE TABLE IF NOT EXISTS CITIES (ID INT NOT NULL, "name" 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 Cities : Table("all_cities") {
                    val id = integer("id")
                    val name = varchar("name", 50)
                }
            

                CREATE TABLE IF NOT EXISTS ALL_CITIES (ID INT NOT NULL, "name" VARCHAR(50) NOT NULL)
            

Some databases, like H2, fold unquoted identifiers to upper case. To keep table name case-sensitivity, manually quote the provided argument:

                object Cities : Table("\"all_cities\"") {
                    val id = integer("id")
                    val name = varchar("name", 50)
                }
            

                CREATE TABLE IF NOT EXISTS "all_cities" (ID INT NOT NULL, "name" VARCHAR(50) NOT NULL)
            

Depending on what DBMS you use, the types of columns could be different in actual SQL queries.

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.

For example, to make the population column nullable, use the following code:

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

default(defaultValue: T) accepts a value with a type of the column.
defaultExpression(defaultValue: Expression<T>) accepts an expression.
clientDefault(defaultValue: () -> T) accepts a function.

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.

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:

val customIndexName: String? = null is a custom name for the index, which will be used in actual SQL queries.
val unique: Boolean defines whether the index is unique or not.
val columns: List<Column<*>> defines a column set.
val functions: List<ExpressionWithColumnType<*>>? = null defines functional key parts.
val indexType: String? = null is a custom type. Can be "BTREE" or "HASH".
val filterCondition: (SqlExpressionBuilder.() -> Op<Boolean>)? = null defines a condition used to create a partial index.

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 parameter customIndexName is not set, the name of the index is determined by the table and column names.

Also, Exposed supports 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)
                }
            

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

Some databases support functional key parts that index expressions instead of columns directly:

                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.

Some databases support creating a partial index by defining a filter expression to improve querying performance. The created index will only contain entries for the table rows that match this predicate:

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

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 manner, for example:

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

It is also possible to define a primary key on a table using multiple columns:

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 foreignKey() directly within an init block.

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

name: String: A name for the foreign key column, which will be used in actual SQL queries.
ref: Column<T>: A target column from another parent table.
onDelete: ReferenceOption? = null: An action for when a linked row from a parent table will be deleted.
onUpdate: ReferenceOption? = null: An action for when a value in a referenced column will be changed.
fkName: String? = null: A name for the foreign key constraint.

Enum class ReferenceOption has five values:

RESTRICT: An option that restricts changes on a referenced column, and the default option for most dialects.
NO_ACTION: The same as RESTRICT in some, but not all, databases, and the default option for Oracle and SQL Server dialects.
CASCADE: An option that allows updating or deleting the referring rows.
SET_NULL: An option that sets the referring column values to null.
SET_DEFAULT: An option that sets the referring column values to the default value.

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

To create a table within a database, you need to use the SchemaUtils.create() method within a transaction:

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

This will generate the SQL necessary to create the table based on your definition.

Last modified: 30 October 2024