Flask SQLAlchemy API on Vercel with Auto-Migration

A comprehensive Flask API server with SQLAlchemy ORM, auto-migration system, and complete web interface. Optimized for Vercel serverless deployment with PostgreSQL database.

🚀 Key Features

✅ Auto-Migration System

Automatic database migrations on app startup
Flask-Migrate integration with comprehensive toolkit
Zero-downtime deployments with fallback mechanisms
Complete migration management (create, status, rollback, backup)

✅ Complete Web Interface

User authentication (register, login, logout)
Post management (create, read, update, delete)
Responsive design with Tailwind CSS
Interactive dashboard and user profiles

✅ Production Ready

Vercel serverless optimized deployment
PostgreSQL database with connection pooling
Session management with security features
Comprehensive error handling and logging

✅ Developer Friendly

SQLAlchemy ORM for clean database operations
Pagination support for large datasets
API documentation with health checks
Testing scripts for development and production

📋 Prerequisites

Vercel account for deployment
PostgreSQL database (Neon, Supabase, or other cloud provider)
Python 3.8+ for local development
Git for version control

🛠️ Quick Setup

1. Database Setup

Choose a cloud PostgreSQL provider:

Option A: Neon (Recommended)

```bash

1. Sign up at neon.tech

2. Create a new project

3. Copy the connection string

Format: postgresql://user:password@host:port/database

```

Option B: Supabase

```bash

1. Sign up at supabase.com

2. Create a new project

3. Go to Settings > Database

4. Copy the connection string

```

2. Local Development

```bash

Clone the repository

git clone cd flask-vercel-app

Install dependencies

pip install -r requirements.txt

Set environment variables

export DATABASE_URL="postgresql://user:password@host:port/database" export SECRET_KEY="your-secret-key"

Initialize auto-migration system (run once)

python scripts/init_migrations.py

Setup database with sample data

python scripts/setup_database.py

Run locally

python api/index.py ```

3. Deploy to Vercel

```bash

Install Vercel CLI

npm install -g vercel

Login to Vercel

vercel login

Set environment variables in Vercel

vercel env add DATABASE_URL vercel env add SECRET_KEY

Deploy

vercel --prod ```

🔄 Auto-Migration System

How It Works

Startup Check: App automatically checks for pending migrations on cold start
Auto-Upgrade: Runs migrations automatically within the serverless function
Fallback Safety: Creates tables if migrations fail
Zero Downtime: Compatible with Vercel serverless cold starts

Development Workflow

```bash

1. Modify models in api/index.py

2. Create migration locally

python scripts/create_migration.py "Add user avatar field"

3. Test migration locally

python scripts/migration_status.py python scripts/manual_upgrade.py

4. Commit and deploy to Vercel

git add . git commit -m "Add user avatar field migration" git push origin main

5. Deploy to Vercel (auto-migration runs on startup)

vercel --prod ```

Migration Toolkit (Local Development)

```bash

Check migration status (local)

python scripts/migration_status.py

Manual migration (local development)

python scripts/manual_upgrade.py

Rollback to previous version (local)

python scripts/rollback_migration.py prev

Backup database (works with any environment)

python scripts/backup_database.py backup

Restore from backup (works with any environment)

python scripts/backup_database.py restore backup_file.sql ```

Vercel Production Commands

```bash

Deploy with auto-migration

vercel --prod

Check deployment logs to see migration status

vercel logs

Force redeploy if migration failed

vercel --prod --force

Check function logs for migration errors

vercel logs --follow ```

Vercel Deployment & Migration

How Auto-Migration Works on Vercel

Cold Start: When Vercel starts your function, run_auto_migrations() executes
Migration Check: Function checks for pending migrations automatically
Execution: Migrations run within the serverless function context
Fallback: If migrations fail, app falls back to db.create_all()
Logging: Migration status appears in Vercel function logs

Vercel-Specific Commands

```bash

Deploy with migration

vercel --prod

Monitor deployment and migration

vercel logs --follow

Check if migrations directory is deployed

vercel ls

Environment variables for migration

vercel env add DATABASE_URL vercel env add SECRET_KEY

Force redeploy if migration stuck

vercel --prod --force ```

Migration Files in Vercel

Migration files must be committed to git
Vercel deploys the entire repository including migrations/ directory
Auto-migration runs during function cold start
No manual flask db upgrade needed in production

📡 API Endpoints

All endpoints are prefixed with /api/:

Authentication

POST /api/register - Register new user
POST /api/login - User login
POST /api/logout - User logout

User Management

GET /api/profile - Get user profile (protected)
PUT /api/profile - Update user profile (protected)
GET /api/users - Get all users (paginated)

Post Management

POST /api/posts - Create new post (protected)
GET /api/posts - Get all posts (paginated)
GET /api/posts/<id> - Get specific post
PUT /api/posts/<id> - Update post (protected)
DELETE /api/posts/<id> - Delete post (protected)

System

GET /api - API documentation
GET /api/health - Health check with migration status

🌐 Web Interface

Available Pages

/web - Homepage with features overview
/web/register - User registration
/web/login - User login (demo: username=demo, password=demo123)
/web/dashboard - User dashboard (protected)
/web/posts - Browse all posts
/web/posts/<id> - View specific post
/web/create-post - Create new post (protected)
/web/profile - User profile management (protected)

Features

Responsive Design: Works on desktop and mobile
Real-time Updates: Dynamic content loading
User Authentication: Session-based with auto-detection
Rich Editor: Post creation with preview
Interactive UI: Modern interface with Tailwind CSS

🧪 Testing

Local Testing

```bash

Test API endpoints

python test_api.py

Test with sample data

python scripts/seed_data.py ```

Production Testing

```bash

Set your Vercel URL

export VERCEL_URL="your-app.vercel.app"

Test production API

python test_vercel_api.py ```

🔧 Configuration

Environment Variables

Set these in Vercel dashboard or CLI:

```bash

Required

DATABASE_URL=postgresql://user:password@host:port/database SECRET_KEY=your-super-secret-key-here

Optional (for enhanced features)

FLASK_ENV=production ```

Vercel Configuration

The vercel.json file is pre-configured for optimal performance:

```json { "version": 2, "builds": [{"src": "api/index.py", "use": "@vercel/python"}], "routes": [ {"src": "/api/(.)", "dest": "api/index.py"}, {"src": "/(.)", "dest": "api/index.py"} ], "functions": { "api/index.py": {"maxDuration": 30} } } ```

🚨 Troubleshooting

Common Issues

Database Connection Errors

```bash

Check DATABASE_URL format

echo $DATABASE_URL

Should be: postgresql://user:pass@host:port/db

Test connection

python scripts/migration_status.py ```

Migration Failures

```bash

For local development

python scripts/migration_status.py python scripts/manual_upgrade.py

For Vercel production

1. Check deployment logs

vercel logs

2. Check if migration files are included in deployment

vercel ls

3. Force redeploy to retry auto-migration

vercel --prod --force

4. If auto-migration keeps failing, check database manually

python scripts/migration_status.py # (with production DATABASE_URL) ```

Cold Start Timeouts

```bash

Increase timeout in vercel.json

"functions": { "api/index.py": { "maxDuration": 30 } } ```

Import Errors

Ensure all imports are in api/index.py
Vercel serverless functions need everything in one file
Check Python path in scripts

Debugging

Check Vercel Logs

```bash vercel logs your-deployment-url ```

Local Development

```bash vercel dev ```

Database Issues

```bash

Backup before troubleshooting

python scripts/backup_database.py backup

Check database status

python scripts/migration_status.py

Reset if needed (CAUTION: Data loss)

python scripts/rollback_migration.py base python scripts/manual_upgrade.py ```

🔍 Serverless Considerations

Vercel Limitations

Execution Time: Max 30 seconds per request
Memory: Limited memory per function
File System: Read-only, no persistent storage
Database: Must use external database
Background Tasks: No long-running processes

Optimizations

Connection Pooling: Optimized for serverless
Auto-Migration: Handles cold starts gracefully
Session Storage: Cookie-based (consider Redis for scale)
Error Handling: Comprehensive rollback mechanisms

🚀 Production Optimizations

Database Performance

```python

Connection pooling configuration

app.config['SQLALCHEMY_ENGINE_OPTIONS'] = { 'pool_size': 5, 'pool_recycle': 300, 'pool_pre_ping': True, 'connect_args': { 'connect_timeout': 10, 'application_name': 'flask_vercel_app' } } ```

Monitoring

Health Checks: /api/health endpoint
Migration Status: Automatic logging
Error Tracking: Comprehensive error handlers
Performance: Vercel Analytics integration

Security

Password Hashing: Werkzeug security
Session Management: Secure cookie configuration
Input Validation: Comprehensive data validation
SQL Injection: SQLAlchemy ORM protection

📚 Project Evolution

Version History

v1.0: Basic Flask server with SQLite
v1.5: PostgreSQL integration with raw SQL
v2.0: SQLAlchemy ORM with relationships
v2.1: Auto-migration system with Flask-Migrate
v2.2: Flask CLI integration with standard commands

Architecture Decisions

SQLAlchemy ORM: Chosen for type safety and relationship management
Flask-Migrate: Selected for robust schema versioning
Vercel Serverless: Optimized for automatic scaling
PostgreSQL: Production-grade database with JSON support
Cookie Sessions: Simple authentication for MVP

🎯 Next Steps & Enhancements

Immediate Improvements

JWT Authentication: Replace cookie sessions
Input Validation: Add comprehensive validation
API Rate Limiting: Prevent abuse
Redis Caching: Improve performance
File Uploads: Add image support

Advanced Features

User Roles: Admin/user permissions
Post Categories: Organize content
Search Functionality: Full-text search
Email Notifications: User engagement
API Documentation: Swagger/OpenAPI

DevOps Enhancements

CI/CD Pipeline: Automated testing
Monitoring: Application performance
Logging: Centralized log management
Backup Automation: Scheduled backups
Load Testing: Performance validation

📞 Support & Resources

Documentation

Database Providers

Deployment Platforms

Vercel - Current setup
Railway - Alternative
Render - Alternative

🤝 Contributing

Development Setup

Fork the repository
Create feature branch
Make changes with tests
Update documentation
Submit pull request

Code Standards

Follow PEP 8 for Python code
Use descriptive commit messages
Include migration descriptions
Update README for new features

📊 Current Status

✅ Database: PostgreSQL with SQLAlchemy ORM
✅ Migrations: Auto-migration system active
✅ Deployment: Vercel serverless optimized
✅ Interface: Complete web UI with authentication
✅ API: RESTful endpoints with documentation
✅ Testing: Comprehensive test scripts
✅ Documentation: Complete setup and usage guides

Version: 2.2.0-flask-cli Last Updated: December 2024 Status: Production Ready 🚀

Name		Name	Last commit message	Last commit date
Latest commit History 50 Commits
api		api
migrations		migrations
scripts		scripts
MIGRATION_GUIDE_CUSTOM_SCRIPTS.md		MIGRATION_GUIDE_CUSTOM_SCRIPTS.md
MIGRATION_GUIDE_FLASK_CLI.md		MIGRATION_GUIDE_FLASK_CLI.md
README.md		README.md
app.py		app.py
config.py		config.py
favicon.ico		favicon.ico
models.py		models.py
package.json		package.json
requirements.txt		requirements.txt
tailwind.config.js		tailwind.config.js
test_api.py		test_api.py
test_vercel_api.py		test_vercel_api.py
vercel.json		vercel.json

Folders and files

Latest commit

History

Repository files navigation

Flask SQLAlchemy API on Vercel with Auto-Migration

🚀 Key Features

✅ Auto-Migration System

✅ Complete Web Interface

✅ Production Ready

✅ Developer Friendly

📋 Prerequisites

🛠️ Quick Setup

1. Database Setup

Option A: Neon (Recommended)

1. Sign up at neon.tech

2. Create a new project

3. Copy the connection string

Format: postgresql://user:password@host:port/database

Option B: Supabase

1. Sign up at supabase.com

2. Create a new project

3. Go to Settings > Database

4. Copy the connection string

2. Local Development

Clone the repository

Install dependencies

Set environment variables

Initialize auto-migration system (run once)

Setup database with sample data

Run locally

3. Deploy to Vercel

Install Vercel CLI

Login to Vercel

Set environment variables in Vercel

Deploy

🔄 Auto-Migration System

How It Works

Development Workflow

1. Modify models in api/index.py

2. Create migration locally

3. Test migration locally

4. Commit and deploy to Vercel

5. Deploy to Vercel (auto-migration runs on startup)

Migration Toolkit (Local Development)

Check migration status (local)

Manual migration (local development)

Rollback to previous version (local)

Backup database (works with any environment)

Restore from backup (works with any environment)

Vercel Production Commands

Deploy with auto-migration

Check deployment logs to see migration status

Force redeploy if migration failed

Check function logs for migration errors

Vercel Deployment & Migration

How Auto-Migration Works on Vercel

Vercel-Specific Commands

Deploy with migration

Monitor deployment and migration

Check if migrations directory is deployed

Environment variables for migration

Force redeploy if migration stuck

Migration Files in Vercel

📡 API Endpoints

Authentication

User Management

Post Management

System

🌐 Web Interface

Available Pages

Features

🧪 Testing

Local Testing

Test API endpoints

Test with sample data

Production Testing

Set your Vercel URL

Test production API

🔧 Configuration

Environment Variables

Packages