Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ephemeral Postgres Databases

eradman.com

Post reply on HN
Print view View original

31–39 of 39 posts

Re: Ephemeral Postgres Databases

#31

Post by masklinn »

latch wrote: post #11

> For some time proponents of unit testing have asserted that unit tests should not touch a real database Is that still a belief in some circles? I feel like the shift away from this started like 15 years ago (largely because of RoR in my mind). Anyways, this essentially launches a pg instance with a postgresql.conf that is aimed for speed (at the risk of possible data loss/corruption). DO NOT DO THIS IN PRODUCTION,…

Also if you have to create new databases for each test (because, say, the data model will not allow reusing it, or you want to run tests concurrently and not all the tests are transactional) creating one as cached template then initialising test-local dbs with `createdb -T ` is extremely effective.

It’s also stupendously easy if you’re using pytest (though the concurrency caching is not there as IIRC xdist can’t reuse session fixtures):

* create a session fixture to initialise and yield the cache db (and clean it up afterwards) * create a regular test fixture which copies (using `createdb`) the template to a new database, and hands that off to the test

Then tests which need the db just have to request the second fixture.

Re: Ephemeral Postgres Databases

#32

Post by numlock86 »

rubenv wrote: post #3

Or in Go, with full databases: https://github.com/rubenv/pgtest/

I use the same approach but with other languages and runtimes like Java, .NETcore/C# or NodeJS, as this is basically just a CLI wrapper for pg anyway. Works very well and doesn't come with all the overhead.

Re: Ephemeral Postgres Databases

#33

Post by Foobar8568 »

luhn wrote: post #18
Foobar8568 wrote: post #13

Earlier quoted context omitted.

Sql server has snapshot database for something similar, and you don't have to play with transactions (that may or not breaks everything in case of errors)

If there's an error, the transaction just rolls back. I've been using this strategy for years and I have never once had a transaction "break everything."

They are many ways for a database to behave in a not obvious way... Typically error handling, xabort (in sql server, especially if you don't want to get blocking orphan connections etc).

Re: Ephemeral Postgres Databases

#34

Post by geewee »

latch wrote: post #11

> For some time proponents of unit testing have asserted that unit tests should not touch a real database Is that still a belief in some circles? I feel like the shift away from this started like 15 years ago (largely because of RoR in my mind). Anyways, this essentially launches a pg instance with a postgresql.conf that is aimed for speed (at the risk of possible data loss/corruption). DO NOT DO THIS IN PRODUCTION,…

> try to write _all_ your tests without ever having to truncate/delete tables between tests.

I've had really good success with running tests like this the way django does it, where you run your entire test within a migration, and then you just rollback the migration at the end of the test.

Re: Ephemeral Postgres Databases

#35

Post by cwcba »

(Shameless plug:)

Here's the same thing as a docker container you can just keep around which auto-destroys the databases after a set time: https://github.com/ClockworkConsulting/tempgres-server

We originally tried to create temporary databases locally, but found that it the fact that each dev had to do extra setup to be a bit of a pain.

A docker container is on dockerhub as 'cwconsult/tempgres:v2.1.1' (I think I messed up the last publishing and 'latest' doesn't point to that. I should really fix that tag.)

We have a couple of published clients (just for convenience, the REST interface is super-simple, so 'manual' integration is trivial):

- https://github.com/ClockworkConsulting/tempgres-client (Java)

- https://github.com/ClockworkConsulting/django-tempgres (Django)

- https://github.com/BardurArantsson/pg-harness (Haskell)

A few nice things about this one is that:

- You don't even need a local PostgreSQL

- You can keep one somewhere in your LAN and never have to worry about it again.

- It integrates trivially with GitLab CI where you just use it as a service inside the build.

- Integration super-simple... simple enough to use even in e.g. shell scripts.

- No need to worry about tear-down

Anyway, just thought I'd mention it since it's relevant to this thread.

Re: Ephemeral Postgres Databases

#36

Post by piaste »

geewee wrote: post #34
latch wrote: post #11

> For some time proponents of unit testing have asserted that unit tests should not touch a real database Is that still a belief in some circles? I feel like the shift away from this started like 15 years ago (largely because of RoR in my mind). Anyways, this essentially launches a pg instance with a postgresql.conf that is aimed for speed (at the risk of possible data loss/corruption). DO NOT DO THIS IN PRODUCTION,…

> try to write _all_ your tests without ever having to truncate/delete tables between tests. I've had really good success with running tests like this the way django does it, where you run your entire test within a migration, and then you just rollback the migration at the end of the test.

I assume that means running everything within a transaction?

Does that mean you can't use regular transactions in code, since Postgres doesn't support 'true' nested transactions? Or does the Django ORM automatically convert those 'inner' transactions into Postgres SAVEPOINTS?

Re: Ephemeral Postgres Databases

#37

Post by quambene »

jschrf wrote: post #26
switchbak wrote: post #16

Earlier quoted context omitted.

For real _unit_ tests? I would argue it's still a good distinction. We use an embedded postgres in our DB tests, and we call those 'Integration Tests' and run them separately than the pure unit tests. While still tremendously valuable, they do take a bit longer to run, and currently aren't written to allow parallel tests running. We've had a typical habit of writing most tests that hit the DB. Since applying a bit mo…

Aside from testing framework concurrency limitations, why wouldn't you allow parallel tests? If your running system speaks to the DB in parallel and handles pre-existing data, why wouldn't you want your tests to do the same?

That's a good point.

I'm using one local db in a docker container. And then all db-related integration tests are running in parallel (which is the default in Rust via "cargo test") on this local db.

Having much more confidence in my application/server if hundreds of (integration) tests are successfully accessing db in parallel.

Re: Ephemeral Postgres Databases

#38

Post by vinayan3 »

switchbak wrote: post #16
latch wrote: post #11

> For some time proponents of unit testing have asserted that unit tests should not touch a real database Is that still a belief in some circles? I feel like the shift away from this started like 15 years ago (largely because of RoR in my mind). Anyways, this essentially launches a pg instance with a postgresql.conf that is aimed for speed (at the risk of possible data loss/corruption). DO NOT DO THIS IN PRODUCTION,…

For real _unit_ tests? I would argue it's still a good distinction. We use an embedded postgres in our DB tests, and we call those 'Integration Tests' and run them separately than the pure unit tests. While still tremendously valuable, they do take a bit longer to run, and currently aren't written to allow parallel tests running. We've had a typical habit of writing most tests that hit the DB. Since applying a bit mo…

Similarly I've called tests with a PG DB in a docker container an integration test cause they take longer to run.

What's really nice if you run the migrations once to setup the tests then subsequent tests can be fast. Each test run within a nested transaction and rolled back at the end. This ensures that each test get a clean DB to work off of with incurring the cost of running all the migrations.

Re: Ephemeral Postgres Databases

#39

Post by geewee »

piaste wrote: post #36
geewee wrote: post #34

Earlier quoted context omitted.

> try to write _all_ your tests without ever having to truncate/delete tables between tests. I've had really good success with running tests like this the way django does it, where you run your entire test within a migration, and then you just rollback the migration at the end of the test.

I assume that means running everything within a transaction? Does that mean you can't use regular transactions in code, since Postgres doesn't support 'true' nested transactions? Or does the Django ORM automatically convert those 'inner' transactions into Postgres SAVEPOINTS?

Yes exactly. And yeah Django automatically maps transactions inside transactions to savepoints.
Post reply on HN