PgTAP: Unit Testing for PostgreSQL • Hacker News Board

Re: PgTAP: Unit Testing for PostgreSQL

#11

Post by joevandyk » Thu, Jul 30, 2015, 5:49 AM UTC

ghufran_syed wrote: post #5

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 w…

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)

Re: PgTAP: Unit Testing for PostgreSQL

#12

Post by rhizome31 » Thu, Jul 30, 2015, 6:04 AM UTC

ghufran_syed wrote: post #5

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 w…

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.

Re: PgTAP: Unit Testing for PostgreSQL

#13

Post by spacemanmatt » Thu, Jul 30, 2015, 6:20 AM UTC

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.

Re: PgTAP: Unit Testing for PostgreSQL

#14

Post by spacemanmatt » Thu, Jul 30, 2015, 6:21 AM UTC

mcfunley wrote: post #4

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 dat…

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

Re: PgTAP: Unit Testing for PostgreSQL

#15

Post by fibo » Thu, Jul 30, 2015, 6:33 AM UTC

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.

Re: PgTAP: Unit Testing for PostgreSQL

#16

Post by dozzie » Thu, Jul 30, 2015, 7:17 AM UTC

transfire wrote: post #6

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.

Re: PgTAP: Unit Testing for PostgreSQL

#17

Post by pmontra » Thu, Jul 30, 2015, 7:33 AM UTC

ghufran_syed wrote: post #5

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 w…

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?

Re: PgTAP: Unit Testing for PostgreSQL

#18

Post by thornygreb » Thu, Jul 30, 2015, 12:17 PM UTC

spacemanmatt wrote: post #14

mcfunley wrote: post #4

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 dat…

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.

Re: PgTAP: Unit Testing for PostgreSQL

#19

Post by mateuszf » Thu, Jul 30, 2015, 1:10 PM UTC

thornygreb wrote: post #18

spacemanmatt wrote: post #14

Earlier quoted context omitted.

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.

Re: PgTAP: Unit Testing for PostgreSQL

#20

Post by spacemanmatt » Thu, Jul 30, 2015, 1:12 PM UTC

thornygreb wrote: post #18

spacemanmatt wrote: post #14

Earlier quoted context omitted.

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.

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.