Applying Modifiers to Columns

Primary Key

To make a column the primary key for a table, set the x-primary-key property on an object property to true. For example:

1
2
3
4
5
6
7
8
9
Employee:
   type: object
   x-tablename: employee
   properties:
     id:
       type: integer
       x-primary-key: true
     name:
       type: string

Auto Increment

To make a column auto increment, set the x-autoincrement property to true. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Employee:
   type: object
   x-tablename: employee
   properties:
     id:
       type: integer
       x-primary-key: true
       x-autoincrement: true
     name:
       type: string

Note

SQLAlchemy turns autoincrement on by default on integer primary key columns. If you wish to disable autoincrement behavior, you must set x-autoincrement to false.

See also

SQLAlchemy autoincrement documentation

Documentation for SQLAlchemy autoincrement.

Index

Indexes can be added at the column level and at the object level. At the column level, the index is applied on a single column. At the object level, an index can be defined over multiple columns.

Column Index

To add an index to a column, set the x-index property on an object property to true. For example:

1
2
3
4
5
6
7
8
9
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
      x-index: true

This applies an index on the name property so that queries filtering by name are faster.

Composite Index

To add an index over multiple columns, set x-composite-index on the object. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
  x-composite-index:
    - id
    - name

This defines an index over id and name to allow for faster querying when filtering by both id and name. Use an array of arrays to define multiple composite indexes for an object. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
    division:
      type: string
    salary:
      type: number
  x-composite-index:
    - - id
      - name
    - - division
      - salary

This defines an index over id and name and over division and salary. It is also possible to define a composite index as an object which has the following properties:

  • expressions: Defines the columns of the index.

  • name (optional): The name of the index. Defaults to letting the database define the name.

  • unique (optional): Whether to enforce a unique constraint on the index. Defaults to false.

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
  x-composite-index:
    name: ix_employee_id_name
    expressions:
      - id
      - name
    unique: true

It is also possible to define multiple composite indexes as an array of objects. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
    division:
      type: string
    salary:
      type: number
  x-composite-index:
    - name: ix_employee_id_name
      expressions:
        - id
        - name
    - name: ix_employee_division_salary
      expressions:
        - division
        - salary

See also

SQLAlchemy Composite Index

Documentation for defining composite indexes in SQLAlchemy.

Unique Constraint

Unique constraints can be added at the column level and at the object level. At the column level, the constraint is applied on a single column. At the object level, a constraint can be defined over multiple columns.

Column Unique Constraint

To add an unique constraint to a column, set the x-unique property on an object property to true. For example:

1
2
3
4
5
6
7
8
9
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
      x-unique: true

This applies a unique constraint on the name property which ensures that there are no duplicate names in the database.

Composite Unique Constraint

To add a unique constraint over multiple columns, set x-composite-unique on the object. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
    address:
      type: string
  x-composite-unique:
    - name
    - address

This applies a unique constraint over the name and address columns to ensure an employee can be uniquely identified by their name and place of residence. Use an array of arrays to define multiple unique constraints for an object. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
    address:
      type: string
    division:
      type: string
    salary:
      type: number
  x-composite-unique:
    - - name
      - address
    - - division
      - salary

This defines two unique constraints, one for name and address and another for division and salary. It is also possible to define a composite unique constraint as an object which has the following properties:

  • columns: Defines the columns of the unique constraint.

  • name (optional): The name of the unique constraint. Defaults to letting the database define the name.

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
    address:
      type: string
  x-composite-unique:
    name: uq_employee_name_address
    columns:
      - name
      - address

It is also possible to define multiple unique constraints as an array of objects. 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
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
    address:
      type: string
    division:
      type: string
    salary:
      type: number
  x-composite-unique:
    - name: uq_employee_name_address
      columns:
        - name
        - address
    - name: uq_employee_division_salary
      columns:
        - division
        - salary

See also

SQLAlchemy Composite Unique Constraint

Documentation for defining composite unique constraint in SQLAlchemy.

Foreign Key Constraint

To add a foreign key constraint, set the x-foreign-key property on an object property to the <table name>.<column>. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
Division:
  type: object
  x-tablename: division
  properties:
    id:
      type: integer
    ...
Employee:
  type: object
  x-tablename: employee
  properties:
    ...
    division_id:
      type: integer
      x-foreign-key: division.id

See also

Defining Relationships shows how to define object references that result in relationships between tables.

Foreign Key kwargs

SQLAlchemy includes support for several keyword arguments for constructing foreign keys. OpenAlchemy supports this behavior through the x-foreign-key-kwargs extension property. For example, the following foreign key constraint adds the ondelete keyword argument:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
Division:
  type: object
  x-tablename: division
  properties:
    id:
      type: integer
    ...
Employee:
  type: object
  x-tablename: employee
  properties:
    ...
    division_id:
      type: integer
      x-foreign-key: division.id
      x-foreign-key-kwargs:
        ondelete: delete

Note that the value of x-foreign-key-kwargs must be an object where keys must be a string. No further validation is done before passing the kwargs to the SQLAlchemy foreign key constructor.

See also

SQLAlchemy Foreign Key kwargs

Documentation for the keyword arguments for foreign keys in SQLAlchemy.

Default

To add a default value for a column use the OpenAPI default property:

1
2
3
4
5
6
7
8
9
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
      default: Unknown

The default value is added to the column constructor as the default value as a “Scalar Default” in SQLAlchemy. The following property types support a default value (including all their formats supported by OpenAlchemy):

  • integer,

  • number,

  • string and

  • boolean.

Adding a default to an object or array type has no effect in OpenAlchemy.

See also

SQLAlchemy “Scalar Default”

Documentation for the scalar default value in SQLAlchemy.

Server Default

To add a default value for a column to be generated by the database, use the x-server-default extension property:

1
2
3
4
5
6
7
8
9
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
    name:
      type: string
      x-server-default: Unknown

The default value is added to the column constructor using the “Server Default” in SQLAlchemy. The following property types support a server default value (including all their formats supported by OpenAlchemy):

  • integer,

  • number,

  • string and

  • boolean.

Adding a server default to a object or array type is not valid in OpenAlchemy. Server default is also not supported by any property that sets x-json to true.

See also

SQLAlchemy Server Default

Documentation for the SQLAlchemy server default.

readOnly

To ensure that the value of a property is always generated server side, mark the property as readOnly:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
      x-primary-key: true
      x-autoincrement: true
      readOnly: true
    name:
      type: string

The typical use case is where the id of an object is generated by auto incrementing a number stored in the database. If the id is ever passed to the server by a user for a table, this can cause conflict with the value that would have been generated by the server.

OpenAlchemy uses readOnly to enforce that the property is not passed to from_dict and from_str.

writeOnly

To ensure that the value of a property is not returned by the server, mark the property as writeOnly:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
Employee:
  type: object
  x-tablename: employee
  properties:
    id:
      type: integer
      x-primary-key: true
      x-autoincrement: true
    name:
      type: string
    passport_number:
      type: string
      writeOnly: true

The typical use case is where the value of the property only make sense to the server or it is sensitive and should not be returned to any consumer.

OpenAlchemy skips writeOnly properties when converting a model instance using to_dict and to_str.

Additional kwargs

SQLAlchemy has further keyword arguments for columns. OpenAlchemy supports these through the x-kwargs extension property for a property. For example, in the following schema, the name column has an additional keyword argument doc added:

1
2
3
4
5
6
7
8
9
Employee:
  type: object
  x-tablename: employee
  properties:
    ...
    name:
      type: string
      x-kwargs:
        doc: The name of the employee

Note that the following restrictions apply for x-kwargs:

  • the value must be an object,

  • the keys of the object must be strings,

  • keys cannot be:

    • nullable,

    • default,

    • primary_key,

    • autoincrement,

    • index and

    • unique.

Also note that no verification, beyond the above, is done before passing the kwargs to the SQLAlchemy column constructor.

See also

SQLAlchemy Column kwargs

Documentation for the keyword arguments for columns in SQLAlchemy.