Having fun with context managers

Working with databases most of the time means working with transactions. You open a transaction, perform data manipulation and commit the transaction to finish up. If something goes wrong, instead of commit you’ll perform rollback that cleans everything you were doing inside of the transaction. Sometimes you want to commit the transaction after a simple database operation, sometimes you want to string lots of simple database operations together and commit only after all of them have been successfully performed. Nested transactions are one solution for this, another is to let the business logic dictate when the operation is finished. The later option means that data access layer should not care about transactions.

Python and Hy have very nice and elegant way of handling transactions with Sqlite databases. The following code is a function that is used to save a person into a database. Parameter person is a dict and connection is Sqlite connection. There is no transaction handling on this level.

(defn save-person [person connection]
  (let [[cursor (.cursor connection)]
        [params (, (:name person) (:id person))]
        [person-id (:id person)]]
    (if person-id (do (.execute cursor "update person set name=? where OID=?" params)
                      (load-person person-id connection))
        (do (.execute cursor "insert into person (name, OID) values (?, ?)" params)
            (let [[new-person-id cursor.lastrowid]]
              (load-person new-person-id connection))))))

The following code is a test that saves and loads person from a database (in-memory database in this case). The transaction handling is on this level, although it might not be apparent on a first glance.

(defn test-save-person []
  (with [connection (create-schema (get-in-memory-connection))]
        (let [[person {:id None :name "Pete"}]
              [saved-person (save-person person connection)]
              [loaded-person (load-person (:id saved-person) connection)]]
          (assert-that (:name loaded-person) (is- (equal-to "Pete"))))))

The magic is in with statement. Connection can be used as a context manager, which delegates transaction management to it. When with – block is entered a transaction is automatically started. When with block ends, the transaction is automatically commited, causing the changes to be written into the database. If there is an exception during the execution of with – block and the user code does not handle it, with catches it and performs a rollback.

The solution is really nice and elegant looking. The drawback is that it is not possible to call another function that uses with – block from within with – block, because that would cause problems with transaction management.