DanCardin / sqlalchemy-declarative-extensions

Library to declare additional kinds of objects not natively supported by SqlAlchemy/Alembic.
https://sqlalchemy-declarative-extensions.readthedocs.io/en/latest/
Apache License 2.0
31 stars 5 forks source link
alembic autogenerate database declarative grant postgresql role sqlalchemy trigger

SQLAlchemy Declarative Extensions

Actions Status Coverage Status Documentation Status

See the full documentation here.

Adds extensions to SQLAlchemy (and/or Alembic) which allows declaratively stating the existence of additional kinds of objects about your database not natively supported by SQLAlchemy/Alembic.

This includes:

The primary function(s) of this library include:

Kitchen Sink Example (using all available features)

from sqlalchemy import Column, types, select
from sqlalchemy.orm import as_declarative
from sqlalchemy_declarative_extensions import (
    declarative_database, Schemas, Roles, Row, View, view,
)
from sqlalchemy_declarative_extensions.dialects.postgresql import (
    DefaultGrant, Function, Trigger, Role
)
from sqlalchemy_declarative_extensions.audit import audit

@declarative_database
@as_declarative
class Base:
    # Note: each object type also has a plural version (i.e. Schemas/Roles/etc) where you can specify
    # collection-level options like `ignore_unspecified`).
    #
    # If you dont set any collection-level options, you can instead use raw list/iterable as the collection.
    schemas = Schemas().are("example")
    roles = Roles(ignore_unspecified=True).are(
        Role("read", login=False),
        Role(
            "app",
            in_roles=['read']
        ),
    )
    grants = [
        DefaultGrant.on_tables_in_schema("public", 'example').grant("select", to="read"),
        DefaultGrant.on_sequences_in_schema("public").grant("usage", to="read"),
        Grant.new("usage", to="read").on_schemas("example")
    ]
    rows = [
        Row('foo', id=1),
    ]
    views = [
        View("low_foo", "select * from foo where i < 10"),
    ]
    functions = [
        Function(
            "fancy_function",
            """
            BEGIN
            INSERT INTO foo (id) select NEW.id + 1;
            RETURN NULL;
            END
            """,
            language="plpgsql",
            returns="trigger",
        ),
    ]
    triggers = [
        Trigger.after("insert", on="foo", execute="fancy_function")
        .named("on_insert_foo")
        .when("pg_trigger_depth() < 1")
        .for_each_row(),
    ]

@audit()
class Foo(Base):
    __tablename__ = 'foo'

    id = Column(types.Integer(), primary_key=True)

audit_table = Foo.__audit_table__

@view(Base)
class HighFoo:
    __tablename__ = "high_foo"
    __view__ = select(Foo.__table__).where(Foo.__table__.c.id >= 10)

Note, there is also support for declaring objects directly through the MetaData for users not using sqlalchemy's declarative API.

Event Registration

By default, the above example does not automatically do anything. Depending on the context, you can use one of two registration hooks: register_sqlalchemy_events or register_alembic_events.

register_sqlalchemy_events

This registers events in SQLAlchemy's event system such that a metadata.create_all(...) call will create the declared database objects.

from sqlalchemy_declarative_extensions import register_sqlalchemy_events

metadata = Base.metadata  # Given the example above.
register_sqlalchemy_events(metadata)
# Which is equivalent to...
register_sqlalchemy_events(metadata, schemas=False, roles=False, grants=False, rows=False)

All object types are opt in, and should be explicitly included in order to get registered.

Practically, this is to avoid issues with testing. In most cases the metadata.create_all call will be run in tests, a context where it's more likely that you dont need grants or grants, and where parallel test execution could lead to issues with role or schema creation, depending on your setup.

register_alembic_events

This registers comparators in Alembic's registration system such that an alembic revision --autogenerate command will diff the existing database state against the declared database objects, and issue statements to create/update/delete objects in order to match the declared state.

# alembic's `env.py`
from sqlalchemy_declarative_extensions import register_alembic_events

register_alembic_events()
# Which is equivalent to...
register_sqlalchemy_events(schemas=True, roles=True, grants=True, rows=True)

All object types are opt out but can be excluded.

In contrast to register_sqlalchemy_events, it's much more likely that you're declaring most of these object types in order to have alembic track them

Database support

In principle, this library can absolutely support any database supported by SQLAlchemy, and capable of being introspected enough to support detection of different kinds of objects.

As you can see below, in reality the existence of implementations are going to be purely driven by actual usage. The current maintainer(s) primarily use PostgreSQL and as such individual features for other databases will either suffer or lack implementation.

Postgres MySQL SQLite Snowflake
Schema N/A
View
Role N/A
Grant N/A
Default Grant N/A
Function *
Trigger *
Database
Row (data)
"Audit Table"

note "Row" is implemented with pure SQLAlchemy concepts, so should work for any dialect that you can use SQLAlchemy to connect to.

The asterisks above note pending or provisional support through basic test cases. The level of expertise in each dialects' particular behaviors is not uniform, and deciding on the correct behavior for those dialects will require users to submit issues/fixes!

Supporting a new dialect can be as simple as providing the dialect-dispatched implementations for detecting existing objects of the given type. Very much the intent is that once a given object type is supported at all, the comparison infrastructure for that type should make it straightforward to support other dialects. At the end of the day, this library is primarily producing SQL statements, so in theory any dialect supporting a given object type should be able to be supported.

In such cases (like Grants/Roles) that different dialects support wildly different options/syntax, there are also patterns for defining dialect-specific objects, to mediate any additional differences.

Alembic-utils

Alembic Utils is the primary library against which this library can be compared. At time of writing, most (but not all) object types supported by alembic-utils are supported by this library. One might begin to question when to use which library.

Below is a list of points on which the two libraries diverge. But note that you can certainly use both in tandem! It doesn't need to be one or the other, and certainly for any object types which do not overlap, you might need to use both.

A separate note on conversion/compatibility. Where possible, this library tries to support alembic-utils native objects as stand-ins for the objects defined in this library. For example, alembic_utils.pg_view.PGView can be declared instead of a sqlalchemy_declarative_extensions.View, and we will internally coerce it into the appropriate type. Hopefully this eases any transitional costs, or issues using one or the other library.