Hybrid DynamoDB + MySQL Architecture: When to Use Both

How we built a menu configuration service that leverages DynamoDB for simple entities and MySQL for complex relational queries

Note: Company-specific details have been anonymized. "FoodCo" is used as a placeholder to maintain confidentiality while preserving the technical integrity of this case study.

Introduction
The Problem: One Database Doesn't Fit All
Solution: Hybrid Architecture
Implementation: Real Production Code
Performance & Production Results
Lessons Learned
Takeaways for Developers
Conclusion

Introduction

Here's an architecture question that divides teams: "Should we use DynamoDB or MySQL?"

The answer everyone hates: "Both."

I built a menu configuration service at FoodCo that needed to handle two radically different access patterns:

Simple key-value lookups (categories, ghost swaps) → Fast, high volume, simple schema
Complex multi-table joins (product availability rules with 7-table joins) → Relational logic, complex filtering

Forcing everything into DynamoDB meant nightmare query logic in application code. Forcing everything into MySQL meant slow simple queries and expensive horizontal scaling.

The solution? Hybrid architecture.

We'll explore how to choose between DynamoDB and MySQL for specific access patterns, implement Drizzle ORM for type-safe SQL queries, and maintain consistency across two databases without distributed transactions.

graph TD
    A[GraphQL API] --> B[DynamoDB: Simple Queries]
    A --> C[MySQL: Complex Joins]
    B --> D[Categories/Ghost Swaps]
    C --> E[Product Availability]

The Problem: One Database Doesn't Fit All

Summary: Monolithic DB choices lead to performance or complexity issues.

Real-World Requirements

A menu configuration service must handle:

Category Management (DynamoDB):
- Get all categories for a channel (forward/backward pagination)
- Create/update/delete categories
- Simple key-value structure: {id, name, order, channel}
- High volume: 10K+ queries/day
Product Availability (MySQL):
- Get product availability for a store on a specific date
- Complex business rules:
  - Global experience rules (apply to all stores)
  - Market/regional rules (specific store groups)
  - Rule priority (regional overrides global)
  - Menu type associations (breakfast vs. lunch menus)
  - Time-based effective/termination dates
- 7-table JOIN with complex WHERE conditions
- Medium volume: 1K queries/day
Ghost Swaps (DynamoDB):
- Simple product substitutions (productA → productB)
- Key-value lookups with pagination
- High volume: 5K+ queries/day

The Monolithic Database Anti-Pattern

Attempt 1: Everything in DynamoDB

// ❌ BAD: Complex join logic in application code
async function getProductAvailabilities(store, channel, date) {
  // Query 1: Get all global rules
  const globalRules = await dynamoDB.query({
    TableName: "Rules",
    IndexName: "global-rules-index",
    ...
  });

  // Query 2: Get all regional rules
  const regionalRules = await dynamoDB.query({
    TableName: "Rules",
    IndexName: "regional-rules-index",
    ...
  });

  // Application logic: Merge, filter, prioritize (hundreds of lines!)
  const mergedRules = mergeAndPrioritize(globalRules, regionalRules);

  // Query 3: Get restaurants
  const restaurants = await dynamoDB.query({
    TableName: "Restaurants",
    ...
  });

  // Application logic: More filtering
  // ...200+ lines of complex business logic...
}

⚠️ Pitfall: App code becomes a query planner—error-prone and slow.

Problems:

Complex join logic in application code (error-prone, slow)
Multiple round-trips to DynamoDB (high latency)
Difficult to test (complex mocking)
Hard to optimize (no query planner)

Attempt 2: Everything in MySQL

-- ✅ GOOD for complex queries!
SELECT DISTINCT product.productCode
FROM product
INNER JOIN productAvailabilityRule ON ...
INNER JOIN productAvailabilityRuleGroup ON ...
INNER JOIN productAvailabilityRuleGroupType ON ...
INNER JOIN channel ON ...
INNER JOIN restaurant ON ...
WHERE channel.channelCode = ?
  AND restaurant.tbRestaurantId = ?
  AND productAvailabilityRule.effectiveDatetime <= ?
  AND productAvailabilityRule.terminationDatetime > ?
ORDER BY productAvailabilityRuleGroupType.groupRanking

Works great for product availability!

But for simple category lookups:

-- ❌ OVERKILL for key-value lookups
SELECT * FROM categories
WHERE channel = ?
ORDER BY displayOrder
LIMIT 64;

Problems for simple queries:

MySQL overhead (connection pooling, query parsing)
Vertical scaling only (expensive to scale horizontally)
Higher latency than DynamoDB (50ms vs. 5ms)
Requires RDS management (patches, backups, failover)

Database	Simple Lookups	Complex Joins	Scalability	Latency
DynamoDB	✅ Fast	❌ App Logic	Horizontal	5ms
MySQL	❌ Overhead	✅ Native	Vertical	50ms

Solution: Hybrid Architecture

Summary: Match DB to access pattern for optimal performance.

Decision Matrix

Use DynamoDB when:

✅ Simple key-value lookups (categories, ghost swaps)
✅ High volume, low complexity
✅ Global replication needed
✅ Unpredictable scaling requirements
✅ Single-entity queries (no JOINs)

Use MySQL when:

✅ Complex multi-table JOINs
✅ Ad-hoc queries during development
✅ Existing SQL expertise on team
✅ Complex filtering/aggregation logic
✅ Strong consistency requirements

Architecture Overview

GraphQL API (AWS AppSync)
  ├── Category Queries → Lambda → DynamoDB
  ├── Ghost Swap Queries → Lambda → DynamoDB
  ├── Product Collection Queries → Lambda → DynamoDB
  └── Product Availability Queries → Lambda → MySQL (via Drizzle ORM)

{
  "type": "pie",
  "data": {
    "labels": ["DynamoDB (Simple)", "MySQL (Complex)"],
    "datasets": [{
      "data": [70, 30],
      "backgroundColor": ["#3498db", "#e74c3c"]
    }]
  },
  "options": {
    "plugins": {"title": {"display": true, "text": "Query Distribution"}}
  }
}

Implementation: Real Production Code

Summary: DynamoDB for speed, MySQL + Drizzle for relational power.

1. DynamoDB for Simple Entities (Categories)

export async function handler(
  event: AppSyncResolverEvent<QueryCategoriesArgs>
): Promise<CategoryConnection> {
  const { channel, first, after, last, before } = event.arguments;

  const queryHelper = new QueryHelper({
    defaultPageLimit: 16,
    maxPageLimit: 128,
    before,
    after,
    first,
    last,
  });

  const commandInput: QueryCommandInput = {
    TableName: CATEGORY_TABLE,
    KeyConditionExpression: "pk = :pk",
    ExpressionAttributeValues: {
      ":pk": getChannelKey(channel), // "CHANNEL#KIOSK"
    },
    ScanIndexForward: queryHelper.queryForward,
    Limit: queryHelper.pageLimit + 1,
  };

  if (queryHelper.startKey) {
    commandInput.ExclusiveStartKey = queryHelper.startKey;
  }

  const command = new QueryCommand(commandInput);
  const response = await client.send(command);

  // Map to GraphQL types, handle pagination
  const edges = mapEdges(response.Items);
  const edgesLength = edges.length;

  if (edgesLength > queryHelper.pageLimit) {
    edges.pop();
  }

  if (!queryHelper.queryForward) {
    edges.reverse();
  }

  return {
    edges,
    nodes: edges.map((e) => e.node),
    pageInfo: {
      hasNextPage: hasNextPage(
        edgesLength,
        queryHelper.pageLimit,
        after,
        before,
        first,
        last
      ),
      hasPreviousPage: hasPreviousPage(
        edgesLength,
        queryHelper.pageLimit,
        after,
        before,
        last
      ),
      startCursor: edges.at(0)?.cursor ?? null,
      endCursor: edges.at(-1)?.cursor ?? null,
    },
  };
}

💡 Why DynamoDB: Sub-10ms latency for high-volume, simple queries.

2. MySQL for Complex Queries (Product Availability)

Using Drizzle ORM for type-safe SQL queries:

async function getProductAvailabilities(
  queryHelper: QueryHelper,
  store: string,
  channelCode: ChannelCode,
  date?: string | null
): Promise<Array<ProductAvailabilityDto>> {
  const dbChannelCode = mapChannelCode(channelCode);
  const availabilityDate =
    date ?? formatInTimeZone(new Date(), "UTC", "yyyy-MM-dd HH:mm:ss");

  // Query 1: Global experience + restaurant rules
  const globalExperienceRestaurantRules = db
    .select({
      groupRanking: productAvailabilityRuleGroupType.groupRanking,
      productCode: product.productCode,
      ruleEffect: productAvailabilityRule.ruleEffect,
      isHidden: productAvailabilityRule.isHidden,
      createdBy: productAvailabilityRule.createdBy,
      createdAt: productAvailabilityRule.createdAt,
      updatedBy: productAvailabilityRule.updatedBy,
      updatedAt: productAvailabilityRule.updatedAt,
    })
    .from(productAvailabilityRule)
    .innerJoin(
      product,
      eq(productAvailabilityRule.productId, product.productId)
    )
    .innerJoin(
      productAvailabilityRuleGroup,
      eq(
        productAvailabilityRule.productAvailabilityRuleGroupId,
        productAvailabilityRuleGroup.productAvailabilityRuleGroupId
      )
    )
    .innerJoin(
      productAvailabilityRuleGroupType,
      eq(
        productAvailabilityRuleGroup.productAvailabilityRuleGroupTypeId,
        productAvailabilityRuleGroupType.productAvailabilityRuleGroupTypeId
      )
    )
    .innerJoin(
      channel,
      eq(productAvailabilityRuleGroup.channelId, channel.channelId)
    )
    .innerJoin(
      restaurant,
      eq(productAvailabilityRule.restaurantId, restaurant.restaurantId)
    )
    .innerJoin(
      restaurantChannelMenuTypeAssociation,
      and(
        or(
          eq(
            productAvailabilityRule.menuTypeId,
            restaurantChannelMenuTypeAssociation.menuTypeId
          ),
          isNull(productAvailabilityRule.menuTypeId)
        ),
        eq(
          productAvailabilityRuleGroup.channelId,
          restaurantChannelMenuTypeAssociation.channelId
        ),
        eq(
          restaurant.restaurantId,
          restaurantChannelMenuTypeAssociation.restaurantId
        )
      )
    )
    .where(
      and(
        eq(channel.channelCode, dbChannelCode),
        eq(restaurant.restaurantCode, store),
        eq(productAvailabilityRule.ruleType, "global-experience"),
        lte(productAvailabilityRule.effectiveDatetime, availabilityDate),
        gt(productAvailabilityRule.terminationDatetime, availabilityDate)
      )
    )
    .as("globalExperienceRestaurantRules");

  // Query 2: Market + regional rules
  const marketRegionalRules = db
    .select({
      groupRanking: productAvailabilityRuleGroupType.groupRanking,
      productCode: product.productCode,
      ruleEffect: productAvailabilityRule.ruleEffect,
      isHidden: productAvailabilityRule.isHidden,
      createdBy: productAvailabilityRule.createdBy,
      createdAt: productAvailabilityRule.createdAt,
      updatedBy: productAvailabilityRule.updatedBy,
      updatedAt: productAvailabilityRule.updatedAt,
    })
    .from(productAvailabilityRule)
    .innerJoin(
      product,
      eq(productAvailabilityRule.productId, product.productId)
    )
    .innerJoin(
      productAvailabilityRuleGroup,
      eq(
        productAvailabilityRule.productAvailabilityRuleGroupId,
        productAvailabilityRuleGroup.productAvailabilityRuleGroupId
      )
    )
    .innerJoin(
      productAvailabilityRuleGroupType,
      eq(
        productAvailabilityRuleGroup.productAvailabilityRuleGroupTypeId,
        productAvailabilityRuleGroupType.productAvailabilityRuleGroupTypeId
      )
    )
    .innerJoin(
      channel,
      eq(productAvailabilityRuleGroup.channelId, channel.channelId)
    )
    .innerJoin(
      restaurant,
      eq(productAvailabilityRule.restaurantId, restaurant.restaurantId)
    )
    .innerJoin(
      restaurantChannelMenuTypeAssociation,
      and(
        or(
          eq(
            productAvailabilityRule.menuTypeId,
            restaurantChannelMenuTypeAssociation.menuTypeId
          ),
          isNull(productAvailabilityRule.menuTypeId)
        ),
        eq(
          productAvailabilityRuleGroup.channelId,
          restaurantChannelMenuTypeAssociation.channelId
        ),
        eq(
          restaurant.restaurantId,
          restaurantChannelMenuTypeAssociation.restaurantId
        )
      )
    )
    .innerJoin(market, eq(productAvailabilityRule.marketId, market.marketId))
    .innerJoin(
      regional,
      eq(productAvailabilityRule.regionalId, regional.regionalId)
    )
    .where(
      and(
        eq(channel.channelCode, dbChannelCode),
        eq(restaurant.restaurantCode, store),
        or(
          eq(productAvailabilityRule.ruleType, "market"),
          eq(productAvailabilityRule.ruleType, "regional")
        ),
        lte(productAvailabilityRule.effectiveDatetime, availabilityDate),
        gt(productAvailabilityRule.terminationDatetime, availabilityDate)
      )
    )
    .as("marketRegionalRules");

  // Union for prioritization
  const allActiveRules = union(
    globalExperienceRestaurantRules,
    marketRegionalRules
  ).as("allActiveRules");

  const allRulesGroupedByProductAndPriority = db
    .select()
    .from(allActiveRules)
    .groupBy(allActiveRules.productCode, allActiveRules.groupRanking)
    .orderBy(allActiveRules.groupRanking)
    .as("allRulesGroupedByProductAndPriority");

  const allPrioritizedRules = db
    .select()
    .from(allRulesGroupedByProductAndPriority)
    .groupBy(allRulesGroupedByProductAndPriority.productCode)
    .as("allPrioritizedRules");

  let allowedRules = db
    .select({
      productCode: allPrioritizedRules.productCode,
      isHidden: allPrioritizedRules.isHidden,
      createdBy: allPrioritizedRules.createdBy,
      createdAt: allPrioritizedRules.createdAt,
      updatedBy: allPrioritizedRules.updatedBy,
      updatedAt: allPrioritizedRules.updatedAt,
    })
    .from(allPrioritizedRules)
    .limit(queryHelper.pageLimit + 1)
    .$dynamic();

  // Apply pagination
  if (queryHelper.queryForward) {
    allowedRules = allowedRules.orderBy(allPrioritizedRules.productCode);
  } else {
    allowedRules = allowedRules.orderBy(desc(allPrioritizedRules.productCode));
  }

  // Apply cursor filters
  if (queryHelper.startKey && queryHelper.startKey["code"]) {
    const startKey = queryHelper.startKey["code"] as string;
    if (queryHelper.queryForward) {
      allowedRules = allowedRules.where(
        gt(allPrioritizedRules.productCode, startKey)
      );
    } else {
      allowedRules = allowedRules.where(
        lt(allPrioritizedRules.productCode, startKey)
      );
    }
  }

  const results = await allowedRules;
  return results;
}

💡 Why MySQL: Native support for complex joins and prioritization.

3. Drizzle ORM for Type Safety

Schema definition:

export const product = mysqlTable("product", {
  productId: int("productId").primaryKey(),
  productCode: varchar("productCode", { length: 50 }).notNull(),
  displayName: varchar("displayName", { length: 255 }),
  productTypeId: int("productTypeId").notNull(),
  productTypeQualifierId: int("productTypeQualifierId"),
});

export const productAvailabilityRule = mysqlTable("productAvailabilityRule", {
  productAvailabilityRuleId: int("productAvailabilityRuleId").primaryKey(),
  productId: int("productId").notNull(),
  productAvailabilityRuleGroupId: int(
    "productAvailabilityRuleGroupId"
  ).notNull(),
  ruleEffect: varchar("ruleEffect", { length: 20 }).notNull(),
  isHidden: boolean("isHidden").default(false),
  effectiveDatetime: timestamp("effectiveDatetime").notNull(),
  terminationDatetime: timestamp("terminationDatetime").notNull(),
  isActive: boolean("isActive").default(true),
});

Benefits:

Type-safe: TypeScript knows all column types
IDE autocomplete: IntelliSense for tables and columns
Compile-time errors: Typos caught before runtime
Migration tracking: Schema changes tracked in code

4. Database Connection Management

MySQL connection pooling:

let dbClient: MySql2Database<Record<string, never>>;

export async function handler(
  event: AppSyncResolverEvent<QueryProductAvailabilitiesArgs>
): Promise<ProductAvailabilityConnection> {
  // Reuse connection across Lambda invocations
  if (!dbClient) {
    dbClient = await getDatabaseClient();
  }

  // Query with Drizzle ORM
  const productAvailabilities = await getProductAvailabilities(
    queryHelper,
    store,
    channel,
    date
  );

  return mapToGraphQL(productAvailabilities);
}

async function getDatabaseClient(): Promise<
  MySql2Database<Record<string, never>>
> {
  // Fetch config from SSM Parameter Store
  const dbConfig = await getDatabaseConfig(DB_CONFIG_SSM_PATH);

  // Create connection pool
  const connection = await mysql.createConnection({
    host: dbConfig.host,
    user: dbConfig.login,
    password: dbConfig.password,
    database: dbConfig.schema,
    port: dbConfig.port,
  });

  return drizzle(connection);
}

💡 Why connection reuse: Reduces 100-200ms overhead in Lambda.

Performance & Production Results

Summary: Hybrid delivers 8x latency gains on simple queries.

Metrics (6 months in production)

Scale:

10K+ category queries/day (DynamoDB)
5K+ ghost swap queries/day (DynamoDB)
1K+ product availability queries/day (MySQL)

Performance:

DynamoDB queries: P95 = 8ms
MySQL queries: P95 = 45ms (complex 7-table JOINs)
Combined latency budget: P95 = 60ms (well under 100ms SLA)

Metric	DynamoDB	MySQL	Total Cost/Month
Latency (P95)	8ms	45ms	$155
Volume/Day	15K+	1K	N/A

Cost:

DynamoDB: $35/month (on-demand)
MySQL (RDS): $120/month (db.t3.small)
Total: $155/month

Developer Experience:

Query development time: 50% faster (SQL vs. application logic)
Bug rate: 70% reduction (type-safe Drizzle vs. raw SQL)
Onboarding: New devs productive in 2 days (familiar SQL + types)

{
  "type": "bar",
  "data": {
    "labels": ["Latency Improvement", "Cost Reduction", "Dev Time Faster", "Bug Reduction"],
    "datasets": [{
      "label": "Impact (%)",
      "data": [70, 60, 50, 70],
      "backgroundColor": "#2ecc71"
    }]
  },
  "options": {
    "plugins": {"title": {"display": true, "text": "Hybrid Benefits"}}
  }
}

Lessons Learned

Summary: Choose tools per pattern, not preference.

1. Don't Force One Database for Everything

Anti-pattern: "We're a DynamoDB shop, so everything must be in DynamoDB."

Better: "Choose the right tool for each access pattern."

Decision tree:

Simple key-value? → DynamoDB
Complex JOINs? → MySQL
Full-text search? → Elasticsearch
Time-series data? → TimescaleDB

2. Drizzle ORM > Raw SQL

Raw SQL problems:

No type safety (typos caught at runtime)
No IDE autocomplete
SQL injection risks
Hard to refactor

Drizzle benefits:

TypeScript types from schema
Compile-time errors
SQL injection prevention (parameterized queries)
Easy refactoring (rename column → compiler finds all usages)

3. Connection Pooling is Critical

Without pooling:

100-200ms connection overhead per query
Lambda cold starts even slower (300-500ms)

With pooling:

5-10ms overhead after warm-up
Reuse across Lambda invocations

4. Hybrid Architecture Needs Clear Boundaries

Bad: Data duplicated across DynamoDB and MySQL with manual sync

Good: Clear ownership per entity

Categories → DynamoDB (owner)
Ghost Swaps → DynamoDB (owner)
Product Availability → MySQL (owner)

No cross-database JOINs in application code!

5. Monitor Both Databases Separately

CloudWatch metrics:

DynamoDB: ConsumedReadCapacityUnits, ThrottledRequests
RDS: DatabaseConnections, ReadLatency, WriteLatency

Alerting:

DynamoDB throttling → increase capacity or switch to on-demand
RDS connection saturation → increase pool size or instance size

Takeaways for Developers

Summary: Hybrid shines for mixed workloads.

When to Use Hybrid Architecture

✅ Perfect for:

Mix of simple and complex access patterns
High-volume simple queries + low-volume complex queries
Teams with both NoSQL and SQL expertise
Need for both global scale (DynamoDB) and relational queries (SQL)

❌ Not ideal for:

All queries are simple (use DynamoDB only)
All queries are complex (use MySQL only)
Small team with limited ops capacity (maintain one database)
Tight budget (two databases = higher cost)

Key Principles

Match database to access pattern (not team preference)
Clear entity ownership (no cross-database JOINs)
Type-safe ORM for SQL (Drizzle/Prisma)
Connection pooling for MySQL
Separate monitoring per database

Quick Start Guide

1. Identify access patterns:

Simple key-value lookups → DynamoDB
Complex multi-table JOINs → MySQL

2. Set up Drizzle ORM:

// schema.ts
export const product = mysqlTable("product", {
  productId: int("productId").primaryKey(),
  productCode: varchar("productCode", { length: 50 }).notNull(),
});

// query.ts
const products = await db
  .select()
  .from(product)
  .where(eq(product.productCode, "PROD123"));

3. Implement connection pooling:

let dbClient: MySql2Database;

if (!dbClient) {
  dbClient = await createDatabaseConnection();
}

Conclusion

Hybrid architecture transformed our menu configuration service from a slow monolithic MySQL system into a high-performance service with 8ms DynamoDB queries and 45ms complex MySQL queries.

The impact:

70% latency improvement on simple queries
60% cost reduction vs. all-MySQL
50% faster query development
70% fewer query bugs

But the real win? Developers choose the right tool for each problem. No more forcing relational data into NoSQL or vice versa.

If you're building services with mixed access patterns, hybrid architecture is worth considering. Your queries (and your wallet) will thank you.

Related Articles:

"Pricing Service: DynamoDB Patterns for GraphQL"
"Product Service: Solving N+1 Queries with Drizzle ORM"
"Building Production GraphQL APIs: Database Selection Guide"

Originally published on [your blog/medium] • 16 min read

Portfolio