Postgression - A PostgreSQL database for every test case | Hacker News Reader

Postgression - A PostgreSQL database for every test case

postgression.com

65 pointswilllll22 comments

Hey all, so I'm one of the authors of postgression. The website isn't totally clear as to all the reasons why this may be useful. I've written a blog post about it, which you may also find helpful if you're interested:

http://rdegges.com/postgression-a-postgresql-database-for-ev...

Thanks, I was confused about the motivation and that cleared it up. I think the slogan "A PostgreSQL Database for Every Test Case" is clouding the purpose for me. It sounds from that slogan like you've solved the problem of having a clean database state for each test in your test suite by creating a new ad-hoc database for each test case. But everything I've read, mostly from you, seems to suggest that this is not about solving that problem at all, but rather making it easy to spin up a clean, ad-hoc database with no installation necessary.

So I'm really curious how that slogan is meant to be understood.

Should probably redo the slogan. It kinda rhymed so we picked it, but it obviously isn't that accurate :) A better slogan might be 'Testing Database as a Service' or something like that.

Huh, several different scenarios where it was obviously useful immediately occured to me. I suspect that for many people, it is immediately clear!

(I don't typically run postgres locally, but work on open source software, both projects of my own as well as projects of other peoples I contribute to -- which need to support postgres for others. This has been a pain in the past for me. Your service is quite exactly what I need -- to be able to run automated test suites against postgres without needing a local install of postgres.)

I'm not convinced that the cumulative latency this would introduce into test runs is lower than the amount of time required to use brew or apt-get to install Postgres.

Have you benchmarked it?

🍺 /usr/local/Cellar/postgresql/9.2.2: 2819 files, 39M, built in 4.5 minutes

    tentd git:(master) $ time bundle exec rake
    #...
    Finished in 14.6 seconds
    818 examples, 0 failures

    real	19.10s
    user	11.59s
    sys 	1.22s
 
    tentd git:(master) $ time bundle exec rake TEST_DATABASE_URL=$(curl http://api.postgression.com)
    #...
    Finished in 15 minutes 0.49244 seconds
    818 examples, 0 failures

    real	905.67s
    user	23.37s
    sys 	3.12s

    $ time brew install postgresql
    ==> Downloading http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.bz2
    ######################################################################## 100.0%
    #...
    ==> Summary
    🍺  /usr/local/Cellar/postgresql/9.2.2: 2819 files, 39M, built in 3.2 minutes

    real	190.33s
    user	338.87s
    sys 	60.70s

Hi Titanous, you're definitely right--the focus of postgression isn't speed, just convenience.

Installing a database server locally to run unit tests is something that has always annoyed me. 99.99% of the time when I'm testing things, I'm at a computer, hooked up to the internet, working on things--and I'm usually not all that concerned about an extra second of overhead.

I think the real benefit to this is that you can use the same DB harness regardless of where you're testing: locally, Jenkins, Travis-CI, whatever. This way, you don't have to write any code to make databases, check credentials, whatever, it's just a thoughtless process.

I understand the premise, but this isn't performant enough to be used for anything but the smallest of projects. It isn't just "an extra second of overhead", it increased a 15 second test run to 15 minutes for me (on a fast fiber connection).

Locally I already have Postgres installed for development, and Travis does as well. I guess this might be useful for Jenkins on EC2 in the same AZ as Heroku Postgres, but the latency is still going to be painful.

Sorry, I'm trying to wrap my head around the workflow. Do you use vagrant or something for tests? Othrewise, wouldn't you need the DB installed locally to run your site locally?

I always use a remote db for developing my site locally (eg: Heroku PostgreSQL). On my laptop I typically install my libraries (in a Python virtualenv), and do coding.

You know what would make this faster? If you ran your tests inside Heroku as well. Then you wouldn't have all the network round-trip time and you'd be test in the same environment you go to production on.

This may be a small nit, especially since this is specifically a public test facility. But you might want to restrict the guest roles to only access their own database public schema.

I can't see other's query activity, but I can see some of the pg_catalog tables.

For instance

    select count(*) from pg_stat_database;
     count 
   -------
     1900
   (1 row)

By default, Heroku allows roles on their shared database plans to view limited info about databases on the same machine. Stuff like name, type and owner are visible.

Here is a sample response when attempting to do anything with a database other than your own:

FATAL: permission denied for database "xxxxxxxxxxxx" DETAIL: User does not have CONNECT privilege. Previous connection kept

Not sure I'd want to throw my schema out of our infrastructure.

At the moment, our integration test run takes nearly two hours but this is rolling out the schema and the sheer number of test cases (we have over 1 million lines of test cases). Something that speeds it up would be welcome but this is not for us.

Oh yah, this would definitely not be ideal for something like that.

In regards to the schema stuff, however, we don't touch any user data at all. I've actually added a section to the bottom of our website: http://www.postgression.com/ which explicitly states our policy:

"In regards to security: postgression does not look at, log, or use any data stored by our users. All data is completely ephemeral, and disappears after 30 minutes."

This is inefficient on multiple levels. Please check out Vagrant.

Hi there, so I'm one of the dudes that built this. Just saw in on HN, so figured I'd respond.

We built this to combat the pain of testing in multiple locations. Sure, it adds an extra second to the start of each test run, and sure, you have to talk to a public database server, but the benefits you get are:

- No database server needs to be running on your local machine, vagrant, jenkins instance, or travis CI instance. This means one test configuration and you can run the tests anywhere.

- No need to write scaffolding code to make testing work with vagrant, jenkins, etc., again--things just work.

It's certainly not suitable for everyone, but it fits my personal use case well, which is why I decided to build it ^^

Good job guys! This is pretty cool!

Interesting, but still this is connecting to outside server. For mac there's Postgres.app

Definitely! Postgres.app is awesome, but this is kinda targeting a difference use case (I'm one of the postgression authors).

This is really meant so that you don't need to run PostgreSQL at all locally. It adds a small bit of overhead, but makes it much easier to run tests locally, on a remote server (Jenkins, Travis CI, etc.), and doesn't require any scaffolding code to create a db, etc. It just works :)

Reply on news.ycombinator.com