Exception Handling with Kotlin Exposed - How to catch unique constraint violation exception?

Question

I have added unique constraint on a column in my table. When the constraint is violated, it throws an exception which I am unable to catch and convey to the user.

Exposed: Transaction attempt #0 failed: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO templates (created_at, is_deleted, name, sections) VALUES ('2018-10-03 16:31:25.732+05:30', 'FALSE', 'Template1', '[{"title":"Introduction"}]') RETURNING * was aborted: ERROR: duplicate key value violates unique constraint "templates_name_key" Detail: Key (name)=(Template1) already exists. Call getNextException to see other errors in the batch.. Statement(s): INSERT INTO templates (created_at, is_deleted, name, sections) VALUES (?, ?, ?, ?) ! org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "templates_name_key" ! Detail: Key (name)=(Template1) already exists.

• How to capture these kind of SQL exceptions with user readable message?
• Is there any best practices to catch these exceptions?
• Should we catch them inside transaction or outside? Is there any necessity like that?

Below is the snippet I tried.

return try {
    val template: TemplateSerializer = transaction {
        val newTemplate = Template.insert(request)
        TemplateSerializer.serialize(newTemplate)
    }
    Response.status(201).entity(template).build()
} catch (e: Exception) {
    if(e is SQLIntegrityConstraintViolationException) {
        resourceLogger.error("SQL constraint violated")
    } else if(e is BatchUpdateException) {
        resourceLogger.error("SQL constraint violated")
    } else
        resourceLogger.error(e.message)
    Response.status(422).entity(mapOf("error" to true, "message" to "Insertion failed")).build()
}

The message SQL constraint violated is not printed at all. Tried multiple catch with different Exception classes too. Nothing worked.

Sending such generic error message is not helping.

Answer 1

Exposed throws ExposedSQLException which is a subtype of SQLException with possibility to access the latest executed query (possibly which cause original exception) via causeByQueries() funciton. Original exception is accessible via cause property.

return try {
    val template: TemplateSerializer = transaction {
        val newTemplate = Template.insert(request)
        TemplateSerializer.serialize(newTemplate)
    }
    Response.status(201).entity(template).build()
} catch (e: Exception) {
    val original = (e as? ExposedSQLException)?.cause
    when (original) {
      is SQLIntegrityConstraintViolationException -> 
        resourceLogger.error("SQL constraint violated")
      is BatchUpdateException -> 
        resourceLogger.error("SQL constraint violated")
      else ->
        resourceLogger.error(e.message)
    }
Response.status(422).entity(mapOf("error" to true, "message" to "Insertion failed")).build()