Defining Relationships

Defining relationships between tables is done by using $ref to reference other objects from an object property.

See also

SQLAlchemy relationship documentation

Documentation for SQLAlchemy relationships.

Many to One

A many to one relationship associates many children with a single parent. For example, a company can have many employees working in the same division (for example engineering, legal, marketing, …) but a particular employee can only work in one division. In this case the employees are the parents on the “many” side and the division is the child on the “one” side. The following OpenAPI specification snippet defines a many to one relationship:

1
2
3
4
5
6
7
8
Division:
  ...
Employee:
  ...
  properties:
    ...
    division:
      "$ref": "#/components/schemas/Division"

By adding a $ref to an object property that points at another object, a relationship with the logical name will be formed. For example, if the $ref points to the Division object, sqlalchemy.orm.relationship with “Division” will be called.

Alongside the relationship, a foreign key will also be added to the table with the $ref property under the name of the property suffixed with _id. Note that, the object being referenced must have the id property.

The only way to make the foreign key column not nullable is to add the property with the reference to the required list. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
Division:
  ...
Employee:
  ...
  properties:
    ...
    division:
      "$ref": "#/components/schemas/Division"
  required:
    ...
    - division

OpenAlchemy defined relationships are equivalent to the following traditional models.py:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Division(Base):
    """Division of a company."""

    __tablename__ = "division"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)


class Employee(Base):
    """Person that works for a company."""

    __tablename__ = "employee"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    division = sa.orm.relationship("Division")
    division_id = sa.Column(sa.Integer, sa.ForeignKey("division.id"))

Nullable

Similar to simple value columns (such as integer), many to one relationships are also nullable. This is supported using the nullable key or by including the property in the required array of the schema. There are 2 places where the nullable key can be defined. The recommended implementation adds it using allOf:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Division:
  ...
Employee:
  ...
  properties:
    ...
    division:
      allOf:
        - "$ref": "#/components/schemas/Division"
        - nullable: false

Note that, when allOf is used, there must be exactly one $ref in the list and at most one nullable in the list.

The other way, which is not recommended, adds nullable to the object being referenced:

1
2
3
4
5
6
7
8
9
Division:
  ...
  nullable: false
Employee:
  ...
  properties:
    ...
    division:
      "$ref": "#/components/schemas/Division"

The reason it is not recommended is because this only allows a nullable per table, whereas the other allows for many. Also, some relationships do not support nullable which means an error is raised when that type of relationship refers to the same schema. If nullable is both in the allOf list and the referenced object, the value from the allOf list will be used.

The following example makes the relationship not nullable using the required array:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Division:
  ...
Employee:
  ...
  properties:
    ...
    division:
      "$ref": "#/components/schemas/Division"
  required:
    - division

Note that if you use both the nullable and required method, nullable takes precedence over required.

Setting nullable to false is equivalent to the following traditional models.py:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Division(Base):
    """Division of a company."""

    __tablename__ = "division"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)


class Employee(Base):
    """Person that works for a company."""

    __tablename__ = "employee"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    division_id = sa.Column(sa.Integer, sa.ForeignKey("division.id"), nullable=False)

See also

Setting the Nullable Property shows how nullable works for simple values such as integers.

Backref

One of the features of SQLAlchemy is that a relationship can be back populated to the referred table. This is supported using the x-backref key. There are 2 places where the x-backref key can be defined. The recommended implementation adds it using allOf:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Division:
  ...
Employee:
  ...
  properties:
    ...
    division:
      allOf:
        - "$ref": "#/components/schemas/Division"
        - x-backref: employees

Note that, when allOf is used, there must be exactly one $ref in the list and at most one x-backref in the list.

The other way, which is not recommended, adds the x-backref to the object being referenced:

1
2
3
4
5
6
7
8
9
Division:
  ...
  x-backref: employees
Employee:
  ...
  properties:
    ...
    division:
      "$ref": "#/components/schemas/Division"

The reason it is not recommended is because this only allows a x-backref per table, whereas the other allows for many. If x-backref is both in the allOf list and the referenced object, the value from the allOf list will be used.

Using x-backref is equivalent to the following traditional models.py:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Division(Base):
    """Division of a company."""

    __tablename__ = "division"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)


class Employee(Base):
    """Person that works for a company."""

    __tablename__ = "employee"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    division = sa.orm.relationship("Division", backref="employees")
    division_id = sa.Column(sa.Integer, sa.ForeignKey("division.id"))

See also

References shows how to reference to other schemas.

Custom Foreign Key

The x-foreign-key-column extension property is used to define a different foreign key constraint column to the default id column. It is similar to the Backref extension property. There are two ways the foreign key constrain can be defined. The first adds the x-foreign-key-column extension property using allOf which allows for a different foreign key column for each relationship:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Division:
  ...
Employee:
  ...
  properties:
    ...
    division:
      allOf:
        - "$ref": "#/components/schemas/Division"
        - x-foreign-key-column: name

The second adds the extension property on the referenced object:

1
2
3
4
5
6
7
8
9
Division:
  ...
  x-foreign-key-column: name
Employee:
  ...
  properties:
    ...
    division:
      "$ref": "#/components/schemas/Division"

This allows for a common definition for the foreign key constraint for all relationships referencing the same object. If x-foreign-key-column is both in the allOf list and the referenced object, the value from the allOf list will be used.

It is also possible to define a property with the name that would otherwise be automatically generated by OpenAlchemy to have more control over how the foreign key property is defined. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
Division:
  x-tablename: division
  ...
  properties:
    ...
    id:
      type: integer
Employee:
  ...
  properties:
    ...
    division:
      "$ref": "#/components/schemas/Division"
    division_id:
      type: integer
      x-foreign-key: division.id

Note the following:

  • When OpenAlchemy constructs the foreign key for you, the name of the property is made up of the name of the property referencing the object (division in the previous example) combined with the name of the property in the foreign key constraint (id in the previous example). These two values are combined as follows: “<reference property name>_<foreign key column name>” (division_id in the previous example).

  • If OpenAlchemy finds that property in the schema, it won’t construct a foreign key property automatically.

  • The defined property is type checked against the expected foreign key type based on the relationship.

  • The defined property is checked for the x-foreign-key property against the expected foreign key constraint based on the relationship.

  • The x-foreign-key-column extension property can be used to control which property is used as the foreign key.

  • When the foreign key is defined, the from_dict and to_dict model functions will use/return the foreign key values.

See also

  • Foreign Key Constraint describes how to define foreign key constraints.

  • from_dict describes how to convert dictionaries to model instances.

  • to_dict describes how to convert model instances to dictionaries.

Other Keyword Arguments

The remaining keyword arguments for relationship can be specified using the x-kwargs extension property. It can be included as a part of the allOf list when defining a reference. All relationship arguments are available except those that have a special meaning within OpenAlchemy (primarily the arguments that implement the features that have already been discussed). These arguments should be specified using the relevant extension property. The following example defines the order_by argument for the relationship:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
Division:
  ...
  properties:
    ...
    name:
      type: string
Employee:
  ...
  properties:
    ...
    division:
      allOf:
        - "$ref": "#/components/schemas/Division"
        - x-kwargs:
            order_by: Division.name

The value of x-kwargs is an arbitrary object with the following restrictions: * each key must be a string and * it is not allowed to contain the backref and secondary keys.

Note that, other than the above, no validation is performed on the keyword arguments before passing them to the relationship constructor.

See also

SQLAlchemy Relationship API

Documentation of the SQLAlchemy relationship API.

One to One

A one to one relationship associates one child with one parent and vice-versa. For example, the pay information for an employee may be stored in a different table than the employee. However, one employee can only be paid in one way and each employee must be paid separately. This means that there is a one to one relationship between an employee and pay information.

The one to one relationship is defined in the same way as the many to one relationship except that it requires the x-uselist extension property to be set to False and x-backref to be defined. Custom foreign keys, nullable and relationship kwargs are also supported. The x-uselist property can be defined along with the x-backref extension property using allOf or on the object being referenced. To define it on allOf:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
PayInfo:
  ...
Employee:
  ...
  properties:
    ...
    pay_info:
      allOf:
        - "$ref": "#/components/schemas/PayInfo"
        - x-backref: employee
          x-uselist: false

This is the recommended approach as it allows for other relationships to the referenced object to be, for example, many to one relationships. For some types of relationships the inclusion of x-uselist causes an error to be raised. To default relationships to an object to one to one, the x-uselist property can be set on the referenced object:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
PayInfo:
  ...
  x-backref: employee
  x-uselist: false
Employee:
  ...
  properties:
    ...
    division:
      "$ref": "#/components/schemas/PayInfo"

If x-uselist is both in the allOf list and the referenced object, the value from the allOf list will be used. The x-uselist and x-backref properties don’t have to be defined together, they can be separated. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
PayInfo:
  ...
  x-backref: employee
Employee:
  ...
  properties:
    ...
    pay_info:
      allOf:
        - "$ref": "#/components/schemas/PayInfo"
        - x-uselist: false

Other permutations are also supported.

One to Many

The one to many relationship is similar to the may to one relationship except that the role of the child and parent is swapped. Following on from the many to one example, in the one to many case the division would be the parent and the employees the children. The following OpenAPI specification snippet defines a one to many relationship:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Division:
  ...
  properties:
    ...
    employees:
      type: array
      items:
        "$ref": "#/components/schemas/Division"
Employee:
  ...

The one to many relationship also supports back references, custom foreign keys and relationship kwargs. They are defined similar to how they are defined for many to one relationships. For back references see many to one backref, for custom foreign keys see many to one custom foreign keys and for relationship kwargs see many to one relationship kwargs. Note that x-uselist is not supported as it does not make sense to turn a one to many relationship defined as an OpenAPI array into a one to one relationship. Also note that nullable is not supported because the foreign key is defined on the referenced schema. If the foreign key was not nullable by default, then that schema cannot be constructed without the foreign key. If you need to define a foreign key that is not nullable, add it as a property on the referenced schema and refer to that property using many to one custom foreign keys.

Note

To be able to support one to many relationships, the schemas stored with the model factory may be modified by putting a particular schema behind an allOf statement together with a object that has a foreign key property. The schema for the added object will include the x-dict-ignore extension property to ensure that from_dict and to_dict don’t attempt to construct the foreign key associated with the one to many relationship. The x-dict-ignore extension property is an internal extension property that may change at any time and should not be used externally.

Many to Many

The many to many relationship is used when a parent model can have many children but the child can also have many parents. For example, an employee could be working on multiple projects and multiple employees are generally assigned to a project. Therefore, there is a many to many relationship between employees and projects. This is implemented using an association table which has a column for each primary key on the parent and child. One row in the association table implies a relationship between the parent and child.

Many to many relationships are defined similar to one to many relationships except that the x-secondary extension property is required to define the name of the association table. Both the parent and child must have a primary key column and, currently, both the parent and child must only have one primary key column. In the presence of the x-secondary extension property, a table with the name as the property value is constructed where the columns are defined based on the schema of the primary key columns of the parent and child. The names of the columns are defined to be “<x-tablename>_<property name>”. Each column is given a foreign key constraint as defined by “<x-tablename>.<property name>”.

There are 2 places where the x-secondary key can be defined. The recommended implementation adds it using allOf:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Project:
  type: object
  x-tablename: project
  ...
  properties:
    id:
      type: integer
      x-primary-key: true
    ...
Employee:
  type: object
  x-tablename: employee
  ...
  properties:
    id:
      type: integer
      x-primary-key: true
    ...
    projects:
      type: array
      items:
        allOf:
          - "$ref": "#/components/schemas/Project"
          - x-secondary: employee_project

Note that, when allOf is used, there must be exactly one $ref in the list and at most one x-secondary in the list. The other way, which is not recommended, adds the x-secondary to the object being referenced:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Project:
  type: object
  x-tablename: project
  ...
  x-secondary: employee_project
  properties:
    id:
      type: integer
      x-primary-key: true
    ...
Employee:
  type: object
  x-tablename: employee
  ...
  properties:
    id:
      type: integer
      x-primary-key: true
    ...
    projects:
      type: array
      items:
        "$ref": "#/components/schemas/Project"

The reason it is not recommended is because this only allows a x-secondary per table, whereas the other allows for many. If x-secondary is both in the allOf list and the referenced object, the value from the allOf list will be used.

Using x-secondary is equivalent to the following traditional models.py:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


employee_project = sa.Table(
    "employee_project",
    Base.metadata,
    sa.Column("project_id", sa.Integer, sa.ForeignKey("project.id")),
    sa.Column("employee_id", sa.Integer, sa.ForeignKey("employee.id")),
)


class Project(Base):
    """A large sized business objective."""

    __tablename__ = "project"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)


class Employee(Base):
    """Person that works for a company."""

    __tablename__ = "employee"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    projects = sa.orm.relationship("Project", secondary="project_employee")

Many to many relationships support x-backref, see many to one backref, and relationship kwargs, see many to one relationship kwargs. Note that x-uselist is not supported as it does not make sense to turn a many to many relationship defined as an OpenAPI array into a many to one relationship. Also, because the association table is defined based on the primary key properties, custom foreign keys are not supported.

Including Parent References with Child

So far, relationships have been used to include the full details for a child with a parent. This is only possible from one side of the relationship without circular references that would produce infinite sized API response payloads. This can be overcome by including a subset of parent properties in the child.

This is done using readOnly properties. At a high level, properties marked as readOnly are not constructed as columns in the SQLAlchemy models. They are also not constructed as a part of a call to from_dict. The purpose of readOnly properties is to include some of the properties of the parent in the child dictionary when calling to_dict. readOnly properties are required to be defined as objects or arrays of object. In both cases object properties may not be of type object nor array to avoid circular references. $ref and allOf are supported as normal.

Many to One

For example, the following Division schema includes the id of all employees working in the division as an array:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Employee:
  type: object
  ...
  properties:
    id:
      type: integer
    ...
    division:
      allOf:
        - $ref: "#/../Division"
        - x-backref: employees
Division:
  type: object
  x-tablename: division
  properties:
    ...
    employees:
      readOnly: True
      type: array
      items:
        type: object
        properties:
          id:
            type: integer

Querying for a Division would, for example, result in the following dictionary:

>>> division = Division.query.first()
>>> division.to_dict()
{'id': 1, 'name': 'Engineering', 'employees': [{'id': 1}, {'id': 2}, ['id': 5}]}

Indicating that employees with the ids 1, 2 and 5 work in the engineering division. To retrieve further details for those employees, the relevant endpoints can be queried with the ids.

Any number of properties may be included in the readOnly object schema. Schema duplication can be reduced by making use of $ref. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
BaseEmployee:
  type: object
  ...
  properties:
    id:
      type: integer
    ...
Employee:
  allOf:
    - $ref: "#/.../BaseEmployee"
    - type: object
      properties:
        division:
          allOf:
            - $ref: "#/../Division"
            - x-backref: employees
Division:
  type: object
  x-tablename: division
  properties:
    ...
    employees:
      readOnly: True
      type: array
      items:
        $ref: "#/../BaseEmployee"

This means that all Employee properties are included except for the reference to Division to avoid circular references.

In the above example, there is a many to one relationship from Employee to Division. The difference for other relationship types is the type of the readOnly property.

One to One

The difference between many to one and one to one is that the type of the readOnly property is an object instead of an array.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
Employee:
  ...
  properties:
    id:
      type: integer
    ...
    pay_info:
      allOf:
        - $ref: "#/.../PayInfo"
        - x-backref: employee
          x-uselist: false
PayInfo:
  ...
  properties:
    ...
    employee:
      readOnly: true
      type: object
      properties:
        id:
          type: integer

Querying for a PayInfo would, for example, result in the following dictionary:

>>> pay_info = PayInfo.query.first()
>>> pay_info.to_dict()
{'id': 1, 'account': '012 345', 'employee': {'id': 1}}

Indicating that the pay information is for the employee with an id of 1.

One to Many

Including a parent reference with a child for a one to many relationship is very similar to the one to one relationship case. To illustrate, the following schema redefines the relationship between Employee and Division so that the Division is the parent resulting in a one to many relationship:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Division:
  type: object
  x-tablename: division
  properties:
    id:
      type: integer
    ...
    employees:
      type: array
      items:
        allOf:
          - $ref: "#/../Employee"
          - x-backref: division
Employee:
  type: object
  ...
  properties:
    ...
    division:
      readOnly: true
      type: object
      properties:
        id:
          type: integer

Querying for an Employee would, for example, result in the following dictionary:

>>> employee = Employee.query.first()
>>> employee.to_dict()
{'id': 1, 'name': 'David Andersson', 'division': {'id': 1}}

Indicating that the employee is working in the division with an id of 1.

Many to Many

Including a parent reference with a child for a many to many relationship is very similar to the many to one relationship case except that both sides are defined as arrays. To illustrate, the following schema defines a many to many relationship between Employee and Project:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Employee:
  type: object
  ...
  properties:
    id:
      type: integer
      x-primary-key: true
    ...
    projects:
      allOf:
        - $ref: "#/.../Project"
        - x-backref: employees
          x-secondary: employee_project
Project:
  type: object
  ...
  properties:
    id:
      type: integer
      x-primary-key: true
    ...
    employees:
      readOnly: true
      type: array
      items:
        type: object
        properties:
          id:
            type: integer

Querying for an Employee would, for example, result in the following dictionary:

>>> employee = Employee.query.first()
>>> employee.to_dict()
{'id': 1, 'name': 'David Andersson', 'projects': [{'id': 1}, {'id': 2}]}

Indicating that the employee is working on the projects with an id of 1 and 2. Querying for a Project would, for example, result in the following dictionary:

>>> project = Project.query.first()
>>> project.to_dict()
{'id': 1, 'name': 'Expand to the USA', 'employees': [{'id': 1}, {'id': 3}]}

Indicating that the project is being worked on by the employees with an id of 1 and 3.

Custom Association Schema

OpenAlchemy supports customizing the association schema. This is useful to, for example, add columns tracking when the association between two objects was first established.

To define a custom association schema, define a schema that has an x-tablename that is the same as the x-secondary value of the many-to-many relationship. The following rules apply to the schema (which are enforced):

  1. Any properties that are primary keys must also be one of the properties establishing a foreign key relationship to the child or the parent.

  2. At most 2 primary key properties can be defined.

  3. The type, format and maxLength of the foreign key property establishing one side of the many-to-many relationship must match the primary key property of the object on that side of the relationship (including whether they are defined).

OpenAlchemy will check for any missing foreign key properties and add them in automatically. For example, if an association only defines one side of a many-to-many relationship, OpenAlchemy will automatically generate a property for the other side.

Custom association schemas can define any other properties that are not primary keys. Note the following to ensure smooth operations:

  • Any additional columns should somehow be generated by the server, for example, through a server default of the current time. This ensures that SQLAlchemy can automatically generate entries into the table to establish a many-to-many link between two objects.

  • It is possible to define relationships to the parent and child objects. However, not all of the usual relationship functionality is supported, for more information check here: SQLAlchemy association object documentation

The following shows an example where the Employee side of the association schema has been defined. OpenAlchemy will automatically generate the Project side before constructing the model.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Project:
  type: object
  x-tablename: project
  ...
  properties:
    id:
      type: integer
      x-primary-key: true
    ...
Employee:
  type: object
  x-tablename: employee
  ...
  properties:
    id:
      type: integer
      x-primary-key: true
    ...
    projects:
      type: array
      items:
        allOf:
          - "$ref": "#/components/schemas/Project"
          - x-secondary: employee_project
EmployeeProject:
  type: object
  x-tablename: employee_project
  ...
  properties:
    employee_id:
      type: integer
      x-primary-key: true
      x-foreign-key: employee.id
    ...