SQLAlchemy is a very powerful and easy to use ORM written in Python. It really helps to develop applications faster. This blog post is about how we can use Unit of Work pattern when using SQLAlchemy ORM (an ORM written in Python for Relational Databases). This blog post assumes that you are already familiar with SQLAlchemy, know how to setup and connect to database and know how to play with basic operations using SQLAlchemy.
When a business transaction involves several database operations, traditionally what you do is you perform each operation separately inside a transaction block. This can lead to lots of very small database calls, which ends up being very slow. Moreover it requires you to have a transaction open for the whole interaction, which is impractical if a business transaction needs multiple requests to be completed. This is a problem in traditional way of performing a business transaction. The Unit of Work design pattern helps to solve these kind of situations.
A Unit of work is a design pattern which is basically used in performing database transactions. “A Unit of Work is used to maintain a list of objects affected by a business transaction and to coordinate the writing out of these changes.” — Martin Fowler
A Unit of Work keeps track of everything you do during a business transaction that can affect the database. When you’re done, it figures out everything that needs to be done to alter the database as a result of your work [2]. Unit of Work design pattern does two important things [3]:
Still confusing? Lets illustrate this with an example.
Suppose, you have an e-commerce application, where your users can order products by using your application. When a user creates an order, this order can contain several items of different quantity. You need to save the order along with the items and the quantity into the database.
Suppose the Database models are as follows:
Note: The models bellow are PostgreSQL compatible only. You can use any other Relational Databases supported by SQLAlchemy and change the models accordingly.
models.py: you can check the bellow code here too.
And, the incoming data having the order details is of the following structure. You can check the following code here too.
In the above order creation operation, session.commit() will be called 3 times to complete the transaction, i.e., it’s calling database 3 times. So, what if the payload you are saving is much bigger? This will use more number of database call. Let’s try to get the same effect in database using Unit of Work.
operations.py: you can check the following code here too.
Here, notice that session.commit() is called only once. All the other operations (in this case, the insert) were done in-memory and then sent to database as a big unit of work. SQLAlchemy will handle the rest of the thing using Unit of Work pattern. This way is much more efficient than the traditional one. It doesn’t require you to save each object separately, thus helps to prevent the transaction being slow. You just need to manipulate the objects in-memory and then send the whole unit of objects at a time, SQLAlchemy will handle the rest of the thing on your behalf. This pattern can be applied in the case of update and delete operations too. In those cases, you’ll update and/or delete multiple objects in-memory and finally apply session.commit() to reflect the changes in database.
Happy Coding !!!