[CHORE]: Fix Database Migration Commands in Makefile

[crivetimihai]

Fix Database Migration Commands in Makefile

Priority: Medium (Development/Operations)

Description:
The make db-current command fails with "No 'script_location' key found in configuration" because it's not passing the correct path to the alembic configuration file. This affects developers' ability to check migration status and manage database schema changes.

Current Issue:

$ make db-current
alembic current
FAILED: No 'script_location' key found in configuration.

Root Cause:
The Makefile is running alembic current without specifying the configuration file path. Alembic needs the -c flag to locate the configuration file at mcpgateway/alembic.ini.

Expected Behavior:

• All database migration commands should work from the project root
• Commands should find the alembic configuration automatically
• Clear output showing current migration status

Current Working Workaround:

alembic -c mcpgateway/alembic.ini current
# Output: e75490e949b1 (head)

Suggested Fix:

Update the Makefile database commands:

# Database migration commands
ALEMBIC_CONFIG = mcpgateway/alembic.ini

.PHONY: db-init
db-init: ## Initialize alembic migrations
	@echo "🗄️ Initializing database migrations..."
	alembic -c $(ALEMBIC_CONFIG) init alembic

.PHONY: db-migrate
db-migrate: ## Create a new migration
	@echo "🗄️ Creating new migration..."
	@read -p "Enter migration message: " msg; \
	alembic -c $(ALEMBIC_CONFIG) revision --autogenerate -m "$$msg"

.PHONY: db-upgrade
db-upgrade: ## Upgrade database to latest migration
	@echo "🗄️ Upgrading database..."
	alembic -c $(ALEMBIC_CONFIG) upgrade head

.PHONY: db-downgrade
db-downgrade: ## Downgrade database by one revision
	@echo "🗄️ Downgrading database..."
	alembic -c $(ALEMBIC_CONFIG) downgrade -1

.PHONY: db-current
db-current: ## Show current database revision
	@echo "🗄️ Current database revision:"
	@alembic -c $(ALEMBIC_CONFIG) current

.PHONY: db-history
db-history: ## Show migration history
	@echo "🗄️ Migration history:"
	@alembic -c $(ALEMBIC_CONFIG) history

.PHONY: db-heads
db-heads: ## Show available heads
	@echo "🗄️ Available heads:"
	@alembic -c $(ALEMBIC_CONFIG) heads

.PHONY: db-show
db-show: ## Show a specific revision
	@read -p "Enter revision ID: " rev; \
	alembic -c $(ALEMBIC_CONFIG) show $$rev

.PHONY: db-stamp
db-stamp: ## Stamp database with a specific revision
	@read -p "Enter revision to stamp: " rev; \
	alembic -c $(ALEMBIC_CONFIG) stamp $$rev

.PHONY: db-reset
db-reset: ## Reset database (CAUTION: drops all data)
	@echo "⚠️  WARNING: This will drop all data!"
	@read -p "Are you sure? (y/N): " confirm; \
	if [ "$$confirm" = "y" ]; then \
		alembic -c $(ALEMBIC_CONFIG) downgrade base && \
		alembic -c $(ALEMBIC_CONFIG) upgrade head; \
		echo "✅ Database reset complete"; \
	else \
		echo "❌ Database reset cancelled"; \
	fi

Additional Improvements:

1. Add database status check:

.PHONY: db-status
db-status: ## Show detailed database status
	@echo "🗄️ Database Status:"
	@echo "Current revision:"
	@alembic -c $(ALEMBIC_CONFIG) current
	@echo ""
	@echo "Pending migrations:"
	@alembic -c $(ALEMBIC_CONFIG) history -r current:head

2. Add migration validation:

.PHONY: db-check
db-check: ## Check if migrations are up to date
	@echo "🗄️ Checking migration status..."
	@if alembic -c $(ALEMBIC_CONFIG) current | grep -q "(head)"; then \
		echo "✅ Database is up to date"; \
	else \
		echo "⚠️  Database needs migration"; \
		echo "Run 'make db-upgrade' to apply pending migrations"; \
		exit 1; \
	fi

3. Add helper for common issues:

.PHONY: db-fix-head
db-fix-head: ## Fix multiple heads issue
	@echo "🗄️ Fixing multiple heads..."
	alembic -c $(ALEMBIC_CONFIG) merge -m "merge heads"

Testing Requirements:

• Verify all db-* commands work from project root
• Test with both SQLite and PostgreSQL databases
• Ensure commands handle missing database gracefully
• Test migration creation and application
• Verify rollback functionality

Documentation Update:
Add to README.md:

## Database Management

MCP Gateway uses Alembic for database migrations. Common commands:

- `make db-current` - Show current database version
- `make db-upgrade` - Apply pending migrations
- `make db-migrate` - Create new migration
- `make db-history` - Show migration history
- `make db-status` - Detailed migration status

### Troubleshooting

If you see "No 'script_location' key found", ensure you're running from the project root directory.

Benefits:

• Consistent database management workflow
• No need to remember alembic command syntax
• Safer operations with confirmations
• Better error messages and status reporting

Acceptance Criteria:

• All db-* commands work without errors
• Commands work from project root directory
• Clear output messages for all operations
• Dangerous operations have confirmations
• Help text (make help) shows all db commands
• Migration status is easy to understand

Related Issues:

• Consider adding automatic migration on startup (configurable)
• May need PostgreSQL-specific commands
• Consider adding database backup/restore commands