Welcome to OpenAlchemy’s documentation!

Translates OpenAPI schemas to SQLAlchemy models to reduce duplication when defining your API and database models. Any required additions to the OpenAPI specification are compliant with the OpenAPI standard.

Getting Started

If you have the following OpenAPI specification:

 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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
openapi: "3.0.0"

info:
  title: Test Schema
  description: API to illustrate OpenAlchemy MVP.
  version: "0.1"

paths:
  /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:
    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
          x-autoincrement: true
        name:
          type: string
          description: The name of the employee.
          example: David Andersson
          x-index: true
        division:
          type: string
          description: The part of the company the employee works in.
          example: Engineering
          x-index: true
        salary:
          type: number
          description: The amount of money the employee is paid.
          example: 1000000.00
      required:
        - name
        - division

To use SQLAlchemy to retrieve Employees from a database you need the following models.py file:

1
2
3
from open_alchemy import init_yaml

init_yaml("example-spec.yml", models_filename="models_auto.py")

The Base for the SQLAlchemy models and the Employee model is now available from open_alchemy.models:

from open_alchemy.models import Base
from open_alchemy.models import Employee

Interfaces

The most user friendly interfaces are the init_yaml and init_json interfaces. The init_model_factory interface is a lower level interface which has fewer dependencies but is not as user friendly.

init_yaml

The init_yaml interface requires the PyYAML library to be installed. The init_yaml interface accepts the following arguments:

  • spec_filename: The name of the file as a positional argument. The file must by a YAML file.
  • base: The SQLAlchemy declarative base as an optional keyword only argument. It is used to as the base class for all SQLAlchemy models. If it is not passed in, a new declarative base is constructed.
  • define_all: Whether to pre-define the SQLAlchemy models as an optional keyword only argument. If it is True, all schemas with the x-tablename property are constructed as a part of the initialization. Defaults to True.
  • models_filename: The name of the file where the SQLAlchemy models will be written as an optional keyword only argument.
  • spec_path: The path to the OpenAPI specification (what would need to be passed to the open function to read the file) as an optional keyword only argument. Used to support remote references.

The return value is a tuple consisting of:

  • Base: The SQLAlchemy declarative based used for the models. It is also importable: from open_alchemy.models import Base.
  • model_factory: The factory that can be used to construct the SQLAlchemy models using the name of the schema in the OpenAPI specification. All constructed models are added to the open_alchemy.models module and are importable. For example: from open_alchemy.models import Employee.

init_json

The init_json interface is similar to the init_yaml interface except that spec_filename must be a JSON file and PyYAML is not a required dependency.

init_model_factory

The init_model_factory interface is less user friendly but perhaps of interest to advanced users. It accepts the specification in dictionary format (so it has fewer dependencies than init_yaml and init_json) and does not construct a declarative base. It accepts the following parameters:

  • base: The SQLAlchemy declarative base as a keyword only argument. It is used to as the base class for all SQLAlchemy models.
  • spec: The OpenAPI specification as a dictionary as a keyword only argument.
  • define_all: Whether to pre-define the SQLAlchemy models as an optional keyword only argument. If it is True, all schemas with the x-tablename property are constructed as a part of the initialization. Defaults to False.
  • models_filename: The name of the file where the SQLAlchemy models will be written as an optional keyword only argument.

The return value is the model_factory as defined as part of the return value of init_yaml.

Models File

OpenAlchemy can optionally generate a file with all the SQLAlchemy models. Each model is constructed based on the OpenApi schema. The class inherits from the SQLAlchemy model defined on open_alchemy.models. The generated classes contain type information only, they do not provide any additional functionality on top of what the SQLAlchemy model provides. They are primarily used to enable IDE auto-complete and type hint support. The models can be used in the same way as the models that can be imported from open_alchemy.models and provide the full functionality of SQLAlchemy models. The following is a sample file generated for the above 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
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
"""Autogenerated SQLAlchemy models based on OpenAlchemy models."""
# pylint: disable=no-member,super-init-not-called,unused-argument

import typing

import sqlalchemy
from sqlalchemy import orm

from open_alchemy import models


class _EmployeeDictBase(typing.TypedDict, total=True):
    """TypedDict for properties that are required."""

    name: str
    division: str


class EmployeeDict(_EmployeeDictBase, total=False):
    """TypedDict for properties that are not required."""

    id: int
    salary: typing.Optional[float]


class TEmployee(typing.Protocol):
    """
    SQLAlchemy model protocol.

    Person that works for a company.

    Attrs:
        id: Unique identifier for the employee.
        name: The name of the employee.
        division: The part of the company the employee works in.
        salary: The amount of money the employee is paid.

    """

    # SQLAlchemy properties
    __table__: sqlalchemy.Table
    __tablename__: str
    query: orm.Query

    # Model properties
    id: int
    name: str
    division: str
    salary: typing.Optional[float]

    def __init__(
        self,
        name: str,
        division: str,
        id: typing.Optional[int] = None,
        salary: typing.Optional[float] = None,
    ) -> None:
        """
        Construct.

        Args:
            id: Unique identifier for the employee.
            name: The name of the employee.
            division: The part of the company the employee works in.
            salary: The amount of money the employee is paid.

        """
        ...

    @classmethod
    def from_dict(
        cls,
        name: str,
        division: str,
        id: typing.Optional[int] = None,
        salary: typing.Optional[float] = None,
    ) -> "TEmployee":
        """
        Construct from a dictionary (eg. a POST payload).

        Args:
            id: Unique identifier for the employee.
            name: The name of the employee.
            division: The part of the company the employee works in.
            salary: The amount of money the employee is paid.

        Returns:
            Model instance based on the dictionary.

        """
        ...

    @classmethod
    def from_str(cls, value: str) -> "TEmployee":
        """
        Construct from a JSON string (eg. a POST payload).

        Returns:
            Model instance based on the JSON string.

        """
        ...

    def to_dict(self) -> EmployeeDict:
        """
        Convert to a dictionary (eg. to send back for a GET request).

        Returns:
            Dictionary based on the model instance.

        """
        ...

    def to_str(self) -> str:
        """
        Convert to a JSON string (eg. to send back for a GET request).

        Returns:
            JSON string based on the model instance.

        """
        ...


Employee: TEmployee = models.Employee  # type: ignore

The following information is recorded in the models file:

  • The name and type of each property of a schema.
  • The from_dict and to_dict function signatures, including the type of the arguments and return values.
  • The from_str and to_str function signatures, including the type of the arguments and return values.
  • The properties created on instance objects due to any Backref.
  • Special SQLAlchemy properties for interacting with the database.
  • The object and property descriptions from the OpenAPI specification in the class and function docstrings.

Note

To be able to add relationships created by x-backrefs to the type annotations of the models file, the schema stored alongside a model, which is accessible at the _schema class variable (not a public interface so it should not be used or relied upon), will use the x-backrefs extension property to record the schema for all back references for the model. x-backrefs is not a public interface and should not be relied upon as it is subject to change.

Model Utilities

There are a few common utility functions that are added to the models. The from_dict utility function constructs a model instance from a dictionary. The to_dict function converts a model instance to a dictionary.

from_dict

The from_dict function is available on all constructed models. It accepts a dictionary and constructs a model instance based on the dictionary. It is similar to Employee(**employee_dict) with a few advantages:

  • The dictionary based on which the model is constructed is checked against the schema used to define the model.
  • If the model includes a relationship, the relationship is constructed recursively.

For example:

>>> employee_dict = {
    "id": 1,
    "name": "David Andersson",
    "division": "engineering",
    "salary": 1000000,
}
>>> employee = Employee.from_dict(**employee_dict)
>>> employee.name
'David Andersson'

Note

To be able to support relationships, the schema stored alongside a model, which is accessible at the _schema class variable (not a public interface so it should not be used or relied upon), won’t store the actual schema for the referenced object. Instead, the object type is noted for the property alongside the x-de-$ref extension property which stores the name of the referenced model.

from_str

The from_str function is available on all constructed models. It accepts a JSON formatted string and constructs a model instance by de-serializing the JSON string and then using from_dict. For example:

>>> employee_str = '''{
    "id": 1,
    "name": "David Andersson",
    "division": "engineering",
    "salary": 1000000
}'''
>>> employee = Employee.from_str(employee_str)
>>> employee.name
'David Andersson'

to_dict

The to_dict function is available on all constructed models. It converts a model instance into a dictionary based on the schema that was used to define the model. If the model includes a relationship, the to_dict function is called recursively on the relationship.

For example:

>>> employee_dict = {
    "id": 1,
    "name": "David Andersson",
    "division": "engineering",
    "salary": 1000000,
}
>>> employee = Employee.from_dict(**employee_dict)
>>> employee.to_dict()
{'id': 1, 'name': 'David Andersson', 'division': 'engineering', 'salary': 1000000}

to_str

The to_str function is available on all constructed models. It converts a model instance into a JSON formatted string by serializing the output of to_dict.

For example:

>>> employee_str = '''{
    "id": 1,
    "name": "David Andersson",
    "division": "engineering",
    "salary": 1000000
}'''
>>> employee = Employee.from_str(employee_str)
>>> employee.to_str()
'{"id": 1, "name": "David Andersson", "division": "engineering", "salary": 1000000}'

Alembic

The standard method for automatically generating database migrations for alembic is supported. The following instructions show how to get started:

# Alembic Interoperability

The following steps were used to generate the migration.

1. Run `alembic init alembic`
2. Modify [alemnbic.ini](alembic.ini) `driver://user:pass@localhost/dbname`
   to `sqlite:///:memory:` to use an in-memory SQLite database.
3. Modify [env.py](alembic/env.py) by adding `import open_alchemy` at the
   bottom of the import section and
   `open_alchemy.init_yaml(spec_filename="../app/api.yaml")` below that to load
   the SQLAlchemy models for the example [app](../app).
4. Modify [env.py](alembic/env.py) by changing `target_metadata = None` to
   `target_metadata = open_alchemy.models.Base.metadata`
5. Run `alembic revision --autogenerate -m "Add emplpoyee table"` to generate
   the [revision](alembic/versions)

The below shows you the diff from the default code generated by alembic:
```diff
diff --git a/alembic.ini b/alembic.ini
index bfcc3c7..f65a55d 100644
--- a/alembic.ini
+++ b/alembic.ini
@@ -35,7 +35,7 @@ script_location = alembic
 # are written from script.py.mako
 # output_encoding = utf-8
 
-sqlalchemy.url = driver://user:pass@localhost/dbname
+sqlalchemy.url = sqlite:///:memory:
 
 
 [post_write_hooks]
diff --git a/alembic/env.py b/alembic/env.py
index c58b79f..71f8edf 100644
--- a/alembic/env.py
+++ b/alembic/env.py
@@ -4,6 +4,10 @@ from alembic import context
 from sqlalchemy import engine_from_config
 from sqlalchemy import pool
 
+import open_alchemy
+
+open_alchemy.init_yaml(spec_filename="../app/api.yaml")
+
 # this is the Alembic Config object, which provides
 # access to the values within the .ini file in use.
 config = context.config
@@ -16,7 +20,7 @@ fileConfig(config.config_file_name)
 # for 'autogenerate' support
 # from myapp import mymodel
 # target_metadata = mymodel.Base.metadata
-target_metadata = None
+target_metadata = open_alchemy.models.Base.metadata
 
 # other values from the config, defined by the needs of env.py,
 # can be acquired:
```

How Does It Work?

Given a name for a schema, OpenAlchemy looks for that schema in the schemas section of the specification. The schema must have the x-tablename property which defines the name of the table. The schema is required to be an object. For each property of the schema, a column is generated for the table mapping OpenAPI types to equivalent SQLAlchemy types.

On top of the information in the OpenAPI specification, certain extension properties are used to define the database schema. The following specification defines the format and provides a description for each of the supported extension properties.

 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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
{
  "x-backref": {
    "description": "Add a back reference to a relationship.",
    "type": "string"
  },
  "x-uselist": {
    "description": "Turn a many to one into a one to one relationship.",
    "type": "boolean"
  },
  "x-secondary": {
    "description": "Turn a one to many into a many to many relationship. The value of x-secondary is used as the name of the association table.",
    "type": "string"
  },
  "x-primary-key": {
    "description": "Make a column a primary key.",
    "type": "boolean"
  },
  "x-autoincrement": {
    "description": "Make an integer primary key column auto increment.",
    "type": "boolean"
  },
  "x-index": {
    "description": "Add index to a column.",
    "type": "boolean"
  },
  "x-composite-index": {
    "description": "Add composite index to a table.",
    "$ref": "#/CompositeIndex"
  },
  "x-unique": {
    "description": "Add unique constraint to a column.",
    "type": "boolean"
  },
  "x-composite-unique": {
    "description": "Add composite unique constraint to a table.",
    "$ref": "#/CompositeUnique"
  },
  "x-foreign-key": {
    "description": "Add a foreign key constraint to a column. Must have the format \"<table name>.<column name>\".",
    "type": "string",
    "pattern": "^.+\\..+$"
  },
  "x-foreign-key-column": {
    "description": "Customize the column used for the foreign key constraint of a relationship.",
    "type": "string"
  },
  "x-foreign-key-kwargs": {
    "description": "Define kwargs to be passed to the foreign key constructor.",
    "type": "object",
    "additionalProperties": true
  },
  "x-tablename": {
    "description": "Define the name of a table.",
    "type": "string"
  },
  "x-kwargs": {
    "description": "Define kwargs to be passed to a function based on the context.",
    "type": "object",
    "additionalProperties": true
  },
  "x-backrefs": {
    "description": "INTERNAL USE ONLY: Note on a schema that a back reference was created.",
    "$ref": "#/BackRef"
  },
  "x-de-$ref": {
    "description": "INTERNAL USE ONLY: Track the name of a constructed model for a property referencing an object.",
    "type": "string"
  },
  "x-dict-ignore": {
    "description": "INTERNAL USE ONLY: Do not add to the schema record used by from_- and to_dict functions.",
    "type": "boolean"
  },
  "x-generated": {
    "description": "INTERNAL USE ONLY: The property is generated automatically (eg. auto incremented or has a default value) which is used to help determine whether the column is nullable.",
    "type": "boolean"
  }
}

To find out more about an extension property, go to the following section of the documentation:

property documentation section
x-backref Backref
x-uselist One to One
x-secondary Many to Many
x-primary-key Primary Key
x-autoincrement Auto Increment
x-index Column Index
x-composite-index Composite Index
x-unique Column Unique Constraint
x-composite-unique Composite Unique Constraint
x-foreign-key Foreign Key Constraint
x-tablename How Does It Work?
x-foreign-key-colum Custom Foreign Key
x-backrefs Models File Note
x-de-$ref from_dict Note
x-dict-ignore One to Many Note
x-generated Nullable Note
x-kwargs
x-foreign-key-kwargs Foreign Key kwargs

The SQLAlchemy Base and any constructed database models are dynamically added to the models module that is available from OpenAlchemy.