SQL & Flask REST API Project

A web backend project built with Flask, SQLAlchemy, and PostgreSQL that manages university-like data: students, groups, and courses.
It includes REST API endpoints, Swagger documentation, Alembic migrations, and test data generation.

---

Features

• CRUD operations for Students, Groups, Courses
• Assigning/removing students from courses
• Filtering groups by student count
• Auto-generated Swagger UI documentation
• Alembic migrations support
• Logging per service and endpoint
• Modular project structure

---

Getting started

Clone the Repository

cd existing_repo
git remote add origin https://github.com/Aleksandr-ln/edu-crm-sql-api.git
git branch -M main
git push -uf origin main

Create & Activate Virtual Environment

python -m venv venv
source venv/bin/activate  # For macOS/Linux
source venv/Scripts/activate	# For Git Bash
venv\Scripts\activate   # For Windows

Install Dependencies

pip install -r requirements.txt

Configure environment variables

Create a .env file in the project root:

FLASK_ENV=production
FLASK_CONFIG=prod
SECRET_KEY=`STRONG_SECRET_KEY`
DATABASE_URL=postgresql://app_user:strong_pass@localhost:5432/app_db
DATABASE_URL_PROD=postgresql://app_user:strong_pass@localhost:5432/app_db

---

SECRET_KEY (for Flask sessions)

The SECRET_KEY is used by Flask for cryptographic signing — e.g., sessions, cookies, CSRF.

In development, it's okay to use a static value.
In production, always generate a strong key, e.g.:

python -c "import secrets; print(secrets.token_urlsafe(32))"

Then copy it into your .env file.

---

Flask configuration

The project uses prod configuration by default for production-readiness.

In .env:

FLASK_ENV=production
FLASK_CONFIG=prod

You may temporarily switch to:

FLASK_ENV=development
FLASK_CONFIG=dev

for local debugging or Swagger editing.

Make sure config.py handles both modes correctly.

---

PostgreSQL Setup

Login to PostgreSQL and create the database:

psql -U postgres

DROP DATABASE IF EXISTS app_db;
DROP USER IF EXISTS app_user;

CREATE DATABASE app_db;
CREATE USER app_user WITH PASSWORD 'strong_pass';
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;
psql -U postgres -d app_db
GRANT USAGE, CREATE ON SCHEMA public TO app_user;

Run Alembic Migrations

alembic upgrade head

(Optional) Generate Sample Data

PYTHONPATH=. python app/data/data_generator.py

Run the Application

python run.py

Access the Swagger UI at:

http://localhost:5000/apidocs/

Project Structure

app/
├── api/             # REST API blueprints and route registration
├── course/          # Course: model, schema, API, service
├── data/            # Data generator script for test DB population
├── db/              # Database session and models
│   ├── models/      # SQLAlchemy models and association table
├── docs/            # Swagger YAML documentation
├── group/           # Group: model, schema, API, service
├── student/         # Student: model, schema, API, service
├── utils/           # Logger and Swagger helpers
│   └── logger.py    # Logger initialization and configuration
├── config.py        # Application configuration (env-based)
migrations/          # Alembic migration versions
scripts/             # Database initialization scripts
tests/               # Unit and integration tests

Logging

All modules log to logs/
Logs are named by module: student_service.log, group_api.log, etc.

Testing

coverage run -m pytest tests
coverage report -m

Author

Author: Oleksandr Onupko
License: MIT