# SQLAlchemy Declarative Extensions
[](https://github.com/dancardin/sqlalchemy-declarative-extensions/actions)
[](https://coveralls.io/github/DanCardin/sqlalchemy-declarative-extensions?branch=main)
[](https://sqlalchemy-declarative-extensions.readthedocs.io/en/latest/?badge=latest)
See the full documentation
[here](https://sqlalchemy-declarative-extensions.readthedocs.io/en/latest/).
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:
- Schemas
- Views
- Roles
- Privileges (Grants/Default Grants)
- Functions
- Triggers
- Rows (i.e. data)
- "audit tables" (i.e. triggers which record data changes to some source table)
The primary function(s) of this library include:
- Registering onto the SQLAlchemy event system such that `metadata.create_all`
creates these objects.
- (Optionally) Registers into Alembic such that
`alembic revision --autogenerate` automatically creates/updates/deletes
declared objects.
## Kitchen Sink Example (using all available features)
```python
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.
```python
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.
```python
# 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 | ✓ | * | | |
| 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 astrisks 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](https://github.com/olirice/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.
- Database Support
- Alembic Utils seems to explicitly only support PostgreSQL.
- This library is designed to support any dialect (in theory). Certainly
PostgreSQL is **best** supported, but there does exist support for specific
kinds of objects to varying levels of support for SQLite and MySQL, so far.
- Architecture
- Alembic Utils is directly tied to Alembic and does not support SQLAlchemy's
`MetaData.create_all`. It's also the responsibility of the user to
discover/register objects in alembic.
- This library **depends** only on SQLAlchemy, although it also supports
alembic. Support for `MetaData.create_all` can be important for creating all
object types in tests. It also is designed such that objects are registered
on the `MetaData` itself, so there is no need for any specific discovery
phase.
- Scope
- Alembic Utils declares specific, individual objects. I.e. you instantiate
one specific `PGGrantTable` or `PGView` instance and Alembic know knows you
want that object to be created. It cannot drop objects it is not already
aware of.
- This library declares ths objects the system as a whole expects to exist.
Similar to Alembic's behavior on tables, it will (by default) detect any
**undeclared** objects that should not exist and drop them. That means, you
can rely on this object to ensure the state of your migrations matches the
state of your database exactly.
- Migration history
- Alembic Utils imports and references its own objects in your migrations
history. This can be unfortunate, in that it deeply ties your migrations
history to alembic-utils.
(In fact, this can be a sticking point, trying to convert **away** from
`alembic_utils`, because it will attempt to drop all the (e.g `PGView`)
instances previously created when we replaced it with this library.)
- This library, by contrast, prefers to emit the raw SQL of the operation into
your migration. That means you know the exact commands that will execute in
your migration, which can be helpful in debugging failure. It also means, if
at any point you decide to stop use of the library (or pause a given type of
object, due to a bug), you can! This library's behaviors are primarily very
much `--autogenerate`-time only.
- Abstraction Level
- Alembic Utils appears to define a very "literal" interface (for example,
`PGView` accepts the whole view definition as a raw literal string).
- This library tries to, as much as possible, provide a more abstracted
interface that enables more compatibility with SQLAlchemy (For example
`View` accepts SQLAlchemy objects which can be coerced into a `SELECT`). It
also tends towards "builder" interfaces which progressively produce a object
(Take a look at the `DefaultGrant` above, for an example of where that's
helpful).
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.
Raw data
{
"_id": null,
"home_page": "https://github.com/dancardin/sqlalchemy-declarative-extensions",
"name": "sqlalchemy-declarative-extensions",
"maintainer": null,
"docs_url": null,
"requires_python": "<4,>=3.8",
"maintainer_email": null,
"keywords": "alembic, declarative, grant, mysql, postgresql, role, schema, sqlalchemy, view",
"author": "Dan Cardin",
"author_email": "ddcardin@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/75/64/fc630d8689e996dd2c92bbdc17992e4004db43c2ffa0845d99a337957f74/sqlalchemy-declarative-extensions-0.8.2.tar.gz",
"platform": null,
"description": "# SQLAlchemy Declarative Extensions\n\n[](https://github.com/dancardin/sqlalchemy-declarative-extensions/actions)\n[](https://coveralls.io/github/DanCardin/sqlalchemy-declarative-extensions?branch=main)\n[](https://sqlalchemy-declarative-extensions.readthedocs.io/en/latest/?badge=latest)\n\nSee the full documentation\n[here](https://sqlalchemy-declarative-extensions.readthedocs.io/en/latest/).\n\nAdds extensions to SQLAlchemy (and/or Alembic) which allows declaratively\nstating the existence of additional kinds of objects about your database not\nnatively supported by SQLAlchemy/Alembic.\n\nThis includes:\n\n- Schemas\n- Views\n- Roles\n- Privileges (Grants/Default Grants)\n- Functions\n- Triggers\n- Rows (i.e. data)\n- \"audit tables\" (i.e. triggers which record data changes to some source table)\n\nThe primary function(s) of this library include:\n\n- Registering onto the SQLAlchemy event system such that `metadata.create_all`\n creates these objects.\n- (Optionally) Registers into Alembic such that\n `alembic revision --autogenerate` automatically creates/updates/deletes\n declared objects.\n\n## Kitchen Sink Example (using all available features)\n\n```python\nfrom sqlalchemy import Column, types, select\nfrom sqlalchemy.orm import as_declarative\nfrom sqlalchemy_declarative_extensions import (\n declarative_database, Schemas, Roles, Row, View, view,\n)\nfrom sqlalchemy_declarative_extensions.dialects.postgresql import (\n DefaultGrant, Function, Trigger, Role\n)\nfrom sqlalchemy_declarative_extensions.audit import audit\n\n\n@declarative_database\n@as_declarative\nclass Base:\n # Note: each object type also has a plural version (i.e. Schemas/Roles/etc) where you can specify\n # collection-level options like `ignore_unspecified`).\n #\n # If you dont set any collection-level options, you can instead use raw list/iterable as the collection.\n schemas = Schemas().are(\"example\")\n roles = Roles(ignore_unspecified=True).are(\n Role(\"read\", login=False),\n Role(\n \"app\",\n in_roles=['read']\n ),\n )\n grants = [\n DefaultGrant.on_tables_in_schema(\"public\", 'example').grant(\"select\", to=\"read\"),\n DefaultGrant.on_sequences_in_schema(\"public\").grant(\"usage\", to=\"read\"),\n Grant.new(\"usage\", to=\"read\").on_schemas(\"example\")\n ]\n rows = [\n Row('foo', id=1),\n ]\n views = [\n View(\"low_foo\", \"select * from foo where i < 10\"),\n ]\n functions = [\n Function(\n \"fancy_function\",\n \"\"\"\n BEGIN\n INSERT INTO foo (id) select NEW.id + 1;\n RETURN NULL;\n END\n \"\"\",\n language=\"plpgsql\",\n returns=\"trigger\",\n ),\n ]\n triggers = [\n Trigger.after(\"insert\", on=\"foo\", execute=\"fancy_function\")\n .named(\"on_insert_foo\")\n .when(\"pg_trigger_depth() < 1\")\n .for_each_row(),\n ]\n\n\n@audit()\nclass Foo(Base):\n __tablename__ = 'foo'\n\n id = Column(types.Integer(), primary_key=True)\n\n\naudit_table = Foo.__audit_table__\n\n\n@view(Base)\nclass HighFoo:\n __tablename__ = \"high_foo\"\n __view__ = select(Foo.__table__).where(Foo.__table__.c.id >= 10)\n```\n\nNote, there is also support for declaring objects directly through the\n`MetaData` for users not using sqlalchemy's declarative API.\n\n## Event Registration\n\nBy default, the above example does not automatically do anything. Depending on\nthe context, you can use one of two registration hooks:\n`register_sqlalchemy_events` or `register_alembic_events`.\n\n### `register_sqlalchemy_events`\n\nThis registers events in SQLAlchemy's event system such that a\n`metadata.create_all(...)` call will create the declared database objects.\n\n```python\nfrom sqlalchemy_declarative_extensions import register_sqlalchemy_events\n\nmetadata = Base.metadata # Given the example above.\nregister_sqlalchemy_events(metadata)\n# Which is equivalent to...\nregister_sqlalchemy_events(metadata, schemas=False, roles=False, grants=False, rows=False)\n```\n\nAll object types are opt in, and should be explicitly included in order to get\nregistered.\n\nPractically, this is to avoid issues with testing. In **most** cases the\n`metadata.create_all` call will be run in tests, a context where it's more\nlikely that you dont **need** grants or grants, and where parallel test\nexecution could lead to issues with role or schema creation, depending on your\nsetup.\n\n### `register_alembic_events`\n\nThis registers comparators in Alembic's registration system such that an\n`alembic revision --autogenerate` command will diff the existing database state\nagainst the declared database objects, and issue statements to\ncreate/update/delete objects in order to match the declared state.\n\n```python\n# alembic's `env.py`\nfrom sqlalchemy_declarative_extensions import register_alembic_events\n\nregister_alembic_events()\n# Which is equivalent to...\nregister_sqlalchemy_events(schemas=True, roles=True, grants=True, rows=True)\n```\n\nAll object types are opt out but can be excluded.\n\nIn contrast to `register_sqlalchemy_events`, it's much more likely that you're\ndeclaring most of these object types in order to have alembic track them\n\n## Database support\n\nIn principle, this library **can** absolutely support any database supported by\nSQLAlchemy, and capable of being introspected enough to support detection of\ndifferent kinds of objects.\n\nAs you can see below, in reality the existence of implementations are going to\nbe purely driven by actual usage. The current maintainer(s) primarily use\nPostgreSQL and as such individual features for other databases will either\nsuffer or lack implementation.\n\n| | Postgres | MySQL | SQLite | Snowflake |\n| ------------- | -------- | ----- | ------ | --------- |\n| Schema | \u2713 | N/A | \u2713 | \u2713 |\n| View | \u2713 | \u2713 | \u2713 | |\n| Role | \u2713 | | N/A | |\n| Grant | \u2713 | | N/A | |\n| Default Grant | \u2713 | | N/A | |\n| Function | \u2713 | * | | |\n| Trigger | \u2713 | * | | |\n| Row (data) | \u2713 | \u2713 | \u2713 | \u2713 |\n| \"Audit Table\" | \u2713 | | | |\n\n**note** \"Row\" is implemented with pure SQLAlchemy concepts, so should work for\nany dialect that you can use SQLAlchemy to connect to.\n\nThe astrisks above note pending or provisional support through basic test cases.\nThe level of expertise in each dialects' particular behaviors is not uniform,\nand deciding on the correct behavior for those dialects will require users to\nsubmit issues/fixes!\n\nSupporting a new dialect **can** be as simple as providing the\ndialect-dispatched implementations for detecting existing objects of the given\ntype. Very much the intent is that once a given object type is supported at all,\nthe comparison infrastructure for that type should make it straightforward to\nsupport other dialects. At the end of the day, this library is primarily\nproducing SQL statements, so in theory any dialect supporting a given object\ntype should be able to be supported.\n\nIn such cases (like Grants/Roles) that different dialects support wildly\ndifferent options/syntax, there are also patterns for defining dialect-specific\nobjects, to mediate any additional differences.\n\n## Alembic-utils\n\n[Alembic Utils](https://github.com/olirice/alembic_utils) is the primary library\nagainst which this library can be compared. At time of writing, **most** (but\nnot all) object types supported by alembic-utils are supported by this library.\nOne might begin to question when to use which library.\n\nBelow is a list of points on which the two libraries diverge. But note that you\n**can** certainly use both in tandem! It doesn't need to be one or the other,\nand certainly for any object types which do not overlap, you might **need** to\nuse both.\n\n- Database Support\n\n - Alembic Utils seems to explicitly only support PostgreSQL.\n\n - This library is designed to support any dialect (in theory). Certainly\n PostgreSQL is **best** supported, but there does exist support for specific\n kinds of objects to varying levels of support for SQLite and MySQL, so far.\n\n- Architecture\n\n - Alembic Utils is directly tied to Alembic and does not support SQLAlchemy's\n `MetaData.create_all`. It's also the responsibility of the user to\n discover/register objects in alembic.\n\n - This library **depends** only on SQLAlchemy, although it also supports\n alembic. Support for `MetaData.create_all` can be important for creating all\n object types in tests. It also is designed such that objects are registered\n on the `MetaData` itself, so there is no need for any specific discovery\n phase.\n\n- Scope\n\n - Alembic Utils declares specific, individual objects. I.e. you instantiate\n one specific `PGGrantTable` or `PGView` instance and Alembic know knows you\n want that object to be created. It cannot drop objects it is not already\n aware of.\n\n - This library declares ths objects the system as a whole expects to exist.\n Similar to Alembic's behavior on tables, it will (by default) detect any\n **undeclared** objects that should not exist and drop them. That means, you\n can rely on this object to ensure the state of your migrations matches the\n state of your database exactly.\n\n- Migration history\n\n - Alembic Utils imports and references its own objects in your migrations\n history. This can be unfortunate, in that it deeply ties your migrations\n history to alembic-utils.\n\n (In fact, this can be a sticking point, trying to convert **away** from\n `alembic_utils`, because it will attempt to drop all the (e.g `PGView`)\n instances previously created when we replaced it with this library.)\n\n - This library, by contrast, prefers to emit the raw SQL of the operation into\n your migration. That means you know the exact commands that will execute in\n your migration, which can be helpful in debugging failure. It also means, if\n at any point you decide to stop use of the library (or pause a given type of\n object, due to a bug), you can! This library's behaviors are primarily very\n much `--autogenerate`-time only.\n\n- Abstraction Level\n\n - Alembic Utils appears to define a very \"literal\" interface (for example,\n `PGView` accepts the whole view definition as a raw literal string).\n\n - This library tries to, as much as possible, provide a more abstracted\n interface that enables more compatibility with SQLAlchemy (For example\n `View` accepts SQLAlchemy objects which can be coerced into a `SELECT`). It\n also tends towards \"builder\" interfaces which progressively produce a object\n (Take a look at the `DefaultGrant` above, for an example of where that's\n helpful).\n\nA separate note on conversion/compatibility. Where possible, this library tries\nto support alembic-utils native objects as stand-ins for the objects defined in\nthis library. For example, `alembic_utils.pg_view.PGView` can be declared\ninstead of a `sqlalchemy_declarative_extensions.View`, and we will internally\ncoerce it into the appropriate type. Hopefully this eases any transitional\ncosts, or issues using one or the other library.\n",
"bugtrack_url": null,
"license": "Apache-2.0",
"summary": "Library to declare additional kinds of objects not natively supported by SQLAlchemy/Alembic.",
"version": "0.8.2",
"project_urls": {
"Homepage": "https://github.com/dancardin/sqlalchemy-declarative-extensions",
"Repository": "https://github.com/dancardin/sqlalchemy-declarative-extensions"
},
"split_keywords": [
"alembic",
" declarative",
" grant",
" mysql",
" postgresql",
" role",
" schema",
" sqlalchemy",
" view"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "1ca7354b9948c4cf1b1d07d6b865e1dfde096a9b1e5fad7e61864935d3bf53d5",
"md5": "9115c8c3b6e114022b5ec657bf5fdd83",
"sha256": "9ac592c86f3954974043e482abd7768e9283f5da7180f9c00018c9eb52a5a75b"
},
"downloads": -1,
"filename": "sqlalchemy_declarative_extensions-0.8.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "9115c8c3b6e114022b5ec657bf5fdd83",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<4,>=3.8",
"size": 75515,
"upload_time": "2024-04-26T17:41:36",
"upload_time_iso_8601": "2024-04-26T17:41:36.519465Z",
"url": "https://files.pythonhosted.org/packages/1c/a7/354b9948c4cf1b1d07d6b865e1dfde096a9b1e5fad7e61864935d3bf53d5/sqlalchemy_declarative_extensions-0.8.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "7564fc630d8689e996dd2c92bbdc17992e4004db43c2ffa0845d99a337957f74",
"md5": "9f628e219a7b04a17a0d7fcbc3ec6e81",
"sha256": "a1fc019c86d7dfe51866f1cbae39113451fe262623abcbf329f83d4fabd64ad8"
},
"downloads": -1,
"filename": "sqlalchemy-declarative-extensions-0.8.2.tar.gz",
"has_sig": false,
"md5_digest": "9f628e219a7b04a17a0d7fcbc3ec6e81",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<4,>=3.8",
"size": 52420,
"upload_time": "2024-04-26T17:41:34",
"upload_time_iso_8601": "2024-04-26T17:41:34.637746Z",
"url": "https://files.pythonhosted.org/packages/75/64/fc630d8689e996dd2c92bbdc17992e4004db43c2ffa0845d99a337957f74/sqlalchemy-declarative-extensions-0.8.2.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-04-26 17:41:34",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "dancardin",
"github_project": "sqlalchemy-declarative-extensions",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlalchemy-declarative-extensions"
}
JSON
