Hacker News Reader

Comment by yummyfajitas | Hacker News Reader

I don't do this because maintaining stored procs is a nightmare. All of a sudden my migrations folder is a mirror of my git history, except that "merge" takes on a nightmarish new meaning.

Nowadays I generally write raw SQL with the Tiramisu library (shameless plug: https://github.com/stucchio/Tiramisu ) I cooked up. It's basically writing straight SQL, but with the ability to safely do (almost) string concatenation to build queries.

View on HN

Why would you need to store the historical versions of the stored procedures in a migrations folder? Wouldn't it be better to treat the stored procs like any other code, create a .sql file for each and keep them in version control? And then you can have a migration script that re-creates all the procs when you do an update.

Generally your migrations folder is supposed to store a complete history of your DB. Maybe doing it the way you suggest is a better way, it's just a bit outside the standard and to be honest I haven't thought carefully about the best way to structure code this way.

Alex at The Daily WTF wrote a great article on this subject.

http://thedailywtf.com/Articles/Database-Changes-Done-Right....

That's a decent read, but unfortunately even running the same precisely-versioned and ordered change scripts against dev, test, prod dbs can produce divergent results depending on the data in the db at the time. For example, prod could have a null somewhere, or data using a different url encoding flavor.... etc.

(Or if you're really lucky, even the current load on the database could make it fail... i.e. transaction serialization failure due to the random row order you attempted to lock)

So it should have ended with 'you're doomed'.

That's a tooling problem. Sqitch or something similar can help.

http://sqitch.org

Reply on news.ycombinator.com