Coding Code ReviewIntermediate30 minSaves 30 minutes

Build a FastAPI CRUD Service with Pydantic V2 and SQLModel

For Python engineers, generate a production-ready FastAPI application to manage a 'Task' resource, integrating Pydantic V2 for data validation, SQLModel for ORM, and dependency-injected database sessions for clean architecture.

Generate a complete FastAPI CRUD application for a Task resource. It uses Pydantic V2 for robust data schemas, SQLModel for database interaction, and incorporates dependency injection for session management, providing a production-minded Python solution with code, tests, and dependency setup.

ChatGPT Gemini Claude

READY-TO-USE PROMPT

Copy Prompt

prompt.txt

Role: Act as a senior Python backend engineer specializing in highly maintainable and performant HTTP services.

Context: I am developing a new FastAPI application that requires robust CRUD operations for a specific resource. This application needs to adhere to modern Python best practices, including explicit typing, Pydantic V2 for data validation and serialization, SQLModel for database interactions, and dependency injection for managing database sessions. The goal is a production-minded solution that includes not just the application code but also a clear module structure, dependency management setup, and unit tests.

Task: Generate a complete, self-contained Python solution for a FastAPI CRUD service for a `{{resource_name}}` resource. The solution should cover:

1.  **Project Structure**: A logical directory and file layout for a small FastAPI application.
2.  **Pydantic V2 Schemas**: Define `{{resource_name}}Base`, `{{resource_name}}Create`, `{{resource_name}}Update`, and `{{resource_name}}Read` models using Pydantic V2 for request and response validation.
3.  **SQLModel ORM**: Implement a SQLModel ORM model for `{{resource_name}}` that inherits from `SQLModel` and the Pydantic `{{resource_name}}Base`. Include a primary key `id` and appropriate fields for the resource.
4.  **Database Session Management**: Set up a `SessionLocal` and a `get_db` dependency-injected function to provide a database session for each request, ensuring proper session closing. Use `SQLModel.metadata.create_all` for initial table creation.
5.  **CRUD Endpoints**: Implement standard `POST /{{resource_name}}s`, `GET /{{resource_name}}s`, `GET /{{resource_name}}s/{id}`, `PUT /{{resource_name}}s/{id}`, and `DELETE /{{resource_name}}s/{id}` endpoints. Each endpoint must use the appropriate Pydantic V2 schemas for request bodies and response models.
6.  **Error Handling**: Basic error handling for common scenarios like resource not found.
7.  **Dependencies**: Provide a `pyproject.toml` file suitable for `poetry` to manage dependencies (FastAPI, Uvicorn, SQLModel, Pydantic, SQLite (or other for example)).
8.  **Unit Tests**: Include `pytest` unit tests for the CRUD endpoints, demonstrating how to test the API using an in-memory SQLite database.

Constraints:
*   All code must be idiomatic Python, fully typed, and adhere to PEP 8.
*   Use `Path` operations for API routes.
*   Ensure Pydantic V2 `model_validate` or `model_dump` methods are used where appropriate.
*   The database connection string should be configurable, defaulting to an SQLite in-memory database for testing and local development, but easily switchable to a file-based SQLite or PostgreSQL. Use `{{database_url}}` as a placeholder for the environment variable or configuration setting.
*   The output should be a complete, runnable set of files.

Output: Provide the solution as a series of distinct code blocks, each representing a file, clearly labeled with its filename and path. Start with the overall directory structure, then each file's content. Include `pyproject.toml` and `README.md` for setup instructions.

Estimated results

DifficultyIntermediate

Setup time30 min

Time saved30 minutes

Best modelsChatGPT, Gemini, Claude

Best audienceSoftware Development, Web Services

Editor's note

Why this prompt matters

Developing a reliable HTTP service often involves repetitive but critical CRUD operations. While FastAPI simplifies API creation, moving from a basic prototype to a production-ready system demands careful attention to architecture, data integrity, and testability. Python engineers frequently face the challenge of integrating various libraries while maintaining a cohesive, typed, and maintainable codebase. This is especially true when dealing with data models, database interactions, and API contracts.

This workflow addresses that need directly. It provides a structured approach to building a FastAPI service for a generic resource, focusing on modern Python best practices. By incorporating Pydantic V2, we ensure rigorous data validation and clear API schemas. SQLModel offers a typed and intuitive ORM for database interactions, simplifying data persistence. Crucially, the solution emphasizes dependency injection for managing database sessions, leading to a more modular and testable application.

The result is a self-contained, production-minded blueprint. It moves beyond just endpoint definitions, providing a complete project structure, dependency management, and unit tests using an in-memory database. For engineers aiming to quickly scaffold a new service or refactor an existing one with a strong foundation, this approach delivers a robust starting point that prioritizes type safety, clarity, and ease of maintenance.

Anatomy

Prompt engineering breakdown

Role

Act as a senior Python backend engineer specializing in highly maintainable and performant HTTP services.

Context

I am developing a new FastAPI application that requires robust CRUD operations for a specific resource. This application needs to adhere to modern Python best practices, including explicit typing, Pydantic V2 for data validation and serialization, SQLModel for database interactions, and dependency injection for managing database sessions. The goal is a production-minded solution that includes not just the application code but also a clear module structure, dependency management setup, and unit tests.

Goal

Generate a complete, self-contained Python solution for a FastAPI CRUD service for a `{{resource_name}}` resource, covering project structure, Pydantic V2 schemas, SQLModel ORM, database session management, CRUD endpoints, error handling, dependencies, and unit tests.

Constraints

All code must be idiomatic Python, fully typed, and adhere to PEP 8. Use `Path` operations for API routes. Ensure Pydantic V2 `model_validate` or `model_dump` methods are used. The database connection string should be configurable, defaulting to an SQLite in-memory database, but easily switchable. Use `{{database_url}}` as a placeholder. The output should be a complete, runnable set of files.

Output format

Provide the solution as a series of distinct code blocks, each representing a file, clearly labeled with its filename and path. Start with the overall directory structure, then each file's content. Include `pyproject.toml` and `README.md` for setup instructions.

Why this structure works

The explicit role priming sets the tone and expertise level for the model, ensuring the output aligns with senior engineering standards. Clear constraints on coding style and specific library usage (Pydantic V2 methods, SQLModel) guide the model to produce high-quality, idiomatic Python. Finally, the detailed structured output requirements ensure a comprehensive, runnable solution with all necessary files and setup instructions.

Pick your version

Prompt variations

BeginnerWorks with any model

For those new to FastAPI, Pydantic, or SQLModel who need a simplified, runnable example of basic CRUD operations.

prompt.txt

As a Python developer, I need a basic FastAPI application. Create a simple CRUD service for a `{{resource_name}}` resource. I need Pydantic models for data (create, read). Use a simple list or in-memory dictionary for storage, not a database. Include endpoints for creating, reading all, reading by ID, updating, and deleting. Provide the main Python file for the app and a `requirements.txt` file listing FastAPI and Uvicorn. Keep the code straightforward, focusing on core API logic.

ProfessionalBest with claude

When you need a detailed, production-ready blueprint for a FastAPI service incorporating modern Python practices, including full typing, ORM integration, and testing.

prompt.txt

As a senior Python backend engineer, generate a fully-typed FastAPI CRUD service for a `{{resource_name}}` entity. The solution must integrate Pydantic V2 for request/response schemas, SQLModel for ORM with a dependency-injected database session, and `pytest` for unit testing. Structure the project into logical modules, provide a `pyproject.toml` for Poetry-based dependency management (FastAPI, Uvicorn, SQLModel, SQLite), and include a `README.md` for setup. Emphasize robust error handling for common API scenarios and configurable database connections, defaulting to in-memory SQLite for testing.

Short VersionWorks with any model

When you need a quick, concise overview or a starting point for a FastAPI CRUD implementation without extensive detail.

prompt.txt

Provide a complete FastAPI CRUD service for a `{{resource_name}}` resource. Include Pydantic V2 schemas for validation, SQLModel for ORM with dependency injection for DB sessions, and all standard CRUD endpoints. The output should be a structured set of Python files, including `pyproject.toml` for dependencies and basic `pytest` unit tests, demonstrating a production-minded approach with typed code.

EnterpriseBest with chatgpt

For complex projects requiring not just functional code but also considerations for scalability, security, logging, and compliance in an enterprise environment.

prompt.txt

As a lead architect for a large-scale enterprise application, design a FastAPI CRUD microservice for a `{{resource_name}}` resource. Beyond the core functionality using Pydantic V2 and SQLModel with dependency injection, the solution must incorporate: structured logging (e.g., using `loguru`), basic authentication/authorization placeholders (e.g., using `Depends` for an `api_key` check), a configurable health check endpoint, and a basic strategy for handling data privacy requirements (e.g., soft deletion or data masking considerations). Include a `pyproject.toml`, comprehensive `pytest` cases, and a `README.md` that outlines deployment considerations for containerization and environment-specific configurations (e.g., `{{database_url}}`, `{{api_key_secret}}`). Emphasize code maintainability, security best practices, and auditability.

What you'll get

Expected output

A complete solution for a FastAPI CRUD service for a Task resource, including project structure, Pydantic V2 models, SQLModel ORM, database session management, CRUD endpoints, dependency management, and unit tests.

`` . ├── pyproject.toml └── src ├── __init__.py ├── main.py ├── database.py └── models.py └── tests └── test_main.py ``

pyproject.toml ```toml [tool.poetry.dependencies] python = "^3.10" fastapi = "^0.111.0" uvicorn = {extras = ["standard"], version = "^0.30.1"} sqlmodel = "^0.0.18" pydantic = "^2.7.4" pydantic-settings = "^2.3.3"

[tool.poetry.group.dev.dependencies] pytest = "^8.2.2" httpx = "^0.27.0" ```

src/models.py ```python from typing import Optional from sqlmodel import Field, SQLModel from pydantic import ConfigDict

class TaskBase(SQLModel): title: str = Field(index=True) description: Optional[str] = None completed: bool = False

class TaskCreate(TaskBase): pass

class TaskUpdate(SQLModel): title: Optional[str] = None description: Optional[str] = None completed: Optional[bool] = None

class TaskRead(TaskBase): id: int model_config = ConfigDict(from_attributes=True) # Pydantic V2 for ORM mapping

class Task(TaskBase, table=True): id: Optional[int] = Field(default=None, primary_key=True) ```

src/database.py ```python from sqlmodel import create_engine, Session, SQLModel from typing import Generator

DATABASE_URL = "sqlite:///./database.db" engine = create_engine(DATABASE_URL, echo=False)

def create_db_and_tables(): SQLModel.metadata.create_all(engine)

def get_db() -> Generator[Session, None, None]: with Session(engine) as session: yield session ```

src/main.py ```python from fastapi import FastAPI, Depends, HTTPException from sqlmodel import Session, select from typing import List from .database import get_db, create_db_and_tables from .models import Task, TaskCreate, TaskRead, TaskUpdate

app = FastAPI()

@app.on_event("startup") def on_startup(): create_db_and_tables()

@app.post("/tasks/", response_model=TaskRead) def create_task(*, session: Session = Depends(get_db), task: TaskCreate): db_task = Task.model_validate(task) # Pydantic V2 validation session.add(db_task) session.commit() session.refresh(db_task) return db_task

@app.get("/tasks/{task_id}", response_model=TaskRead) def read_task(*, session: Session = Depends(get_db), task_id: int): task = session.get(Task, task_id) if not task: raise HTTPException(status_code=404, detail="Task not found") return task

@app.get("/tasks/", response_model=List[TaskRead]) def read_tasks(*, session: Session = Depends(get_db), offset: int = 0, limit: int = 100): tasks = session.exec(select(Task).offset(offset).limit(limit)).all() return tasks

# Additional PUT and DELETE endpoints would follow a similar pattern. ```

tests/test_main.py ```python from fastapi.testclient import TestClient from sqlmodel import SQLModel, create_engine, Session from src.main import app, get_db from src.models import TaskCreate, TaskRead import pytest

TEST_DATABASE_URL = "sqlite:///:memory:" engine = create_engine(TEST_DATABASE_URL)

@pytest.fixture(name="session") def session_fixture(): SQLModel.metadata.create_all(engine) with Session(engine) as session: yield session SQLModel.metadata.drop_all(engine)

@pytest.fixture(name="client") def client_fixture(session: Session): def get_session_override(): return session app.dependency_overrides[get_db] = get_session_override client = TestClient(app) yield client app.dependency_overrides.clear()

def test_create_task(client: TestClient): response = client.post("/tasks/", json={"title": "Test Task", "description": "Test description"}) assert response.status_code == 200 task = TaskRead.model_validate(response.json()) assert task.title == "Test Task" assert task.id is not None

def test_read_task(client: TestClient): create_response = client.post("/tasks/", json={"title": "Read Task"}) created_task = create_response.json() task_id = created_task["id"]

response = client.get(f"/tasks/{task_id}") assert response.status_code == 200 task = TaskRead.model_validate(response.json()) assert task.title == "Read Task"

def test_read_task_not_found(client: TestClient): response = client.get("/tasks/999") assert response.status_code == 404 assert response.json() == {"detail": "Task not found"} ```

Under the hood

Why this prompt works

This prompt functions effectively due to its clear, structured approach. Role-playing as a "senior Python backend engineer" sets an expert persona for the model, influencing the quality and architectural decisions in the generated code. The "Context" section provides essential background, detailing the specific technical stack and modern best practices expected, such as Pydantic V2, SQLModel, and dependency injection. This pre-frames the solution.

The "Task" list is a critical component. By breaking down the request into eight distinct, numbered deliverables—from project structure to unit tests—it ensures comprehensive coverage and prevents omissions. Each point is specific, guiding the model to generate a complete, production-ready application rather than just code snippets. For example, explicitly requesting pyproject.toml and pytest unit tests elevates the output beyond basic functionality.

Mandating specific technologies like FastAPI, SQLModel, and Pydantic V2 removes ambiguity. The {{resource_name}} placeholder demonstrates effective parameterization, making the prompt reusable for different resources. Finally, the "Constraints" section, with directives like "idiomatic Python," "fully typed," and "PEP 8," refines the output quality, ensuring the generated code meets professional standards.

Model fit

Best AI models for this prompt

Claude

Claude models excel at generating structured, well-commented Python code, often producing solutions that are immediately runnable. Its ability to follow complex instructions across multiple files and maintain consistency in typing and architectural patterns makes it suitable for this task. It handles dependency injection patterns and Pydantic V2 schema generation effectively. See the full Claude hub for deeper guidance.

ChatGPT

ChatGPT models are proficient at generating Python application code and tests. They generally produce correct syntax and can integrate different libraries like FastAPI, Pydantic, and SQLModel. Users might need to guide it more explicitly on specific architectural choices or ensure full type hint consistency across all generated files, but it provides a strong baseline. See the full ChatGPT hub for deeper guidance.

Gemini

Gemini models are capable of generating complete Python solutions, including API endpoints, ORM models, and testing frameworks. They are good at understanding the interaction between different components (FastAPI, Pydantic, SQLModel) and producing functional code. Reviewing the generated code for optimal SQLModel query patterns and Pydantic V2 specific features might be necessary. See the full Gemini hub for deeper guidance.

When to use

Initiating a new microservice or API with well-defined data contracts.
Building applications where data validation, serialization, and explicit typing are critical.
Projects needing a straightforward ORM that integrates well with Pydantic for data models.
Teams already familiar with FastAPI's dependency injection and asynchronous patterns.
Rapidly prototyping HTTP services that require a relational database backend.
Developing internal tools where clear API specifications and maintainability are priorities.

When not to use

Very small, single-file scripts or command-line utilities without HTTP requirements.
Applications where an existing, different ORM (e.g., SQLAlchemy Core) is already deeply embedded.
Projects requiring a NoSQL database, as SQLModel is designed for relational databases.
Extremely low-latency systems where Python's GIL or asyncio overhead is a bottleneck.
Simple data transformations that do not warrant a full API service setup.

Get more from it

Pro tips

1
Use environment variables for the `DATABASE_URL` to manage database connections across environments. This prevents hardcoding credentials and simplifies deployment.
2
Develop and test against an in-memory SQLite database. This provides fast, isolated tests without side effects on persistent data stores.
3
Implement Alembic for database migrations early in the development cycle. This manages schema evolution effectively, preventing data loss during model changes.
4
Define `{{resource_name}}Update` schemas with `Optional` fields. This allows partial updates, avoiding overwriting unrelated data when only specific fields change.
5
Override the `get_db` dependency in tests to use a dedicated testing database session. This ensures test isolation and avoids interfering with actual data.
6
Consider adding `try...except` blocks for specific database errors, like unique constraint violations. This provides more granular feedback than generic 500 errors.

Don't ship this

Common mistakes

✗ Forgetting to close the database session after each request, leading to connection leaks and resource exhaustion.
Fix — Ensure the `get_db` dependency uses a `yield` statement within a `try...finally` block to guarantee `session.close()` is called.
✗ Not using Pydantic V2's `model_validate` or `model_dump` methods, resulting in less efficient data handling or manual attribute access.
Fix — Adopt `model_validate` for parsing incoming data and `model_dump` for serializing responses, aligning with Pydantic V2 best practices.
✗ Defining `{{resource_name}}Update` schemas without `Optional` fields, forcing clients to send all fields for an update operation.
Fix — Make fields in the `{{resource_name}}Update` schema `Optional` to allow partial updates, improving API flexibility.
✗ Mixing synchronous and asynchronous database operations, leading to runtime errors or blocking the event loop.
Fix — Consistently use `await` with all SQLModel database operations within async functions to maintain non-blocking behavior.
✗ Testing directly against a persistent development database, causing tests to be non-isolated and potentially corrupting data.
Fix — Configure pytest to use an in-memory SQLite database for tests, ensuring each test run starts with a clean slate.

Frequently asked questions

Q.Can this setup be used with a different relational database like PostgreSQL or MySQL?

Yes, absolutely. SQLModel supports various relational databases. To switch, change the DATABASE_URL environment variable to point to your PostgreSQL or MySQL connection string. You'll also need to install the corresponding database driver, like psycopg2-binary for PostgreSQL.

Q.How do I handle database migrations when my SQLModel schemas change?

Integrate Alembic into your project. It's the standard tool for database migrations in Python with SQLAlchemy (which SQLModel builds upon). Alembic can generate migration scripts based on changes detected between your SQLModel definitions and the current database schema.

Q.What's the best way to add authentication and authorization to these endpoints?

FastAPI integrates well with various authentication methods. You can use FastAPI's Security dependency for token-based authentication (e.g., OAuth2 with JWTs). Implement a dependency that validates the token and extracts user information, then inject it into your path operations.

Q.Will this architecture scale for a high-traffic application?

This architecture provides a solid foundation. FastAPI is highly performant. Scaling largely depends on your database choice (e.g., PostgreSQL for production), proper indexing, and potentially adding caching layers. SQLModel performance is generally good for typical CRUD operations.

Q.How can I add more complex relationships (one-to-many, many-to-many) between resources?

SQLModel supports defining relationships directly within your models using Relationship. For instance, a Team model could have a Relationship to Hero models. Ensure you define both ends of the relationship for proper ORM behavior and Pydantic schema generation.

Version 1.0Last reviewed July 20, 2026

Reviewed by PromptInFlow Editorial Team