Flask API with Flask-SQLAlchemy and marshmallow-sqlalchemy, validation errors as 422: working versions (tested 2026-10-15)

flask==3.1.3
flask-sqlalchemy==3.1.1
marshmallow==3.26.1
marshmallow-sqlalchemy==1.5.0
sqlalchemy==2.0.36
pytest==9.1.1

from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
from marshmallow import ValidationError
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema
from sqlalchemy import String
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Mapped, mapped_column

db = SQLAlchemy()


class Book(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120))
    isbn: Mapped[str] = mapped_column(String(13), unique=True)


class BookSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = Book
        load_instance = True
        sqla_session = db.session
        dump_only = ("id",)


def create_app(uri: str = "sqlite:///:memory:") -> Flask:
    app = Flask(__name__)
    app.config.update(SQLALCHEMY_DATABASE_URI=uri)
    db.init_app(app)

    @app.post("/books")
    def create_book():
        try:
            book = BookSchema().load(request.get_json(silent=True) or {})
        except ValidationError as err:
            return {"errors": err.messages}, 422
        db.session.add(book)
        try:
            db.session.commit()
        except IntegrityError:
            db.session.rollback()
            return {"errors": {"isbn": ["already exists"]}}, 409
        return BookSchema().dump(book), 201

    return app

import pytest
from app import create_app, db


@pytest.fixture
def client():
    app = create_app()
    app.config["TESTING"] = True
    with app.app_context():
        db.create_all()
        yield app.test_client()
        db.session.remove()
        db.drop_all()


def test_create_returns_201_and_dumps_id(client):
    r = client.post("/books", json={"title": "Dune", "isbn": "9780441013593"})
    assert r.status_code == 201
    body = r.get_json()
    assert body["id"] == 1
    assert body["title"] == "Dune"


def test_missing_required_field_is_422(client):
    r = client.post("/books", json={"isbn": "9780441013593"})
    assert r.status_code == 422
    assert "title" in r.get_json()["errors"]


def test_title_length_comes_from_column_type_422(client):
    # marshmallow-sqlalchemy derives a Length validator from String(120)
    r = client.post("/books", json={"title": "x" * 121, "isbn": "9780441013593"})
    assert r.status_code == 422
    assert "title" in r.get_json()["errors"]


def test_unknown_field_is_422(client):
    # marshmallow 3 defaults to unknown=RAISE, so extra keys fail validation
    r = client.post("/books", json={"title": "Dune", "isbn": "9780441013593", "pages": 412})
    assert r.status_code == 422
    assert "pages" in r.get_json()["errors"]


def test_duplicate_isbn_is_409_and_session_recovers(client):
    client.post("/books", json={"title": "Dune", "isbn": "9780441013593"})
    r = client.post("/books", json={"title": "Dune again", "isbn": "9780441013593"})
    assert r.status_code == 409
    # the session was rolled back, so a fresh insert still works
    r = client.post("/books", json={"title": "Emma", "isbn": "9780141439587"})
    assert r.status_code == 201

[pytest]
pythonpath = .