How to save multiple related Django ORM instances in one go without separate database calls via the network?
Here is the example:
article1 = Article(title="Global Warming", content="...")
article2 = Article(title="Visiting Mars", content="...")
comment1 = Comment(content="Some comment", article=article1)
user1 = User(username="user1")
some_updated_article.title = "updated title"
article_to_delete.delete()
In SQLAlchemy, I know you can save multiple instances to the database in one call as follows:
db.session.add_all([article1, article2, comment1, user1])
db.session.commit()
This approach sends all instructions to the database in one go (please correct me if I'm wrong). db.session.add_all()
followed by db.session.commit()
would work, and there wouldn’t be three separate calls to the database server.
In Django, I know I can use bulk_create
, bulk_update
, for each model:
Article.objects.bulk_create([article1, article2])
Comment.objects.bulk_create([comment1])
User.objects.bulk_create([user1])
Article.objects.bulk_update([some_updated_article], fields=["title"])
But this sends separate calls to the database server for each model. Is there a way to achieve something similar to SQLAlchemy's add_all()
where I can send all objects in one go, regardless of the model?
I was thinking about using transaction.atomic for this:
with transaction.atomic():
Article.objects.bulk_create([article1, article2])
Comment.objects.bulk_create([comment1])
User.objects.bulk_create([user1])
Article.objects.bulk_update([some_updated_article], fields=["title"])
Using transaction.atomic()
ensures that all operations either succeed or fail as one atomic transaction. However, in my use case, I don't want full rollback behavior. For example, if there’s an error while creating comments, I want to still save the articles and users successfully. I know Django provides savepoints with transaction.savepoint()
and transaction.savepoint_rollback()
, but it feels a bit cumbersome.
Is there a better way to achieve this in Django? Ideally, I would like to:
Avoid rolling back the entire transaction if one operation fails.
Be notified of which step failed while still saving the successful data.
Make everything in one database call (so if for example my backend server in Australia and my DB in North America, there won't be 4 separate network requests)
What’s the best approach to handle such scenarios? Perhaps there is some reason why you can't do this easily that I don't know and it is better to use default behaviour?
Common table expressions aren't really supported natively by the the ORM, so you might be looking at a cursor
situation to execute some plain old sql (https://docs.djangoproject.com/en/5.1/topics/db/sql/#executing-custom-sql-directly).
Not sure if you are using postgres or another relational database but CTEs should probably be similar between them https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING.
You would maybe end up with something like:
with connection.cursor() as cursor:
cursor.execute(
"""
WITH insertedArticles AS (
INSERT INTO articles
(name, subtitle)
VALUES
('name_value', 'subtitle_value')
RETURNING article_id
)
INSERT INTO comments
(article_id, comment_text)
SELECT
insertedArticles.article_id, $1
FROM insertedArticles;
"""
As always with using raw sql rather than ORM methods, make sure to parameterize inputs.