Optimizing MODX Performance with Migx Custom Tables

The Problem: Resource Bloat and Manager Lag

In a default MODX Revolution installation, every page, category, or data object is stored as a row in the modxResource table. While this hierarchical approach is excellent for content management, it becomes a bottleneck when handling high-volume datasets—such as product catalogs, employee directories, or event listings. As the modxResource table grows into the thousands, the MODX Manager slows down significantly because it must parse the entire resource tree for various administrative tasks.

The technical takeaway is to decouple your content (pages) from your data (records). By moving flat datasets into custom SQL tables and using the Migx Extra to manage them, you keep the resource tree lean and the Manager responsive.

Implementing Custom Tables via Migx

Migx is a powerful Extra that allows you to create a management interface for data that doesn't live in the standard MODX resource tree. This is achieved through Client Configs (CC), which map a database table to a user-friendly grid in the backend.

1. Database Schema Setup

First, create your custom table. This must be done via a database tool (like phpMyAdmin) or the command line, as MODX does not provide a native SQL table creator in the Manager. Run the following command as a database user with CREATE permissions:

CREATE TABLE modx_product_catalog (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    createdon TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Risk: Ensure the table prefix (modx_) matches your site's specific installation prefix to avoid permission errors.

2. Mapping the Table in Migx

After installing Migx via the Package Manager, navigate to Components → Migx → Client Configs. Create a new config named product_catalog and use the following JSON configuration to define the UI:

{
  "formtabs":[{
    "fields":[
      {"field":"sku","caption":"SKU"},
      {"field":"name","caption":"Product Name"},
      {"field":"price","caption":"Price"},
      {"field":"stock","caption":"In Stock"}
    ]
  }],
  "columns":[
    {"header":"SKU","sortable":true,"dataIndex":"sku"},
    {"header":"Name","sortable":true,"dataIndex":"name"},
    {"header":"Price","sortable":true,"dataIndex":"price"},
    {"header":"Stock","sortable":true,"dataIndex":"stock"}
  ],
  "customtables":{"product_catalog":"modx_product_catalog"}
}

This configuration tells Migx exactly which columns to display in the grid and which fields to show in the edit form. You can now manage your products under Components → Migx → product_catalog.

Efficient Data Retrieval

Since this data exists outside the resource system, you cannot use standard MODX tags like [[*price]]. Instead, you should use a Snippet to query the table directly. This is significantly faster than loading multiple resources.

Create a snippet named GetProducts with the following logic:

getOption('table_prefix');
$table = $prefix . 'product_catalog';

// Use a prepared statement or the MODX query wrapper for safety
$sql = "SELECT sku, name, price, stock FROM {$table} WHERE stock > 0 ORDER BY name ASC";
$result = $modx->query($sql);

if (!$result) return 'Error fetching products';

$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$output = '<ul>';

foreach ($rows as $row) {
    $output .= '<li>' . htmlspecialchars($row['name']) . ' - $' . number_format($row['price'], 2) . '</li>';
}

$output .= '</ul>';
return $output;
?>

Trade-offs and Limitations

• No Native Versioning: Unlike Resources, custom table rows do not have a history of changes. If a user deletes a row, it is gone unless you implement a soft-delete system.
• ACL Bypass: MODX Access Control Lists (ACLs) apply to resources. Custom tables are managed via Migx, meaning you must manage permissions at the Component level rather than the individual record level.
• Cache Management: Changes to custom tables do not trigger the automatic clearing of the MODX resource cache. You may need to use a custom cache-clearing snippet or set your output snippet to be uncacheable ([[!GetProducts]]) during development.

Verification Checklist

To ensure the implementation is correct, perform these checks:

1. UI Check: Open the Migx grid; verify you can add a record and that it persists after a page refresh.
2. DB Check: Run SELECT * FROM modx_product_catalog; in your SQL console to confirm the data is physically present in the custom table.
3. Front-end Check: Call the [[!GetProducts]] snippet on a page. If the list renders correctly, the connection between the custom table and the MODX API is functional.