Nested transactions using savepoints for tests in clojure.jdbc and postgres

Question

In my unit tests, I have a fixture that creates a rollback transaction in order to clean up the database after my tests have run. It looks something like this:

(defn with-rollback [test-fn]
  (let [db-conn    (db/connect db/test-pg-db)]
    (jdbc/with-db-transaction [txn db-conn {:isolation :serializable}]
      (jdbc/db-set-rollback-only! txn)
      (-> (mount/only [#'db/db])
          (mount/swap {#'db/db txn})
          (mount/start))
      (test-fn)
      (mount/stop))
    (db/disconnect db-conn)))

Then in the tests I do (use-fixtures :each fixtures/with-rollback).

This works great, except in tests where I want to test that error conditions actually rolls back transactions. For example I want to test that when a request to an external service fails I don't write anything to the db.

Is there any way to get behaviour similar to nested transactions for my use case? I had an idea to override the jdbc/db-transaction* function with something that creates savepoints when a transaction is opened and rolls back to the nearest one when an exception is caught - but I could never get it to work. Appriciate any help!

vkopio · Accepted Answer · 2021-03-12 09:28:18Z

2

I was struggling with this problem as well and ultimately decided to go with a workaround of having separate files for "normal" tests and transaction tests. This way it is possible to use separate fixtures.

So you would use a rollback fixture for the normal tests and a clear/truncate fixture for the transaction tests. Truncating has a bigger performance cost, hence the fixture should be used sparingly.

Here is the truncate fixture implementation. It is assumed that the used schema is public and that there are some data stored about database migrations in a schema_migrations table that should be left intact.

(defn truncate-db [tests]
  (tests)
  (jdbc/execute! db-conn
                 "DO $$ BEGIN
                    EXECUTE 'TRUNCATE TABLE '
                    || (SELECT string_agg(table_name::text, ',')
                        FROM information_schema.tables
                        WHERE table_schema = 'public'
                        AND table_type = 'BASE TABLE'
                        AND table_name != 'schema_migrations')
                    || ' CASCADE';
                  END; $$;"))

answered Mar 12, 2021 at 9:28

vkopio

1,0341 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rutchkiwi Over a year ago

That is how I do it as well. Nice way to auto truncate all tables

Collectives™ on Stack Overflow

Nested transactions using savepoints for tests in clojure.jdbc and postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related