MySQL/MariaDB to HeliosDB-Lite Migration Guide

MySQL/MariaDB to HeliosDB-Lite Migration Guide

Category: Database Migration HeliosDB-Lite Version: 3.5+

---

Overview

MySQL and MariaDB are among the most widely deployed relational databases in the world, powering millions of web applications, SaaS platforms, and enterprise systems. While MySQL excels as a general-purpose RDBMS, organizations increasingly find themselves constrained by its client-server architecture, cloud hosting costs, and feature gaps in areas like JSONB, full-text search, vector similarity, and time-travel queries.

HeliosDB-Lite speaks the PostgreSQL wire protocol, which means any standard PostgreSQL client library (psycopg2, node-postgres, pgx, etc.) connects directly. The migration path from MySQL is: export your data via mysqldump, transform the SQL dialect, and import through the PostgreSQL wire protocol. Most applications require only connection string changes and minor query adjustments.

This guide walks through every step of the migration: data type mapping, query translation, tooling, common patterns, limitations, and rollback strategy.

---

Why Migrate?

Cost Reduction

Cost Factor	MySQL (Managed Cloud)	HeliosDB-Lite (Embedded)	Savings
Database hosting (RDS db.m5.large)	$1,200/month	$0 (embedded in app)	100%
Data transfer (cross-AZ)	$400/month	$0 (in-process)	100%
Connection pooling (ProxySQL/RDS Proxy)	$150/month	$0 (not needed)	100%
DBA labor (partial FTE)	$4,000/month	$0 (zero admin)	100%
Total annual	~$69,000	~$1,500 (compute only)	97%

Performance Gains

Every MySQL query traverses the network stack — even on localhost, TCP adds 1-5ms overhead. HeliosDB-Lite executes in-process, eliminating network round-trips entirely.

Operation	MySQL (RDS)	HeliosDB-Lite	Improvement
Simple SELECT (1 row)	48ms P95	15ms P95	69% faster
INSERT (single)	72ms P95	22ms P95	69% faster
Transaction (10 stmts)	280ms P95	85ms P95	70% faster
JOIN query (3 tables)	165ms P95	55ms P95	67% faster

Feature Gains

HeliosDB-Lite provides capabilities that MySQL lacks entirely:

• Vector search: HNSW indexes with SIMD-accelerated similarity (cosine, L2, dot product)
• Time-travel queries: SELECT * FROM orders AS OF '2025-01-15T10:00:00Z'
• Database branching: Create isolated branches for testing or feature development
• Transparent Data Encryption: AES-256-GCM at rest with key rotation
• Native JSONB: Binary JSON with indexing, containment operators (@>), and 20+ functions
• Full-text search: @@ operator with ranking, stemming, and phrase matching
• Window functions: Full suite (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, etc.)
• Recursive CTEs: WITH RECURSIVE for hierarchical data traversal
• PL/pgSQL: Stored procedures with variables, loops, cursors, and exception handling
• Embedded deployment: Single-binary, zero-config, no separate database server

---

Compatibility Matrix

Feature	MySQL 8.0+	MariaDB 10.6+	HeliosDB-Lite 3.5+	Migration Effort
SELECT/INSERT/UPDATE/DELETE	Yes	Yes	Yes	None
JOINs (INNER, LEFT, RIGHT, FULL, CROSS)	Yes	Yes	Yes	None
Subqueries (scalar, correlated)	Yes	Yes	Yes	None
CTEs (WITH clause)	Yes (8.0+)	Yes (10.2+)	Yes	None
Recursive CTEs	Yes (8.0+)	Yes (10.2+)	Yes	None
Window functions	Yes (8.0+)	Yes (10.2+)	Yes	None
UNION / INTERSECT / EXCEPT	Yes	Yes	Yes	None
Transactions (BEGIN/COMMIT/ROLLBACK)	Yes	Yes	Yes	None
SAVEPOINT	Yes	Yes	Yes	None
Prepared statements	Yes	Yes	Yes	None
JSON functions	Yes (limited)	Yes (limited)	Yes (JSONB, 20+ funcs)	Minor syntax changes
Full-text search	FULLTEXT index	FULLTEXT index	@@ operator + FTS index	Rewrite queries
Stored procedures	Yes (SQL/PSM)	Yes (SQL/PSM)	Yes (PL/pgSQL)	Rewrite procedures
Triggers	Yes	Yes	Yes	Minor syntax changes
AUTO_INCREMENT	Yes	Yes	SERIAL / GENERATED	DDL change
ENUM type	Yes	Yes	TEXT + CHECK constraint	DDL change
Partitioning	Yes	Yes	Yes (HASH/RANGE/LIST)	DDL change
Replication	Yes	Yes	Yes (WAL streaming)	Architecture change
Vector search	No	No	Yes (HNSW)	New capability
Time-travel	No	No	Yes (AS OF)	New capability
Branching	No	No	Yes	New capability
Encryption at rest	Enterprise only	Yes (plugin)	Yes (TDE, AES-256-GCM)	Configuration

---

Migration Steps

Step 1: Export from MySQL

Use mysqldump with PostgreSQL-compatible options:

# Export schema and data from MySQL
mysqldump \
  --host=mysql-server.example.com \
  --user=admin \
  --password \
  --databases myapp \
  --no-create-db \
  --compatible=postgresql \
  --default-character-set=utf8mb4 \
  --single-transaction \
  --routines \
  --triggers \
  --result-file=mysql_export.sql

Step 2: Transform the SQL Dialect

MySQL’s SQL dialect differs from PostgreSQL in several ways. Use a transformation script to handle the conversion:

#!/usr/bin/env python3
"""Transform mysqldump output for HeliosDB-Lite (PostgreSQL wire protocol) import."""

import re
import sys

def transform_mysql_to_helios(input_path: str, output_path: str):
    with open(input_path, 'r', encoding='utf-8') as f:
        sql = f.read()

    # Remove MySQL-specific settings
    sql = re.sub(r'/\*![\d]+ .*?\*/;?\n?', '', sql)
    sql = re.sub(r'SET @.*?;\n', '', sql)
    sql = re.sub(r'SET character_set.*?;\n', '', sql)
    sql = re.sub(r'LOCK TABLES.*?;\n', '', sql)
    sql = re.sub(r'UNLOCK TABLES;\n', '', sql)

    # Replace backtick quoting with double quotes
    sql = sql.replace('`', '"')

    # Replace AUTO_INCREMENT with SERIAL in CREATE TABLE
    sql = re.sub(
        r'(\w+)\s+(?:INT|INTEGER|BIGINT)\s+(?:NOT NULL\s+)?AUTO_INCREMENT',
        r'\1 SERIAL',
        sql,
        flags=re.IGNORECASE
    )

    # Remove ENGINE=InnoDB and other engine specifications
    sql = re.sub(r'\)\s*ENGINE\s*=\s*\w+[^;]*;', ');', sql, flags=re.IGNORECASE)

    # Replace DATETIME with TIMESTAMPTZ
    sql = re.sub(r'\bDATETIME\b', 'TIMESTAMPTZ', sql, flags=re.IGNORECASE)

    # Replace TINYINT(1) with BOOLEAN
    sql = re.sub(r'\bTINYINT\s*\(\s*1\s*\)', 'BOOLEAN', sql, flags=re.IGNORECASE)

    # Replace VARCHAR(N) with TEXT (HeliosDB uses TEXT for all strings)
    sql = re.sub(r'\bVARCHAR\s*\(\s*\d+\s*\)', 'TEXT', sql, flags=re.IGNORECASE)

    # Replace LONGTEXT, MEDIUMTEXT, TINYTEXT with TEXT
    sql = re.sub(r'\b(?:LONG|MEDIUM|TINY)TEXT\b', 'TEXT', sql, flags=re.IGNORECASE)

    # Replace LONGBLOB, MEDIUMBLOB, TINYBLOB with BYTEA
    sql = re.sub(r'\b(?:LONG|MEDIUM|TINY)?BLOB\b', 'BYTEA', sql, flags=re.IGNORECASE)

    # Replace DOUBLE with FLOAT8
    sql = re.sub(r'\bDOUBLE\b', 'FLOAT8', sql, flags=re.IGNORECASE)

    # Replace ENUM('a','b','c') with TEXT CHECK(col IN ('a','b','c'))
    def replace_enum(match):
        values = match.group(1)
        return f"TEXT"  # CHECK constraints added separately
    sql = re.sub(r"ENUM\s*\(([^)]+)\)", replace_enum, sql, flags=re.IGNORECASE)

    # Replace JSON type with JSONB
    sql = re.sub(r'\bJSON\b', 'JSONB', sql, flags=re.IGNORECASE)

    # Replace IFNULL with COALESCE
    sql = re.sub(r'\bIFNULL\s*\(', 'COALESCE(', sql, flags=re.IGNORECASE)

    # Replace NOW() -- already compatible, keep as-is
    # Replace CURDATE() with CURRENT_DATE
    sql = re.sub(r'\bCURDATE\s*\(\s*\)', 'CURRENT_DATE', sql, flags=re.IGNORECASE)

    # Replace CURTIME() with CURRENT_TIME
    sql = re.sub(r'\bCURTIME\s*\(\s*\)', 'CURRENT_TIME', sql, flags=re.IGNORECASE)

    # Replace UNIX_TIMESTAMP() with EXTRACT(EPOCH FROM NOW())
    sql = re.sub(
        r'\bUNIX_TIMESTAMP\s*\(\s*\)',
        "EXTRACT(EPOCH FROM NOW())::INT",
        sql,
        flags=re.IGNORECASE
    )

    with open(output_path, 'w', encoding='utf-8') as f:
        f.write(sql)

    print(f"Transformed {input_path} -> {output_path}")

if __name__ == "__main__":
    transform_mysql_to_helios(sys.argv[1], sys.argv[2])

Step 3: Import into HeliosDB-Lite

Since HeliosDB-Lite speaks the PostgreSQL wire protocol, use psql to import:

# Start HeliosDB-Lite with PostgreSQL wire protocol enabled
heliosdb-lite serve --listen 127.0.0.1:5432 --data ./myapp.db &

# Import the transformed SQL
psql "postgresql://localhost:5432/myapp" -f helios_import.sql

# Verify table counts
psql "postgresql://localhost:5432/myapp" -c "
  SELECT schemaname, tablename
  FROM pg_tables
  WHERE schemaname = 'public'
  ORDER BY tablename;
"

Step 4: Update Application Connection Strings

Python (before — MySQL):

import mysql.connector

conn = mysql.connector.connect(
    host="mysql-server.example.com",
    user="admin",
    password="secret",
    database="myapp"
)

Python (after — HeliosDB-Lite via psycopg2):

import psycopg2

conn = psycopg2.connect(
    host="127.0.0.1",
    port=5432,
    user="app",
    dbname="myapp"
)

TypeScript (before — MySQL):

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'mysql-server.example.com',
  user: 'admin',
  password: 'secret',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
});

TypeScript (after — HeliosDB-Lite via pg):

import { Pool } from 'pg';

const pool = new Pool({
  host: '127.0.0.1',
  port: 5432,
  user: 'app',
  database: 'myapp',
  max: 5,  // Embedded DB needs fewer connections
});

Go (before — MySQL):

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

db, err := sql.Open("mysql", "admin:secret@tcp(mysql-server:3306)/myapp")

Go (after — HeliosDB-Lite via pgx):

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib"
)

db, err := sql.Open("pgx", "postgresql://app@127.0.0.1:5432/myapp")

Step 5: Validate and Test

-- Verify row counts match
SELECT 'users' AS table_name, COUNT(*) AS row_count FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'products', COUNT(*) FROM products;

-- Test key queries
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.name
ORDER BY total_spent DESC
LIMIT 10;

---

Data Type Mapping

MySQL / MariaDB Type	HeliosDB-Lite Type	Notes
TINYINT	INT2	2-byte integer
SMALLINT	INT2	Direct equivalent
MEDIUMINT	INT4	Upsize to 4-byte integer
INT / INTEGER	INT4	Direct equivalent
BIGINT	INT8	Direct equivalent
FLOAT	FLOAT4	4-byte floating point
DOUBLE	FLOAT8	8-byte floating point
DECIMAL(p,s)	NUMERIC(p,s)	Exact numeric
CHAR(n)	TEXT	HeliosDB uses TEXT for all strings
VARCHAR(n)	TEXT	HeliosDB uses TEXT for all strings
TINYTEXT	TEXT	Direct mapping
TEXT	TEXT	Direct mapping
MEDIUMTEXT	TEXT	Direct mapping
LONGTEXT	TEXT	Direct mapping
BINARY(n)	BYTEA	Binary data
VARBINARY(n)	BYTEA	Binary data
TINYBLOB	BYTEA	Binary data
BLOB	BYTEA	Binary data
MEDIUMBLOB	BYTEA	Binary data
LONGBLOB	BYTEA	Binary data
DATE	DATE	Direct equivalent
TIME	TIME	Direct equivalent
DATETIME	TIMESTAMPTZ	Timezone-aware timestamp
TIMESTAMP	TIMESTAMPTZ	Timezone-aware timestamp
YEAR	INT2	Store as integer
BOOLEAN / TINYINT(1)	BOOLEAN	Direct equivalent
ENUM('a','b','c')	TEXT + CHECK constraint	See pattern below
SET('a','b','c')	TEXT[] (ARRAY) or JSONB	Use array or JSON array
JSON	JSONB	Binary JSON with indexing
AUTO_INCREMENT	SERIAL / BIGSERIAL	Auto-incrementing primary key
GEOMETRY / POINT	JSONB	Store as GeoJSON
BIT(n)	INT4 or BYTEA	Depends on usage

ENUM Replacement Pattern

MySQL:

CREATE TABLE tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  status ENUM('open', 'in_progress', 'resolved', 'closed') NOT NULL DEFAULT 'open'
);

HeliosDB-Lite:

CREATE TABLE tickets (
  id SERIAL PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'in_progress', 'resolved', 'closed'))
);

---

Query Translation Examples

LIMIT / OFFSET (Compatible)

MySQL and HeliosDB-Lite use the same syntax:

-- Works identically in both
SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

IFNULL —> COALESCE

-- MySQL
SELECT IFNULL(nickname, username) AS display_name FROM users;

-- HeliosDB-Lite
SELECT COALESCE(nickname, username) AS display_name FROM users;

GROUP_CONCAT —> STRING_AGG

-- MySQL
SELECT department_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY department_id;

-- HeliosDB-Lite
SELECT department_id, STRING_AGG(name, ', ' ORDER BY name) AS members
FROM employees
GROUP BY department_id;

INSERT IGNORE —> ON CONFLICT DO NOTHING

-- MySQL
INSERT IGNORE INTO tags (name) VALUES ('rust'), ('database'), ('embedded');

-- HeliosDB-Lite
INSERT INTO tags (name) VALUES ('rust'), ('database'), ('embedded')
ON CONFLICT DO NOTHING;

REPLACE INTO —> UPSERT

-- MySQL
REPLACE INTO settings (key, value) VALUES ('theme', 'dark');

-- HeliosDB-Lite
INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

INSERT … ON DUPLICATE KEY UPDATE —> ON CONFLICT DO UPDATE

-- MySQL
INSERT INTO counters (page_url, visit_count)
VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE visit_count = visit_count + 1;

-- HeliosDB-Lite
INSERT INTO counters (page_url, visit_count)
VALUES ('/home', 1)
ON CONFLICT (page_url) DO UPDATE SET visit_count = counters.visit_count + 1;

Backtick Quoting —> Double Quotes

-- MySQL
SELECT `user`.`name`, `order`.`total`
FROM `user`
JOIN `order` ON `user`.`id` = `order`.`user_id`;

-- HeliosDB-Lite
SELECT "user"."name", "order"."total"
FROM "user"
JOIN "order" ON "user"."id" = "order"."user_id";

Note: Only reserved words need quoting. Prefer using non-reserved identifiers (e.g., users instead of user) to avoid quoting entirely.

DATE_FORMAT —> TO_CHAR

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS formatted FROM orders;

-- HeliosDB-Lite
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI') AS formatted FROM orders;

LAST_INSERT_ID() —> RETURNING

-- MySQL (two separate statements)
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT LAST_INSERT_ID();

-- HeliosDB-Lite (single statement with RETURNING)
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
RETURNING id;

JSON Functions

-- MySQL: Extract JSON value
SELECT JSON_EXTRACT(metadata, '$.color') FROM products;
SELECT metadata->>'$.color' FROM products;  -- MySQL 8.0+

-- HeliosDB-Lite: JSONB operators
SELECT metadata->>'color' FROM products;
SELECT metadata->'nested'->'key' FROM products;

-- MySQL: Check if JSON contains key
SELECT * FROM products WHERE JSON_CONTAINS_PATH(metadata, 'one', '$.color');

-- HeliosDB-Lite: Key existence operator
SELECT * FROM products WHERE metadata ? 'color';

-- MySQL: Check if JSON contains value
SELECT * FROM products WHERE JSON_CONTAINS(metadata, '"red"', '$.color');

-- HeliosDB-Lite: Containment operator
SELECT * FROM products WHERE metadata @> '{"color": "red"}';

Full-Text Search

-- MySQL: FULLTEXT index
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database migration' IN BOOLEAN MODE);

-- HeliosDB-Lite: FTS with @@ operator
CREATE INDEX idx_articles_fts ON articles USING gin(to_tsvector('english', title || ' ' || body));
SELECT * FROM articles WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('database & migration');

Stored Procedures

-- MySQL
DELIMITER //
CREATE PROCEDURE get_user_orders(IN p_user_id INT)
BEGIN
  SELECT o.id, o.total, o.created_at
  FROM orders o
  WHERE o.user_id = p_user_id
  ORDER BY o.created_at DESC;
END //
DELIMITER ;

CALL get_user_orders(42);

-- HeliosDB-Lite (PL/pgSQL)
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id INT4)
RETURNS TABLE(id INT4, total NUMERIC, created_at TIMESTAMPTZ)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT o.id, o.total, o.created_at
    FROM orders o
    WHERE o.user_id = p_user_id
    ORDER BY o.created_at DESC;
END;
$$;

SELECT * FROM get_user_orders(42);

Triggers

-- MySQL
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END;

-- HeliosDB-Lite
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- Where the function is:
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

---

Common Patterns

Pattern 1: Migrating a Python/Django Application

# settings.py -- change one block
DATABASES = {
    'default': {
        # BEFORE: MySQL
        # 'ENGINE': 'django.db.backends.mysql',
        # 'HOST': 'mysql-server.example.com',
        # 'PORT': '3306',

        # AFTER: HeliosDB-Lite (PostgreSQL wire protocol)
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '127.0.0.1',
        'PORT': '5432',
        'NAME': 'myapp',
        'USER': 'app',
        'PASSWORD': '',
    }
}

Django models require zero changes. The ORM abstracts the SQL dialect automatically.

Pattern 2: Migrating a TypeScript/Node.js Application

import { Pool } from 'pg';

const pool = new Pool({
  host: '127.0.0.1',
  port: 5432,
  database: 'myapp',
  user: 'app',
});

// CRUD operations work identically
async function createUser(name: string, email: string): Promise<number> {
  const result = await pool.query(
    'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id',
    [name, email]
  );
  return result.rows[0].id;
}

async function getUserOrders(userId: number) {
  const result = await pool.query(`
    SELECT o.id, o.total, o.status,
           ROW_NUMBER() OVER (ORDER BY o.created_at DESC) AS order_rank
    FROM orders o
    WHERE o.user_id = $1
    ORDER BY o.created_at DESC
    LIMIT 20
  `, [userId]);
  return result.rows;
}

// HeliosDB-Lite exclusive features
async function searchProducts(query: string) {
  // Full-text search with ranking
  const result = await pool.query(`
    SELECT id, name, description,
           ts_rank(to_tsvector('english', name || ' ' || description),
                   to_tsquery('english', $1)) AS relevance
    FROM products
    WHERE to_tsvector('english', name || ' ' || description) @@ to_tsquery('english', $1)
    ORDER BY relevance DESC
    LIMIT 10
  `, [query]);
  return result.rows;
}

async function findSimilarProducts(embedding: number[]) {
  // Vector similarity search (not possible in MySQL)
  const result = await pool.query(`
    SELECT id, name, price,
           embedding <-> $1::vector AS distance
    FROM products
    ORDER BY embedding <-> $1::vector
    LIMIT 5
  `, [JSON.stringify(embedding)]);
  return result.rows;
}

Pattern 3: Migrating a Go Microservice

package main

import (
    "context"
    "fmt"
    "log"

    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    ctx := context.Background()

    // Connect via PostgreSQL wire protocol
    pool, err := pgxpool.New(ctx, "postgresql://app@127.0.0.1:5432/myapp")
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()

    // Standard CRUD -- works identically to MySQL
    var userID int
    err = pool.QueryRow(ctx,
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
        "Alice", "alice@example.com",
    ).Scan(&userID)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Created user ID: %d\n", userID)

    // UPSERT (replaces MySQL's INSERT ... ON DUPLICATE KEY UPDATE)
    _, err = pool.Exec(ctx, `
        INSERT INTO user_preferences (user_id, theme, language)
        VALUES ($1, $2, $3)
        ON CONFLICT (user_id) DO UPDATE
        SET theme = EXCLUDED.theme, language = EXCLUDED.language
    `, userID, "dark", "en")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Upserted user preferences")

    // JSONB queries (richer than MySQL JSON)
    rows, err := pool.Query(ctx, `
        SELECT id, name, metadata->>'category' AS category
        FROM products
        WHERE metadata @> '{"in_stock": true}'
        ORDER BY (metadata->>'rating')::FLOAT8 DESC
        LIMIT 10
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name, category string
        rows.Scan(&id, &name, &category)
        fmt.Printf("  Product %d: %s (%s)\n", id, name, category)
    }

    // Time-travel query (MySQL cannot do this)
    rows2, err := pool.Query(ctx, `
        SELECT * FROM orders AS OF '2025-06-01T00:00:00Z'
        WHERE user_id = $1
    `, userID)
    if err != nil {
        log.Fatal(err)
    }
    defer rows2.Close()
    fmt.Println("Time-travel query executed successfully")
}

Pattern 4: Batch Data Migration Script

#!/usr/bin/env python3
"""Migrate data from MySQL to HeliosDB-Lite in batches."""

import mysql.connector
import psycopg2
import psycopg2.extras

BATCH_SIZE = 5000

def migrate_table(mysql_conn, helios_conn, table_name: str):
    """Migrate a single table with batched inserts."""
    mysql_cur = mysql_conn.cursor(dictionary=True)
    mysql_cur.execute(f"SELECT COUNT(*) AS cnt FROM {table_name}")
    total = mysql_cur.fetchone()['cnt']
    print(f"  Migrating {table_name}: {total} rows")

    mysql_cur.execute(f"SELECT * FROM {table_name}")
    columns = [desc[0] for desc in mysql_cur.description]
    placeholders = ', '.join([f'%({c})s' for c in columns])
    insert_sql = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders})"

    helios_cur = helios_conn.cursor()
    batch = []
    migrated = 0

    for row in mysql_cur:
        batch.append(row)
        if len(batch) >= BATCH_SIZE:
            psycopg2.extras.execute_batch(helios_cur, insert_sql, batch)
            helios_conn.commit()
            migrated += len(batch)
            batch = []
            print(f"    {migrated}/{total} rows migrated")

    if batch:
        psycopg2.extras.execute_batch(helios_cur, insert_sql, batch)
        helios_conn.commit()
        migrated += len(batch)

    print(f"    {migrated}/{total} rows migrated (complete)")

def main():
    # Source: MySQL
    mysql_conn = mysql.connector.connect(
        host="mysql-server.example.com",
        user="admin",
        password="secret",
        database="myapp"
    )

    # Destination: HeliosDB-Lite (via PostgreSQL wire protocol)
    helios_conn = psycopg2.connect(
        host="127.0.0.1",
        port=5432,
        user="app",
        dbname="myapp"
    )

    # Get table list from MySQL
    cursor = mysql_conn.cursor()
    cursor.execute("SHOW TABLES")
    tables = [row[0] for row in cursor]

    print(f"Migrating {len(tables)} tables from MySQL to HeliosDB-Lite\n")

    for table in tables:
        migrate_table(mysql_conn, helios_conn, table)

    # Verify counts match
    print("\nVerification:")
    for table in tables:
        cursor.execute(f"SELECT COUNT(*) FROM {table}")
        mysql_count = cursor.fetchone()[0]

        h_cursor = helios_conn.cursor()
        h_cursor.execute(f"SELECT COUNT(*) FROM {table}")
        helios_count = h_cursor.fetchone()[0]

        status = "OK" if mysql_count == helios_count else "MISMATCH"
        print(f"  {table}: MySQL={mysql_count}, HeliosDB={helios_count} [{status}]")

    mysql_conn.close()
    helios_conn.close()
    print("\nMigration complete.")

if __name__ == "__main__":
    main()

---

Limitations & Workarounds

MySQL Features Not Directly Supported

MySQL Feature	Status	Workaround
ENUM type	Not native	Use TEXT with CHECK(col IN (...)) constraint
SET type	Not native	Use TEXT[] (array) or JSONB array
UNSIGNED integers	Not native	Use next-larger signed type (e.g., INT4 for UNSIGNED SMALLINT)
ZEROFILL	Not supported	Use LPAD(col::TEXT, n, '0') in queries
GROUP_CONCAT	Different syntax	Use STRING_AGG(col, separator)
FOUND_ROWS()	Not supported	Use window function: COUNT(*) OVER()
SQL_CALC_FOUND_ROWS	Not supported	Use COUNT(*) OVER() in the query
HANDLER statements	Not supported	Use standard SELECT with cursors
LOAD DATA INFILE	Not supported	Use INSERT batches or COPY command
MySQL-specific hints	Not supported	HeliosDB optimizer handles query planning automatically
@@session variables	Not supported	Use SET / SHOW for configuration
Spatial/GIS functions	Not native	Store as JSONB (GeoJSON); compute in application layer
MySQL events (scheduler)	Not supported	Use external scheduler (cron, application-level timers)
MySQL user-defined variables (@var)	Not supported	Use CTEs or subqueries instead

Character Set Considerations

MySQL defaults vary by version (latin1 or utf8mb4). HeliosDB-Lite uses UTF-8 internally. Ensure your MySQL export uses --default-character-set=utf8mb4 to avoid encoding issues.

Transaction Isolation

MySQL InnoDB defaults to REPEATABLE READ. HeliosDB-Lite defaults to READ COMMITTED. If your application depends on gap locking or consistent non-locking reads within a transaction, test behavior carefully after migration.

Auto-Increment Gaps

MySQL’s AUTO_INCREMENT can produce gaps (e.g., after rollbacks or deletes). HeliosDB-Lite’s SERIAL type behaves similarly — gaps are normal and expected. Do not rely on sequential, gap-free IDs.

---

Rollback Strategy

A safe migration preserves the ability to revert to MySQL if issues arise.

Pre-Migration

1. Keep MySQL running — do not decommission until HeliosDB-Lite has been validated in production for at least 2 weeks
2. Dual-write period — optionally write to both MySQL and HeliosDB-Lite during the transition window
3. Snapshot MySQL — take a full backup before starting migration

During Migration

# Create MySQL backup before any changes
mysqldump --host=mysql-server --user=admin --password \
  --all-databases --single-transaction --routines --triggers \
  --result-file=pre_migration_backup.sql

# Take HeliosDB-Lite snapshot (for rollback within HeliosDB)
psql "postgresql://localhost:5432/myapp" -c "
  SELECT helios_create_branch('pre_migration_snapshot');
"

Rollback Procedure

If critical issues are found after switching to HeliosDB-Lite:

1. Revert connection strings to point back to MySQL
2. Replay writes — if dual-write was enabled, no data loss occurs. Otherwise, export delta data from HeliosDB-Lite and import into MySQL:

import psycopg2
import mysql.connector

# Export recent changes from HeliosDB-Lite
helios_conn = psycopg2.connect("postgresql://localhost:5432/myapp")
mysql_conn = mysql.connector.connect(host="mysql-server", user="admin",
                                     password="secret", database="myapp")

cursor = helios_conn.cursor()
cursor.execute("""
    SELECT id, name, email, created_at
    FROM users
    WHERE created_at > %s
""", (cutover_timestamp,))

mysql_cursor = mysql_conn.cursor()
for row in cursor:
    mysql_cursor.execute(
        "INSERT INTO users (id, name, email, created_at) VALUES (%s, %s, %s, %s) "
        "ON DUPLICATE KEY UPDATE name=VALUES(name), email=VALUES(email)",
        row
    )
mysql_conn.commit()

3. Validate MySQL — confirm all data is present and application functions correctly
4. Post-mortem — document what went wrong to address before the next migration attempt

---

References

1. MySQL 8.0 Reference Manual: SQL Syntax Differences (2025)
2. MariaDB Knowledge Base: PostgreSQL Compatibility (2025)
3. HeliosDB-Lite Documentation: PostgreSQL Wire Protocol Specification (2026)
4. psycopg2 Documentation: Python PostgreSQL Adapter (2025)
5. node-postgres (pg): TypeScript/JavaScript PostgreSQL Client (2025)
6. pgx: Go PostgreSQL Driver and Toolkit (2025)
7. Django Database Backends: PostgreSQL Configuration Guide (2025)
8. AWS RDS Pricing: MySQL vs. Self-Hosted Cost Analysis (2025)