Adapting SqlAlchemy Automap to get declarative SQLModel classes and the fitting Pydantic validation

[MRigal]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm.decl_api import DeclarativeMeta
from sqlmodel import SQLModel
from sqlmodel.main import SQLModelMetaclass

connection = create_engine().connect()  # simplified
# works creating SQLAlchemy ORM models in Base.classes
# Base = automap_base()
# This is some try to reuse the code
Base = automap_base(cls=SQLModel, metaclass=SQLModelMetaclass)
Base.prepare(autoload_with=connection)

Description

I think one of the big unsolved areas where SQLModel could evolve to (although not on the Roadmap as of now) is to support generative / declarative SQLModel declarations based on a DB connection (or a SQL dump).

There is high interest for this, in #43 #658 and #592 at least.

My take would be to try to use the Automap feature of SqlAlchemy, which was also written to be configurable (although not documented).

The Automap module uses declarative_base to generate a base with a mapper and I was hoping to be able to bring SQLModels here but I can't get it really to work.

Any advice on how to go on from there? Or is it a bad idea to be willing to reuse or extend AutoMap? @tiangolo ?

The motivation beyond is to be able to use the ORM and to have Pydantic validation on a DB / Schema that is managed elsewhere.

The only actual other possible option is to use the poorly managed and buggy sqlacodegen to write python files out of it. The declarative style would be much better/cleaner.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.22

Python Version

Python 3.12.7

Additional Context

No response