nimaxin

Posted on Oct 12 AI-assisted

FastAPI Admin Panel with SQLAlchemy

#python #fastapi #sqlalchemy #tutorial

Here's how to add an admin panel to your FastAPI app, with search, filters, a sign-in page and an audit log for your SQLAlchemy models.

The admin panel is adminsite, an open source library I wrote. It's not a side project: our back office runs on it every day.

There's also a live demo. Sign in as admin with the password admin.

1. The app

You need Python 3.11 or newer, and a FastAPI app with SQLAlchemy models.

Already have one? Run pip install adminsite, skip to step 2, and use your own models where the steps say Customer and Order.

Otherwise, install these:

pip install fastapi uvicorn aiosqlite adminsite

The steps use two models, customers and their orders:

class Customer(Base):
    __tablename__ = "customers"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120))
    email: Mapped[str] = mapped_column(String(255), unique=True)


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    status: Mapped[OrderStatus] = mapped_column(default=OrderStatus.PENDING)
    total: Mapped[Decimal] = mapped_column(Numeric(10, 2))
    created_at: Mapped[datetime] = mapped_column(default=lambda: datetime.now(UTC))

    customer: Mapped[Customer] = relationship()

Save the whole file as main.py. Besides the models, it has the engine, and it adds a few rows of sample data on the first start:

main.py

import enum
from collections.abc import AsyncIterator
from contextlib import asynccontextmanager
from datetime import UTC, datetime
from decimal import Decimal

from fastapi import FastAPI
from sqlalchemy import ForeignKey, Numeric, String
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class OrderStatus(enum.StrEnum):
    PENDING = "pending"
    PAID = "paid"
    SHIPPED = "shipped"


class Customer(Base):
    __tablename__ = "customers"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120))
    email: Mapped[str] = mapped_column(String(255), unique=True)


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    status: Mapped[OrderStatus] = mapped_column(default=OrderStatus.PENDING)
    total: Mapped[Decimal] = mapped_column(Numeric(10, 2))
    created_at: Mapped[datetime] = mapped_column(default=lambda: datetime.now(UTC))

    customer: Mapped[Customer] = relationship()


engine = create_async_engine("sqlite+aiosqlite:///shop.db")


@asynccontextmanager
async def lifespan(app: FastAPI) -> AsyncIterator[None]:
    # Create the tables, and a few orders the first time.
    async with engine.begin() as connection:
        await connection.run_sync(Base.metadata.create_all)
    async with AsyncSession(engine) as session:
        if await session.get(Customer, 1) is None:
            lena = Customer(name="Lena Fischer", email="lena@example.com")
            omar = Customer(name="Omar Haddad", email="omar@example.com")
            for customer, total, status in [
                (lena, Decimal("120.00"), OrderStatus.PAID),
                (omar, Decimal("35.50"), OrderStatus.PENDING),
                (lena, Decimal("12.90"), OrderStatus.SHIPPED),
                (omar, Decimal("78.00"), OrderStatus.PAID),
            ]:
                session.add(Order(customer=customer, total=total, status=status))
            await session.commit()
    yield


app = FastAPI(lifespan=lifespan)

Start it:

uvicorn main:app --reload

The app starts, and shop.db now holds two customers and four orders. But there's no way to see them yet.

2. Mount the admin

Add this import:

from adminsite import Admin, ModelView

And these two lines at the end of the file:

admin = Admin(engine, title="Shop", views=[ModelView[Customer], ModelView[Order]])
app.mount("/admin", admin)

ModelView[Order] is a view of the Order model with no settings. Admin is an ASGI app, so you mount it like any other.

Save the file, and open http://127.0.0.1:8000/admin/orders:

Every order is there, and each one has a page of its own with a form to change it. You can also add orders and delete them.

But the Customer column says "Customer #1". The admin doesn't know what to call a customer yet.

3. Name the customers

Give customers a view of their own, with a record_title:

class CustomerView(ModelView[Customer]):
    record_title = "{name}"

And use it in place of the plain one:

admin = Admin(engine, title="Shop", views=[CustomerView, ModelView[Order]])

The names in braces are the model's columns. Reload the page, and the column says Lena Fischer and Omar Haddad. The same name shows wherever a customer appears.

That's fine for four orders. With a few thousand, you'll want to find Lena's orders without scrolling.

4. Add search and filters

Now the orders get a view too. Add Link to the import:

from adminsite import Admin, Link, ModelView

Then add this class next to CustomerView, and use it in the admin:

class OrderView(ModelView[Order]):
    searchable_fields = [Order.id, Link(Order.customer, Customer.name)]
    list_filters = [Order.status, Order.created_at]

admin = Admin(engine, title="Shop", views=[CustomerView, OrderView])

Link(Order.customer, Customer.name) goes from an order to its customer's name, so the search box finds orders by who placed them. Type lena, and only orders #1 and #3 are left.

Filters opens the drawer from the screenshot at the top. It lists each status with how many orders have it, and ranges for the date.

The settings name your models' attributes. So a typo like Order.stauts is an error as soon as Python reads the file, and your editor underlines it before that.

Now anyone who can reach /admin can change your data. That's fine on your laptop, but not on a server.

5. Add a sign-in page

PasswordAuth takes a username and a password hash. Make the hash, and a random key for the session cookie:

python -c "from adminsite.auth import hash_password; print(hash_password('choose a password'))"
python -c "import secrets; print(secrets.token_urlsafe(32))"

Stop uvicorn with Ctrl+C, and put both in environment variables in that terminal. Use single quotes, because the hash has $ signs in it:

export ADMIN_PASSWORD_HASH='pbkdf2_sha256$...'
export ADMIN_SECRET_KEY='...'

On Windows PowerShell, it's $env:ADMIN_PASSWORD_HASH = '...'.

Then add the imports, and pass both to the admin:

import os

from adminsite.auth import PasswordAuth

admin = Admin(
    engine,
    title="Shop",
    views=[CustomerView, OrderView],
    auth=PasswordAuth({"admin": os.environ["ADMIN_PASSWORD_HASH"]}),
    secret_key=os.environ["ADMIN_SECRET_KEY"],
)

Start uvicorn again. Now /admin sends you to a sign-in page first. Sign in as admin with the password you chose.

Once more people sign in, you'll want to know who changed an order, and when.

6. Add an audit log

Add one more argument:

    secret_key=os.environ["ADMIN_SECRET_KEY"],
    audit=True,
)

The admin now keeps an audit log, in a SQLite file of its own named adminsite_audit.db.

Open order #2, click Edit, set its status to Shipped and click Save changes. The order's page now has a History tab:

The sidebar has a new Activity page too, with every change made in the admin.

The whole file