9 ms·

Pg_tmp – Run tests on an isolated, temporary PostgreSQL database

181 points by whalesalad 6y ago

njacobs5074 6y ago
This is a great little package. I added it to one of our back-end services' test suites and it definitely improved issues around tests interfering with one another. The challenge we had was that, if a test crapped out because one of our CI/CD runners had issues, it would leave the shared database for that runner in an undefined state. pg_tmp incurs little or no overhead and they just magically disappear the end of a test run.
makkesk8 6y ago
I would love to see something like dbaclone [1] but for postgres which in principle creates a new database and redirects reads to the source db and writes to your clone. This works very well in unit testing for us but there are situations where we use postgres and not sql server. [1] https://github.com/sqlcollaborative/dbaclone https://github.com/sqlcollaborative/dbaclone
- zimpenfish 6y ago
  > redirects reads to the source db and writes to your clone. Looks like you can do almost that kind of thing with pgpool[1] (although it does look like you'd need a weird replication setup first which might be a deal-breaker.) [1] https://blog.pythian.com/comparing-pgpool-ii-and-pgbouncer/ https://blog.pythian.com/comparing-pgpool-ii-and-pgbouncer/ "Because Pgpool can detect the master and slaves, it can automatically route traffic writes to the master and read traffic to both servers."
sandGorgon 6y ago
for python - i have a fully setup template that creates a scratch postgresql db, loads the schema and then tears it down. its pre-integrated with Black, types, etc https://github.com/RedCarpetUp/rush https://github.com/RedCarpetUp/rush
hn_throwaway_99 6y ago
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-postgresql-service-containers 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 6y ago
  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 6y ago
  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 6y ago
    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-containers https://docs.github.com/en/actions/guides/about-service-cont....
    - OJFord 6y ago
      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 6y ago
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 6y 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 6y 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 6y 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 6y 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 6y ago
bdauvergne 6y ago
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.
nicoburns 6y ago
I haven't had a chance 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 https://github.com/allaboutapps/integresql
- the_duke 6y ago
  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 6y ago
    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
    - hobs 6y ago
      That is much slower for any test of meaningful size.
      - weird-eye-issue 6y ago
        Eh I have run hundreds of tests in Django in like 15 seconds
        hobs 6y ago
        The count of tests is not important, the size of the data is.
Wicher 6y ago
Related, something I wrote years ago: https://pypi.org/project/quickpiggy/ 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())
- rcv 6y ago
  Looks interesting - Unfortunately the homepage is currently inaccessible. http://smormedia.gavagai.nl/dist/quickpiggy/ http://smormedia.gavagai.nl/dist/quickpiggy/
  - Wicher 6y ago
    Thanks! I put it on pypi in 2011, when you could provide external http links to the tarballs. At some point they've changed all that, I've modernized it now so have another look if you're interested! pip install quickpiggy quickpiggy a_test_db should do it.
rad_gruchalski 6y ago
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-with-dockertest/ 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.
mjw1007 6y ago
Here's a list of initdb-wrappers I've come across before: http://manpages.ubuntu.com/manpages/hirsute/man1/pg_virtualenv.1.html http://manpages.ubuntu.com/manpages/hirsute/man1/pg_virtuale... pg_virtualenv (shipped with postgresql-common in Debian and Ubuntu) https://crates.io/crates/postgresfixture https://crates.io/crates/postgresfixture (for Rust) https://launchpad.net/postgresfixture https://launchpad.net/postgresfixture (for Python) https://github.com/tk0miya/testing.postgresql https://github.com/tk0miya/testing.postgresql https://github.com/TJC/Test-postgresql https://github.com/TJC/Test-postgresql
nextaccountic 6y ago
Can this be used with https://pgtap.org/ https://pgtap.org/? How well does they work in practice?
lemarchr 6y ago
For those using Java, it's also possible to achieve this using Testcontainers[0]. It's nice to have the test DB setup co-located with your test code, and if you're using JUnit it's really easy to manage and align the DB lifecycle, e.g. fresh DB for each test, shared across class, etc. ---------- 0: https://www.testcontainers.org/modules/databases https://www.testcontainers.org/modules/databases
- oweiler 6y ago
  Testcontainers is also available for go https://github.com/testcontainers/testcontainers-go https://github.com/testcontainers/testcontainers-go
- twic 6y ago
  I've been using this embedded PostgreSQL thing for tests: https://github.com/opentable/otj-pg-embedded https://github.com/opentable/otj-pg-embedded It's very simple to use and works perfectly. The one problem, and it's a significant one, is that it only ships one version of PostgreSQL, and adding another was difficult enough that I didn't.
pantuza 6y ago
Super cool and useful. In the page there is this link about A guide for accessing the features of PostgreSQL in test-driven development which is nice: https://www.youtube.com/watch?v=4Vigf4NG6-s https://www.youtube.com/watch?v=4Vigf4NG6-s
hardwaresofton 6y ago
With the advent of easy to use docker containers, this is really easy to do for most backing services, right on your machine during tests (and in CI). If all your app needs is rabbitmq (or some queue), redis, and postgres you can run all of them in the background and give out databases/exchanges to your app as you see fit. This also does something interesting -- it turns you away from tools that don't do badly in containerized environments or who are hard to set up, which is a benefit in my mind. It also turns you away from proprietary cloud solutions that you can't run yourself (which isn't always possible but meh). I trust my E2E tests a lot more when they're not built on mocking. I'm almost tempted to say that mocking is pointless in this world -- just test the real thing. [EDIT] Since most people will know how to run redis/postgres/rabbitmq easily, one of the high value things is smtp testing. Here are a few projectsfor that: - https://github.com/sj26/mailcatcher https://github.com/sj26/mailcatcher (https://mailcatcher.me https://mailcatcher.me) - https://github.com/mailhog/MailHog https://github.com/mailhog/MailHog - https://github.com/maildev/maildev https://github.com/maildev/maildev (http://maildev.github.io/maildev/ http://maildev.github.io/maildev/) - https://github.com/remko/smtp-http-proxy https://github.com/remko/smtp-http-proxy (this is more for turning SMTP into HTTP but it's cool) - https://github.com/flashmob/go-guerrilla https://github.com/flashmob/go-guerrilla Remember, for a lot of these projects don't be worried if you don't see updates for years -- once you've written a reasonably working SMTP server, it may not need to change much. I might take one of these and try to build a MailTrap[0] competitor eventually. $10/month is twice the cost of a digital ocean droplet running one of these pieces of software (and I know that I can run the instances for even cheaper). [0]: https://mailtrap.io/pricing/ https://mailtrap.io/pricing/
- okamiueru 6y ago
  I agree with most of what you write, and I use test fixtures that spin up real services with docker for running integration tests. However, I'd argue that mocking will always have a usefulness, as it goes hand in hand with unit tests. Integration tests confirms the behaviour for the code branching you are able to reach that way, and more closely reflects reality and normal operations. Mocking allows you to more easily test the edge cases that the software unit might encounter that are hard to create conditions for otherwise. In fact, from also being tempted by what you write (ie. why mock, when you can automatically spin up a docker service). I've done this too, and my conclusion is that it is more trouble than it's worth, although that can differ for other systems, I suppose. The only realistic approach is that a test fixture spins services up once, and not per test/unit, so it just introduced a concern for correct clean and concurrency limitations that wouldn't exist. It also has unexpected issues with wildly varying startup times whenever or not the container image is cached. All in all, it works well enough now, but when I do it all over somewhere, I'm tempted to keep a clean distinction of deploying temporary services for running integration tests, and regular unit tests, without the in between. TL;DR: mocking isn't just for "i need service A in my test", but also "I need A to behave like X", and don't get that with external services.
0xbadcafebee 6y ago
Are other people not already doing this? You just need a shell script and Docker and you can run your own copies of a database, modify them, export them, hell, even diff both DML and DDL, just with the diff command and stock backup/restore tools. Pop it into a CI/CD pipeline and you don't need to write custom SQL migrations anymore.
thejosh 6y ago
Makes me super pleased to use Elixir/Ecto and their sandboxes with these sorts of hacks that are required.
g_delgado14 6y ago
Is there anything like this for MySQL?
- redis_mlc 6y ago
  Unlikely, MySQL does not have transactional DDL (like CREATE TABLE). It's not a big loss when managing production databases in practice because with large tables you roll forward, but it would be nice for dev/qa environments. https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.htm... Source: DBA.
- hardwaresofton 6y ago
  You could run MySQL in a container? Start a container per test-run and create a database per test or test suite. If your code is written well, you'll be able to change the database that your DAO/ORM/Query-layer runs on quite simply, and execute/ensure migrations from code.
- abraae 6y ago
  As sibling says, run mysql in a container. Configure the container without any attached volumes, so that the data is stored in the container itself. Run your setup scripts, and get the database the way you want it. Now commit the changes to the container, to create a new container. Now you can spin up the new container, and your setup data will be already loaded within it.
trestletech 6y ago
Sounds like a neat utility but you lost me at "download this shell script via unencrypted HTTP."
tony 6y ago
The author is Eric Radman. The visionary behind the entr(1) file watcher http://eradman.com/entrproject/ http://eradman.com/entrproject/ GitHub: https://github.com/eradman https://github.com/eradman, also: rset(1): http://scriptedconfiguration.org/ http://scriptedconfiguration.org/ - Configure systems using any scripting language
- john-tells-all 6y ago
  I use `entr` _constantly_, I can't imagine not having it available. I study Feedback Loops, and the incredible ease of saying "run this program when those files change" has been revolutionary. Auto-deploy when a Terraform file changes, or auto-test when a Python file changes. Wonderful!
  - BiteCode_dev 6y ago
    The problem with entr is that: - it's unix only, which, when using a multi-plateform language like python, is limitating - can't be used programmatically without subprocess hacks If you use Python, watchdog is an interesting alternative: - it works were Python works: pretty much everywhere - it uses the best API for the job (e.g: inotify on linux), but will fallback on manual scanning in a thread if the underlying OS has no special file system watcher API - it's a lib that can be use programmatically - it features the command "watchmedo", which is basically what entr does, but with an optional rich file format and plugin system - for simple use cases, all that stuff stays super simple
  - kevincox 6y ago
    My workflow is hugely reliant on entr. I use it to rerun a command whenever I save a file. 1. Configure vim to touch a well-known file on save. 2. Create a command `w` which reruns it's arguments whenever that file is touched. I must run hundreds of reruns under this setup every day. Basically every language, every too, everything runs on this. Changing my NixOS config? `w sudo nixos-rebuild test` Writing rust? `w cargo ltest` running terraform? `terraform init && w terraform plan`.
netcraft 6y ago
There are two things I would love to have in postgres - 1. The ability to set a point in time to roll back to, revert everything back to that point. Would be useful in lots of development scenarios 2. The ability to set a point in time and then allow me to make changes however - direct sql, using an attached application, whatever - and then get the DDL/DDM necessary to take it from the snapshot to the current state. I would love to be able to take a seed database, hook it up to an app and use the UI to set up scenarios that I could then use in my unit tests. This isn't bad in isolated cases, but when setting up a scenario takes coordination of tens of tables, this would be much easier. I could imagine it being useful in a lot of other scenarios as well. I'm not sure if you could do #2 with something on top of logical replication - ive started to look at that in the past but I would really like something that outputs SQL.
- tpetry 6y ago
  Your first point is already there ;) it‘s called savepoints and works exactly like nested transactions.
  - aljungberg 6y ago
    Unfortunately savepoints only work for a non-transactional single connection test. E.g. you can only set a savepoint within a transaction. If the code under test, like an integration test, uses transactions itself, or multiple connections, savepoints won't help you. I would love to be able to globally revert all committed changes to a point in time. SUPER SAVEPOINT A; <NO HOLDS BARRED CODE USING MULTIPLE CONNECTIONS, TRANSACTIONS, NORMAL SAVEPOINTS ETC> SUPER RESTORE A; It could be implemented by playing the WAL logs backwards, I imagine. Alternatively, just making CREATE DATABASE WITH TEMPLATE fast would be nice. Even on tmpfs and no fsync this operation takes about 300 ms on my (fast) workstation. Then you could consider the template database a savepoint in itself.
- cjheppell 6y ago
  Full disclosure way up front: I'm one of the developers working on https://spawn.cc https://spawn.cc at Redgate and the two scenarios you describe are some examples of exactly what we've built Spawn for. Not trying to sell you anything (since we are still in beta anyway!) but I'd encourage you to try it out and see if it does what you're looking for. We're in the early stages so any and all feedback is really helpful! It's a hosted service where you can immediately spin up copies of "data images" (a snapshot of schema and data in a database instance) instantly regardless of the size. As an example, we created a copy of the 400GB public stack overflow DB for development in ~15s. You can also snapshot the state of the DB at any time and move around those save points whenever you like. Creating a "data image" for later copies is also possible based off the current state of the database, so you can curate data through your web UI, save the state, and then make that dataset available for multiple copies across your dev team. We've got a couple of repos to show it in action: - https://github.com/red-gate/spawn-demo https://github.com/red-gate/spawn-demo - https://github.com/red-gate/flyway-spawn-demo/actions https://github.com/red-gate/flyway-spawn-demo/actions As well as a youtube video: https://www.youtube.com/watch?v=sxHtca85CoE https://www.youtube.com/watch?v=sxHtca85CoE

181 points by whalesalad 6y ago

This is a great little package. I added it to one of our back-end services' test suites and it definitely improved issues around tests interfering with one another. The challenge we had was that, if a test crapped out because one of our CI/CD runners had issues, it would leave the shared database for that runner in an undefined state. pg_tmp incurs little or no overhead and they just magically disappear the end of a test run.

makkesk8 6y ago

I would love to see something like dbaclone [1] but for postgres which in principle creates a new database and redirects reads to the source db and writes to your clone. This works very well in unit testing for us but there are situations where we use postgres and not sql server. [1] https://github.com/sqlcollaborative/dbaclone https://github.com/sqlcollaborative/dbaclone

zimpenfish 6y ago
> redirects reads to the source db and writes to your clone. Looks like you can do almost that kind of thing with pgpool[1] (although it does look like you'd need a weird replication setup first which might be a deal-breaker.) [1] https://blog.pythian.com/comparing-pgpool-ii-and-pgbouncer/ https://blog.pythian.com/comparing-pgpool-ii-and-pgbouncer/ "Because Pgpool can detect the master and slaves, it can automatically route traffic writes to the master and read traffic to both servers."

sandGorgon 6y ago

for python - i have a fully setup template that creates a scratch postgresql db, loads the schema and then tears it down. its pre-integrated with Black, types, etc https://github.com/RedCarpetUp/rush https://github.com/RedCarpetUp/rush

hn_throwaway_99 6y ago

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-postgresql-service-containers 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 6y ago
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 6y ago
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 6y ago
  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-containers https://docs.github.com/en/actions/guides/about-service-cont....
  - OJFord 6y ago
    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 6y ago

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 6y 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 6y 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 6y 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 6y 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 6y ago

bdauvergne 6y ago

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.

nicoburns 6y ago

I haven't had a chance 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 https://github.com/allaboutapps/integresql

the_duke 6y ago
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 6y ago
  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
  - hobs 6y ago
    That is much slower for any test of meaningful size.
    - weird-eye-issue 6y ago
      Eh I have run hundreds of tests in Django in like 15 seconds
      - hobs 6y ago
        The count of tests is not important, the size of the data is.

Wicher 6y ago

Related, something I wrote years ago: https://pypi.org/project/quickpiggy/ 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())

rcv 6y ago
Looks interesting - Unfortunately the homepage is currently inaccessible. http://smormedia.gavagai.nl/dist/quickpiggy/ http://smormedia.gavagai.nl/dist/quickpiggy/
- Wicher 6y ago
  Thanks! I put it on pypi in 2011, when you could provide external http links to the tarballs. At some point they've changed all that, I've modernized it now so have another look if you're interested! pip install quickpiggy quickpiggy a_test_db should do it.

rad_gruchalski 6y ago

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-with-dockertest/ 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.

mjw1007 6y ago

Here's a list of initdb-wrappers I've come across before: http://manpages.ubuntu.com/manpages/hirsute/man1/pg_virtualenv.1.html http://manpages.ubuntu.com/manpages/hirsute/man1/pg_virtuale... pg_virtualenv (shipped with postgresql-common in Debian and Ubuntu) https://crates.io/crates/postgresfixture https://crates.io/crates/postgresfixture (for Rust) https://launchpad.net/postgresfixture https://launchpad.net/postgresfixture (for Python) https://github.com/tk0miya/testing.postgresql https://github.com/tk0miya/testing.postgresql https://github.com/TJC/Test-postgresql https://github.com/TJC/Test-postgresql

nextaccountic 6y ago

Can this be used with https://pgtap.org/ https://pgtap.org/? How well does they work in practice?

lemarchr 6y ago

For those using Java, it's also possible to achieve this using Testcontainers[0]. It's nice to have the test DB setup co-located with your test code, and if you're using JUnit it's really easy to manage and align the DB lifecycle, e.g. fresh DB for each test, shared across class, etc. ---------- 0: https://www.testcontainers.org/modules/databases https://www.testcontainers.org/modules/databases

oweiler 6y ago
Testcontainers is also available for go https://github.com/testcontainers/testcontainers-go https://github.com/testcontainers/testcontainers-go
twic 6y ago
I've been using this embedded PostgreSQL thing for tests: https://github.com/opentable/otj-pg-embedded https://github.com/opentable/otj-pg-embedded It's very simple to use and works perfectly. The one problem, and it's a significant one, is that it only ships one version of PostgreSQL, and adding another was difficult enough that I didn't.

pantuza 6y ago

Super cool and useful. In the page there is this link about A guide for accessing the features of PostgreSQL in test-driven development which is nice: https://www.youtube.com/watch?v=4Vigf4NG6-s https://www.youtube.com/watch?v=4Vigf4NG6-s

hardwaresofton 6y ago

With the advent of easy to use docker containers, this is really easy to do for most backing services, right on your machine during tests (and in CI). If all your app needs is rabbitmq (or some queue), redis, and postgres you can run all of them in the background and give out databases/exchanges to your app as you see fit. This also does something interesting -- it turns you away from tools that don't do badly in containerized environments or who are hard to set up, which is a benefit in my mind. It also turns you away from proprietary cloud solutions that you can't run yourself (which isn't always possible but meh). I trust my E2E tests a lot more when they're not built on mocking. I'm almost tempted to say that mocking is pointless in this world -- just test the real thing. [EDIT] Since most people will know how to run redis/postgres/rabbitmq easily, one of the high value things is smtp testing. Here are a few projectsfor that: - https://github.com/sj26/mailcatcher https://github.com/sj26/mailcatcher (https://mailcatcher.me https://mailcatcher.me) - https://github.com/mailhog/MailHog https://github.com/mailhog/MailHog - https://github.com/maildev/maildev https://github.com/maildev/maildev (http://maildev.github.io/maildev/ http://maildev.github.io/maildev/) - https://github.com/remko/smtp-http-proxy https://github.com/remko/smtp-http-proxy (this is more for turning SMTP into HTTP but it's cool) - https://github.com/flashmob/go-guerrilla https://github.com/flashmob/go-guerrilla Remember, for a lot of these projects don't be worried if you don't see updates for years -- once you've written a reasonably working SMTP server, it may not need to change much. I might take one of these and try to build a MailTrap[0] competitor eventually. $10/month is twice the cost of a digital ocean droplet running one of these pieces of software (and I know that I can run the instances for even cheaper). [0]: https://mailtrap.io/pricing/ https://mailtrap.io/pricing/

okamiueru 6y ago
I agree with most of what you write, and I use test fixtures that spin up real services with docker for running integration tests. However, I'd argue that mocking will always have a usefulness, as it goes hand in hand with unit tests. Integration tests confirms the behaviour for the code branching you are able to reach that way, and more closely reflects reality and normal operations. Mocking allows you to more easily test the edge cases that the software unit might encounter that are hard to create conditions for otherwise. In fact, from also being tempted by what you write (ie. why mock, when you can automatically spin up a docker service). I've done this too, and my conclusion is that it is more trouble than it's worth, although that can differ for other systems, I suppose. The only realistic approach is that a test fixture spins services up once, and not per test/unit, so it just introduced a concern for correct clean and concurrency limitations that wouldn't exist. It also has unexpected issues with wildly varying startup times whenever or not the container image is cached. All in all, it works well enough now, but when I do it all over somewhere, I'm tempted to keep a clean distinction of deploying temporary services for running integration tests, and regular unit tests, without the in between. TL;DR: mocking isn't just for "i need service A in my test", but also "I need A to behave like X", and don't get that with external services.

0xbadcafebee 6y ago

Are other people not already doing this? You just need a shell script and Docker and you can run your own copies of a database, modify them, export them, hell, even diff both DML and DDL, just with the diff command and stock backup/restore tools. Pop it into a CI/CD pipeline and you don't need to write custom SQL migrations anymore.

thejosh 6y ago

Makes me super pleased to use Elixir/Ecto and their sandboxes with these sorts of hacks that are required.

g_delgado14 6y ago

Is there anything like this for MySQL?

redis_mlc 6y ago
Unlikely, MySQL does not have transactional DDL (like CREATE TABLE). It's not a big loss when managing production databases in practice because with large tables you roll forward, but it would be nice for dev/qa environments. https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.htm... Source: DBA.
hardwaresofton 6y ago
You could run MySQL in a container? Start a container per test-run and create a database per test or test suite. If your code is written well, you'll be able to change the database that your DAO/ORM/Query-layer runs on quite simply, and execute/ensure migrations from code.
abraae 6y ago
As sibling says, run mysql in a container. Configure the container without any attached volumes, so that the data is stored in the container itself. Run your setup scripts, and get the database the way you want it. Now commit the changes to the container, to create a new container. Now you can spin up the new container, and your setup data will be already loaded within it.

trestletech 6y ago

Sounds like a neat utility but you lost me at "download this shell script via unencrypted HTTP."

tony 6y ago

The author is Eric Radman. The visionary behind the entr(1) file watcher http://eradman.com/entrproject/ http://eradman.com/entrproject/ GitHub: https://github.com/eradman https://github.com/eradman, also: rset(1): http://scriptedconfiguration.org/ http://scriptedconfiguration.org/ - Configure systems using any scripting language

john-tells-all 6y ago
I use `entr` _constantly_, I can't imagine not having it available. I study Feedback Loops, and the incredible ease of saying "run this program when those files change" has been revolutionary. Auto-deploy when a Terraform file changes, or auto-test when a Python file changes. Wonderful!
- BiteCode_dev 6y ago
  The problem with entr is that: - it's unix only, which, when using a multi-plateform language like python, is limitating - can't be used programmatically without subprocess hacks If you use Python, watchdog is an interesting alternative: - it works were Python works: pretty much everywhere - it uses the best API for the job (e.g: inotify on linux), but will fallback on manual scanning in a thread if the underlying OS has no special file system watcher API - it's a lib that can be use programmatically - it features the command "watchmedo", which is basically what entr does, but with an optional rich file format and plugin system - for simple use cases, all that stuff stays super simple
- kevincox 6y ago
  My workflow is hugely reliant on entr. I use it to rerun a command whenever I save a file. 1. Configure vim to touch a well-known file on save. 2. Create a command `w` which reruns it's arguments whenever that file is touched. I must run hundreds of reruns under this setup every day. Basically every language, every too, everything runs on this. Changing my NixOS config? `w sudo nixos-rebuild test` Writing rust? `w cargo ltest` running terraform? `terraform init && w terraform plan`.

netcraft 6y ago

There are two things I would love to have in postgres - 1. The ability to set a point in time to roll back to, revert everything back to that point. Would be useful in lots of development scenarios 2. The ability to set a point in time and then allow me to make changes however - direct sql, using an attached application, whatever - and then get the DDL/DDM necessary to take it from the snapshot to the current state. I would love to be able to take a seed database, hook it up to an app and use the UI to set up scenarios that I could then use in my unit tests. This isn't bad in isolated cases, but when setting up a scenario takes coordination of tens of tables, this would be much easier. I could imagine it being useful in a lot of other scenarios as well. I'm not sure if you could do #2 with something on top of logical replication - ive started to look at that in the past but I would really like something that outputs SQL.

tpetry 6y ago
Your first point is already there ;) it‘s called savepoints and works exactly like nested transactions.
- aljungberg 6y ago
  Unfortunately savepoints only work for a non-transactional single connection test. E.g. you can only set a savepoint within a transaction. If the code under test, like an integration test, uses transactions itself, or multiple connections, savepoints won't help you. I would love to be able to globally revert all committed changes to a point in time. SUPER SAVEPOINT A; <NO HOLDS BARRED CODE USING MULTIPLE CONNECTIONS, TRANSACTIONS, NORMAL SAVEPOINTS ETC> SUPER RESTORE A; It could be implemented by playing the WAL logs backwards, I imagine. Alternatively, just making CREATE DATABASE WITH TEMPLATE fast would be nice. Even on tmpfs and no fsync this operation takes about 300 ms on my (fast) workstation. Then you could consider the template database a savepoint in itself.
cjheppell 6y ago
Full disclosure way up front: I'm one of the developers working on https://spawn.cc https://spawn.cc at Redgate and the two scenarios you describe are some examples of exactly what we've built Spawn for. Not trying to sell you anything (since we are still in beta anyway!) but I'd encourage you to try it out and see if it does what you're looking for. We're in the early stages so any and all feedback is really helpful! It's a hosted service where you can immediately spin up copies of "data images" (a snapshot of schema and data in a database instance) instantly regardless of the size. As an example, we created a copy of the 400GB public stack overflow DB for development in ~15s. You can also snapshot the state of the DB at any time and move around those save points whenever you like. Creating a "data image" for later copies is also possible based off the current state of the database, so you can curate data through your web UI, save the state, and then make that dataset available for multiple copies across your dev team. We've got a couple of repos to show it in action: - https://github.com/red-gate/spawn-demo https://github.com/red-gate/spawn-demo - https://github.com/red-gate/flyway-spawn-demo/actions https://github.com/red-gate/flyway-spawn-demo/actions As well as a youtube video: https://www.youtube.com/watch?v=sxHtca85CoE https://www.youtube.com/watch?v=sxHtca85CoE