Introduction

SQLAlchemy API is a library that helps to turn the SQLAlchemy models into a REST API. It uses the power of Pydantic 2, to validate and serialize the data. This is a framework-agnostic library that can be used with any web framework. Currently, it provides support for Starlette and FastAPI.

Documentation: https://nacosdev.github.io/sqlalchemy_api

Source Code: https://github.com/nacosdev/sqlalchemy_api

Table of Contents

Features
Requirements
Installation
Example
License

Features

Mount CRUD endpoints for a SQLAlchemy model.
Automatic serialization and validation of the data using Pydantic.
Automatic pagination of the data.
Allow querying the data using different operators depending on the column data type.
Support Starlette
Support Support FastAPI
Support Blacksheep 🚧
Support custom queries. 🚧
Autentication. 🚧

Requirements

Python>=3.7
SQLAlchemy>=1.4
Pydantic>=2

Installation

pip install sqlalchemy-api

Example

Create it

Create a file main.py with SQLAlchemy models and mount the crud using one of the adapters, in this example we will use the FastAPI adapter:

from sqlalchemy_api.adapters.fastapi_crud import APICrud
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.orm import relationship, mapped_column, Mapped
from sqlalchemy.ext.declarative import declarative_base
from fastapi import FastAPI
from typing import List

Base = declarative_base()

engine = create_engine(
    "sqlite:///example.db",
    connect_args={"check_same_thread": False},
)

class User(Base):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(default="John Doe")
    age: Mapped[int] = mapped_column(nullable=False)
    posts: Mapped[List['Post']] = relationship(back_populates="user")

class Post(Base):
    __tablename__ = "post"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column()
    content: Mapped[str] = mapped_column()
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"), nullable=False)
    user: Mapped['User'] = relationship(back_populates="posts")

Base.metadata.create_all(engine)  # Create tables

user_crud_router = APICrud(User, engine)
post_crud_router = APICrud(Post, engine)

app = FastAPI()
app.include_router(user_crud_router, prefix="/user", tags=["User"])
app.include_router(post_crud_router, prefix="/post", tags=["Post"])

You will also need an ASGI server and FastAPI to be able to run this app, both are optional dependencies of SQLAlchemy API:

pip install sqlalchemy-api[fastapi]

Run it

uvicorn main:app --reload

Use it

Endpoints are automatically generated for the defined models and the FastAPI adapter provides automatic Swagger documentation, you can access localhost:8000/docs to interact with them:

SQLAlchemyAPI also provides different operators depending on the column data type, to filter the data:

The data returned is automatically paginated and serialized, including the relationships defined in the models:

Post data is automatically validated and serialized using Pydantic, for example, if you try to create a user wihout the required age field, you will get an error like this:

License

sqlalchemy-api is distributed under the terms of the MIT license.