FastAPI SQLAlchemy CRUD API

A modern, production-ready REST API built with FastAPI and SQLAlchemy for managing user records with full CRUD operations.

Features

✨ Core Capabilities

• Full CRUD operations (Create, Read, Update, Delete) for users
• SQLite database with SQLAlchemy ORM
• Pydantic models for request/response validation
• Automatic API documentation (Swagger UI & ReDoc)
• Email uniqueness validation
• Proper HTTP status codes and error handling
• Dependency injection for database sessions

Tech Stack

• FastAPI - Modern Python web framework
• SQLAlchemy - SQL toolkit and ORM
• Pydantic - Data validation and serialization
• SQLite - Lightweight database

Project Structure

.
├── myapi.py           # Main application file
├── users.db          # SQLite database (auto-generated)
└── README.md         # Documentation

Installation

Prerequisites

• Python 3.8+
• pip

Setup

1. Clone the repository

git clone https://github.com/RaghPIP/FastAPI_SQLAlchemy_Crud.git
cd FastAPI_SQLAlchemy_Crud

2. Create virtual environment

python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install dependencies

pip install fastapi sqlalchemy uvicorn pydantic

Running the Application

Start the development server:

uvicorn myapi:app --reload

The API will be available at http://localhost:8000

API Documentation

• Swagger UI: http://localhost:8000/docs
• ReDoc: http://localhost:8000/redoc

API Endpoints

Root Endpoint

GET /

Returns a welcome message.

User Operations

Get All Users

GET /users/

Retrieves all users from the database.

Get User by ID

GET /users/{user_id}

Retrieves a specific user by ID.

Create User

POST /users/

Creates a new user.

Request Body:

{
  "name": "John Doe",
  "email": "john@example.com",
  "role": "admin"
}

Update User

PUT /users/{user_id}

Updates an existing user's information.

Request Body:

{
  "name": "Jane Doe",
  "email": "jane@example.com",
  "role": "user"
}

Delete User

DELETE /users/{user_id}

Deletes a user from the database.

Data Models

User Model

Database model with the following fields:

• id (Integer) - Primary key, auto-indexed
• name (String, 100) - Required, user's name
• email (String, 100) - Required, unique constraint
• role (String, 100) - Required, user's role

Request/Response Schema

{
  "id": 1,
  "name": "John Doe",
  "email": "john@example.com",
  "role": "admin"
}

Error Handling

The API returns appropriate HTTP status codes:

• 200 OK - Successful request
• 201 Created - Resource created successfully
• 400 Bad Request - Invalid input or duplicate email
• 404 Not Found - Resource not found
• 500 Internal Server Error - Server error

Example Usage

Using cURL

Create a user:

curl -X POST "http://localhost:8000/users/" \
  -H "Content-Type: application/json" \
  -d '{"name":"John Doe","email":"john@example.com","role":"admin"}'

Get all users:

curl -X GET "http://localhost:8000/users/"

Get user by ID:

curl -X GET "http://localhost:8000/users/1"

Update user:

curl -X PUT "http://localhost:8000/users/1" \
  -H "Content-Type: application/json" \
  -d '{"name":"Jane Doe","email":"jane@example.com","role":"user"}'

Delete user:

curl -X DELETE "http://localhost:8000/users/1"

Using Python Requests

import requests

BASE_URL = "http://localhost:8000"

# Create user
response = requests.post(f"{BASE_URL}/users/", json={
    "name": "John Doe",
    "email": "john@example.com",
    "role": "admin"
})
print(response.json())

# Get all users
response = requests.get(f"{BASE_URL}/users/")
print(response.json())

Database

The application uses SQLite for data persistence. The database file (users.db) is automatically created in the project root on first run.

Database Operations

• Tables are automatically created on application startup
• SQLite is configured to allow multi-threaded access via check_same_thread=False

Development

Running with auto-reload

uvicorn myapi:app --reload --host 0.0.0.0 --port 8000

Running in production

uvicorn myapi:app --host 0.0.0.0 --port 8000

Best Practices Implemented

✅ Proper dependency injection for database sessions
✅ Email uniqueness validation
✅ Comprehensive error handling
✅ Pydantic models for data validation
✅ RESTful API design
✅ Automatic API documentation

Future Enhancements

• Authentication & Authorization (JWT tokens)
• Password hashing and security
• Request pagination
• Filtering and sorting options
• Rate limiting
• Database migrations with Alembic
• Unit and integration tests
• Docker containerization
• CI/CD pipeline

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is open source and available under the MIT License.

Support

For support, please open an issue on the GitHub repository.

---

Made with ❤️ by Raghu