# RE: Using rails with stored procedures

**URL:** <https://www.ruby-forum.com/t/re-using-rails-with-stored-procedures/50768>\
**Category:** Rails\
**Created:** [December 21, 2005, 12:43am UTC](https://www.ruby-forum.com/t/re-using-rails-with-stored-procedures/50768 "2005-12-21T00:43:28Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![OlsonS\_Curtis\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/a88e4f/32.png) [@OlsonS\_Curtis\_B](https://www.ruby-forum.com/u/OlsonS_Curtis_B)\
**Post date:** [December 21, 2005, 12:43am UTC](https://www.ruby-forum.com/t/re-using-rails-with-stored-procedures/50768/1 "2005-12-21T00:43:28Z")

</div>

> Stored Procedures is not the rails way…
> 
> All features that make our database, [business] smart we  
> hate…

It’s hard to disagree with that. However, I’m working (playing  
actually) with an interesting quandary, specifically with the PostGIS  
extensions to PostgreSql. Not really stored procedures, but much of the  
functionality is encapsulated as functions within the database. If you  
want to know the airports within 100 km of Berlin, you create a SELECT  
statement with a function call embedded in it.

This kinda rubs me the wrong way. If I’m using Active Record, this is  
not just an attribute I can get to, I’ll have to make another round trip  
to the database. But, I can’t see another way. There are some  
geo-functions that could be calculated on the object already retrieved  
(like say, what are the dimensions of a rectangle that encloses this  
geo-shape) and which could be encapsulated at the object level, but  
clearly finding how distinct geo-shapes relate to each other needs a  
PostGIS query. Can this still be considered “the rails way”?

Please do tell me if you find my thinking flawed, I’d love to be set  
straight.

Cheers,  
Curtis Olson

---

<div class="post-metadata">

**Author:** ![OlsonS\_Curtis\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/a88e4f/32.png) [@OlsonS\_Curtis\_B](https://www.ruby-forum.com/u/OlsonS_Curtis_B)\
**Post date:** [December 21, 2005, 1:55am UTC](https://www.ruby-forum.com/t/re-using-rails-with-stored-procedures/50768/2 "2005-12-21T01:55:51Z")

</div>

I have been watching the rails stored procedure discussion that keeps  
popping up here and elsewhere for a while.

Has anyone suggested that stored procedures be allowed if they are  
associated as methods on models? ActiveRecord could include code to  
form  
the association (e.g. set\_stored\_proc\_method) and code to handle the  
return  
values and to refresh the model if necessary.

For example:

# :stored\_proc should be optional if the stored procedure name is the

same  
as the method name

# e.g. :doFunDBStuff has a stored procedure named doFunDBStuff

# overrides for parameters could also be allowed in case they cannot be

pulled from the schema

# or need a type definition or name change

class myModel \< ActiveRecord::Base  
set\_stored\_proc\_method :doFunDBStuff, :stored\_proc =\> “myStoredProc”  
end

What do you guys think? I know this “isn’t the Rails way”, but wouldn’t  
this make Rails even more useful without changing the preferred way of  
working with Rails?

---

<div class="post-metadata">

**Author:** ![OlsonS\_Curtis\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/a88e4f/32.png) [@OlsonS\_Curtis\_B](https://www.ruby-forum.com/u/OlsonS_Curtis_B)\
**Post date:** [December 21, 2005, 2:34am UTC](https://www.ruby-forum.com/t/re-using-rails-with-stored-procedures/50768/3 "2005-12-21T02:34:54Z")

</div>

That’s how I’ve been using stored procs.

It’s fine to say stored procs aren’t part of “the Rails way”, but the  
reality is that for many many apps that are a good fit for Rails,  
they’re mandatory. Stored procs are widely used to enforce security,  
by ensuring that only a controlled set of interfaces (i.e. stored  
procs and views) are made available to people wishing to access data.  
In these cases, either you use stored procs with Rails, or you use  
stored procs with something else (C#, Java, …). I’m not prepared to  
sacrifice the productivity that comes with Rails when I need to work  
with stored procs.

Stored procs also have some significant performance/scalability  
benefits compared to other solutions.

Finally, many businesses quite rightly realise that their data is  
absolutely fundamental to their continued operation, and just aren’t  
going to throw open their databases to any (Rails) developer that  
might come along. You want to access their data - you play by their  
rules. If you want to use Rails for only green fields apps, or for  
small department-level stuff, that’s fine, but I think doing so  
greatly limits your ability to leverage Rails’ productivity benefits.  
Look at it this way - if Rails gives (say) a 5x productivity increase  
over Java, where are the truly noteworthy savings going to be? The  
app that would take 5 man-months in Java, or the app that would take  
15 man-years in Java?

On the other hand, I’m finding it hard to use an agile approach to app  
development that includes stored procs - for anything non-trivial,  
dependencies within and between stored procs start to become too  
difficult to track. If I’ve got (say) 50 stored procs and need to  
change one to e.g. cater for a new field, there can be a sizeable  
amount of testing required to ensure that the change doesn’t break  
another stored proc somewhere. If anyone can point me to a reference  
detailing a solution for this issue, I’d greatly appreciate it.

Regards

Dave M.