/aurora-dsql

MySQL to DSQL Migration: Full Example

End-to-end example migrating a complete MySQL CREATE TABLE to DSQL.

MUST read type-mapping.md first for data type mappings and the CRITICAL Destructive Operations Warning. MUST read ddl-operations.md for DDL operation patterns.

Original MySQL Schema

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description MEDIUMTEXT,
  price DECIMAL(10,2) NOT NULL,
  category ENUM('electronics', 'clothing', 'food', 'other') DEFAULT 'other',
  tags SET('sale', 'new', 'featured'),
  metadata JSON,
  stock INT UNSIGNED DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  INDEX idx_tenant (tenant_id),
  INDEX idx_category (category),
  FULLTEXT INDEX idx_name_desc (name, description)
) ENGINE=InnoDB;

Migrated DSQL Schema

-- Step 1: Create table (one DDL per transaction)
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  category VARCHAR(255) DEFAULT 'other' CHECK (category IN ('electronics', 'clothing', 'food', 'other')),
  tags TEXT,
  metadata TEXT,
  stock INTEGER DEFAULT 0 CHECK (stock >= 0),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Create indexes (each in separate transaction, MUST use ASYNC)
CREATE INDEX ASYNC idx_products_tenant ON products(tenant_id);
CREATE INDEX ASYNC idx_products_category ON products(tenant_id, category);
-- MUST implement text search at application layer for FULLTEXT index equivalent

Migration Decisions Summary

Best Practices Summary

User Verification (CRITICAL)

• MUST present complete migration plan to user before any execution
• MUST obtain explicit user confirmation before DROP TABLE operations
• MUST verify with user at each checkpoint during migration
• MUST obtain explicit user approval before proceeding with destructive actions
• MUST recommend testing migrations on non-production data first
• MUST confirm user has backup or accepts data loss risk

MySQL-Specific Migration Rules

• MUST map all MySQL data types to DSQL equivalents before creating tables
• MUST convert AUTO_INCREMENT to one of: UUID with gen_random_uuid() (preferred for distributed workloads), IDENTITY column with GENERATED AS IDENTITY (CACHE ...), or explicit SEQUENCE. When choosing integer auto-increment, ALWAYS use GENERATED AS IDENTITY syntax (not SERIAL). See AUTO_INCREMENT Migration.
• MUST replace ENUM with VARCHAR and CHECK constraint
• MUST replace SET with TEXT (comma-separated)
• MUST replace JSON columns with TEXT
• MUST replace FOREIGN KEY constraints with application-layer referential integrity
• MUST replace ON UPDATE CURRENT_TIMESTAMP with application-layer updates
• MUST convert all index creation to use CREATE INDEX ASYNC
• MUST omit ENGINE, CHARSET, COLLATE, and other MySQL-specific table options
• MUST replace UNSIGNED with CHECK (col >= 0) constraint
• MUST convert TINYINT(1) to BOOLEAN

Technical Requirements

• MUST validate data compatibility before type changes
• MUST batch tables exceeding 3,000 rows
• MUST verify row counts before and after migration
• MUST recreate indexes after table swap using ASYNC
• MUST verify new table before dropping original table
• PREFER cursor-based batching for very large tables
• PREFER batches of 500-1,000 rows for optimal throughput