OpenAlchemy

OpenAlchemy 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.

See also

Online Editor

Online editor including schema validation.

Getting Started

If you have the following OpenAPI specification:

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:

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

Used to initialize the SQLAlchemy models based on a YAML OpenAPI specification which has been extended with any relevant OpenAlchemy extension properties.

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.

• 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.

Note

the define_all parameter has been removed and OpenAlchemy behaves as though it is set to True.

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.

build_yaml

Used to build a package with the SQLAlchemy models (including type hints) based on a YAML OpenAPI specification which has been extended with any relevant OpenAlchemy extension properties.

To build models from the command line, run:

openalchemy build openapi.yml simple dist

This interface is described in details in the build_yaml section of the Advanced chapter.

build_json

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

Models File

Optionally, model definitions can be persisted to disk, mainly for type hinting and IDE auto-completion.

To discover the internal details of the models file, refer to the Models File section in the Advanced chapter.

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 employee 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?

Helped by a series of extension properties, OpenAlchemy turns OpenAPI schemas into SQL entities.

To understand how all this works under the hood, refer to How Does It Work? section in the Advanced chapter.

Table of Content

GENERAL

• Technical Details
• Examples
• OpenAlchemy CLI
• Advanced
• Package Service
• Changelog

DEVELOPMENT

• Contributing