Why do you use an Object Relational Mapping (ORM) System in Development?
Here’s a programmer that is saying goodbye to ORMs at Hatful of Hollow.
And another site offering a tutorial of sorts dealing with ORMs Why should you use an ORM.
While both have their points, both have missed a fundamental benefit that an ORM hands you.
Most of my development is in Pylons. Django’s ORM and template language can do the same thing. A programmer that has used PHP/Smarty to develop large scale systems will likely resist ORMs. After working with a team to develop 90k+ lines of PHP/Smarty over a six year period, making the shift required a paradigm shift.
Let’s consider the following structure. We have a cp_ticket table and a cp_ticket_detail table. A Ticket can have multiple detail records. The output we wish to have is:
ticket id, ticket header information
ticket detail line
ticket detail line #2
ticket id, ticket header information
ticket detail line
ticket detail line #2
ticket detail line #3
ticket id, ticket header information
ticket detail line
ticket detail line #2
Our model:
class cp_ticket(DeclarativeBase):
__tablename__ = 'cp_ticket'
ticket_id = Column(mysql.MSBigInteger(20, unsigned = True), primary_key=True, autoincrement = True)
priority = Column(mysql.MSEnum('1','2','3','4','5'), default = '3')
ticket_detail = relation('cp_ticket_detail', order_by='cp_ticket_detail.ticket_detail_id')
class cp_ticket_detail(DeclarativeBase):
__tablename__ = 'cp_ticket_detail'
ticket_id = Column(mysql.MSBigInteger(20, unsigned = True), ForeignKey('cp_ticket.ticket_id'), default = '0')
ticket_detail_id = Column(mysql.MSBigInteger(20, unsigned = True), primary_key=True, autoincrement = True)
stamp = Column(mysql.MSTimeStamp, PassiveDefault('CURRENT_TIMESTAMP'))
detail = Column(mysql.MSLongText, default = '')
Our query to pass to our template:
tickets = meta.Session.query(cp_ticket).filter(cp_ticket.client_id==1).all()
Compared with the query as you would write it without an ORM:
select * from cp_ticket,cp_ticket_detail where client_id=1 and cp_ticket.ticket_id=cp_ticket_detail.ticket_id;
Both are doing the same fundamental thing, but, the ORM maps the results almost identical to the way we want to display the data. This makes template design easy.
Using Mako, we use the following code to display the results:
<table border="1">
<tr><th>Ticket ID</th><th>Status</th><th>Detail</th></tr>
%for ticket in tmpl_context.tickets:
<tr>
<td><strong>${ticket.ticket_id}</strong></td>
<td><strong>${ticket.priority}</strong></td>
</tr>
%for detail in ticket.ticket_detail:
<tr>
<td></td>
<td>${detail.stamp}</td>
<td>${detail.detail}</td>
</tr>
% endfor
% endfor
</table>
To do the same thing without using an ORM, you need to revert to a control break structure similar to the following:
current_ticket=0
for ticket in tickets:
if (current_ticket != ticket.ticket_id):
#new row, print the header
print "<tr><td>first piece</td></tr>"
current_ticket = ticket.ticket_id
# print our detail row
print "<tr><td></td><td>stamp and detail</td></tr>"
Control Break structures require you to be able to set a variable within your template language. Some template languages don’t allow that. If your template language (in any language) can’t do variable assignments in the template, guess where your html generation logic needs to go?
With an ORM, the template contains your display logic. Your webmaster/design team can modify the template without having to modify html contained within your code. The loops are simple to understand and designers usually have little problem avoiding the lines that start with %.
Sure, you could wrap much of this logic in your template to do the control-break structure, but, as you get more complex data, deciding how to display the data requires a define or some other functionality.
An ORM adds some insulation to the process, but, the result is a much easier page structure when displaying related data. Granted there are some performance hits and SQLAlchemy appears to create some queries that are not optimal, unless there is a tremendous performance hit, I think the benefits of the ORM for developing a web application are tremendous.
Once you move into an environment where you are dealing with multiple developers, having a defined schema with comments is much easier than using reflection to figure out what the meaning of a status field as enum(‘U’,’A’,’P’,’C’,’R’,’S’).
However, as the original poster mentions, you can do raw SQL within SQLAlchemy and do all of your work with reflection as he has done with his ORM^H^H^H, abstraction. If he’s still using SQLAlchemy, he can selectively decide when to use it and when to avoid it.