hi, is it possible to log the raw sql for a DSL qu...

# exposed

[Luke]

hi, is it possible to log the raw sql for a DSL query before it's attempted to be executed? trying to figure out an error im getting with upserts com.impossibl.postgres.jdbc.PGSQLSimpleException: invalid reference to FROM-clause entry for table "company_stock"

[Daniel Pitts]

I think they’re logged at the TRACE level already.

[Chantal Loncle]

Hi @Luke I've seen that error more often with select queries that use joins, and those queries are only executed when a terminal operation is applied, so you can get the sql pre-execution using `prepareSQL()`:

transaction {
    val query = Words.selectAll().where { Words.count greater 1 }
    println(query.prepareSQL(this))
}

If the exception is thrown by a statement that is automatically executed, like upsert(), then it becomes trickier, because Exposed is not currently set up to be an SQL generator tool.
But it's not impossible.

Option #1:
Adding a logger should show the failing SQL just before the exception output:

transaction {
    addLogger(StdOutSqlLogger)
    // invalid upsert...
}

// the above should result in a stacktrace like:
SQL: INSERT INTO words ("name", "count") VALUES ('A', 10) ON CONFLICT ("count") DO UPDATE SET "name"=EXCLUDED."name"
15:27:17.585 WARN Test worker Exposed:handleSQLException:415 - Transaction attempt #0 failed: org.postgresql.util.PSQLException: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification. Statement(s): INSERT INTO words ("name", "count") VALUES (?, ?) ON CONFLICT ("count") DO UPDATE SET "name"=EXCLUDED."name" 
...

Option #2:
Depending on how complicated your upsert is, you could instantiate the statement directly, so the execution step can be avoided:

UpsertStatement<Long>(
    table = Words,
    keys = emptyArray(),
    onUpdateExclude = null,
    where = null
).apply {
    this[Words.word] = "A"
    this[Words.count] = 10
}.also {
    println(it.prepareSQL(this))
}

Here's the source code in case you need more details about how to configure a statement instance.

Option #3:
This is a hack, but, if your database supports it, you could take advantage of explain() and call prepareSQL() within its context:

explain {
    Words.upsert {
        it[word] = "A"
        it[count] = 10
    }.also {
        println(it.prepareSQL(this))
    }
}

[Luke]

tysm for the detailed explanation! turns out i was making a silly mistake 🤦‍♂️ i was assigning a column instead of the variable for one of the values, like it[word] = word