Building SQL Statements

Exposed DSL provides various functions to perform database operations, such as CRUD operations. If you need to access the SQL generated by this DSL without executing it, Exposed provides this functionality through Statement.prepareSQL().

The following examples refer to the StarWarsFilmsTable defined previously, and any generated SQL is based on output from the H2 database.

Read operations

An instance of a Query is not executed until its results are consumed, for example through iteration. For this reason, queries can be built and stored for later use:

        val filmQuery = StarWarsFilmsTable
            .selectAll()
            .where { StarWarsFilmsTable.sequelId lessEq 3 }

You can then call .prepareSQL() to check the SQL string representation of the query that would be sent to the database:

        val querySql: String = filmQuery
            .orWhere { StarWarsFilmsTable.sequelId greater 6 }
            .prepareSQL(this)

            SELECT STARWARSFILMS.ID, STARWARSFILMS.SEQUEL_ID, STARWARSFILMS."name", STARWARSFILMS.DIRECTOR
            FROM STARWARSFILMS
            WHERE (STARWARSFILMS.SEQUEL_ID <= ?) OR (STARWARSFILMS.SEQUEL_ID > ?)

By default, a parameterized SQL string is prepared and returned. To generate a SQL string without parameter placeholders, set the prepared argument to false:

        val fullQuerySql = filmQuery
            .orWhere { StarWarsFilmsTable.sequelId greater 6 }
            .prepareSQL(this, prepared = false)

            SELECT STARWARSFILMS.ID, STARWARSFILMS.SEQUEL_ID, STARWARSFILMS."name", STARWARSFILMS.DIRECTOR
            FROM STARWARSFILMS
            WHERE (STARWARSFILMS.SEQUEL_ID <= 3) OR (STARWARSFILMS.SEQUEL_ID > 6)

Other operations

When calling a function like .insert() on a table, Exposed automatically sends the generated SQL to the database to create a new row. To avoid automatic execution, you can instantiate the underlying statement class directly, namely InsertStatement.

Since version 1.0.0, you can create instances of these underlying statements without automatic execution, by using the same DSL inside a buildStatement {} block:

        val insertFilm = buildStatement {
            StarWarsFilmsTable.insert {
                it[sequelId] = 8
                it[name] = "The Last Jedi"
                it[director] = "Rian Johnson"
            }
        }

As for queries, you can access the SQL string to be executed using .prepareSQL():

val preparedSql: String = insertFilm.prepareSQL(this, prepared = true)

INSERT INTO STARWARSFILMS (SEQUEL_ID, "name", DIRECTOR) VALUES (?, ?, ?)

val fullSql: String = insertFilm.prepareSQL(this, prepared = false)

            INSERT INTO STARWARSFILMS (SEQUEL_ID, "name", DIRECTOR) VALUES (8, 'The Last Jedi', 'Rian Johnson')

Executing a statement

A stored Statement can be sent to the database by first passing it to an executable class, either a subclass of BlockingExecutable (JDBC) or SuspendExecutable (R2DBC). The executable can then be sent to the database using exec() in a transaction block.

This can be done manually, if the appropriate class is known or a custom statement or executable class is being used:

exec(InsertBlockingExecutable(insertFilm))

Alternatively, if the statement is created using the Exposed API, you can use Statement.toExecutable() (JDBC, R2DBC) to resolve the appropriate executable class instance for the calling statement type:

exec(insertFilm.toExecutable())

Last modified: 27 November 2025