Advanced Tutorial - Documentation

[milanzmitrovic]

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

https://sqlmodel.tiangolo.com/advanced/

Description

Just want to ask about advanced tutorial?

Is there a plan to add documentation and what is timeline?

https://sqlmodel.tiangolo.com/advanced/

Tutorial for basic usage is excellent. Thanks to Tiangolo for making it so great and helpful.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.7

Additional Context

No response

[aaron-junot]

I agree that the basic usage tutorial is excellent. We'd like to use SQLModel with the async engine from SQLAlchemy. The advanced page indicates this is possible.

I was hoping you would post an example at least, even if the guide is not complete yet, @tiangolo? 🙏🏻 🙏🏻 🙏🏻

EDIT: Also, we were previously using imperative mapping from SQLAlchemy. I'm curious if your advanced guide will include instructions on working with MetaData using SQLModel?

[perezzini]

Would be awesome to have just a peek about how to use the async engine!

[flattoride]

I order to use async engine, I change sqlmodel.ext.asyncio.session.AsyncSession method exec, from statement: Union[Select[_T], Executable[_T]], to statement: Union[ Select[_TSelectParam], SelectOfScalar[_TSelectParam], Executable[_TSelectParam], ], and then use code:

from typing import Optional, cast
import asyncio

from sqlmodel import Field, Session, SQLModel, create_engine, select
from sqlmodel.ext.asyncio.session import AsyncSession, AsyncEngine


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


sqlite_file_name = "database.db"
sqlite_url = f"sqlite+aiosqlite:///{sqlite_file_name}"

async_engine = AsyncEngine(create_engine(sqlite_url, echo=True))
engine = create_engine(f"sqlite:///{sqlite_file_name}", echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


async def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    async with AsyncSession(async_engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        await session.commit()


def run():
    with Session(engine) as session:
        query = select(Hero).where(Hero.name == "Deadpond")
        heroes = session.exec(query)
        hero = heroes.first()
        for hero in heroes:
            print(hero.name)


async def select_heroes():
    async with AsyncSession(async_engine) as session:
        query = select(Hero).where(Hero.name == "Deadpond")
        # heroes = await session.exec(cast(Select[Hero], query))
        heroes = await session.exec(query)
        hero = heroes.one()
        print(hero.name)
        # for heroe in heroes:
            # print(heroe.name)


async def main():
    await create_heroes()
    await select_heroes()


if __name__ == "__main__":
    # create_db_and_tables()
    asyncio.run(main())

Everything is OK, But I don’t know if there will be other side effects.

[vargasjona]

You can find a sample here https://github.com/jonra1993/fastapi-alembic-sqlmodel-async. It can help others trying to create an async setup with SQLModel.

[plocher]

As a convert-from-Flask, I'm struggling to make fastAPI and SQLModel work together in a non-trivial use case, with many models, relationships and business logic abstractions that preclude putting everything into single models, routes and crud files. The core foundation is working well, but I keep slipping off the edges 🤕

An unexpected "feature" of the fastAPI tutorial (users and items) was that the majority of fastapi ... foo ... search hits on stackexchange, blogs etc featured people using the same core example. Chances are that the answers show code derived from the fastAPI tutorial, making it and the accompanying explanation that much clearer!

With SQLModel, we get a different example. While conceptually similar, it is subtly structurally different - from directory layout and modularity, to design mindset and completeness. For someone struggling to understand the magic that sifts between the cracks, these differences are a search engine comprehension killer :-(

All this is to say that, in the best of worlds, going back and updating the fastAPI tutorial to use SQLModel instead of BaseModel would, in my mind, be a fantastic "advanced" tutorial.

With that in place, a "a world class async app" tutorial with a modular, maintainable file structure would be nice, too 👍

[kiriti-kumo]

Are there any plans for publishing this documentation? Would love to learn more on how to use async engine and SQLModel models with SQLAlchemy

[Kyu]

Collating useful guides i've come across:

Self referential relationships: #127 (comment)
Async database (A little outdated, a couple lines need tweaking): https://github.com/deshetti/sqlmodel-async-example

[geektousif]

I see that the AsyncEngine is not available anymore in sqlmodel.ext.asyncio.session

What's the current working flow everyone's using for async support with SQLModel

[Kyu]

@geektousif

from sqlalchemy.ext.asyncio import create_async_engine, AsyncEngine, async_sessionmaker, AsyncSession

related(for ref) #706