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 tofalse
.
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
andboolean
.
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
andboolean
.
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
andunique
.
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.