rhoboro / async-fastapi-sqlalchemy Goto Github PK

License: MIT License

Python 97.53% Mako 2.47%

async-fastapi-sqlalchemy's Introduction

Async Web API with FastAPI + SQLAlchemy 2.0

This is a sample project of Async Web API with FastAPI + SQLAlchemy 2.0. It includes asynchronous DB access using asyncpg and test code covering them.

This sample project is explained in this blog (written in Japanese).
https://www.rhoboro.com/2021/06/12/async-fastapi-sqlalchemy.html

If you want to use prisma instead of sqlalchemy, see rhoboro/async-fastapi-prisma.

Setup

Install

$ python3 -m venv venv
$ . venv/bin/activate
(venv) $ pip install -r requirements.lock

Setup a database and create tables

(venv) $ docker run -d --name db \
  -e POSTGRES_PASSWORD=password \
  -e PGDATA=/var/lib/postgresql/data/pgdata \
  -v pgdata:/var/lib/postgresql/data/pgdata \
  -p 5432:5432 \
  postgres:16.3-alpine

# Cleanup database
# $ docker stop db
# $ docker rm db
# $ docker volume rm pgdata

(venv) $ APP_CONFIG_FILE=local alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> a8483365f505, initial_empty
INFO  [alembic.runtime.migration] Running upgrade a8483365f505 -> 24104b6e1e0c, add_tables

Run

After start-up, you can access localhost:8000/docs to see the api documentation.

Using `fastapi dev`

The fastapi>=0.111.0 has a fastapi command.

(venv) $ APP_CONFIG_FILE=local fastapi dev
INFO:     Will watch for changes in these directories: ['/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/app']
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [49448] using WatchFiles
INFO:     Started server process [49450]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO     Using path app/main.py
INFO     Resolved absolute path /Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy/app/main.py
INFO     Searching for package file structure from directories with __init__.py files
INFO     Importing from /Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy

 ╭─ Python package file structure ─╮
 │                                 │
 │  📁 app                         │
 │  ├── 🐍 __init__.py             │
 │  └── 🐍 main.py                 │
 │                                 │
 ╰─────────────────────────────────╯

INFO     Importing module app.main
INFO     Found importable FastAPI app

 ╭── Importable FastAPI app ──╮
 │                            │
 │  from app.main import app  │
 │                            │
 ╰────────────────────────────╯

INFO     Using import string app.main:app

 ╭────────── FastAPI CLI - Development mode ───────────╮
 │                                                     │
 │  Serving at: http://127.0.0.1:8000                  │
 │                                                     │
 │  API docs: http://127.0.0.1:8000/docs               │
 │                                                     │
 │  Running in development mode, for production use:   │
 │                                                     │
 │  fastapi run                                        │
 │                                                     │
 ╰─────────────────────────────────────────────────────╯

INFO:     Will watch for changes in these directories: ['/Users/rhoboro/go/src/github.com/rhoboro/async-fastapi-sqlalchemy']
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [47967] using WatchFiles
INFO:     Started server process [47969]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

Using uvicorn's multiprocess manager

The uvicorn>=0.30.0 has a new multiprocess manager.

(venv) $ APP_CONFIG_FILE=local uvicorn --workers 4 app.main:app
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started parent process [46740]
INFO:     Started server process [46744]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     Started server process [46742]
INFO:     Waiting for application startup.
INFO:     Started server process [46745]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     Application startup complete.
INFO:     Started server process [46743]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

Test

(venv) $ pip install -r requirements_test.txt
(venv) $ black app
(venv) $ ruff app
(venv) $ mypy app
(venv) $ pytest app

async-fastapi-sqlalchemy's People

Contributors

Stargazers

Watchers

async-fastapi-sqlalchemy's Issues

Fails when

Have you ever tried to create a Notebook with include_notes set to False?

new = await cls.read_by_id(session, notebook.id, include_notes=False)

When I try it I get an ugly:

...
<clipped long stack_trace>
...
 File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2128, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 479, in execute
    self._adapt_connection.await_(
  File "/mnt/projects/testProj/repos/webapp/backend/.venv/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 59, in await_only
    raise exc.MissingGreenlet(
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)

that I do not know where it comes from.

Thanks for the great ideas in your repo anyways... :)

Session not committed in tests

It is not an issue, but rather an important notice, that if you have a middleware, that accesses database for any purpose, and does it not through dependencies injection (as it is done in views), then overriding dependencies in app.dependency obviously will not affect middleware and it will not see any data in database, since it is never committed throughout the tests. If you need data to be actually committed and not just flushed, to be accessible in different sessions, then you would need something like this for session generation:

@pytest.fixture(scope='session')
async def session_generator() -> AsyncGenerator:
    async_engine = create_async_engine(
        settings.TEST_DB_URL,
        pool_pre_ping=True,
        echo=settings.ECHO_SQL
    )

    AsyncSessionLocal = async_sessionmaker(
            autocommit=False,
            autoflush=False,
            bind=async_engine,
            future=True,
        )
    
    def test_get_session() -> Generator:
        try:
            yield AsyncSessionLocal
        except SQLAlchemyError:
            pass

    app.dependency_overrides[get_session] = test_get_session

    yield AsyncSessionLocal

And test case would start like this

@pytest.mark.anyio
async def test_create_account( session_generator: AsyncSession):

    async with session_generator.begin() as session:
        await Account.get_or_create()....

Again this is not an issue, but rather a different approach, especially if you rely on some auth middleware. Also it is possible potentially to replace such middleware with Global dependency, but for redirections to auth app you will need to do some dirty stuff with exceptions, etc...

alembic is not using APP_CONFIG_FILE

It seems that alembic is not using APP_CONFIG_FILE variable but just using what is in alembic.ini. Since APP_CONFIG_FILE is set when calling alembic in documentation I am not sure if this was intended?

Long teardown of test database.

Hi! Firstly, thank you very much for the example, I've been struggling to find a proper implementation of tests in async FastAPI+SQLAlchemy App for a few days, but now I have it.

There is one thing I wanted to discuss, it is about those 5 extra seconds on teardown of test database. Since you drop /test DB at the beginning of each test rerun, if we remove drop after yield, it will make it a bit faster. Not sure if in a huge application with lots of tests it will make any difference, but just in case, is there any reason why you keep it?

 conn = engine.connect()
     # トランザクションを一度終了させる
     conn.execute(text("commit"))
     try:
         conn.execute(text("drop database test"))
     except SQLAlchemyError:
         pass
     conn.close()

Best regards,

Andrii

Test setup flow - why such complex db initialization?

This is more of a question/discussion - I'm trying to understand if there's a reason for the rather complex "session" fixture.
If you have an actual PG database already setup, all tables were recreated by setup_test_db (that said, this is happening per session and not per test), why do you need a special flow to create the session and override the fast api dependency?

Some observations I made -

The engine is returned before the transaction is completed in setup_test_db - is that how you are making sure the db remains clean?
That said, you begin a new transaction in session fixture. At the end, you rollback the connection (but assuming some "commits" might have happened).
end_savepoint - what is the point of it? if I understand correctly, it will automatically re-start a nested transaction when one is over in a sync session. But why?

Appreciate your clarifications!