Comments (7)
First of all we have to import from sqlalchemy which I dont like. It is a bit confusing. Hence I would like to import it from sqlmodel itself.
Like how we have
with Session(engine) as session:
session.exec()
we should have
async with AsyncSession(engine) as session:
await session.exec()
so it becomes easier for us to make sessions.
from sqlmodel.
First of all we have to import from sqlalchemy which I dont like. It is a bit confusing. Hence I would like to import it from sqlmodel itself.
Like how we have
with Session(engine) as session: session.exec()we should have
async with AsyncSession(engine) as session: await session.exec()so it becomes easier for us to make sessions.
It looks like sqlmodel has already implemented its own asyncsession but is just not importable from the root directory. Currently I believe you need to import it through
from sqlmodel.ext.asyncio.session import AsyncSession
If we want to adhere to sqlalchemy model import structure it should be
from sqlmodel.ext.asyncio import AsyncSession
However, according to @alvynabranches the proposed solution is
from sqlmodel import AsyncSession
@tiangolo what do you think?
from sqlmodel.
A full working example of the following is here: https://github.com/deshetti/sqlmodel-async-example
Opened a PR to add documentation to the docs: #633
from contextlib import asynccontextmanager
from typing import Optional
from fastapi import FastAPI
from pydantic import BaseModel
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlmodel import Field, SQLModel
# Initialize FastAPI application
app = FastAPI()
# Define User model for SQLModel
class User(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
age: int
# Define UserCreate model for Pydantic validation
# For id field to not show up on the OpenAPI spec
class UserCreate(BaseModel):
name: str
age: int
# Database connection string
DATABASE_URL = "postgresql+asyncpg://postgres:postgres@localhost/sampledb"
# Create an asynchronous engine for the database
engine = create_async_engine(
DATABASE_URL,
echo=True,
future=True,
pool_size=20,
max_overflow=20,
pool_recycle=3600,
)
# Ayschronous Context manager for handling database sessions
@asynccontextmanager
async def get_session() -> AsyncSession:
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
async with async_session() as session:
yield session
# Function to create a new user in the database
async def create_user(user: User) -> User:
async with get_session() as session:
session.add(user)
await session.commit()
await session.refresh(user)
return user
# Event handler for startup event of FastAPI application
@app.on_event("startup")
async def on_startup():
async with engine.begin() as conn:
# For SQLModel, this will create the tables (but won't drop existing ones)
await conn.run_sync(SQLModel.metadata.create_all)
# Endpoint to create a new user
@app.post("/users/", response_model=User)
async def create_user_endpoint(user: UserCreate):
db_user = User(**user.dict())
result = await create_user(db_user)
return result
# Main entry point of the application
if __name__ == "__main__":
import uvicorn
uvicorn.run(app, host="0.0.0.0", port=8000)
from sqlmodel.
from collections.abc import AsyncGenerator
from typing import Annotated, Callable
from fastapi import Depends
from sqlalchemy import create_engine
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker, Session
from core.config import settings
##############
# region 'Async Session'
engine = create_async_engine(
settings.SQLALCHEMY_DATABASE_URI_ASYNC,
future=True,
echo=settings.LOCAL_DEV,
hide_parameters=not settings.LOCAL_DEV,
connect_args={
# https://www.postgresql.org/docs/current/runtime-config.html
"server_settings": {
"application_name": f"{settings.PROJECT_NAME} {settings.VERSION} async",
"jit": "off",
},
},
)
AsyncSessionFactory = sessionmaker(
bind=engine,
autoflush=False,
expire_on_commit=False,
class_=AsyncSession,
)
async def get_db() -> AsyncGenerator:
yield AsyncSessionFactory
Session = Annotated[AsyncSession, Depends(get_db)]
# endregion
##############
@router.post(...)
async def some_function(
session: Session)
async with session() as db: # noqa
...
stmt = select(Model1)
items = await db.execute(stmt)
data = items.all()
return data
asyncpg, works fine, except:
- asyncpg cant execute sql-files with multistatements
- asyncpg not shows params statements
from sqlmodel.
First of all we have to import from sqlalchemy which I dont like. It is a bit confusing. Hence I would like to import it from sqlmodel itself.
Like how we have
with Session(engine) as session: session.exec()we should have
async with AsyncSession(engine) as session: await session.exec()so it becomes easier for us to make sessions.
@tiangolo Can I work on this? Add a sqlmodel version of asyncsession as well as create_async_engine
from sqlmodel.
@tiangolo when do you plan to add a fully-functional async support to this library?
We are using the the SQL Alchemy at the moment and recently rewrote our code to use async. But problem is in the async m2m and m2o relations, that we need to use .awaitable_attrs
that our code a bit ugly. Look on this:
docs = await folder.awaitable_attrs.documents
docs_to_authors = {doc.id: await doc.awaitable_attrs.authors for doc in docs}
Do you plan to get rid of such awaitable_attrs
syntax in your version and have something like this?
docs = await folder.documents
docs_to_authors = {doc.id: await doc.authors for doc in docs}
so that under the hood it will know that "documents" and "authors" are actually awaitable props and we need to use await keyword for them. Otherwise mypy needs to catch such a problems and notify us that we forgot to add await keyword for such m2m and m2o relations when trying to load them from DB.
from sqlmodel.
@tiangolo when do you plan to add a fully-functional async support to this library?
We are using the the SQL Alchemy at the moment and recently rewrote our code to use async. But problem is in the async m2m and m2o relations, that we need to use
.awaitable_attrs
that our code a bit ugly. Look on this:docs = await folder.awaitable_attrs.documents docs_to_authors = {doc.id: await doc.awaitable_attrs.authors for doc in docs}Do you plan to get rid of such
awaitable_attrs
syntax in your version and have something like this?docs = await folder.documents docs_to_authors = {doc.id: await doc.authors for doc in docs}so that under the hood it will know that "documents" and "authors" are actually awaitable props and we need to use await keyword for them. Otherwise mypy needs to catch such a problems and notify us that we forgot to add await keyword for such m2m and m2o relations when trying to load them from DB.
If you want to fetch all related data, I suggest you to try selectinload
from sqlalchemy.orm import selectinload
...
(await session.exec(select(User).options(selectinload(User.roles))).all()
from sqlmodel.
Related Issues (20)
- `enum.IntFlag` field error when the flag is zero or many field enabled.
- The SQL model cannot perform insertion into another table using queried data HOT 2
- [M2M] Query dependent incl. `link_model` fields HOT 3
- Could not refresh instance HOT 10
- SQLModel doesn't recognize Relationship between models HOT 1
- Get select with options (selectinload) using response schema HOT 2
- Dose there any better way to write timezone aware datetime field without using the SQLAlchemy ? HOT 3
- Obtaining `TypeError: Cannot pickle 'module' object` on models with many-to-many relationships HOT 2
- Order of columns in the table created does not have 'id' first, despite the order in the SQLModel. Looks like it's prioritising fields with sa_column HOT 4
- Erro ao executar uvicorn.run(...) HOT 1
- Many to many relationship between a table and itself HOT 5
- How to add current date time by default on a table declaration? HOT 13
- async relationship bug HOT 14
- 🚀 Roadmap HOT 28
- Internal link failed at create-db-and-table.md
- Field cannot autocompletion when its a SQLModel HOT 7
- Add an overload to the `exec` method with `_Executable` statement for update and delete statements HOT 4
- How to define table prefix name
- docs/contributing.md outdated using poetry HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlmodel.