Skip to content

Bulk writes across relations, atomic upserts, update_returning, relation traversal and SQLAlchemy 2.1

Latest

Choose a tag to compare

@collerek collerek released this 16 Nov 10:21
· 2 commits to master since this release
cd6bdb7

Release notes

0.30.0

Highlights:

  • Bulk writes across relations: update() and delete() accept filters on related models of any kind, and expressions in update() can read fields of models reached through foreign keys. #1967, #1973
  • Atomic upserts: create(on_conflict="update") inserts or updates in one statement, and update_values sets expressions on the conflicting row (F("x") is the existing value, ormar.Inserted("x") the new one). #1978, #1985
  • update_returning() updates rows and returns them as stored. #1986
  • Greatest, Least, Coalesce and Random in column expressions. #1977
  • Columns: enums stored by value with ormar.Enum(store_values=True), unsigned integers with unsigned=True. #1991, #1992
  • Relation traversal without a precomputed relation map: model_dump(), save_related(), get_pydantic() and follow=True stay fast and correct on densely related schemas. #1998
  • SQLAlchemy 2.1 supported. #1981
await Booking.objects.filter(training__instructor=instructor).delete()
await Member.objects.create(
    chat=chat, user=user, read_id=message_id,
    on_conflict="update", conflict_target=["chat", "user"],
    update_values={"read_id": Greatest(F("read_id"), Inserted("read_id"))},
)
expired = await Booking.objects.filter(status="waitlisted").update_returning(status="expired")

Please read the Breaking changes before upgrading: relation traversal (the old map mode stays for one release), select_all(follow=True) / load_all(follow=True) follow only forward foreign keys 5 levels deep by default, get_pydantic() nested model names, table aliases in raw SQL, values() / values_list() auto-joins and ormar.Enum option forwarding.

✨ Breaking changes

  • Relations are traversed from the loaded instances, not from a precomputed relation map (#1998)

    model_dump(), save_related(), get_pydantic() and select_all(follow=True) / load_all(follow=True) decided which relations to follow from a relation tree cached on each model class. On densely related schemas (4+ models related to each other) that tree left out valid paths and contained paths with a model twice on them, and building it correctly grows exponentially. The default relation map is now decided per node from the set of models on the current path (a relation is followed unless its target is already on the path, a direct self relation once), and the nodes are shared and cached per class (at most 256 per class). Where the old tree was right the output is identical.

    The old behaviour stays available for one release with ORMAR_RELATION_TRAVERSAL=map (environment variable) or ormar.models.traversal.set_relation_traversal(mode="map"). Selecting it emits a DeprecationWarning; the map mode and the relation tree are removed in 0.31.0.

  • follow=True follows forward foreign keys, 5 levels deep (#1998)

    select_all(follow=True) and load_all(follow=True) followed every relation without a limit, which on dense schemas meant hundreds of thousands of joins and multiplied rows. They now follow foreign keys and one to one relations of the model (nullable ones too) up to max_depth levels (default 5), so the joins never multiply rows. Reverse foreign keys and many to many relations are loaded with follow_reverse=N (they are followed in the first N levels, True = 1) or by naming them in select_related(). Unlike Django's select_related() without arguments, nullable foreign keys are followed, as ormar foreign keys are nullable by default.

    await Department.objects.select_all(follow=True, follow_reverse=2).get()
    await chapter.load_all(follow=True, max_depth=2)
  • Table aliases in generated SQL change (#1998)

    Joined tables and their column labels are prefixed with a 10 hex digit hash of the relation path instead of 6 (t6bba8518ed_albums instead of t1b26c7_albums), so two paths of one query practically never share an alias. Only code that matches on the raw SQL text (tests asserting SQL, query log filters) is affected.

  • get_pydantic() generates one named nested model per model (#1998)

    Nested relations point to a nested model of the related model (Book_nested), so cycles become recursive models ($ref in OpenAPI) instead of a fixed-depth copy of the tree. Relation fields of nested models are optional and include the relation back to the parent; the root model keeps its required/optional rules. Names no longer have a random suffix and do not depend on the order of calls (Book, Book_nested, Book_depth2, a hash suffix for include/exclude/flatten_fields), so the OpenAPI document is stable between runs. New parameters: max_depth= limits nesting, omit_back_relations=True leaves the relation back to the parent out of nested models (closer to the old shape, more models). Generated models validate an ormar instance through its model_dump(), so returning an instance from a FastAPI endpoint does not loop over its relations. This also fixes the cached nested model of a class being reused under every parent (Author.get_pydantic() followed by Book.get_pydantic() returned Book without author).

    Migration: if a response must keep exactly one level of nesting use get_pydantic(max_depth=1); if select_all(follow=True) / load_all(follow=True) must load reverse or many to many relations pass follow_reverse=N or list them in select_related(); for deeper forward chains pass max_depth=.

  • values() and values_list() join the relations of selected related fields (#2005)

    Related fields they select (e.g. values(["name", "category__name"]), also after fields()) are joined like in filters. Without select_related() those fields used to be silently dropped. As with select_related(), a reverse or many to many relation returns one row per related model, so such queries can return more rows than before.

  • ormar.Enum forwards sqlalchemy.Enum options (#1385, #1987)

    values_callable, native_enum, create_constraint, length, validate_strings, schema, metadata, inherit_schema and quote are passed to the column type. They used to be accepted and silently dropped, so e.g. an int enum with values_callable stored member names ("UK") instead of values. If you passed any of them, check the generated DDL and the stored data: rows written as names are not read back with values_callable.

🆕 Features

  • update() and delete() with related models (#1967, #1973)

    update() and delete() accept filters on related models of any kind (forward, reverse, many to many) and touch the rows the same filters select in all(), instead of raising QueryDefinitionError. Expressions in update() can read fields of models reached through foreign keys; rows with a NULL foreign key on the path are not updated. See update.

    await Booking.objects.filter(training__instructor=instructor).delete()
    await Track.objects.filter(album__name="A").update(position=F("album__rank"))
  • Greatest, Least and Coalesce in column expressions (#1974, #1977)

    Functions for filters and update(), e.g. atomic "never go back" or "never below zero" updates. On SQLite Greatest / Least compile to the scalar max() / min(); with a NULL argument PostgreSQL ignores it, MySQL and SQLite return NULL. See update.

    await Member.objects.filter(id=1).update(read_id=Greatest(F("read_id"), newest))
    await Pass.objects.filter(id=1).update(refunded=Coalesce(F("refunded"), 0) + amount)
  • Random() in column expressions (#1977)

    A random float in [0, 1) per row for filters and update(), also accepted by order_by() like "?" and ormar.random().

    await Player.objects.update(each=True, score=Random())
  • Atomic upsert with create(on_conflict="update") (#1975, #1978)

    Inserts the row or updates the one it conflicts with on conflict_target, in one statement, and returns the stored row. Unlike update_or_create() concurrent calls neither fail nor overwrite each other. pre_save / post_save are sent in both cases. See conflict handling.

    device = await Device.objects.create(
        endpoint_hash=h, keys=k,
        on_conflict="update", conflict_target=["endpoint_hash"], update_fields=["keys"],
    )
  • update_returning(): update rows and get them back (#1986)

    QuerySet.update_returning(each=False, **kwargs) (and on QuerysetProxy) updates like update(), with the same filters and expressions, and returns the updated models as stored, with their own columns. PostgreSQL and SQLite (3.35+) run one UPDATE ... RETURNING; MySQL locks the rows with SELECT ... FOR UPDATE, updates them by primary key and reads them back in one transaction. update() is unchanged. See update_returning.

    expired = await Booking.objects.filter(status="waitlisted").update_returning(
        status="expired"
    )
  • Expressions on conflict with update_values (#1985)

    create() and bulk_create() with on_conflict="update" take update_values: values or column expressions set on the conflicting row in the same statement, e.g. an atomic counter or a "never go back" marker. F("x") is the existing row's column, the new ormar.Inserted("x") the value the insert tried to write (excluded.x, VALUES(x) on MySQL). With update_values the update_fields default to none; on_update fields not named in either still get their value. The rules of expressions in update() apply, the statement is cached per expression shape, and MySQL bulk upserts keep their single batch. See expressions on conflict.

    await TrackLevel.objects.create(
        track=track, level=3, completed=1,
        on_conflict="update", conflict_target=["track", "level"],
        update_values={"completed": F("completed") + 1},
    )
    await Member.objects.create(
        chat=chat, user=user, read_id=message_id,
        on_conflict="update", conflict_target=["chat", "user"],
        update_values={"read_id": Greatest(F("read_id"), Inserted("read_id"))},
    )
  • Enums stored by value with ormar.Enum(store_values=True) (#1991)

    An int enum gets an INTEGER column with a check constraint on its values instead of a string enum column, so the database holds 1, 2 and compares them as numbers. Other enums store their values instead of member names.

    class Country(IntEnum):
        UK = 1
        US = 2
    
    country: Country = ormar.Enum(enum_class=Country, store_values=True)
    # country INTEGER NOT NULL CHECK (country IN (1, 2))
  • Unsigned integers with unsigned=True (#1015, #1992)

    Integer, BigInteger and SmallInteger accept unsigned=True: the column is UNSIGNED on MySQL and MariaDB (foreign keys to it too), gets CHECK (column >= 0) on PostgreSQL and SQLite, and minimum defaults to 0. Alembic autogenerate no longer recreates columns that were made unsigned by hand.

  • Rows as tuples from the query executor (#1983)

    fetch_all() and fetch_one() of database.get_query_executor() take as_tuples=True and return tuples of values in select order instead of rows keyed by result label, with the same type conversion.

    async with database.get_query_executor() as executor:
        rows = await executor.fetch_all(select(users.c.id, users.c.name), as_tuples=True)
        # -> [(1, "a"), (2, "b")]

🐛 Fixes

  • OrmarConfig(constraints=[...]) and ormar_config.copy(constraints=[...]) accept IndexColumns for type checkers. The annotation was list[ColumnCollectionConstraint], which IndexColumns (a subclass of sqlalchemy.Index) is not, so mypy rejected it although it worked at runtime. #1982
  • fetch_val(query, column=...) of the query executor returned the first column for any column when the statement ran through SQLAlchemy (drivers without native execution, native_execution=False); it returns the requested column now. #1984
  • Two relation paths of one query whose table aliases had the same hash raised QueryDefinitionError: Alias ... collides with an already joined relation (since 0.27.0, aliases were a 24 bit hash of the relation path). Rare for one query, but likely with many joins (about 11 % for 2,000 joins). Aliases are now a 40 bit hash (0.0002 % for 2,000 joins), and a remaining collision is resolved: every alias is owned by the first path that gets it and a colliding path gets the next free salted hash. #1998

📝 Docs

  • SQLAlchemy core statements: how to run core statements on ormar's connection and in its transactions with database.get_query_executor(), what its methods return and how to turn the rows back into models. See SQLAlchemy core statements. #1984

⚡ Performance

  • Relation trees keep their size with densely related models (#1995, #1996)

    The relation tree behind model_dump(), save_related() and get_pydantic() (only used by the deprecated map traversal since #1998) was built by spelling out every relation path of the model, and the number of paths grows exponentially with how densely models are related (self references, several relations to the same model, reverse relations). It was cached on each model class, so a class could keep tens of megabytes for the life of the process. The tree is now built directly and a repeated subtree is stored once: 7 models related to each other through foreign keys and many to many relations take 0.02 MB and 3 ms instead of 64 MB and 1.2 s per class. The tree has the same content; the flat list of paths is only built for load_all(follow=True) and select_all(follow=True).

  • Upserts reuse their compiled statement (#1979, #1980)

    SQLAlchemy gives no cache key to inserts with ON CONFLICT / ON DUPLICATE KEY, so create(on_conflict=...) and bulk_create(on_conflict=...) compiled their statement on every call and asyncpg prepared it again for a single row. Statements without a cache key now keep their plan per statement object (bounded like the other plans): a one-row upsert on PostgreSQL takes one round trip instead of up to three.

  • MySQL bulk upserts with on_update fields run as one batch (#1979, #1980)

    bulk_create(on_conflict="update") of a model with on_update fields ran one statement per row on MySQL. The on_update values are now set once in user variables, so the rows go in one multi-row insert.

📦 Dependencies

  • SQLAlchemy 2.1 supported, the range is now >=2.0.50,<2.2. Native execution reads the asyncpg transaction state the way both versions do, and an empty execute_many() no longer passes the empty parameter list SQLAlchemy 2.1 deprecates. The lower bound goes up from 2.0.40, because ormar 0.29 already needed 2.0.50 (asyncmy error translation and asyncpg disconnects failed on older releases); CI now also runs the suite on 2.0.50. #1981

Full notes: release notes