[HN Gopher] Pg_tmp - Run tests on an isolated, temporary Postgre...
___________________________________________________________________
Pg_tmp - Run tests on an isolated, temporary PostgreSQL database
Author : whalesalad
Score : 50 points
Date : 2021-04-26 20:52 UTC (2 hours ago)
(HTM) web link (eradman.com)
(TXT) w3m dump (eradman.com)
| rad_gruchalski wrote:
| Wow, this is neat when one has a Postgres instance running out
| there.
|
| A portable alternative which I find very useful is to start a
| database in a container. I use ory/dockertest for that. I've
| written more about it here:
| https://gruchalski.com/posts/2021-04-24-on-software-testing-....
| It's in the context of golang and launching the Ory platform for
| integration testing. Edit / added: could be relevant here for
| some. ory/dockertest launches containers for tests. It's super
| easy to do this in any technology.
| hn_throwaway_99 wrote:
| FWIW, if you use GitHub Actions for CI/CD they make it super easy
| to start up a fresh postgres instance:
| https://docs.github.com/en/actions/guides/creating-postgresq...
|
| We do this on every build: start a fresh postgres instance, run
| all of our DB migrations to get us to the latest DB schema, and
| then run our test suite.
| chaos_emergent wrote:
| Yeah, was going to comment something similar. For local testing
| I have some docker-compose files set up that create an
| ephemeral db that I can test against by migrating up to the
| current schema revision and then running tests. Saves time in
| pushing to the cloud and needing to wait for GitHub/Lab to
| build, pull and provision containers.
| OJFord wrote:
| That seems the same as any other method of using a postgres
| container?
|
| It'd look basically the same in GitLab, Circle, Travis, docker-
| compose, whatever.
| hn_throwaway_99 wrote:
| Point is that GitHub has built-in support for including
| "service containers" in workflows that make it trivial to
| spin up services in different Docker containers,
| https://docs.github.com/en/actions/guides/about-service-
| cont....
| OJFord wrote:
| It seems to me that my comment you're replying to applies
| equally well in response to this...
|
| I agree it's nice, painless, etc. I just wanted to be clear
| that it's not some novel ground-breaking GitHub feature,
| it's just a generic CI/CD SaaS feature. Not switch to/use
| GH Actions, but do this/have CI.
| plasma wrote:
| 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 fixture should run migrations against a test database
| (used only for test fixtures).
| rst wrote:
| 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 wrote:
| 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.
| pritambaral wrote:
| > 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`.
| desas wrote:
| 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 wrote:
| 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 wrote:
| thanks for that awesome read
| Wicher wrote:
| Related, something I wrote years ago:
|
| https://pypi.org/project/quickpiggy/
|
| From its README: A makeshift PostgresSQL instance
| can be obtained quite easily: pig =
| quickpiggy.Piggy(volatile=True, create_db='somedb') conn =
| psycopg2.connect(pig.dsnstring())
| mjw1007 wrote:
| Here's a list of initdb-wrappers I've come across before:
|
| http://manpages.ubuntu.com/manpages/hirsute/man1/pg_virtuale...
| pg_virtualenv (shipped with postgresql-common in Debian and
| Ubuntu)
|
| https://crates.io/crates/postgresfixture (for Rust)
|
| https://launchpad.net/postgresfixture (for Python)
|
| https://github.com/tk0miya/testing.postgresql
|
| https://github.com/TJC/Test-postgresql
| bdauvergne wrote:
| It seems similar to pg_virtualenv that I use with `-o fsync=off`
| in my Jenkinsfile.
|
| PS: I just learned that it's a Debian only command.
| nextaccountic wrote:
| Can this be used with https://pgtap.org/? How well does they work
| in practice?
| nicoburns wrote:
| I haven't had a change to try it yet, but IntegreSQL[0] looks
| like this on steroids. It allows you to create a template (run
| migrations and seed data), and then uses Postgres's built in
| cloning functionality to maintain a _pool_ of fresh databases.
| They claim 500ms to clone a database without the pool, and that
| the pool pretty much hides the latency entirely.
|
| [0]: https://github.com/allaboutapps/integresql
| the_duke wrote:
| Thanks for sharing this!
|
| If the numbers hold true, you could actually use a separate
| database for each test, which could be hugely beneficial. It's
| way to easy to cause some unintended interaction between tests
| if sharing a db.
|
| On the other hand I've seen quite a few bugs caught in testing
| exactly due to the lack of isolation between tests, because the
| same ones would have shown up in production...
| weird-eye-issue wrote:
| Just use a new transaction for each test and have them all
| roll back when the test finishes. It leaves the db in a clean
| state for the next test
| tfussell wrote:
| What if your test includes a transaction already?
| hobs wrote:
| That is much slower for any test of meaningful size.
___________________________________________________________________
(page generated 2021-04-26 23:00 UTC)