akeemamusat511/subly repository overview

⁠Subly: A Subscription Management API

A RESTful API using Flask and SQLAlchemy for managing user subscriptions with a focus on SQL query optimization and performance.

⁠Features

• User registration and authentication using JWT
• Subscription plan management
• User subscription management (subscribe, upgrade, cancel)
• Optimized SQL queries for handling subscriptions at scale
• Performance testing and comparison of optimized vs standard queries

⁠Optimization Techniques

This API implements several SQL optimization techniques:

⁠1. Strategic Indexing

• Composite indexes for commonly filtered fields (e.g., idx_user_active on user_id and is_active)
• Indexes on frequently joined or filtered columns to improve lookup speed
• Date range indexes for subscription period queries

⁠2. Raw SQL Queries

• Custom SQL queries for performance-critical operations instead of relying solely on ORM
• Optimized JOIN operations to retrieve data in a single query
• Careful selection of columns to avoid unnecessary data transfer

⁠3. Pagination

• Efficient pagination using LIMIT and OFFSET with proper indexing
• Separate count queries to optimize page calculation

⁠4. Query Analysis

• Included tools for analyzing query performance and execution plans
• Performance comparison tests between standard ORM and optimized queries

⁠Setup Instructions

⁠Using Docker

1. Build the Docker image:

   docker pull akeemamusat511/subly:latest
   

   Copy
2. Run the container image

   docker run -p 5020:5000 --env FLASK_APP=subly --env FLASK_ENV=development --env JWT_ACCESS_TOKEN_EXPIRES=3600 --env JWT_SECRET_KEY=secret --env APP_ADMIN_PASSWORD=admin12345 --env SQLALCHEMY_DATABASE_URI=mysql+pymysql://username:password@db_host/db_name --env DEBUG=0 akeemamusat511/subly:latest
   

   Copy
3. The API will be available at http://localhost:5020