Adding x-kwargs to the relationship arguments

[eheiberg]

I am wondering if there is a way to specify in my openapi.yml to pass in the argument passive_deletes=True to the relationship parameters of the model.

For Reference: https://docs.sqlalchemy.org/en/14/orm/cascades.html#passive-deletes

An example:

Setup:

I have an Employee that belongs to a Division.
In the database, I have already set up Cascading Deletes.

Problem:

I need to tell SQLAlchemy that the database is going to be responsible for the cascading deletes. By passing the kwarg passive_deletes=True to the relationship specified in the parent model (Division), I can keep SQLAlchemy from erroring out when it attempts to delete the Division with that Employee in it.

An example openapi.yml file

openapi: "3.0.0"

info:
  title: Test Schema
  description: API to illustrate One to Many Relationships.
  version: "0.1"

paths:
  /division:
    get:
      summary: Used to retrieve all divisions.
      responses:
        200:
          description: Return all divisions from the database.
          content:
            application/json:
              schema:
                type: array
                items:
                  "$ref": "#/components/schemas/Division"
  /employee:
    get:
      summary: Used to retrieve all employees.
      responses:
        200:
          description: Return all employees from the database.
          content:
            application/json:
              schema:
                type: array
                items:
                  "$ref": "#/components/schemas/Employee"

components:
  schemas:
    Division:
      description: A part of a company.
      type: object
      x-tablename: divisions
      properties:
        id:
          type: integer
          description: Unique identifier for the division.
          example: 0
          x-primary-key: true
        name:
          type: string
          description: The name of the division.
          example: Engineering
        employees:
          type: array
          items:
            "$ref": "#/components/schemas/Employee"
          description: The employees working in the division.
    Employee:
      description: Person that works for a company.
      type: object
      x-tablename: employees
      properties:
        id:
          type: integer
          description: Unique identifier for the employee.
          example: 0
          x-primary-key: true
        name:
          type: string
          description: The name of the employee.
          example: David Andersson
      x-foreign-key: divisions.id
      x-foreign-key-kwargs:
        name: employee_divisions_fkey
        ondelete: CASCADE

You can see from the above example, that, through the foreign-key kwargs, I've made it so that the database will ultimately be responsible for the cascading deletes.

However, this does not satisfy SQLAlchemy. If I can pass in the passive_deletes kwarg to the parent relationship, (as specified in the link at the top), I think we'd be great.

Is this currently possible with OpenAlchemy?

Thanks

[jdkandersson]

Yes I believe it is possible, please take a look at this example, if it isn't what you are looking for please let me know: https://openapi-sqlalchemy.readthedocs.io/en/latest/examples/relationship/many_to_one.html#relationship-kwargs

[eheiberg]

Hi, just now getting back to this.
I wanted to set the relationship kwargs passive_deletes option to "all".
According to the SQLAlchemy docs this will keep the ORM from attempting to set the foreign key in the child object to NULL before attempting to delete the parent.

Using the example you provided in the above comment it seems I should be able to do this:

components:
  schemas:
    Division:
      description: A part of a company.
      type: object
      x-tablename: division
      properties:
        id:
          type: integer
          description: Unique identifier for the division.
          example: 0
          x-primary-key: true
        name:
          type: string
          description: The name of the division.
          example: Engineering
    Employee:
      description: Person that works for a company.
      type: object
      x-tablename: employee
      properties:
        id:
          type: integer
          description: Unique identifier for the employee.
          example: 0
          x-primary-key: true
        name:
          type: string
          description: The name of the employee.
          example: David Andersson
        division_id:
          type: integer
          format: int64
          x-index: true
          x-foreign-key: employee.id
          x-foreign-key-kwargs:
            name: employee_division_id_fkey
            ondelete: CASCADE
        division:
          allOf:
            - "$ref": "#/components/schemas/Division"
            - x-kwargs:
                passive_deletes: "all"

Note: all I've done here is

1. Change the x-kwargs to use the passive_deletes kwarg, and set it to all.
2. Added a division_id to Employee, and then gave it x-foreign_key_kwargs of ondelete: CASCADE, which is how I am getting the database to be responsible for cascading the deletions. (That part works, BTW.)

Problem:

It still seems I'm getting an error when attempting to delete a Division; SQLAlchemy (v 1.4) is still attempting to null out the division_id in the employee(s) before attempting to delete the division. This of course gives a NULL Constraint error, as my employee.division_id is not nullable.

Q1: Do you see anything that I might be doing wrong here?
Q2: Is there any way I can see or generate the models in a way such that I could get transparency into what is actually being generated by the models?

Thank you as always!

[eheiberg]

Ah follow-up, when using SQLAchemy's inspect feature, I'm not seeing that the [object].mapper.passive_deletes is being set at all. It's still set to the default (false).

[jdkandersson]

What version of OpenAlchemy are you using? When I run this against master I get a couple of errors related to that the division_id property is a foreign key on employee.id which seems incorrect, shouldn't it be division.id? Also, division_id is int64, whereas id on Division is not, OpenAlchemy expects those to be aligned.

I also see the correct relationship being constructed on the latest version including the passive_deletes option set to all after I fix the above errors. Here is a reading of the relationship configuration:

{
    'uselist': None,
    'argument': 'Division',
    'secondary': None,
    'primaryjoin': None,
    'secondaryjoin': None,
    'post_update': False,
    'direction': None,
    'viewonly': False,
    'sync_backref': None,
    'lazy': 'select',
    'single_parent': False,
    '_user_defined_foreign_keys': None,
    'collection_class': None,
    'passive_deletes': 'all',
    'cascade_backrefs': True,
    'passive_updates': True,
    'remote_side': None,
    'enable_typechecks': True,
    'query_class': None,
    'innerjoin': False,
    'distinct_target_key': None,
    'doc': None,
    'active_history': False,
    'join_depth': None,
    'omit_join': None,
    'local_remote_pairs': None,
    'extension': None,
    'bake_queries': True,
    'load_on_pending': False,
    'comparator_factory': <class 'sqlalchemy.orm.relationships.RelationshipProperty.Comparator'>,
    'comparator': <sqlalchemy.orm.relationships.RelationshipProperty.Comparator object at 0x10e04bf40>,
    '_creation_order': 18,
    'strategy_key': (('lazy','select'),),
    '_reverse_property': set(),
    '_cascade': CascadeOptions('merge,save-update'),
    'order_by': False,
    'back_populates': None,
    'backref': None
}