Add sessionmaker

[hitman-gdg]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

Session = sessionmaker(engine)

Description

Add an sqlalchemy compatible sessionmaker that generates SqlModel sessions

Wanted Solution

I would like to have a working sessionmaker

Wanted Code

from sqlmodel import sessionmaker

Alternatives

No response

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.6

Additional Context

No response

[Zaffer]

Please add this because now it give error that .exec() does not exist on session if you using sqlalchemy import.

[olzhasar]

I faced the same issue and it looks like SQLAlchemy's sessionmaker works just fine with sqlmodel. The only caveat is that you have to provide sqlmodel.Session class to it:

from sqlmodel import Session
from sqlalchemy.orm import sessionmaker

TestSession = sessionmaker(class_=Session)

[tiangolo]

Why do you want a sessionmaker instead of using the new/future and more modern approach of using a with statement with a Session?

Just for completeness, AFAIK, the sessionmaker exists mainly because there was no with statement at the time.

[gazpachoking]

Why do you want a sessionmaker instead of using the new/future and more modern approach of using a with statement with a Session?

My understanding is that the point of the sessionmaker is so that you don't have to pass the configuration (e.g. the engine) to the Session every time. https://docs.sqlalchemy.org/en/14/orm/session_basics.html#using-a-sessionmaker

[tobiasfeil]

You can achieve the desired functionality like this:

from sqlmodel import Session as SQLModelSession
from contextlib import contextmanager

engine = ...

@contextmanager
def Session():
    session = SQLModelSession(engine)
    try:
        yield session
    finally:
        session.close()

Then, you can use Session as a context manager without passing the engine every time:

with Session() as session:
    ...

[seanhuang514]

I faced a similar issue when I tried to call session.exec,then I got an error

AttributeError: 'Session' object has no attribute 'exec'

I found that if you use Sessionmaker like the below, you will get an instance of sqlalchemy.orm.session.Session instead of sqlmodel.orm.session.Session, and the instance of the SQLAlchemy session doesn't have the method exec, only the SQLModel session has.

from sqlalchemy.orm import sessionmaker
SessionLocal = sessionmaker(autoflush=False , bind=engine)

to fix the issue, just like @olzhasar mentioned, you have to pass Session that is imported from sqlmodel and pass to sessionmaker

from sqlmodel import Session as SQLModelSession
SessionLocal = sessionmaker(class_ = SQLModelSession, autoflush=False , bind=engine)

def get_session():
    with SessionLocal() as session:
        yield session
        
Session = Annotated[SQLModelSession, Depends(get_session)] 

# endpoint
@router.get("/db-test")
async def db_test(session: Session):
    statement = select(User)
    results = session.exec(statement)
    user = results.first()
    return user

[msftcangoblowm]

@seanhuang514 provided a workaround. If the underlying issue were understood it would be trivial to fix. Then the workaround would be unnecessary.

sessionmaker has kwarg class_, which is set to sqlmodel.orm.session.Session.
In sessionmaker.__init__ there is this line.

self.class_ = type(class_.__name__, (class_,), {})

^^ makes a subclass of class_ with name, class_.__name__. Which resolves to "Session". sqlalchemy.orm.session however already has module object sqlalchemy.orm.session.Session. So instead of a subclass of sqlmodel.orm.session.Session, get (a strange) subclass of sqlalchemy.orm.session.Session. See what is happened by viewing that subclass's mro

>>> from sqlalchemy.orm.session import sessionmaker
>>> from sqlalchemy.orm.session import Session as Session_
>>>
>>> from sqlmodel.orm.session import Session
>>>
>>> msg = "Good guys finish last! Brand your Session as SessionSM"
>>> assert Session.__name__ != Session_.__name__, msg
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AssertionError: Good guys finish last! Brand your Session as SessionSM
>>> SesMaker = sessionmaker(class_=Session)
>>> Session_.__mro__
(<class 'sqlalchemy.orm.session.Session'>, <class 'sqlalchemy.orm.session._SessionClassMethods'>, <class 'sqlalchemy.event.registry.EventTarget'>, <class 'object'>)
>>> Session.__mro__
(<class 'sqlmodel.orm.session.Session'>, <class 'sqlalchemy.orm.session.Session'>, <class 'sqlalchemy.orm.session._SessionClassMethods'>, <class 'sqlalchemy.event.registry.EventTarget'>, <class 'object'>)
>>> SesMaker.class_.__mro__
(<class 'sqlalchemy.orm.session.Session'>, <class 'sqlmodel.orm.session.Session'>, <class 'sqlalchemy.orm.session.Session'>, <class 'sqlalchemy.orm.session._SessionClassMethods'>, <class 'sqlalchemy.event.registry.EventTarget'>, <class 'object'>)
>>> mro_dodgy = [kls.__qualname__ for kls in SesMaker.class_.__mro__]
>>> mro_dodgy
['Session', 'Session', 'Session', '_SessionClassMethods', 'EventTarget', 'object']
>>> assert mro_dodgy.count("Session") == 1
False

After the refactor this ^^ test should pass. Failing now cuz "Session" occurs 3 times.

The solution is: class naming cannot be "Session". Name it anything else, except:

• "Session" / "AsyncSession"

• any other objects names' within modules sqlalchemy.orm.session or sqlalchemy.ext.asyncio.session

• anything imported

My suggestion for naming:

• sqlmodel.orm.session.SessionSM

• sqlmodel.ext.asyncio.session.AsyncSessionSM

SM is short for sqlmodel.

seanhuang514 suggestion, i object to.

SQLModelSession and SQLModelAsyncSession are too long and assumes ownership of Session and AsyncSession, rather than being a humble variant.

After renaming both classes, sessions created with sessionmaker, SessionSM().exec(...) will work as expected.

Bite the bullet, take the bitter pill, and refactor! Every test (docs, issues, discussions, web article, AI bot response) that uses Session or AsyncSession will need to be modified to use new class naming. Unfortunately the refactor will be a breaking change (major version release) for anyone importing sqlmodel.orm.session.Session or sqlmodel.ext.asyncio.session.AsyncSession.

Please relabel this as a (subtle) bug rather than a feature request.

Gentlemen grab the popcorn and beer. What comes after is going to be epic!

CC @tiangolo

[msftcangoblowm]

To help visualize the issue, a fixed mro would look precisely like:

(<class 'sqlalchemy.orm.session.SessionSM'>, <class 'sqlmodel.orm.session.SessionSM'>, <class 'sqlalchemy.orm.session.Session'>, <class 'sqlalchemy.orm.session._SessionClassMethods'>, <class 'sqlalchemy.event.registry.EventTarget'>, <class 'object'>)

Notice "Session" occurs only one.

"SessionSM" could be added to 'sqlalchemy.orm.session' namespace whereas "Session" couldn't because it already exists within 'sqlalchemy.orm.session' namespace.

Once you see it, you can't unsee it.

[msftcangoblowm]

#81 does not fix the issue. It simply reflects a desperate desire for sqlmodel to have a wrapper of sessionmaker. The wrapped sessionmaker suffers from / repeats the same issue.

This children is why we write tests to prove code, not only works, but actually resolves the issue. No such tests came along with the PR. The PR is also ridiculous and childish, but may bring a smile to your face. Just for the comic value, recommend reading it.

Whoever closed the PR is now vindicated. Thanks for saving us from going down the wrong path.

[msftcangoblowm]

Created upstream discussion to confirm this is can be elevated to SQLA issue status

sqlalchemy#13054

[msftcangoblowm]

Fixing sqlmodel#75 seanhuang514 example. The point is to demonstrate
renaming the import from sqlmodel.ext.asyncio.session import AsyncSession
has zero effect.

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlmodel.ext.asyncio.session import AsyncSession
from sqlmodel import Field, SQLModel, select
from fastapi import Depends

SQLModel.metadata.naming_convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    email: str

engine_async = create_async_engine("sqlite+aiosqlite:///example.db")
SQLModel.metadata.create_all(engine_async.sync_engine)
AsyncSessionLocal = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_session():
    async with AsyncSessionLocal() as session:
        yield session

@router.get("/db-test")
async def db_test(session: AsyncSession = Depends(get_session)):
    result = await session.exec(select(User))
    user = result.first()
    return user

The line:

from sqlmodel import Session as SQLModelSession is unnecessary. Renaming the import does not create a custom class or fix any issue.

Renaming Session to SQLModelSession has no effect on method availability or class behavior. The .exec() method is lost only when using the wrong session class — not due to import naming.

[msftcangoblowm]

Please read sqlalchemy/sqlalchemy#13054 to see how two classes can have the same __name__ and __module__ and not cause a module namespace conflict.

sqlmodel.orm.session.Session and sqlmodel.ext.asyncio.session.AsyncSession do not have to be renamed. It's a good idea to avoid confusion, but it's not absolutely necessary.