search

edelooff / sqlalchemy-json

Full-featured JSON type with mutation tracking for SQLAlchemy
http://variable-scope.com/posts/mutation-tracking-in-nested-json-structures-using-sqlalchemy
BSD 2-Clause "Simplified" License
189 stars 34 forks source link

Allow choosing column type #22

Closed morganchristiansson closed 4 years ago

morganchristiansson commented 4 years ago

Currently sqlalchemy-utils json type doesn't support JSON data type in mysql8.

sqlalchemy-utils will not be updated to support it and sqlalchemy.types.JSON should be used according to https://github.com/kvesteri/sqlalchemy-utils/issues/164#issuecomment-171681943

We should default to sqlalchemy.types.JSON and maybe allow it to be configurable. Or make new major/minor release for breaking change if needed.

Postgres and MySQL8 support JSON column type. MariaDB supports it as an alias to LONGTEXT. Others DBs probably do not support it.

It looks like the postgres-specific subclass has some extra methods so not sure if it's preferred in some scenario? https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

edelooff commented 4 years ago

Being able to use the specific Postgres JSON type would be pretty cool. The pending changes in #23 are solid as a start to get MySQL 8 support, but I'll have a look at backend type selection over the weekend.

morganchristiansson commented 4 years ago

Is this possible? As column type is defined as parent by parent class of MutableJson configuring it at runtime doesn't seem straightforward. I'm sure there's some hacks to workaround tho?

Either way I am satisfied with using sqlalchemy.types.JSON for MySQL 5.7/8.0 support merged in #23

edelooff commented 4 years ago

Turns out this is necessary to fix a regression introduced by #23. SQLite doesn't provide a native JSON type, so it really benefited from the sqla-utils type that provided a fallback solution via a simple TypeDecorator (like this one from the documentation).

The good news is that it looks like it's an easy enough change. A function to create a mutable JSON type, a little like:

def mutable_json_type(dbtype=JSON, nested=False):
    mutable_type = NestedMutable if nested else MutableDict
    return mutable_type.as_mutable(dbtype)
edelooff commented 4 years ago

@morganchristiansson could you give #25 a try on your environment and see if that works well for you?