FastAPI File Storage and Multi-Binds#

This guide shows how to attach uploaded files to SQLAlchemy models in FastAPI and how to connect a single FastAPI app to more than one database.

Storing Uploaded Files#

Register a local folder or cloud bucket (S3, GCS, Azure) in storages and add a StoredObject column to your model. In FastAPI routes that receive an UploadFile, pass FileObject(backend=..., filename=file.filename, content=await file.read()) to service.create(...). Pending files are written when the transaction commits and deleted when the row is removed:

from pathlib import Path
import tempfile
from typing import Optional
from uuid import UUID
from pydantic import BaseModel, Field
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.extensions.fastapi import base, repository, service
from advanced_alchemy.types import FileObject, StoredObject, storages

storage_tmp = tempfile.mkdtemp()
storages.register_backend(f"file://{Path(storage_tmp)}", key="fastapi_documents")


class DocumentModel(base.UUIDBase):
    __tablename__ = "fastapi_document"

    name: Mapped[str]
    file: Mapped[Optional[FileObject]] = mapped_column(
        StoredObject(backend="fastapi_documents"),
        nullable=True,
    )


class DocumentRepository(repository.SQLAlchemyAsyncRepository[DocumentModel]):
    model_type = DocumentModel


class DocumentService(service.SQLAlchemyAsyncRepositoryService[DocumentModel, DocumentRepository]):
    repository_type = DocumentRepository


class DocumentResponse(BaseModel):
    model_config = {"from_attributes": True}

    id: Optional[UUID] = None
    name: str
    file: Optional[FileObject] = Field(default=None, exclude=True)


storages.clear_backends()
from pathlib import Path
import tempfile
from uuid import UUID
from pydantic import BaseModel, Field
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.extensions.fastapi import base, repository, service
from advanced_alchemy.types import FileObject, StoredObject, storages

storage_tmp = tempfile.mkdtemp()
storages.register_backend(f"file://{Path(storage_tmp)}", key="fastapi_documents")


class DocumentModel(base.UUIDBase):
    __tablename__ = "fastapi_document"

    name: Mapped[str]
    file: Mapped[FileObject | None] = mapped_column(
        StoredObject(backend="fastapi_documents"),
        nullable=True,
    )


class DocumentRepository(repository.SQLAlchemyAsyncRepository[DocumentModel]):
    model_type = DocumentModel


class DocumentService(service.SQLAlchemyAsyncRepositoryService[DocumentModel, DocumentRepository]):
    repository_type = DocumentRepository


class DocumentResponse(BaseModel):
    model_config = {"from_attributes": True}

    id: UUID | None = None
    name: str
    file: FileObject | None = Field(default=None, exclude=True)


storages.clear_backends()

Connecting Multiple Databases#

Pass a list of configs with distinct bind_key names when your FastAPI app connects to multiple databases (for example, an async primary database and a sync reporting database):

from fastapi import FastAPI
from advanced_alchemy.extensions.fastapi import (
    AdvancedAlchemy,
    SQLAlchemyAsyncConfig,
    SQLAlchemySyncConfig,
)

app = FastAPI()
alchemy = AdvancedAlchemy(
    config=[
        SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///:memory:"),
        SQLAlchemySyncConfig(
            connection_string="sqlite+pysqlite:///:memory:",
            bind_key="reporting",
        ),
    ],
    app=app,
)
assert alchemy.get_async_config().bind_key == "default"
assert alchemy.get_sync_config("reporting").bind_key == "reporting"