Why Chengwu Low-Code Chose MySQL 8 Over Elasticsearch for Dynamic Tables

Low-Code Platform Data Storage Requirements

The Chengwu low-code platform identified five core requirements for its data layer:

Dynamic data structures : Metadata-driven schema where tables and fields can be created, modified, and extended at runtime.

High concurrency and availability : Multi-tenant, cross-scenario workloads with complex queries.

Security and compliance : Row/column-level permissions, encryption, audit logging.

Horizontal scalability : Elastic growth as business data volume increases.

Reporting, analytics, and full-text search : Aggregation, grouping, and text indexing needs.

MySQL 8 Core Advantages Over MySQL 5

1. Character Set and Collation Improvements

MySQL 5 defaulted to latin1 / utf8 (incomplete UTF-8, emoji storage issues).

MySQL 8 defaults to utf8mb4, native full Unicode support including emoji, smarter collations, suitable for multi-language, multi-tenant platforms.

2. Window Functions and CTE Support

Window functions ( ROW_NUMBER, RANK, LEAD, LAG) dramatically improve SQL performance and readability for complex reporting.

Common Table Expressions (CTEs) enable recursive queries and multi-level analysis; MySQL 5 had no support.

3. Native JSON Type and Generated (Virtual) Columns

Native JSON type provides NoSQL flexibility with ACID transaction safety.

Virtual columns can automatically extract and index values from JSON documents, greatly boosting dynamic field query efficiency and enabling a hybrid structured/semi-structured data model.

4. Performance and Concurrency Gains

InnoDB rewrite: transaction processing, MVCC, global temporary tables re-architected.

Significant concurrent read/write improvement, better hot-table/hot-spot handling for multi-tenant high-traffic apps.

Enhanced GIS spatial types and indexes for IoT and mapping use cases.

5. Enhanced Security and Role Management

Richer role and privilege system with fine-grained control, stronger auditing, meeting large-customer compliance needs.

6. Additional Experience Improvements

Native full-text indexing with Chinese word segmentation and multi-language support.

Improved data dictionary for self-describing schema and metadata management.

Native XA distributed transaction support for data consistency.

MySQL 8 is fundamentally designed for modern applications, complex scenarios, and cloud-native environments, giving low-code platforms enterprise-grade database capabilities while retaining open-source ecosystem benefits.

Dynamic Table Mechanism — Core Low-Code Requirement

Chengwu built a Dynamic Table mechanism on MySQL 8:

Users/admins create business tables dynamically via UI, adding/deleting/modifying fields and custom data types.

Platform auto-generates physical MySQL tables and maintains metadata tables mapping each business table's fields and types.

Supports cross-table joins, complex filtering, grouping, import/export, and permission configuration without writing SQL.

Core Advantages

Extreme flexibility : New business lines launch without DBA involvement; operations staff define schemas — "business is data".

Low-cost scalability : No repeated table creation or column migrations; unlimited extension; data and schema decoupled.

Security and control : All dynamic table changes are auditable and rollback-capable.

SQL compatibility : All queries translate to standard SQL, easing secondary development and analytics.

This mechanism fuses relational structure with NoSQL flexibility, heavily relying on MySQL 8's data types, JSON, virtual columns, and CTEs.

Why Not Elasticsearch as Primary Database?

1. ES Is a Search Engine, Not a Strong Transaction Store

ES is built for full-text search and aggregation analytics — suited for logs, sentiment, IoT (weak transaction, write-heavy).

Low-code core data (master tables, workflows, users, forms, approvals) demands ACID consistency, strong security, rollback — ES cannot deliver.

ES only achieves eventual consistency; failover risks data loss, difficult rollback, dirty data — unfit for core data foundation.

2. ES Data Model Weaknesses

JSON document storage lacks cross-table strong constraints, transactions, foreign keys, uniqueness — business data becomes scattered and uncontrolled.

Dynamic tables, joins, grouping, aggregation: SQL capability far weaker than MySQL; complex reports require custom code, high maintenance.

3. High Resource Consumption and Operational Complexity

ES clusters consume far more memory, disk, CPU than MySQL; maintenance, migration, upgrade costs high — difficult for small teams.

Index bloat, fragmentation, shard routing are steep learning curves.

4. Security and Compliance Gaps

ES permission system weaker than MySQL (X-Pack is paid); row/column-level security, encryption, compliance auditing require custom development.

Exposed API ports have led to public data leaks; security compliance is a hard flaw.

5. Ecosystem and Operational Barriers

MySQL: mature ecosystem, rich tooling, abundant talent, comprehensive docs.

ES: talent scarcity, fragmented community, expensive tooling; higher tuning difficulty.

6. Motivations and Blind Spots of Platforms Choosing ES

Some vendors chase "cool features" or heavy search needs, going all-in on ES.

Pros: fast search, flexible aggregation — good for log platforms, data hubs, intelligent search, reporting.

Cons: Using ES as primary store is high-risk — only fits weak-consistency, no-transaction scenarios. Core business (approvals, finance, CRM) on ES leads to maintenance quagmire, migration nightmares, or data disasters.

Best practice: cold-hot separation — core data in MySQL, search/reporting/logs in ES. This is Chengwu's recommended mode.

MySQL 8 Dynamic Table vs. ES Storage Comparison

The article provides a detailed dimension-by-dimension comparison:

Data structure : MySQL 8 — strong structured + semi-structured (JSON); ES — weak structured (JSON documents).

Query capability : MySQL 8 — powerful SQL, complex reporting friendly; ES — strong DSL, high report complexity.

Transaction consistency : MySQL 8 — strong ACID; ES — eventual consistency.

Permissions/compliance : MySQL 8 — fine-grained, row/column support; ES — weak, requires custom dev.

Horizontal scalability : MySQL 8 — medium (sharding); ES — high (sharded clusters).

Operational cost : MySQL 8 — low, mature ecosystem; ES — high, requires specialized ops.

Performance : MySQL 8 — balanced read/write; ES — extremely fast search/aggregation, fast writes.

Ecosystem support : MySQL 8 — comprehensive, talent-rich; ES — specialized, high barrier.

Dynamic table implementation : MySQL 8 — metadata-driven support; ES — high implementation difficulty.

Scenario fit : MySQL 8 — best for business data primary store; ES — logs, search, analytics.

Conclusion: Choose MySQL 8 for business data primary store; use ES only for search and analysis.

Chengwu Low-Code Dynamic Table Practical Experience

1. Fully Automated Table/Column Creation

User configures metadata → platform auto-generates DDL, safely creates physical tables, updates metadata tables.

Field changes/deletions support seamless rollback; type, length, comments fully synced.

2. Fully Dynamic SQL and Metadata-Driven Queries

Query interfaces need no fixed schema; frontend config auto-generates SQL, zero code.

Supports generic CRUD, batch import/export, conditional filtering, pagination/sorting, aggregation reports, joins.

3. Row/Column Permissions, Encryption, and Auditing

Leveraging MySQL 8's privilege system, platform configures per-field visibility, editability, encryption rules, with audit logs.

4. Complex Data Type Compatibility

Multi-select, tree, images, files, rich text stored via JSON or virtual columns for dynamic extension.

5. Cross-Tenant Sharding

Multi-tenant mode maps table structures to different databases, ensuring data isolation and performance.

Future Outlook: Tiered Storage and Cold-Hot Separation

Hot data (high-frequency operational data) : Continue with MySQL 8 for strong consistency and transactions.

Cold data (archives, reports, history) : Adopt ES, ClickHouse, HBase for tiered storage, balancing retrieval and analytics.

Platform will further strengthen MySQL 8 dynamic capabilities and elastic architecture, introducing distributed SQL, HTAP, Serverless to balance innovation and safety.

Conclusion: Use the Right Database for the Right Job

Database selection is the "anchor" of low-code platform architecture. Chengwu's choice of MySQL 8 stems from comprehensive trade-offs across business scenarios, technology trends, ecosystem sustainability, and enterprise deployment reality. The dynamic table innovation lets the platform balance flexibility with security, efficiency with control, innovation with stability. Regardless of how database technology evolves, adhering to "use the right database for the right job" remains the bottom line for technical teams.