Sunday, 16 October 2011

My thoughts on ORM

I used to be fanatical about performance. Every database access must be raw SQL, or smart system generated SQL which are optimized so that I do not have to worry about performance, or at the very least, I know how to tweak the SQL to solve performance issues.

However, the buzz of ORM (Object-Relational Mapping) made me explore a few options such as JDO and JPA (I was using datanucleus implementation at one time as that is what Google AppEngine uses). At first I was quite frustrated due to the difficulties I faced to setup correctly in the initial stages. But after a while, I got more comfortable.

Once, I remember developing a Data Synchronization System early Year 2010, which is a JSP + JDO solution that synchronizes data between two databases based on preset rules (defined in JSON, which is in my universe, the king of complex data structures). I had to port this system to MySQL from Microsoft SQL Server, and my limited knowledge of MySQL really got me worried about how difficult this would be. However, it turns out to be just a snap. Generate the DDL (Data Definition Language), to create the database schema, and that's it. I might face a problem or two, which were so minor I cannot remember them, but all I know is, it is magical. The masking of Database tables as Classes and SQL in some different query language, it makes things so transparent. Not only did I not notice any performance impact, but not needing to maintain separate SQLs or DDLs means I do not have to worry about more modifications in the future if I am to add support for, say PostgreSQL. Amazing!

Fast forward to today, as I am playing with Django Framework for Python, I found that ORM are not only abstractions for database independence, but they actually mean you can play around with code more, to put the validation logic into the classes, as well as modifying them to your heart's content, which feels so much more natural than changing the database design, as well as changing the SQL separately in a program source code. Below is the source code for a simple model, enjoy!

class ServerActionHandler(basemodel.CreateUpdateInfo):
    nameRegEx = re.compile(r'[a-zA-Z][a-zA-Z0-9\.]+',re.DOTALL)
    name = models.CharField(max_length=128,unique=True)
    state = models.CharField(max_length=1,choices=basemodel.DEFAULT_STATE_CHOICES)
    method = models.CharField(max_length=256)
    def clean(self):
        if not self.__class__.nameRegEx.match(
            raise ValidationError('ServerAction name must start with an alphabet and can only contain alphanumeric and dot (.).')
    def __str__(self):