Pyranid | 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 specific 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().get();
  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.execute("""
      INSERT INTO account_award
      (account_id, award_type) 
      VALUES (?,?)
    """, accountId, AwardType.BIG);
  } catch(DatabaseException e) {
    // Detect a unique constraint violation and gracefully continue on.
    if("account_award_unique_idx".equals(e.getConstraint().orElse(null)) {
      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().get();
  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.execute("""
      INSERT INTO account_award
      (account_id, award_type) 
      VALUES (?,?)
    """, accountId, AwardType.BIG);
  } catch(DatabaseException e) {
    // Detect a unique constraint violation and gracefully continue on.
    if("account_award_unique_idx".equals(e.getConstraint().orElse(null)) {
      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:

Exception Properties

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

errorCode (optional)
sqlState (optional)

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)

Similar extended property support for Oracle and MySQL is planned.