Should I create mapper objects or use the declarative syntax in SQLAlchemy?

Question

There are two (three, but I'm not counting Elixir, as its not "official") ways to define a persisting object with SQLAlchemy:

Explicit syntax for mapper objects

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import mapper

metadata = MetaData()

users_table = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
)

class User(object):
    def __init__(self, name):
        self.name = name

    def __repr__(self):
       return "<User('%s')>" % (self.name)

mapper(User, users_table) # &lt;Mapper at 0x...; User&gt;

Declarative syntax

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class User(Base):
     __tablename__ = 'users'
     id = Column(Integer, primary_key=True)
     name = Column(String)

     def __init__(self, name):
         self.name = name

     def __repr__(self):
         return "<User('%s')>" % (self.name)

I can see that while using the mapper objects, I separate completely the ORM definition from the business logic, while using the declarative syntax, whenever I modify the business logic class, I can edit right there the database class (which ideally should be edited little).

What I'm not completely sure, is which approach is more maintainable for a business application?

I haven't been able to find a comparative between the two mapping methods, to be able to decide which one is a better fit for my project.

I'm leaning towards using the "normal" way (i.e. not the declarative extension) as it allows me to "hide", and keep out of the business view all the ORM logic, but I'd like to hear compelling arguments for both approaches.

Answer 1

"What I'm not completely sure, is which approach is more maintainable for a business application?"

Can't be answered in general.

However, consider this.

The Django ORM is strictly declarative -- and people like that.

SQLAlchemy does several things, not all of which are relevant to all problems.

1. SQLAlchemy creates DB-specific SQL from general purpose Python. If you want to mess with the SQL, or map Python classes to existing tables, then you have to use explicit mappings, because your focus is on the SQL, not the business objects and the ORM.

2. SQLAlchemy can use declarative style (like Django) to create everything for you. If you want this, then you are giving up explicitly writing table definitions and explicitly messing with the SQL.

3. Elixir is an alternative to save you having to look at SQL.

The fundamental question is "Do you want to see and touch the SQL?"

If you think that touching the SQL makes things more "maintainable", then you have to use explicit mappings.

If you think that concealing the SQL makes things more "maintainable", then you have to use declarative style.

• If you think Elixir might diverge from SQLAlchemy, or fail to live up to it's promise in some way, then don't use it.

• If you think Elixir will help you, then use it.

Answer 2

In our team we settled on declarative syntax.

Rationale:

• metadata is trivial to get to, if needed: User.metadata.
• Your User class, by virtue of subclassing Base, has a nice ctor that takes kwargs for all fields. Useful for testing and otherwise. E.g.: user=User(name='doe', password='42'). So no need to write a ctor!
• If you add an attribute/column, you only need to do it once. "Don't Repeat Yourself" is a nice principle.

Regarding "keeping out ORM from business view": in reality your User class, defined in a "normal" way, gets seriously monkey-patched by SA when mapper function has its way with it. IMHO, declarative way is more honest because it screams: "this class is used in ORM scenarios, and may not be treated just as you would treat your simple non-ORM objects".

Answer 3

I've found that using mapper objects are much simpler then declarative syntax if you use sqlalchemy-migrate to version your database schema (and this is a must-have for a business application from my point of view). If you are using mapper objects you can simply copy/paste your table declarations to migration versions, and use simple api to modify tables in the database. Declarative syntax makes this harder because you have to filter away all helper functions from your class definitions after copying them to the migration version.

Also, it seems to me that complex relations between tables are expressed more clearly with mapper objects syntax, but this may be subjective.