Flask SQLAlchemy Data Mapper vs Active Record Pattern

Question

I have recently started working on Flask and Flask-SQLAlchemy. Coming from Django background I found Flask-SQLAlchmey to be quite complex. I have read that SQLAlchemy implements Data Mapper pattern while Django ORM is based on Active Record Pattern.

Here is a sample code written that implements repository pattern to access the database.

Here is another link of a comment by S.Lott (271k reputation) who says that ORM is the data access layer and it is separate from model.

My questions are these:

1. Can you provide a practical use case in the above example or an example of your own where Data mapper pattern is useful? Everywhere I have read is that data mapper pattern is useful in complex situation, but not seen examples.
2. Is using repositories pattern as in above case same as using a data mapper pattern?
3. Does data mapper advocates write select queries in a different class than the model as done in the example?
4. Why is Question.query.filter_by(text = text).all() not better to use than db.session.query(Question).filter(Question.text == text).all()?

This is not a duplicate of DataMapper vs ActiveRecord pattern because this just tells the definition, I am more interested in the practical examples.

Answer 1

Point by point.

1.

I have a legacy database for which I have to write a few data-handling utilities. Using the Mapper pattern, without ORM / ActiveRecord style, made things for me about as easy when writing queries as ActiveRecord would. It is operating on nice composable objects that resemble SQL clauses, shielded from SQL injections.

Objects being 'passive' allowed for more flexibility / uniformity: a result of a complex join is a named tuple, as is a result of a simple select. There's no identity to care about, no cached objects with the same identity.

All updates are explicit; not a "save" of some state altered elsewhere, no hooks running on .save(), etc. This made efficient batch updates trivial, without troubling if the right data are sent to the DB. Both were benefits in my case. In general case, 'it depends'. For instance, I had to manually fetch database-generated IDs after inserts. Running this query explicitly is a bit of extra work. Being able to do that in one query instead of one per record was a huge boon in my case.

SQLAlchemy has a layered design that allows you to access the lower "mapper" level even if you declare things on upper ORM level and normally operate on it. In Django, for instance, it's not as straightforward if/when still possible.

2.

In the example, the 'repository' looks like a level built above the 'mapper'. The repository could have been built on top of plain DBAPI, but the mapper makes a few things simpler, like nicer parameter binding, named tuples for the result sets, and a wrapper above plain SQL with composable, reusable parts.

The mapper also provides a certain degree of database independence. E.g. SQL Server and Postgres have different ways to concatenate strings; the mapper provides a unified interface.

3.

You write your select where you use it. If you have a select that you constantly reuse in different contexts, you can put it into a method or function. Most of the selects have one use and are built on the spot.

A nice feature of SQLAlchemy's design is that you can easily store conditions and whole where clauses and reuse them across select / update / delete statements.

4.

Question.query.filter_by(text = text).all() uses an implicit transaction. db.session.query(Question).filter(Question.text == text).all() uses an explicit transaction.

Explicit transactions give you a peace of mind with DML. They are important with selects, too, when you are querying a quickly changing database and want your several related selects see the same consistent state.

I usually write a trivial wrapper around sessionmaker and write things like so:

with my_database.transaction() as trans:
   records = trans.query(...)
   ...
   updated = trans.execute(...).rowcount
# Here the transaction commits if all went well.

When I definitely know no DML should run in this block, I use .readonly_transaction() that always rolls back.

In many cases, the implicit transaction is fine. Django allows you to decorate a method with @transaction.atomic and have a semi-explicit transaction control, sufficient in 99% of cases. But sometimes you need even finer granularity.

Answer 2

Completely agree with above answer: yes, SQLAlchemy's Data Mapper pattern is really more flexible and for complex queries it's really more powerful, less magical and more controlled.

But, in simple tasks such as CRUD SQLAlchemy's code becomes too overweight/excessive/redundant.

For example, to just create some object in simplest "create" controller, you need something like this:

user = User(name='Nick', surname='Nickson')
session.add(user)
session.flush()

While in Active Record ORM you will only need single string.

Well, for simple tasks, some of us may want something simpler. I mean it will be cool to have Active Record for SQLAlchemy.

Good news: I recently created package for this (it also contains other useful stuff).

Check it out: https://github.com/absent1706/sqlalchemy-mixins

Answer 3

1. The only reason why I would use Data Mapper over Active Record is if you have serious scalability issues. Data Mapper encourages separation of domain objects and database access logic, whereas Active Records puts database access logic in the domain object. For example when you lift the Flask instance it will connect to the database only on demand, whereas in Django it will always be connected to the database.

2. Data Mapper isolates domain objects from database access logic whereas Repository pattern is a layer between the domain objects and Data Mapper. It's a level higher than Data Mapper. For instance in Data Mapper pattern you'll have straightforward getters and setters, in the repository pattern you'll have getters and setters that might also contain some complex business logic.

3. The Data Mapper is separated from the model class. Only Active Record pattern joins the getters and setters in the same class.

4. I've worked with both SQLAlchemy and Django for awhile and I definitely prefer Django like queries. For my own projects the probability of me using Flask + SQLAlchemy over Django is next to nothing. Productivity and community are the two most deciding factors when considering these two frameworks.