Support SQLAlchemy 2's `DeclarativeBase` and `MappedAsDataclass`

[tadams42]

I've been playing with mapped_column and MappedAsDataclass (new stuff in SQLAlchemy 2.x):

Declarative Table with mapped_column()
Declarative Dataclass Mapping

Example:

mkdir sqlaplayground
cd sqlaplayground
pyenv local 3.11 
python -m venv .venv 
source .venv/bin/activate
pip install -U pip wheel setuptools
cat requirements.txt
# sqlalchemy >= 2.0.0b3
# flask-sqlalchemy @ git+https://github.com/pallets-eco/flask-sqlalchemy@3.0.x
# flask >= 2.0.0
pip install -r requirements.txt

from __future__ import annotations

from typing import Optional

from sqlalchemy import ForeignKey, String, create_engine, orm
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, MappedAsDataclass, mapped_column, sessionmaker
)

CONFIG = {
    "SQLALCHEMY_DATABASE_URI": "sqlite+pysqlite://",
}

Engine = create_engine(CONFIG["SQLALCHEMY_DATABASE_URI"], future=True)
Session = sessionmaker(bind=Engine, future=True)
session = Session()


class BaseModel(MappedAsDataclass, DeclarativeBase):
    """subclasses will be converted to dataclasses"""

    pass


class Book(BaseModel):
    __tablename__ = "books"

    id: Mapped[Optional[int]] = mapped_column(
        init=False, primary_key=True, autoincrement=True
    )

    title: Mapped[Optional[str]] = mapped_column(String(length=64), default=None)

    author_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("authors.id"), nullable=False, index=True, default=None
    )
    author: Mapped[Optional[Author]] = orm.relationship(
        "Author", uselist=False, back_populates="books", default=None
    )


class Author(BaseModel):
    __tablename__ = "authors"

    id: Mapped[Optional[int]] = mapped_column(
        init=False, primary_key=True, autoincrement=True
    )
    name: Mapped[Optional[str]] = mapped_column(String(length=64), default=None)
    books: Mapped[list[Book]] = orm.relationship(
        "Book", uselist=True, back_populates="author", default_factory=list
    )

BaseModel.metadata.create_all(Engine)

book = Book(title="42", author=Author(name="Name"))
session.add(book)
session.commit()

This rather verbose declaration of models gives us some nice things:

• more precise mypy and PyRight static typechecking.
• dataclass-like __init__

repr(book)
# "Book(id=1, title='42', author_id=1, author=Author(id=1, name='Name', books=[...]))"
?book.__init__
# book.__init__(title=None, author_id=None, author=None)

First try at using my BaseModel with Flask-SQLAlchemy gets me into trouble with metaclass inheritance:

import flask
import flask_sqlalchemy


app = flask.Flask(__name__)
app.config.from_mapping(CONFIG)

db = flask_sqlalchemy.SQLAlchemy(model_class=BaseModel)

# TypeError: metaclass conflict: the metaclass of a derived class must be a (non-strict) 
# subclass of the metaclasses of all its bases

which is fair enough...

I did try few things from Advanced Customization but so far came empty handed.

Can Flask-SQLAlchemy support this "new" declarative models?
Should it?
Maybe avoid this problem by somehow replacing metaclass magic with __init_subclass__ from
PEP 487 - Simpler customisation of class creation

[davidism]

I looked into both DeclarativeBase and __init_subclass__ while writing Flask-SQLAlchemy 3, but concluded that it was not clear how to support DeclarativeBase or __init_subclass__ given the features we provide. I'd be happy for someone to show me how we could do both of those things.

Flask-SQLAlchemy allows passing either a plain class or a declarative base class as model_class. It creates a declarative base from the plain class if needed. It does that by looking at isinstance(model_class, DeclarativeMeta).

flask-sqlalchemy/src/flask_sqlalchemy/extension.py

Lines 453 to 457 in 6605f9a

	if not isinstance(model, sa.orm.DeclarativeMeta):
	metadata = self._make_metadata(None)
	model = sa.orm.declarative_base(
	metadata=metadata, cls=model, name="Model", metaclass=DefaultMeta
	)

Since you're using SQLAlchemy 2's new DeclarativeBase, you're not getting DeclarativeMeta as a base class, leading to that error since Flask-SQLAlchemy tries to make a new declarative base.

Our code for setting table names and supporting joined-table inheritance requires running before and after DeclarativeMeta.__init__. __init_subclass__ runs strictly before the metaclass, I couldn't figure out a way to support what we need.

flask-sqlalchemy/src/flask_sqlalchemy/model.py

Lines 108 to 119 in 6605f9a

	if should_set_tablename(cls):
	cls.__tablename__ = camel_to_snake_case(cls.__name__)
	super().__init__(name, bases, d, **kwargs)
	# __table_cls__ has run. If no table was created, use the parent table.
	if (
	"__tablename__" not in cls.__dict__
	and "__table__" in cls.__dict__
	and cls.__dict__["__table__"] is None
	):
	del cls.__table__

I'm optimistic that there's some way to refactor our code to provide support for both DeclarativeMeta and DeclarativeBase. But I need someone else to step up and help figure it out, it's not something I have time to devote to.

[tadams42]

I understand what and why is happening but, unfortunately, have no useful input on how.

Thanks for looking into this.

[baggiponte]

Ciao! I had a look at the previous issues and this seems to me the first time the compatibility with SQLAlchemy 2.0 is discussed, am I right? This seems like a pretty advanced topic to start contributing to, but I'd be willing to help out with some guidance.

[davidism]

Not sure that it matters, but this is the first time compatibility with DeclarativeBase was brought up, not with SQLAlchemy 2 in general.

I don't have any guidance to give besides what I've written above. I don't know how to solve this any more than you do, I would be doing the same investigation.

[behai-nguyen]

I'm not sure if this is the appropriate thread to post my question. If not, I do apologise and please let me know and I will remove it.

I am combining flask_sqlalchemy.model.DefaultMeta and abc.ABCMeta to give my BaseTable ability to have abstract methods.

It works fine with Flask-SQLAlchemy==2.5.1 and SQLAlchemy==1.4.39.

I updated to current version, Flask-SQLAlchemy==3.0.2 and SQLAlchemy==1.4.44, and it gives me the following error on start up: it fails at the first import statement of a model descends from WriteCapableTable, who is in turn descends from BaseTable:

(venv) F:\book_keeping>venv\Scripts\flask.exe run
Traceback (most recent call last):
  File "C:\PF\Python310\lib\runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\PF\Python310\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "F:\book_keeping\venv\Scripts\flask.exe\__main__.py", line 7, in <module>
  File "F:\book_keeping\venv\lib\site-packages\flask\cli.py", line 1054, in main
    cli.main()
  File "F:\book_keeping\venv\lib\site-packages\click\core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "F:\book_keeping\venv\lib\site-packages\click\core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "F:\book_keeping\venv\lib\site-packages\click\core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "F:\book_keeping\venv\lib\site-packages\click\core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "F:\book_keeping\venv\lib\site-packages\click\decorators.py", line 84, in new_func
    return ctx.invoke(f, obj, *args, **kwargs)
  File "F:\book_keeping\venv\lib\site-packages\click\core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "F:\book_keeping\venv\lib\site-packages\flask\cli.py", line 918, in run_command
    raise e from None
  File "F:\book_keeping\venv\lib\site-packages\flask\cli.py", line 904, in run_command
    app = info.load_app()
  File "F:\book_keeping\venv\lib\site-packages\flask\cli.py", line 308, in load_app
    app = locate_app(import_name, name)
  File "F:\book_keeping\venv\lib\site-packages\flask\cli.py", line 218, in locate_app
    __import__(module_name)
  File "F:\book_keeping\app.py", line 14, in <module>
    from book_keeping.models.user import User
  File "f:\book_keeping\src\book_keeping\models\user.py", line 16, in <module>
    from book_keeping.models.base_table import WriteCapableTable
  File "f:\book_keeping\src\book_keeping\models\base_table.py", line 70, in <module>
    class BaseTable( declarative_base(cls=BaseModel, metaclass=DeclarativeABCMeta) ):
  File "F:\book_keeping\venv\lib\site-packages\flask_sqlalchemy\model.py", line 94, in __init__
    metadata = cls.__fsa__._make_metadata(bind_key)
AttributeError: type object 'BaseTable' has no attribute '__fsa__'. Did you mean: '__hash__'?

Versions 3.0.0a1 and 3.0.0 also give the same problem. I am not sure where the problem is.

I'm guessing it has something do with with my metaclass declaration? I have to do something to give my object 'BaseTable' the attribute 'fsa'... Would you be able to suggest anything please?

Below is the implemention of my BaseTable and descendant WriteCapableTable. I've left out WriteCapableTable's write_to_database( ... ) method, it is a bit too long and not relevant to the issue.

"""
ABC-SQLAlchemy declarative base table which provides
abstractmethod capability.
"""
from abc import (
    ABCMeta,
    abstractmethod,
)

import logging

from sqlalchemy.ext.declarative import declarative_base

from flask_sqlalchemy.model import DefaultMeta

from sqlalchemy import inspect

from book_keeping import db

class DeclarativeABCMeta( DefaultMeta, ABCMeta ):
    """Preparing meta classes for ABC and declarative_base()."""
    pass

class BaseModel( object ):
    def __str__( self ):
        fmt = u'{}: {}'
        class_ = self.__class__.__name__
        attrs = [ ( k, getattr(self, k) ) for k in self.__mapper__.columns.keys() ]

        sattrs = u', '.join( '{}: {!r}'.format(*x) for x in attrs )
        return fmt.format( class_, sattrs )

    def __repr__( self ):
        fmt = u'{}({})'
        class_ = self.__class__.__name__

        attrs = [ ( k, getattr(self, k) ) for k in self.__mapper__.columns.keys() ]

        sattrs = u', '.join( '{}={!r}'.format(*x) for x in attrs )
        return fmt.format( class_, sattrs )

class BaseTable( declarative_base(cls=BaseModel, metaclass=DeclarativeABCMeta) ):
    """
    Base table class for all tables.

    Assumptions:

    Tables have a single integer primary key.

    Protected Variables:

    self._primary_keys: list of primary key column names. Most tables have only one.

    self._primary_key: the first primary key column name from self._primary_keys.
    Assuming this is the only integer primary key column.

    self._type: Run-time info. Instance to class type.
    """

    __abstract__ = True

    def __get_primary_keys( self ):
        """
        Collect primary key column names and return all as an array.
        Most tables have only a single primary key.
        """
        res = []
        ins_obj = inspect( type(self) )

        for item in ins_obj.primary_key:
            res.append( item.key )

        return res

    def __init__( self, **kwargs ):
        self._primary_keys = self.__get_primary_keys()
        self._primary_key = self._primary_keys[ 0 ]

        self._type = type( self )

        self.__dict__.update( kwargs )

        self._app_logger = logging.getLogger( self.__class__.__name__.lower() )

    @abstractmethod
    def write_to_database( self, data ):
        """
		Writes data to database.

        Return:

        Up to individual descendant class.
        """

class NonWriteTable( BaseTable, db.Model ):
    __abstract__ = True

    """
    Parent class tables who contents do not change by application.
    For example, static look up tables.
    """
    def write_to_database( self, data ):
        """
        Override.See BaseTable's docstring also.
        """
        return True

class WriteCapableTable( BaseTable, db.Model ):
    __abstract__ = True

    def write_to_database( self, data ):
        """
        Override.
		Writes data to database.

        Arguments:

		data: contains both new records and updated records.

        Return:

        On successful: a JSON with 200 status.
        On failure: a JSON with 500 status.
        """

        """
        CODES OMITTED. BASICALLY WRITES DATA TO DATABASE.
        """

Thank you and best regards.

[jdimmerman]

I can take a look at this if that works for you @davidism @baggiponte (though not suggesting I'll definitely make the the required changes, in case others have time/appetite)

[davidism]

No need to ask to work on something, go for it.

[jdimmerman]

Query result typehints (my selfish concern) actually work fine when upgrading to the latest sqlalchemy 2.0 beta without using DeclarativeBase.

That being said, a few things:

1. I did need to change the return value typehint on _make_scoped_session for session to be properly typed and therefore types to flow to query results properly. I'm unclear why this change was necessary but is likely clear to others. Should I submit a PR with this change?

   # current
   from sqlalchemy import sa
    def _make_scoped_session(self, options: dict[str, t.Any]) -> sa.orm.scoped_session
   
   # what works for typing `db.session` properly:
   from sqlalchemy.orm import scoped_session
   def _make_scoped_session(self, options: dict[str, t.Any]) -> scoped_session
   

2. Types don't flow through the extension object methods and convenience Model.query as of now. I didn't look at this, probably worth a separate issue to track. Example:

   todo1 = db.session.get(Todo, my_id)  # properly typed as Todo | None
   todo2 = db.get_or_404(Todo, my_id)  # improperly typed as Any
   todo3 = db.session.query(Todo).all()  # properly typed as List[Todo]
   todo4 = Todo.query.all()  # improperly typed as Any
   

   the extension object methods can be solved but must use internals from sqlalchemy. Example:

   from sqlalchemy.orm._typing import _O
   from sqlalchemy.orm.session import _EntityBindKey
   def get_or_404(
       self,
       entity: _EntityBindKey[_O],
       ... (etc)
   ) -> t.Optional[_O]:
   

   Not sure yet about the query property on Model.

3. Finally note that there is also a DeclarativeBaseNoMeta equivalent to DeclarativeBase that their docs suggest could be used when you're already using a meta class.

[pamelafox]

Thanks @jdimmerman for the investigation, very helpful.

1. This suggestion is being made in pr Fix little problem with type hints and adopt a test to make sure it works in all platform #1207
2. I made an attempt for typing the view functions in pr Add type to get_or_404, update tests #1208
3. DeclarativeBaseNoMeta seems to work for me, with a slight mod of this package.

In flask_sqlalchemy/extension.py:make_declarative_base, I changed the if to:

if not isinstance(model, sa.orm.DeclarativeMeta) and not issubclass(model, sa.orm.DeclarativeBaseNoMeta):

Then in my own code, I inherited from DeclarativeBaseNoMeta:

class BaseModel(DeclarativeBaseNoMeta):
    pass

And specified that as the model class:

db = SQLAlchemy(model_class=BaseModel)

And that enabled me to use the Mapped attributes.

See full change in:
https://github.com/pamelafox/flask-db-quiz-example/pull/18/files

I'll see if I can make the change to extension.py inside flask-sqlalchemy itself with tests.

I haven't tried data classes however, don't know if that'll work too.

[pamelafox]

Okay, so I've done more investigation of my approach inside the flask-sqlalchemy codebase itself. This comment may be a bit long as I share my findings.

First, in order to understand the metaclass/subclass distinction better, I made a diagram of relevant SQLAlchemy classes:

I realized that my above approach was also compatible with using DeclarativeBase (versus DeclarativeBaseNoMeta) as long as I checked for it in the make_declarative_base function.

With that small change, I could use Flask-SQLAlchemy features like get_or_404. However, I couldn't use the functionality provided by the mixins in model.py, like table name generation, bind keys, and custom repr. All of those are implemented as metaclass mixins, which isn't compatible with the new subclass approach.

The SQLAlchemy2.0 docs suggest 2 main approaches for using mixins with the DeclarativeBase:

1. Defining mixin classes, and mixing those into the actual table subclasses (e.g. class Table(MyMixin, Base))
   https://docs.sqlalchemy.org/en/20/orm/declarative_mixins.html
2. Augmenting the Base class itself. https://docs.sqlalchemy.org/en/20/orm/declarative_mixins.html#augmenting-the-base

A third possibility mentioned in some GitHub threads is to use @event listeners, but I could not find an event that was fired at the appropriate time for setting tablename. Only one event is thrown before that happens, and that particular event doesn't provide access to the cls object anyway.
https://docs.sqlalchemy.org/en/20/core/event.html#events

I tried both approaches #1 and #2 but it felt weird for Flask-SQLAlchemy to create its own Base, so I settled on #1 as the better of those options. Here's what it looks like:
https://github.com/pallets-eco/flask-sqlalchemy/compare/3.0.x...pamelafox:flask-sqlalchemy:mixin-base?expand=1

I basically combined all the previous mixins into a single DefaultMixin class in model.py, and then manually added those to my user-defined classes. And there you see the drawbacks to this approach: 1) the mixins are mashed together for ease of adding 2) the dev has to add the mixin themselves. I don't know of a way in Python to auto-add a mixin to newly created db.Model subclasses.

So, in conclusion:

• 🙂 It looks like a fairly minor change to get some aspects of flask-sqlalchemy working with the 2.0 classes.
• 😢 Getting the mixins working is harder, and its not obvious what the API should look like. It also introduces some code/test duplication.

I'm happy to send the PR for the minor change if that still seems useful/desired.
A next step might be asking the SQLAlchemy team for their insight in the GitHub discussions.

[pamelafox]

I've made another branch with a different approach:
https://github.com/pallets-eco/flask-sqlalchemy/compare/3.0.x...pamelafox:flask-sqlalchemy:mixin-alternate?expand=1

In this one, the dev only needs to specify the model class of DeclarativeBase:

b = SQLAlchemy(app, model_class=DeclarativeBase)

Then, in the flask-sqlalchemy code, it dynamically creates a Base class that mixes in a subclass version of each mixin:

  model = types.new_class(
      "Base",
      (BindMixin, NameMixin, ReprMixin, sa.orm.DeclarativeBase),
      {"metaclass": type(sa.orm.DeclarativeBase)},
      lambda ns: ns.update(body),
  )

This code passes at least basic tests for the mixin functionality - bind key, name, repr, etc. And if someone wanted just some of those, they could create their own Base class.

This seems fairly promising as an approach.