I have a project where I would want to (need to?) mix SQLAlchemy models, Django models and respective ORMs in the same web codebase. I'd like to have atomic requests by default and tie SQLAlchemy transaction lifecycle to Django request and Django transaction manager.
Does there exist prior art how to make SQLAlchemy to use Django/connections and transaction machinery or vice versa?
What would be good starting point for such integration work? What limitations there are e.g. if you try to reuse the same database connection?
To narrow down the problem:
Django ORM and SQLAlchemy ORM won't touch the same tables
At the first step, all I care is that when the HTTP request ends both transaction managers commit in somewhat coherent manner e.g. if Django commits the transaction SQLAlchemy does also
How can I I say SQLAlchemy to use the database connection configured for Django?
Can I bind SQLAlchemy session to Django transaction manager? When Django opens the database connection I could open a new SQLAlchemy session and bind it to opened Django transactions? When Django commits I could signal SQLAlchemy to flush its stuff so it goes along the same cmmit. Django 1.6 introduced new semantics on atomic transactions, so this might help.
That's really not going to be easy. I wonder if the effort is worth it. SQLAlchemy and Django use very different abstractions and patterns to deal with object persistence and transactions.
The Django ORM follows the Active Record pattern, in which the object maps more directly to a database table and encapsulates all access and logic. Changes in the object translate directly into a row being changed by SQL code when you call the `save()' method. You can manage transactions on your own, but everything is basically just syntactic sugar for dealing with the underlying database.
SQLAlchemy follows the Data Mapper pattern, where there's another layer of abstraction responsible for moving data between the active objects and the database, independent of each other. The objects don't even know there's a database present, and the mapping between object and database table is very, very flexible. Also, SQLAlchemy has another transaction layer on the Python side, following the Unit of Work pattern, which basically encapsulates the whole SQL transaction as an atomic entity. Objects are tracked in the session by primary key, and changes are saved atomically, in correct order.
Coming from Django, the first time I worked with Flask and SQLAlchemy I did a mistake a lot of people do, which is to try to mimic the usage patterns from the Django ORM on SQLAlchemy. For instance, creating a save() method that commits the transaction looks like something obvious to do when you're used to the Django-ORM, but that's a terrible idea in SQLAlchemy. I learned the hard way how they don't mix very well.
The SQLAlchemy declarative base method encapsulates the class-mapper-table relationship and makes it look more like the ActiveRecord pattern, but that can be very misleading, because you start to think the object itself have knowledge of the database.
If you REALLY need to do that, considering how SQLAlchemy semantics maps more cleanly to the database, I think the best bet is treating SQLAlchemy itself as a database and create an SQLAlchemy backend that knows how to map Django models and queries to an SQLAlchemy model-mapper-table. Maybe you can even use the Django model itself with an SQLAlchemy mapper-table.
So, for instance, when you run the save() method in Django, instead of generating and running SQL on the database, it should retrieve and change the equivalent SQLAlchemy object from the current session, so anyone dealing with the object on the SQLAlchemy layer sees everything as if it were the database. When you commit the transaction in Django, you commit the SQLAlchemy session.
That might be an interesting exercise, but I really don't see much point in doing that for real-world use cases.
It looks like all you want is to synchronize Django transactions with SQLAlchemy sessions. You don't need to share the connection instance for that. You can use something like django-transaction-hooks to trigger a callback responsible for committing the SQLAlchemy session. If you need to do the opposite, commit the Django transaction when the SQLAlchemy session is commited, you can use the after_commit event.
Be aware that you won't have atomicity between the two engines. If omething goes wrong in the SQLAlchemy commit, you can't rollback the Django commit and vice-versa.