i am using sqlalchemy[asyncio], sqlalchemy, fastapi, alembic, asyncmy. my database is mysql and have the following project structure:
core/
├─ database.py
├─ settings.py
alemibc/
├─ versions/
├─ env.py
├─ script.py.mako
models/
├─ users/
│ ├─ user_model.py
├─ notes/
│ ├─ note_model.py
alembic.ini
this is my database.py:
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker, declarative_base
from .settings import settings
# Create the async engine
engine = create_async_engine(
settings.DATABASE_CONNECTION_URL,
pool_size=50, # Start with 50, adjust based on testing
max_overflow=100, # Allow up to 100 additional connections
pool_timeout=30, # 30 seconds timeout for waiting for a connection
pool_recycle=1800, # Recycle connections every 30 minutes
echo=True # Set to False in production
)
# Create an async session factory
AsyncSessionLocal = sessionmaker(
bind=engine,
class_=AsyncSession,
autocommit=False,
autoflush=False,
)
# Base class for our models
Base = declarative_base()
metadata = Base.metadata
# Dependency to get DB session in FastAPI
async def get_db():
async with AsyncSessionLocal() as session:
yield session
my user model:
from sqlalchemy import Column, Integer, String
from core.database import Base
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
username = Column(String(30), unique=False, nullable=False)
email = Column(String(50), unique=True, index=True, nullable=False)
password = Column(String(200), nullable=False)
note model:
from sqlalchemy import Column, Integer, String, ForeignKey
from core.database import Base
class Note(Base):
__tablename__ = 'notes'
id = Column(Integer, primary_key=True)
title = Column(String(100), unique=False, nullable=False)
content = Column(String(30000), unique=True, index=True, nullable=False)
author_id = Column(Integer, ForeignKey('users.id', ondelete="CASCADE"), nullable=False)
env.py:
from logging.config import fileConfig
from sqlalchemy.ext.asyncio import AsyncEngine
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
from core.database import Base
from models.notes.note_model import Note
from models.users.user_model import User
config = context.config
if config.config_file_name is not None:
fileConfig(config.config_file_name)
target_metadata = Base.metadata
def run_migrations_offline() -> None:
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
)
with context.begin_transaction():
context.run_migrations()
async def run_migrations_online() -> None:
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = AsyncEngine(
engine_from_config(
config.get_section(config.config_ini_section, {}),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
)
with await connectable.connect() as connection:
context.configure(
connection=connection, target_metadata=target_metadata
)
with await context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
when i run the migrations i get empty upgrade and downgrade functions
"""Initial migration
Revision ID: ae6c8d3aeccc
Revises:
Create Date: 2024-07-13 00:35:37.358247
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision: str = 'ae6c8d3aeccc'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
pass
def downgrade() -> None:
pass
i have also problems with the async and await in the run_migrations_online() function when i run it as synchronous by default it gives error so i figured a solution out to put await after the ‘with’ keyword.
default run_migrations_online() func:
def run_migrations_online() -> None:
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section, {}),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(
connection=connection, target_metadata=target_metadata
)
with context.begin_transaction():
context.run_migrations()
error thrown if i run the above default function code:
(notes-app-env) PS F:Codingtest-python-backendsfastapi-blog> alembic revision –autogenerate -m “Initial migration4”
Traceback (most recent call last):
File “”, line 198, in _run_module_as_main
File “”, line 88, in run_code
File “F:Codingtest-python-backendsfastapi-blognotes-app-envScriptsalembic.exe_main.py”, line 7, in
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagesalembicconfig.py”, line 636, in main
CommandLine(prog=prog).main(argv=argv)
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagesalembicconfig.py”, line 626, in main
self.run_cmd(cfg, options)
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagesalembicconfig.py”, line 603, in run_cmd
fn(
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagesalembiccommand.py”, line 236, in revision
script_directory.run_env()
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagesalembicscriptbase.py”, line 582, in run_env
util.load_python_file(self.dir, “env.py”)
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagesalembicutilpyfiles.py”, line 95, in load_python_file
module = load_module_py(module_id, path)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagesalembicutilpyfiles.py”, line 113, in load_module_py
spec.loader.exec_module(module) # type: ignore
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “”, line 995, in exec_module
File “”, line 488, in _call_with_frames_removed
File “F:Codingtest-python-backendsfastapi-blogalembicenv.py”, line 81, in
run_migrations_online()
File “F:Codingtest-python-backendsfastapi-blogalembicenv.py”, line 69, in run_migrations_online
with connectable.connect() as connection:
^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemyenginebase.py”, line 3276, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemyenginebase.py”, line 146, in init
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemyenginebase.py”, line 3300, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemypoolbase.py”, line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemypoolbase.py”, line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemypoolbase.py”, line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemypoolimpl.py”, line 308, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemypoolbase.py”, line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemypoolbase.py”, line 674, in init
self.__connect()
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemypoolbase.py”, line 900, in __connect
with util.safe_reraise():
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemyutillanghelpers.py”, line 146, in exit
raise exc_value.with_traceback(exc_tb)
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemypoolbase.py”, line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemyenginecreate.py”, line 643, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemyenginedefault.py”, line 620, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemydialectsmysqlasyncmy.py”, line 284, in connect
await_only(creator_fn(*arg, **kw)),
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “F:Codingtest-python-backendsfastapi-blognotes-app-envLibsite-packagessqlalchemyutil_concurrency_py3k.py”, line 123, 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/20/xd2s)
i red almost every stackoverflow and github question but can’t figure it out.
coding_billi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.