PgTAP: Unit Testing for PostgreSQL | Hacker News Reader

PgTAP: Unit Testing for PostgreSQL

pgtap.org

51 pointsdmmalam21 comments

I was at Etsy when this was being built by a consultant, ostensibly for us to use to test our morass of stored procedures in perpetuity. Early on, someone got some bad advice and thought you were safe from sql injection if you wrote your sql inside stored procedures. This misconception got wildly out of hand.

We stopped writing sprocs and migrated to MySQL over many years instead. I'm happy with the decision. The database isn't a good place to put things that need extensive unit testing.

Or at least it isn't if you're a giant consumer-facing website. YMMV.

Why the migration to MySQL rather than just migrating away from the stored procedures?

We hired some key employees from Flickr who knew a particular MySQL sharding strategy really well. Postgres would have worked fine, just not the way we were using it back then.

Ya PostGIS is dumb and worthless. You should only use your db as a dumb data dump. You shouldn't even bother with keys.

I'll probably never understand why people believe that a stored proc is not subject to injection attacks. Like they're magic or something.

Because if you use the input parameters correctly they are immune to injection.

If you concatenate unsanitized input you are susceptible no matter where you write the SQL.

Though that's also true if it's done application-server side.

Yup. Examples: value stored in a table is concatenated into a query without escapement, leaving it vulnerable to injection. Whose job was it to ensure the DB contained clean data? My policy has been to call quote_identifier or quote_literal (PostgreSQL) where applicable, or use typecasts to enforce value literals.

I get that. That is exactly the same condition as sanitizing inputs in every other context of passing parameters to a service/proc/func. I would not phrase immunity to injection conditionally; there is no immunity, only sanitizing inputs.

I've always loved TAP-style testing over xUnit because it gives me more information in the event of failure: instead of bailing out of a test function as soon as an assertion fails, TAP continues on, providing more failures and more output. When the tests were run on a system you don't have access to, that can mean the difference between being stymied and successful debugging. Sure, you can code up xUnit tests to meet that requirement but it's not idiomatic.

Glad to see PgTAP is still going strong!

Me too.

TAP output encourages longer more informative failure messages as well as more comprehensive output due to exceptions not being the default method of expressing test failures.

Most xUnit style tests allow you give more informative messages and some even provide alternatives to Assert for expressing failures that allow you to collect much more information about the state of a failing test. But the API's and culture around xUnit style testing don't encourage it so almost no one writes them that way.

Does the use case for this maybe depend on your application language and testing facilities? We are comfortable writing sql where it offers an advantage over the alternative, but I can't see what benefit PgTAP offers for our situation (but very willing to be persuaded).

We have a rails app backed by postgres. We routinely write database migrations in sql (activerecord doesn't seem to offer much advantage here), and write plpgsql functions to enforce data integrity across tables that we feel we can't do as well in Rails. However, we test our plpgsql functions using rspec / factorygirl / rails ruby code and find it's much easier to read, write and run these tests than it would be in sql. Maybe it would be different from a different application language? Or are there some specific types of tests that cannot be done from application code?

you can drop down to sql using activerecord, so there's not much advantage to using pgtap (other than possibly speed, since you don't have the overhead of arel and serializing objects into ruby)

I use a similar approach with Python. I gave PgTAP a try but thought that it was easier to write a small Python test harness that loads the stored procedures and exercise them within a transaction.

I understand enforcing data integrity inside the db (checks, triggers, etc) but, especially if your main client is a Rails application, why are you writing migrations in SQL and not in Ruby? I feel that since the introduction of reversible transformations (the change method) and "native" foreign keys (I had been rolling my own methods before) we get so much for free. Do you write two different scripts for migrating up and down?

It was a little while ago, but I think it was because we wanted to use foreign keys, and it just seemed easier to do it directly in sql. We also wanted to use some postgres specific types that weren't supported at the time (range types) as well as a custom data type.

I love writing ruby code, but the extra layer of indirection didn't seem to have much benefit to us in terms of time and effort, and we kept running into features that we wanted to use from postgres that we couldn't use from activerecord.

We do write a separate down migration script.

For Amazon Linux AMI users on EC2 we have packaged pgTAP.

https://lambda-linux.io/blog/2015/06/18/announcing-pgtap-sup...

I liked testing schemas with PgTAP because it let me keep all of the database development and testing independent of other language/app modules. It was not feasible to let the front-end developers take responsibility for testing schema components, where I started with PgTAP.

You may be interested in this: https://github.com/rubyworks/tapout/wiki/TAP-Y-J-Specificati...

Or, more probably, not. Ruby kids thought at some point: "hey! let's do something similar but incompatible to stuff that was already in wide use for a decade!" And guess what? They succeeded.

Great! Now TAP acronym means also TestAlsoPostgreSQL :)

TAP is great, with tape, PgTAP, Test::More, we have a cross method for testing.

Btw, on JavaScript context I use accidentally mocha+should, not tape, just cause I discovered tape later ... but it is ok, cause TIMTOWTDI.

Reply on news.ycombinator.com