Using MySQL with Microservices: Patterns & Anti-Patterns

While microservices architectures and MySQL may seem like odd companions at first glance, many organizations successfully integrate this traditional relational database with modern distributed application designs. This article explores proven patterns for effectively using MySQL in microservices environments, as well as anti-patterns to avoid.

Understanding the Challenge

Microservices architecture emphasizes:

• Service autonomy
• Independent deployability
• Decentralized data management
• Resilience and fault isolation

Traditional MySQL deployments often encourage:

• Centralized database instances
• Shared schemas
• ACID transactions across business processes
• Tight coupling between services

Reconciling these differences requires thoughtful design and implementation strategies.

Effective Patterns

1. Database-per-Service

Pattern: Each microservice owns and exclusively manages its private MySQL database (or schema).

Implementation:

# Example Docker Compose snippet showing database-per-service
services:
  order-service:
    image: order-service:latest
    depends_on:
      - order-db
    environment:
      DB_HOST: order-db
      DB_NAME: order_data
      
  order-db:
    image: mysql:8.0
    volumes:
      - order-db-data:/var/lib/mysql
    environment:
      MYSQL_DATABASE: order_data
      MYSQL_USER: order_service
      MYSQL_PASSWORD: ${ORDER_DB_PASSWORD}

  inventory-service :
    image: inventory-service:latest
    depends_on:
      - inventory-db
    environment:
      DB_HOST: inventory-db
      DB_NAME: inventory_data
      
  inventory-db:
    image: mysql:8.0
    volumes:
      - inventory-db-data:/var/lib/mysql
    environment:
      MYSQL_DATABASE: inventory_data
      MYSQL_USER: inventory_service
      MYSQL_PASSWORD: ${INVENTORY_DB_PASSWORD}

Benefits:

• Services can evolve independently
• Schemas can change without coordination
• No unexpected impacts from other services
• Clear ownership boundaries
• Ability to scale each database according to its specific needs
• Simplified security management

Considerations:

• Requires data duplication across services
• Increases infrastructure complexity
• May increase licensing costs
• Requires careful cross-service data consistency strategies

2. Event-Driven Data Synchronization

Pattern: Services publish events when their data changes, allowing other services to maintain their own copies of relevant data.

Implementation:

// Example event publisher in a Java service
@Transactional
public void createOrder(Order order) {
    // Save to local database
    orderRepository.save(order);
    
    // Publish event for other services
    OrderCreatedEvent event = new OrderCreatedEvent(
        order.getId(),
        order.getCustomerId(),
        order.getItems(),
        order.getTotalAmount()
    );
    eventPublisher.publish("order.created", event);
}

// Example event consumer in Node.js
async function handleOrderCreated(event) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    
    // Update local read model with data from order service
    await connection.query(
      'INSERT INTO order_summaries (order_id, customer_id, amount, status) VALUES (?, ?, ?, ?)',

[event.orderId, event.customerId, event.totalAmount, ‘CREATED’]

); await connection.commit(); } catch (err) { await connection.rollback(); console.error(‘Failed to process order created event’, err); // Implement retry or dead-letter queue logic } finally { connection.release(); } }

Benefits:

• Enables eventual consistency across services
• Allows each service to optimize its data model for its specific needs
• Reduces direct coupling between services

Considerations:

• Requires implementing event production and consumption logic
• Eventual (rather than immediate) consistency
• Need for idempotent event handlers
• Error handling and retry logic complexity

3. Command Query Responsibility Segregation (CQRS)

Pattern: Separate read and write operations, potentially using MySQL for the write side and a more scalable/optimized solution for reads.

Implementation Example:

// Write operation - stored in MySQL
public async Task<Guid> CreateProduct(CreateProductCommand command)
{
    var product = new Product(
        Guid.NewGuid(),
        command.Name,
        command.Description,
        command.Price,
        command.InventoryCount
    );
    
    await _productRepository.SaveAsync(product);
    
    // Publish event for read model population
    await _eventBus.PublishAsync(new ProductCreatedEvent(product));
    
    return product.Id;
}

// Read operation - from optimized read store
public async Task<IEnumerable<ProductViewModel>> GetProductsByCategory(string category)
{
    // This could query a denormalized MySQL view or a different datastore
    return await _productReadStore.GetByCategoryAsync(category);
}

Benefits:

• Write operations maintain MySQL’s ACID guarantees
• Read operations can be optimized for specific query patterns
• Read side can scale independently from write side

Considerations:

• Increased architectural complexity
• Eventual consistency between write and read stores
• Additional infrastructure requirements

4. Sharding for Scale

Pattern: Partition data across multiple MySQL instances based on some logical criteria (customer ID, geography, etc.)

Implementation Example:

def get_connection_for_tenant(tenant_id):
    """Determine which database shard to use based on tenant ID"""
    shard_number = determine_shard(tenant_id)
    return mysql.connector.connect(
        host=f"mysql-shard-{shard_number}.example.com",
        database=f"tenant_data",
        user="app_user",
        password=os.environ.get("DB_PASSWORD")
    )

def determine_shard(tenant_id):
    """Simple sharding logic based on tenant ID"""
    return hash(tenant_id) % TOTAL_SHARDS

Benefits:

• Enables horizontal scaling
• Can improve performance through data locality
• Maintains MySQL’s transactional features within each shard

Considerations:

• Increases operational complexity
• Complicates queries that span multiple shards
• Requires sharding strategy aligned with access patterns

5. Read Replicas for Scaling Reads

Pattern: Direct read-heavy queries to MySQL replicas while keeping writes to the primary.

Implementation:

// Go example with read/write connection separation
func initializeConnections() (*sql.DB, *sql.DB) {
    writeConn, err := sql.Open("mysql", os.Getenv("MYSQL_MASTER_DSN"))
    if err != nil {
        log.Fatalf("Failed to connect to write database: %v", err)
    }
    
    readConn, err := sql.Open("mysql", os.Getenv("MYSQL_REPLICA_DSN"))
    if err != nil {
        log.Fatalf("Failed to connect to read database: %v", err)
    }
    
    return writeConn, readConn
}

// Repository using separate connections
type ProductRepository struct {
    writeDB *sql.DB
    readDB  *sql.DB
}

func (r *ProductRepository) Create(product Product) error {
    _, err := r.writeDB.Exec(
        "INSERT INTO products(id, name, price) VALUES(?, ?, ?)",
        product.ID, product.Name, product.Price,
    )
    return err
}

func (r *ProductRepository) FindByID(id string) (Product, error) {
    var product Product
    err := r.readDB.QueryRow(
        "SELECT id, name, price FROM products WHERE id = ?",
        id,
    ).Scan(&product.ID, &product.Name, &product.Price)
    return product, err
}

Benefits:

• Improves read scalability with minimal application changes
• Maintains data consistency (with small replication delay)
• Utilizes MySQL’s built-in replication capabilities

Considerations:

• Potential for reading stale data
• Limited scaling for write operations
• Need to handle replication lag in application logic

Common Anti-Patterns to Avoid

1. Shared Database Anti-Pattern

Anti-Pattern: Multiple microservices reading from and writing to the same database tables.

Why It’s Problematic:

• Creates tight coupling between services
• Schema changes require coordination across teams
• Difficult to evolve services independently
• Performance bottlenecks affect all services
• Violates service autonomy principle

Alternative: Implement the Database-per-Service pattern described above.

2. Distributed Transactions Anti-Pattern

Anti-Pattern: Using two-phase commit or distributed transactions across multiple microservices.

Example to Avoid:

// DON'T DO THIS in microservices
@Transactional
public void processOrder(OrderRequest request) {
    // Updates database of order service
    Order order = createOrder(request);
    
    // Makes a synchronous call to another service, expecting 
    // it to participate in the same transaction
    boolean paymentProcessed = paymentService.processPayment(
        request.getPaymentDetails(), 
        order.getTotalAmount()
    );
    
    if (!paymentProcessed) {
        throw new PaymentFailedException();
    }
    
    // Updates inventory in yet another service's database
    boolean inventoryUpdated = inventoryService.reserveItems(
        request.getItems()
    );
    
    if (!inventoryUpdated) {
        throw new InsufficientInventoryException();
    }
}

Why It’s Problematic:

• Tightly couples service implementations
• Creates a distributed monolith
• Single service failure affects the entire process
• Performance degradation due to locking
• Difficult to implement correctly in practice

Alternative: Use the Saga pattern with compensating transactions, or event-driven eventual consistency.

3. Database as an Integration Point Anti-Pattern

Anti-Pattern: Services integrating by accessing each other’s databases directly.

Example to Avoid:

-- DON'T DO THIS: Service A directly reading from Service B's database
SELECT o.id, o.customer_id, p.status 
FROM service_b.payments p
JOIN service_a.orders o ON p.order_id = o.id
WHERE o.status = 'PENDING' AND p.status = 'COMPLETED';

Why It’s Problematic:

• Creates hidden dependencies between services
• Schema changes can break other services unexpectedly
• Violates encapsulation of domain logic
• Complicates security management

Alternative: Use well-defined APIs (REST, GraphQL, gRPC) or events for service communication.

4. Reporting Database Anti-Pattern

Anti-Pattern: Adding complex reporting queries directly to production transactional databases.

Why It’s Problematic:

• Reporting queries can lock tables and degrade performance
• Different access patterns often require different optimizations
• Operational concerns of reporting vs. transactions differ

Alternative: Use a dedicated reporting database with data replication, or implement CQRS pattern.

5. One-Size-Fits-All Database Anti-Pattern

Anti-Pattern: Forcing MySQL to handle all data storage needs regardless of access patterns or requirements.

Why It’s Problematic:

• Not all data is relational by nature
• Some workloads may be better served by specialized databases
• Inefficient use of resources for certain data types

Alternative: Consider polyglot persistence – use MySQL where its strengths align with requirements, and other databases (document, key-value, graph) where appropriate.

Implementation Considerations

Connection Management

Properly managing MySQL connections in microservices:

// Example connection pool in Node.js
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

async function executeQuery(sql, params) {
  const connection = await pool.getConnection();
  try {
    const [results] = await connection.execute(sql, params);
    return results;
  } finally {
    connection.release();
  }
}

Schema Evolution

Managing MySQL schema changes in microservices:

# Example Flyway configuration for schema migration
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    baseline-version: 0

-- Example migration script (V1__Create_product_table.sql)
CREATE TABLE products (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Resilience

Implementing circuit breakers for database interactions:

// Example using Resilience4j in Java
@CircuitBreaker(name = "databaseCircuitBreaker", fallbackMethod = "findProductFallback")
public Product findProductById(String id) {
    return jdbcTemplate.queryForObject(
        "SELECT * FROM products WHERE id = ?", 
        new Object[]{id}, 
        productRowMapper
    );
}

public Product findProductFallback(String id, Exception e) {
    log.error("Database access failed for product {}: {}", id, e.getMessage());
    // Return cached data or default response
    return getCachedProduct(id).orElse(new Product(id, "Unavailable", 0.0));
}

Conclusion

MySQL can work effectively in microservices architectures when used with appropriate patterns. By maintaining service autonomy, implementing event-driven communication, and deliberately designing data access patterns, teams can leverage MySQL’s strengths while avoiding the pitfalls that lead to distributed monoliths.

Remember that successful microservices architectures focus on business capabilities and service boundaries first, with database decisions following from those boundaries rather than driving them. Choose the patterns that best align with your specific requirements, team capabilities, and existing infrastructure investments.