Metadata-Version: 2.1
Name: SqlAlchemy-Enum-Tables
Version: 1.0.0
Summary: Making Python enums into SQLAlchemy tables with support for Alembic migrations
Home-page: https://git.heptacle.fr/neshiraini/sqlalchemy-enum-tables
Author: Nathan/Eilisha Shiraini
Author-email: neshiraini+sqlalchemy@heptacle.fr
License: Apache Software License
Keywords: sql sqlalchemy orm enum alembic migrations database relational
Platform: UNKNOWN
Classifier: Development Status :: 2 - Pre-Alpha
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Description-Content-Type: text/x-rst
Requires-Dist: sqlalchemy
Provides-Extra: alembic
Requires-Dist: alembic ; extra == 'alembic'

SqlAlchemy Enumeration Tables
=============================

SQLAlchemy has built-in ``enum.Enum`` support,
via its column type ``sqlalchemy.Enum``.
However, this type relies either on the backend's enum type,
or on a check constraints. Both of these are immutable objects,
which are a pain in the butt to modify
(only PostgreSQL supports adding values to an enum type,
and even then it doesn't support removing them).

Another often-used pattern to support enums in a database
is via a dedicated table that reflects the enum values.
This requires updating the table everytime the enum is modified,
but doing so is much simpler than replacing a type.

This package allows you to create the enum table,
and columns referencing that table, directly from
a Python enum class. It also interfaces with Alembic
to automatically add ``INSERT`` and ``DELETE`` statements
to your autogenerated migration scripts.

When to use
-----------

1. Only works with Pythons's enumeration classes,
   or at least one with a behavior similar to ``enum.Enum``.
   Does not work with collections of arbitrary entries.
2. Only works with SqlAlchemy's declarative ORM system.
   If you only use SqlAlchemy Core... *deal with it*.
3. Better used for frequently updated enumeration classes.
4. **Do not** use with another package that provides
   ``op.enum_insert`` and ``op.enum_delete`` operations in Alembic.

How to use with SqlAlchemy
--------------------------

::

    import enum
    import sqlalchemy as sa
    from sqlalchemy.ext.declarative import declarative_base

    import enumtables as et

    # Create the Python enumeration class
    class MyEnum(enum.Enum):
        HELLO = "HELLO"
        WORLD = "WORLD"

    Base = declarative_base()

    # Create the enumeration table
    # Pass your enum class and the SQLAlchemy declarative base to enumtables.EnumTable
    MyEnumTable = et.EnumTable(MyEnum, Base)

    # Create a model class that uses the enum
    class MyModel(Base):
        __tablename__ = "my_model"
        # Pass the enum table (not the enum class) to enumtables.EnumColumn
        # It replaces sqlalchemy.Column, but aside from the enum table,
        # it can take the same parameters.
        # It will automatically create a ForeignKeyConstraint referencing the enum table.
        enum_value = et.EnumColumn(MyEnumTable, primary_key = True)

        # When valued (on an instance of MyModel), enum_value will be an instance of MyEnum.

First, the ``EnumTable`` factory takes the enum class and the declarative base class
to create the actual ORM class. Then this ORM class is passed to the ``EnumColumn`` class
to create the column linked to the enum table.
The column behaves just as if it had SqlAlchemy's own ``Enum`` type.

On the implementation side, ``EnumTable`` is not a class,
it's a factory function that performs Python black magic
to create a subclass of the declarative base, and set it up to be a DB table
containing the enum items (actually it just has one column ``item_id`` of type String).

``EnumColumn`` is a subclass of SqlAlchemy's ``Column`` that gets initialized
with a custom type and a foreign key to the enum table.

How to use with Alembic
-----------------------

First add::

    import enumtables

at the begining of your ``env.py`` file,
then add the same line in the imports of your ``script.py.mako`` file.
The package uses Alembic's standard hooks to take care of migration generation.

Don't forget to review the migrations afterwards.
Especially make sure that, if the table did not exist before,
the ``op.enum_insert`` commands are located *after* the corresponding ``op.create_table`` command.

Other uses
-----------

Using the enum table class directly
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The enum table class behaves like any SqlAlchemy ORM class::

    enum_query = session.query(MyEnumTable)
    result = enum_query.first()

    # The column item_id stores the name of the enum item as a string
    enum_name = result.item_id

Adding more columns to the enum tables
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Any keyword argument passed to the ``EnumTable`` factory becomes a member of the table class.
Which means, you can pass anything (like a column) exactly as you would defined a usual ORM class::

    BetterEnumTable = et.EnumTable(
        MyEnum,
		Base,

        # tablename is turned into __tablename__
        tablename = "better_enum",

        # Let's add a new column!
        order = sa.Column(sa.Integer, nullable = False),

        # And since it's an ordering number, let's make it unique too.
        __table_args__ = (
            sa.UniqueConstraint('order'),
        ),
    )

