DAB MySQL Quickstart

A complete Data API Builder (DAB) quickstart template for MySQL, designed for API-only deployment. This project automatically generates both REST and GraphQL APIs over a MySQL database using Microsoft's Data API Builder.

🚀 What This Project Does

This is a backend-as-a-service solution that:

• ✅ Auto-generates REST & GraphQL APIs from your MySQL database schema
• ✅ Complete local development environment with MySQL in Docker containers
• ✅ No frontend dependencies - pure API deployment ready for any client
• ✅ Sample AdventureWorks database with products, categories, and models
• ✅ Azure-ready infrastructure templates for production deployment

API Endpoints Available

REST API (http://localhost:5000/api):

• GET /api/Product - List all products
• GET /api/Product/id/{id} - Get specific product
• GET /api/ProductCategory - Product categories
• GET /api/ProductModel - Product models
• Full CRUD operations on all entities

GraphQL API (http://localhost:5000/graphql):

• Single endpoint with full query/mutation support
• Schema introspection enabled
• Real-time subscriptions support

Sample GraphQL Query

query {
  products {
    items {
      ProductID
      Name
      ProductNumber
      Color
      ListPrice
    }
  }
}

Database Schema

The project includes a MySQL version of AdventureWorks sample data:

• Product (295 items): Main product catalog with pricing, colors, categories
• ProductCategory (41 items): Hierarchical product categories
• ProductModel (128 items): Product model information with manufacturing details

📋 Prerequisites

• Visual Studio Code with Dev Containers extension
• Docker Desktop (running)

⚡ Quick Start - Local Development

1. Clone and Open:

   git clone <your-repo>
   cd dabmysql
   code .

2. Open in Dev Container:

   • Press F1 and select "Dev Containers: Reopen in Container"
   • Wait 3-5 minutes for initial build and MySQL setup

3. Verify Database:

   mysql -h mysql -u dabuser -p<your-pass-here> SalesLT
   SHOW TABLES;
   SELECT COUNT(*) FROM Product;
   exit

4. Start the API Server:

   cd src/api
   dab start --config dab-config.json

5. Test Your APIs:

   • REST: http://localhost:5000/api/Product
   • GraphQL: http://localhost:5000/graphql
   • API Documentation: http://localhost:5000/api (shows available endpoints)

🧪 Testing with Sample Requests

REST API Examples

# Get all products
curl http://localhost:5000/api/Product

# Get specific product
curl http://localhost:5000/api/Product/id/1

# Get products by category
curl "http://localhost:5000/api/Product?\$filter=ProductCategoryID eq 1"

# Create a new product
curl -X POST http://localhost:5000/api/Product \
  -H "Content-Type: application/json" \
  -d '{
    "Name": "Test Product",
    "ProductNumber": "TP-001",
    "ListPrice": 99.99,
    "ProductCategoryID": 1
  }'

GraphQL Examples

# Simple query
curl -X POST http://localhost:5000/graphql \
  -H "Content-Type: application/json" \
  -d '{"query": "{ products { items { ProductID Name ListPrice } } }"}'

# Query with filtering
curl -X POST http://localhost:5000/graphql \
  -H "Content-Type: application/json" \
  -d '{
    "query": "query GetProductsByCategory($categoryId: Int) {
      products(filter: { ProductCategoryID: { eq: $categoryId } }) {
        items { ProductID Name ListPrice Color }
      }
    }",
    "variables": { "categoryId": 1 }
  }'

🛠️ Development Features

Built-in Tools

• Hot reload: Changes to DAB config auto-restart the server
• Debug mode: dab start --config dab-config.json --LogLevel Debug
• Schema validation: dab validate --config dab-config.json
• MySQL client: Direct database access via mysql command

Project Structure

dabmysql/
├── .devcontainer/          # Dev container configuration
├── src/api/                # DAB API configuration and Dockerfile
├── database/               # MySQL schema and seed data
├── infra/                  # Azure infrastructure templates
├── postman/                # API testing collections
└── README.md               # This file

Configuration Files

• dab-config.json: Data API Builder configuration
• docker-compose.yml: Local development services (MySQL + app)
• schema.sql & seed.sql: Database structure and sample data

🔧 Troubleshooting

Common Issues

Dev container won't start:

# Rebuild the container
F1 > "Dev Containers: Rebuild Container"

MySQL connection fails:

# Check container status
docker ps

# View MySQL logs
docker logs devcontainer-mysql-1

DAB API returns 500 errors:

# Validate configuration
cd src/api
dab validate --config dab-config.json

# Run in debug mode
dab start --config dab-config.json --LogLevel Debug

Port already in use:

# Stop existing containers
docker-compose down

# Check what's using port 5000 or 3306
lsof -i :5000
lsof -i :3306

🧪 Testing with Postman

1. Import Collection: Import postman/DAB-MySQL-API.postman_collection.json
2. Set Environment: Use postman/Local-Environment.postman_environment.json
3. Update Variables: Set baseUrl to http://localhost:5000
4. Run Tests: Try the "Get All Products" request first

🌐 Frontend Integration Examples

Since this is an API-only deployment, you can integrate with any frontend:

React/Next.js

const API_BASE = 'http://localhost:5000';

// Fetch products
const products = await fetch(`${API_BASE}/api/Product`).then(r => r.json());

// GraphQL with fetch
const graphqlQuery = await fetch(`${API_BASE}/graphql`, {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    query: '{ products { items { ProductID Name ListPrice } } }'
  })
}).then(r => r.json());

Vue.js

// Using axios
const products = await axios.get(`${API_BASE}/api/Product`);

Angular

// Using HttpClient
constructor(private http: HttpClient) {}

getProducts() {
  return this.http.get(`${API_BASE}/api/Product`);
}

🎯 Coming Soon

Azure Cloud Deployment

• One-command deployment with Azure Developer CLI (azd up)
• Azure Container Apps hosting with auto-scaling
• Azure Database for MySQL Flexible Server
• Azure Key Vault for secure credential management
• Production-ready configuration with authentication and CORS restrictions
• Monitoring and logging with Application Insights

Additional Features

• Authentication integration with Azure AD and custom providers
• Advanced querying with OData filters and GraphQL subscriptions
• Performance optimization guides and caching strategies
• CI/CD pipeline templates for automated deployments

📚 Resources

• Data API Builder Documentation
• Azure Database for MySQL
• GraphQL Best Practices
• REST API Design Guidelines

📄 License

MIT License - see LICENSE file for details.

---

Ready to build? Start with the Quick Start section above and have your API running in under 5 minutes! 🚀