5 ms·

A related tip, your unit test fixtures can begin and then rollback a transaction between each test, to retain a pristine database between test runs. The test f

• rst 5y ago
  There are test frameworks which automate this behavior -- both Django and Rails provide test-case libraries which run each test in a transaction, and roll back to reset. (NB they also have to provide ways to disable this -- in order to, e.g., test application code which itself can conditionally roll back -- so both can also reset database state "the hard way", at a performance penalty.)
• whalesalad 5y ago
  Unless you rely on transactions in your code. I’m going to take the schema route for a spin. You essentially utilize a schema as a namespace and destroy it when tests are complete (as opposed to the “public” default schema) This way you don’t need to worry about provisioning a stand-alone db for each test run, but still get ephemerality. Plus the ability to run transactions.
  • desas 5y ago
    We modified our code base to give us nested transactions using postgres savepoints, came in handy for automatically wrapping tests in transactions too (though you can opt-out per test)
    • throwdbaaway 5y ago
      https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/ https://buttondown.email/nelhage/archive/notes-on-some-postg... - Be aware that there is a very bad performance cliff in postgres when using savepoints.
      • edoceo 5y ago
        thanks for that awesome read
  • pritambaral 5y ago
    > You essentially utilize a schema as a namespace and destroy it when tests are complete (as opposed to the “public” default schema) Unless you rely on schemas as static namespaces in your code (even if the schemas your code expects includes the "public" common schema). I prefer using dedicated schemas for dedicated micro-apps/services/components. Say, one for the event log, one for the (business) metadata, one for the reports. > This way you don’t need to worry about provisioning a stand-alone db for each test run Practically, `CREATE DATABASE tmp_xxx; \c tmp_xxx` isn't much different from `CREATE SCHEMA tmp_xxx; SET search_path TO tmp_xxx, public;`. They both do essentially the same thing when the dust settles. But the latter requires migrations to be applied to the new schema, while the former allows you to short-circuit that with a simple `CREATE DATABASE tmp_xxx FROM TEMPLATE seeded_test_db`.
• koolba 5y ago
  Unless your transactional code relies on deferred constraints. They don’t get checked until commit time so any test that rolls back upon “success” will not reflect an error.
  • psYchotic 5y ago
    Deferred constraints can be awesome, but as you point out, they can be a massive blind spot in tests that rely on transactions to keep the database clean. However, I've had success with using the `SET CONSTRAINTS ALL IMMEDIATE`[0] statement prior to rolling back the transaction. [0]: https://www.postgresql.org/docs/current/sql-set-constraints.html https://www.postgresql.org/docs/current/sql-set-constraints....