3 ms·

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 th

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?

joevandyk 11y ago
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)
rhizome31 11y ago
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.
pmontra 11y ago
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?
- ghufran_syed 11y ago
  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.

joevandyk 11y ago

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)

rhizome31 11y ago

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.

pmontra 11y ago

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?

ghufran_syed 11y ago
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.