Row locks, column expressions, conflict handling, random order and bugfixes

Release notes

0.29.0

Highlights:

• select_for_update() row locks: lock the queried rows until the end of the transaction, with nowait, skip_locked, read (FOR SHARE) and no_key options. #781, #1963
• Column expressions: compare two columns in a filter, also of related models, or update a column from its own value in one statement, with ormar.F("field") or field accessors. #782, #1962
• Conflict handling: create(on_conflict="ignore") returns None instead of raising IntegrityError, and bulk_create() can skip conflicting rows or update them (upsert). #1016, #1964
• ne operator: filter(name__ne="a") and Model.name != "a". #1962
• Random order: order_by("?") or order_by(ormar.random()). #605, #1971

async with database.transaction():
    jobs = await Job.objects.select_for_update(skip_locked=True).order_by("id").limit(10).all()

await Slot.objects.filter(taken__lt=F("capacity")).all()
await Slot.objects.filter(id=1, taken__lt=F("capacity")).update(taken=Slot.taken + 1)

await Tag.objects.create(name="python", on_conflict="ignore")
await Stock.objects.bulk_create(rows, on_conflict="update", conflict_target=["sku"], update_fields=["amount"])

winners = await User.objects.order_by(ormar.random()).limit(3).all()

Upgrade note: a filter key ending in an operator name is always that operator, now also for ne. Filter a field named like an operator with __exact (ne__exact=1, location__ne__exact=5); a key that is only an operator (gt=1) raises QueryDefinitionError.

🐛 Fixes

• get_or_create() inside database.transaction() runs the insert in a savepoint, so a lost race on PostgreSQL returns the other caller's row instead of aborting the transaction. #1960
• prefixed_table() and the other cached helpers are keyword-only; a positional call produced a duplicate table alias on PostgreSQL. #1961
• nulls_ordering on a mariadb:// URL is emulated like on MySQL instead of emitting NULLS FIRST/LAST. #1966
• Through-model paths without the relation (postcategory__sort_order from Post) join the relation instead of producing a cross join; aggregates on them no longer raise KeyError. #1962

Full notes: release notes