Error Handling

Checked exceptions are an important feature of Java, but were a design misstep as implemented in JDBC.

Pyranid will instead throw a runtime DatabaseException when errors occur. Often this will wrap the checked java.sql.SQLException.

Practical Application

Here we detect if a unique constraint was violated by examining DatabaseException. We then handle that case specially by rolling back to a known-good savepoint.

// Gives someone at most one big award
database.transaction(() -> {
  Transaction transaction = database.currentTransaction().orElseThrow();
  Savepoint savepoint = transaction.createSavepoint();

  try {
    // We don't want to give someone the same award twice!
    // Let the DBMS worry about constraint checking to avoid race conditions
    database.query("""
      INSERT INTO account_award (account_id, award_type) 
      VALUES (:accountId, :awardType)
      """)
      .bind("accountId", accountId)
      .bind("awardType", AwardType.BIG)
      .execute();
  } catch(DatabaseException e) {
    // Detect a unique constraint violation and gracefully continue on.
    if(e.isUniqueConstraintViolation()) {
      out.printf("The %s award was already given to account ID %s\n",
        AwardType.BIG, accountId);
      // Puts transaction back in good state 
      // (prior to constraint violation)
      transaction.rollback(savepoint);
    } else {
      // There must have been some other problem, bubble out
      throw e;
    }
  }
});

// Gives someone at most one big award
database.transaction(() -> {
  Transaction transaction = database.currentTransaction().orElseThrow();
  Savepoint savepoint = transaction.createSavepoint();

  try {
    // We don't want to give someone the same award twice!
    // Let the DBMS worry about constraint checking to avoid race conditions
    database.query("""
      INSERT INTO account_award (account_id, award_type) 
      VALUES (:accountId, :awardType)
      """)
      .bind("accountId", accountId)
      .bind("awardType", AwardType.BIG)
      .execute();
  } catch(DatabaseException e) {
    // Detect a unique constraint violation and gracefully continue on.
    if(e.isUniqueConstraintViolation()) {
      out.printf("The %s award was already given to account ID %s\n",
        AwardType.BIG, accountId);
      // Puts transaction back in good state 
      // (prior to constraint violation)
      transaction.rollback(savepoint);
    } else {
      // There must have been some other problem, bubble out
      throw e;
    }
  }
});

References:

• DatabaseException
• Transaction::createSavepoint()
• Transaction::rollback(java.sql.Savepoint)

Exception Properties

For convenience, DatabaseException exposes additional properties, which are populated if provided by the underlying java.sql.SQLException:

• errorCode (optional)
• sqlState (optional)

It also exposes conservative classification predicates:

• isUniqueConstraintViolation()
• isForeignKeyViolation()
• isDeadlock()
• isTransient()
• isSerializationFailure()
• isTimeout()

These predicates are intentionally conservative and database-type-aware. They recognize well-known SQLState and vendor error codes for PostgreSQL, MySQL, MariaDB, SQLite, SQL Server, Oracle, and generic JDBC transient/recoverable exception classes; they return false when Pyranid cannot reliably classify the failure. The predicates return non-null Boolean values.

For PostgreSQL, the following properties are also available:

• column (optional)
• constraint (optional)
• datatype (optional)
• detail (optional)
• file (optional)
• hint (optional)
• internalPosition (optional)
• internalQuery (optional)
• line (optional)
• dbmsMessage (optional)
• position (optional)
• routine (optional)
• schema (optional)
• severity (optional)
• table (optional)
• where (optional)