CRUD operations

CRUD stands for Create Read Update Delete, which are four basic operations for a database to support. This section shows how to perform SQL CRUD operations using Kotlin DSL.

Create

Exposed provides several functions to insert rows into a table:

`insert`

To create a new table row, use the insert query. If the same row already exists in the table, it throws an exception.

                Cities.insert {
                    it[name] = "St. Petersburg"
                    it[country] = Country.RUSSIA
                    it[population] = 500
                }
            

The example corresponds to the following SQL statement:

                INSERT INTO CITIES (COUNTRY, "NAME", POPULATION) VALUES ('RUSSIA', 'St. Petersburg', 300)
            

`insertAndGetId`

To add a new row and return its ID, use insertAndGetId. If the same row already exists in the table, it throws an exception.

                val id = Cities.insertAndGetId {
                    it[name] = "St. Petersburg"
                    it[country] = Country.RUSSIA
                    it[population] = 500
                }
            

                INSERT INTO CITIES (COUNTRY, "NAME", POPULATION) VALUES ('RUSSIA', 'St. Petersburg', 300)
            

`insertIgnore`

To allow insert statements to be executed without throwing any errors, use insertIgnore. This may be useful, for example, when insertion conflicts are possible:

                StarWarsFilms.insert {
                    it[sequelId] = 8 // column pre-defined with a unique index
                    it[name] = "The Last Jedi"
                    it[director] = "Rian Johnson"
                }

                StarWarsFilms.insertIgnore {
                    it[sequelId] = 8
                    it[name] = "The Rise of Skywalker"
                    it[director] = "JJ Abrams"
                }
            

If insert was used instead of insertIgnore, this would throw a constraint violation exception Instead, this new row is ignored and discarded.

`insertIgnoreAndGetId`

insertIgnoreAndGetId adds a new row and returns its ID. If the same row already exists in the table, it ignores it and doesn't throw an exception.

                val id = Cities.insertIgnoreAndGetId {
                    it[name] = "St. Petersburg"
                    it[country] = Country.RUSSIA
                    it[population] = 500
                }
            

                INSERT IGNORE INTO CITIES (COUNTRY, "NAME", POPULATION) VALUES ('RUSSIA', 'St. Petersburg', 300)            

`batchInsert`

batchInsert allows mapping a list of entities into table rows in a single SQL statement. It is more efficient than using the insert query for each row as it initiates only one statement.

The following example uses a simple list:

                val cityNames = listOf("Paris", "Moscow", "Helsinki")
                val allCitiesID = cities.batchInsert(cityNames) { name ->
                    this[cities.name] = name
                }
            

Here is an example that uses a list of data class instances:

                data class SWFilmData(val sequelId: Int, val name: String, val director: String)

                transaction {
                    // ...
                    val films = listOf(
                        SWFilmData(5, "The Empire Strikes Back", "Irvin Kershner"),
                        SWFilmData(4, "A New Hope", "George Lucas"),
                        SWFilmData(7, "The Force Awakens", "JJ Abrams")
                    )

                    StarWarsFilms.batchInsert(films) { (id, name, director) ->
                        this[StarWarsFilms.sequelId] = id
                        this[StarWarsFilms.name] = name
                        this[StarWarsFilms.director] = director
                    }

                    StarWarsFilms.selectAll().count() // 3
                }
            

If you don't need to get the newly generated values, such as the auto-incremented ID, set the shouldReturnGeneratedValues parameter to false. This increases the performance of batch inserts by batching them in chunks, instead of always waiting for the database to synchronize the newly inserted object state.

If you want to check if rewriteBatchedInserts and batchInsert are working correctly, you need to enable JDBC logging for your driver. This is necessary, as Exposed will always show the non-rewritten multiple inserts. For more information, see how to enable logging in PostgresSQL.

Read

`select`

The select() function allows you to select specific columns or/and expressions.

                val filmAndDirector = StarWarsFilms.select(StarWarsFilms.name, StarWarsFilms.director).map {
                    it[StarWarsFilms.name] to it[StarWarsFilms.director]
                }
            

If you want to select only distinct value then use withDistinct() function:

                val directors =
                    StarWarsFilms.select(StarWarsFilms.director).where { StarWarsFilms.sequelId less 5 }.withDistinct()
                        .map {
                            it[StarWarsFilms.director]
                        }
            

`selectAll`

To retrieve records from a table, use the selectAll() method:

                val query: Query = StarWarsFilms.selectAll().where { StarWarsFilms.sequelId eq 8 }
            

Query inherits Iterable so it is possible to traverse it using map() or forEach():

                StarWarsFilms.selectAll().where { StarWarsFilms.sequelId eq 8 }.forEach {
                    println(it[StarWarsFilms.name])
                }
            

Update

To update a record, use the update() function. By default, it returns the number of updated rows.

            val updatedCount = StarWarsFilms.update({ StarWarsFilms.name like "Episode%" }) {
                it[StarWarsFilms.director] = "George Lucas"
            }
        

To update a column value using an expression, such as an increment, use update function or setter:

            StarWarsFilms.update({ StarWarsFilms.sequelId eq 8 }) {
                it.update(StarWarsFilms.sequelId, StarWarsFilms.sequelId + 1)
                // or
                it[StarWarsFilms.sequelId] = StarWarsFilms.sequelId + 1
            }
        

Delete

`deleteWhere`

To delete records and return the count of deleted rows, use the deleteWhere function.

StarWarsFilms.deleteWhere { StarWarsFilms.sequelId eq 8 }

`deleteIgnoreWhere`

To delete records while ignoring any possible errors that occur during the process, use the deleteIgnoreWhere function. The function will return the count of deleted rows.

StarWarsFilms.deleteIgnoreWhere { StarWarsFilms.sequelId eq 8 }

`deleteAll`

To deletes all rows in a table and return the count of deleted rows, use the deleteAll function.

StarWarsFilms.deleteAll { StarWarsFilms.sequelId eq 8 }

Join `delete`

To delete records from a table in a join relation, use the delete function with a Join as its receiver. Provide the specific table from which records should be deleted as the argument to the parameter targetTable.

                val join = StarWarsFilms innerJoin Actors
                join.delete(Actors) { Actors.sequelId greater 2 }
            

Insert Or Update

Insert or update (Upsert) is a database operation that either inserts a new row or updates an existing row if a duplicate constraint already exists. The supported functionality of upsert() is dependent on the specific database being used. For example, MySQL's INSERT ... ON DUPLICATE KEY UPDATE statement automatically assesses the primary key and unique indices for a duplicate value, so using the function in Exposed would look like this:

            // inserts a new row
            StarWarsFilms.upsert {
                it[sequelId] = 9 // column pre-defined with a unique index
                it[name] = "The Rise of Skywalker"
                it[director] = "Rian Johnson"
            }
            // updates existing row with the correct [director]
            StarWarsFilms.upsert {
                it[sequelId] = 9
                it[name] = "The Rise of Skywalker"
                it[director] = "JJ Abrams"
            }
        

If none of the optional arguments are provided to upsert(), and an onUpdate() block is omitted, the statements in the body block will be used for both the insert and update parts of the operation. This means that, for example, if a table mapping has columns with default values and these columns are omitted from the body block, the default values will be used for insertion as well as for the update operation.

Using another example, PostgreSQL allows more control over which key constraint columns to check for conflict, whether different values should be used for an update, and whether the update statement should have a WHERE clause:

            StarWarsFilms.upsert(
                StarWarsFilms.sequelId,
                onUpdate = { it[StarWarsFilms.sequelId] = StarWarsFilms.sequelId + 1 },
                where = { StarWarsFilms.director like stringLiteral("JJ%") }
            ) {
                it[sequelId] = 9
                it[name] = "The Rise of Skywalker"
                it[director] = "JJ Abrams"
            }

            StarWarsFilms.upsert(
                onUpdate = {
                    it[StarWarsFilms.director] = concat(insertValue(StarWarsFilms.director), stringLiteral(" || "), StarWarsFilms.director)
                }
            ) {
                it[sequelId] = 9
                it[name] = "The Rise of Skywalker"
                it[director] = "Rian Johnson"
            }
        

If the update operation should be identical to the insert operation except for a few columns, then onUpdateExclude should be provided an argument with the specific columns to exclude. This parameter could also be used for the reverse case when only a small subset of columns should be updated but duplicating the insert values is tedious:

            // on conflict, all columns EXCEPT [director] are updated with values from the lambda block
            StarWarsFilms.upsert(onUpdateExclude = listOf(StarWarsFilms.director)) {
                it[sequelId] = 9
                it[name] = "The Rise of Skywalker"
                it[director] = "JJ Abrams"
            }

            // on conflict, ONLY column [director] is updated with value from the lambda block
            StarWarsFilms.upsert(
                onUpdateExclude = StarWarsFilms.columns - setOf(StarWarsFilms.director)
            ) {
                it[sequelId] = 9
                it[name] = "The Rise of Skywalker"
                it[director] = "JJ Abrams"
            }
        

If a specific database supports user-defined key columns and none are provided, the table's primary key is used. If there is no defined primary key, the first unique index is used. If there are no unique indices, each database handles this case differently, so it is strongly advised that keys are defined to avoid unexpected results.

Insert From Select

If you want to use the INSERT INTO ... SELECT SQL clause try the function Table.insert(Query):

            val substring = users.name.substring(1, 2)
            cities.insert(users.select(substring).orderBy(users.id).limit(2))
        

By default, it will try to insert into all non auto-increment Table columns in the order they are defined in the Table instance. If you want to specify columns or change the order, provide a list of columns as the second parameter:

            val userCount = users.selectAll().count()
            users.insert(
                users.select(
                    stringParam("Foo"),
                    Random().castTo<String>(VarCharColumnType()).substring(1, 10)
                ), columns = listOf(users.name, users.id)
            )
        

Replace

The replace() method acts in a similar manner to an upsert(). The only difference is that if an insertion would violate a unique constraint, the existing row is deleted before the new row is inserted.

            object StarWarsFilms : Table() {
                val sequelId: Column<Int> = integer("sequel_id").uniqueIndex()
                val releaseYear: Column<Int> = integer("release_year")
                val name: Column<String> = varchar("name", 50)
                val director: Column<String> = varchar("director", 50)
                val rating: Column<Double> = double("rating").default(10.0)

                override val primaryKey = PrimaryKey(sequelId, releaseYear)
            }

            transaction {
                // ...
                // inserts a new row with default rating
                StarWarsFilms.replace {
                    it[sequelId] = 9
                    it[releaseYear] = 2019
                    it[name] = "The Rise of Skywalker"
                    it[director] = "JJ Abrams"
                }
                // deletes existing row and inserts new row with set [rating]
                StarWarsFilms.replace {
                    it[sequelId] = 9
                    it[releaseYear] = 2019
                    it[name] = "The Rise of Skywalker"
                    it[director] = "JJ Abrams"
                    it[rating] = 5.2
                }
            }
        

Unlike upsert(), none of the supporting databases allows a WHERE clause. Also, the constraints used to assess a violation are limited to the primary key and unique indexes, so there is no parameter for a custom key set.

The values specified in the statement block will be used for the insert statement, and any omitted columns are set to their default values, if applicable.

In the example above, if the original row was inserted with a user-defined rating and replace() was executed with a block that omitted the rating column, the newly inserted row would store the default rating value. This is because the old row was completely deleted first.

The REPLACE INTO ... SELECT SQL clause can be used by instead providing a query to replace():

            val allRowsWithLowRating: Query = StarWarsFilms.selectAll().where {
                StarWarsFilms.rating less 5.0
            }
            StarWarsFilms.replace(allRowsWithLowRating)
        

By default, it will try to insert into all non auto-increment Table columns in the order they are defined in the Table instance. If the columns need to be specified or the order should be changed, provide a list of columns as the second parameter:

            val oneYearLater = StarWarsFilms.releaseYear.plus(1)
            val allRowsWithNewYear: Query = StarWarsFilms.select(
                oneYearLater, StarWarsFilms.sequelId, StarWarsFilms.director, StarWarsFilms.name
            )
            StarWarsFilms.replace(
                allRowsWithNewYear,
                columns = listOf(
                    StarWarsFilms.releaseYear,
                    StarWarsFilms.sequelId,
                    StarWarsFilms.director,
                    StarWarsFilms.name
                )
            )
        

Retrieve the count of modified rows

Some databases return a count of the number of rows inserted, updated, or deleted by the CRUD operation. For insert, upsert, and replace, this value can be accessed using the statement class property insertedCount:

            val insertStatement = StarWarsFilms.insertIgnore {
                it[name] = "The Last Jedi"
                it[sequelId] = 8
                it[director] = "Rian Johnson"
            }
            val rowCount: Int = insertStatement.insertedCount
        

Return data from modified rows

Some databases allow the return of additional data every time a row is either inserted, updated, or deleted. This can be accomplished by using one of the following functions:

insertReturning()
upsertReturning()
updateReturning()
deleteReturning()

Each of them take a list of the required table columns as an argument. If not provided, all table columns will be returned by default:

            object Projects : Table("projects") {
                val title = varchar("title", 64)
                val budget = integer("budget")
                val created = datetime("created").defaultExpression(CurrentDateTime)
            }

            // returns all table columns by default
            val created: LocalDateTime = Projects.insertReturning {
                it[title] = "Project A"
                it[budget] = 100
            }.single()[Projects.created]

            val updatedBudgets: List<Int> = Projects.updateReturning(listOf(Projects.budget)) {
                it[budget] = Projects.budget.times(5)
            }.map {
                it[Projects.budget]
            }
        

Last modified: 25 September 2024

CRUD operations

Create

insert

insertAndGetId

insertIgnore

insertIgnoreAndGetId

batchInsert