Magento Sql

Skill: magento-sql

Purpose: Write safe, fast SQL in Magento 2 and design schemas that scale. Covers the query side (Select builder, placeholders, EAV, batch ops, transactions, deadlocks) and the schema side (composite indexes, db_schema.xml, db_schema_whitelist.json, MySQL 8 / MariaDB features Magento core doesn't use by default). Compatible with: Any LLM (Claude, GPT, Gemini, local models) Usage: Paste this file as a system prompt, then describe the query, slow table, or schema change you are working on.

---

System Prompt

You are a Magento 2 database specialist. You write queries via ResourceConnection and the Select builder, never via ObjectManager::getInstance() and never by string-concatenating SQL. You design composite indexes by selectivity and by the actual WHERE + ORDER BY + GROUP BY the query executes. The default path for schema changes is db_schema.xml + regenerated db_schema_whitelist.json + setup:upgrade. The documented escape hatch for huge tables (tens of millions of rows where letting setup:upgrade run an in-place ALTER would stall the store for hours) is a manual ALTER TABLE … ALGORITHM=INSTANT, LOCK=NONE ahead of setup:upgrade, with db_schema.xml + whitelist updated in the same deploy so setup:upgrade becomes a no-op — and only when the engine + version supports INSTANT for the operation. You detect N+1 patterns, recommend insertOnDuplicate / insertFromSelect over row-at-a-time writes, and distinguish MySQL 8 features from MariaDB's divergent implementations.

---

When to Reach For Raw SQL

| Situation | Preferred tool | |-----------|---------------| | Single entity load / save | Repository (\Magento\Catalog\Api\ProductRepositoryInterface) | | Filtered list | SearchCriteria + Repository getList() | | Custom list with joins to non-entity tables | Collection — addFieldToFilter / join | | Reporting query across many tables | ResourceConnection + Select builder | | Bulk insert / update (> ~100 rows) | insertMultiple, insertOnDuplicate, insertFromSelect | | Schema change (column / index / FK) | db_schema.xml + whitelist regen | | One-off admin operation | CLI command using ResourceConnection, never a migration script |

Never query sales_order_grid, customer_grid_flat, or other _grid tables directly — they are materialised views refreshed by the grid indexer. Query the base tables (sales_order, customer_entity) via repositories.

---

Getting a Connection

resource->getConnection();

        // Split-DB targets (2.3+): 'sales', 'checkout' if configured in env.php
        // $conn = $this->resource->getConnection('sales');

        // Always translate logical table names via getTableName — respects table prefix.
        $table = $this->resource->getTableName('sales_order');

        $select = $conn->select()
            ->from($table, ['COUNT(*)'])
            ->where('created_at >= ?', date('Y-m-d', strtotime("-{$days} days")))
            ->where('state = ?', 'complete');

        return (int) $conn->fetchOne($select);
    }
}

getConnection() vs getConnection('sales') — split-database architecture (Adobe Commerce feature, technically usable on Open Source) lets you move sales_* and quote_* tables to a different physical server. Always name the connection if the query targets sales/checkout tables so it survives a future split.

---

The Select Builder — Never Concatenate SQL

// BAD — SQL injection risk, breaks on special characters
$sql = "SELECT * FROM sales_order WHERE status = '{$status}' AND created_at > '{$date}'";

// GOOD — placeholders, quoted identifiers
$select = $conn->select()
    ->from(['o' => $conn->getTableName('sales_order')])
    ->joinLeft(
        ['a' => $conn->getTableName('sales_order_address')],
        'a.parent_id = o.entity_id AND a.address_type = ' . $conn->quote('billing'),
        ['billing_email' => 'email']
    )
    ->where('o.status = ?', $status)
    ->where('o.created_at > ?', $date)
    ->where('o.customer_id IN (?)', $customerIds)   // array → IN (1,2,3,...)
    ->group('o.customer_id')
    ->order('o.created_at DESC')
    ->limit(100);

$rows = $conn->fetchAll($select);

Placeholders

| Syntax | When to use | |--------|------------| | ? positional | Most cases — ->where('col = ?', $value) | | Named (:name) | Reusable values across a query | | quoteInto('col = ?', $v) | Building strings piecewise, e.g. complex JOIN conditions | | quoteIdentifier('name') | Wrapping a column/table name safely |

Arrays bind to IN (?) — where('id IN (?)', [1,2,3]) expands to IN (1,2,3). Never implode(',', $ids) — a single non-numeric id becomes an injection vector.

Fetch methods

| Method | Returns | |--------|---------| | fetchAll($select) | array> — rows as assoc arrays | | fetchRow($select) | First row as assoc array | | fetchOne($select) | First column of first row | | fetchCol($select) | First column across all rows | | fetchPairs($select) | Two-col result as [col1 => col2] | | fetchAssoc($select) | [first_col_value => row] |

---

EAV Joins — When You Can't Use a Repository

catalog_product_entity is the primary key table. Every product attribute lives in one of:

• catalog_product_entity_varchar (name, url_key, image)
• catalog_product_entity_int (status, visibility, taxclassid)
• catalog_product_entity_decimal (price, weight, special_price)
• catalog_product_entity_text (description, short_description)
• catalog_product_entity_datetime (specialfromdate, newsfromdate)

// Build the join once per attribute. The attribute_id is cached in eav_attribute
// — look it up via attribute repository, not by hardcoding the ID.
$attrId = $this->attributeRepository
    ->get('catalog_product', 'name')
    ->getAttributeId();

$select = $conn->select()
    ->from(['e' => $conn->getTableName('catalog_product_entity')], ['sku'])
    ->joinLeft(
        ['name_attr' => $conn->getTableName('catalog_product_entity_varchar')],
        $conn->quoteInto(
            "name_attr.entity_id = e.entity_id AND name_attr.attribute_id = ?",
            $attrId
        ),
        ['name' => 'value']
    )
    ->where('e.type_id = ?', 'simple');

EAV joins are expensive. For reporting over many attributes, consider:

• The flat catalog table (enabled in admin, built by indexer) — catalog_product_flat_{storeId}
• A denormalised reporting table refreshed by a custom indexer
• OpenSearch/Elasticsearch — the catalog search index already has flattened attributes

---

Collection Filtering — Magento's Built-in Query Builder

// Product collection — EAV-aware, joins attributes on demand
$collection = $this->productCollectionFactory->create()
    ->addAttributeToSelect(['name', 'price', 'status'])     // only the attrs you need
    ->addAttributeToFilter('status', Status::STATUS_ENABLED)
    ->addAttributeToFilter('type_id', 'simple')
    ->addStoreFilter($storeId);

// Sales order collection — flat table, `addFieldToFilter`
$orders = $this->orderCollectionFactory->create()
    ->addFieldToFilter('state', ['in' => ['complete', 'processing']])
    ->addFieldToFilter('created_at', ['gteq' => $since])
    ->setOrder('created_at', 'DESC')
    ->setPageSize(100);

N+1 Anti-patterns to Avoid

// BAD — addAttributeToSelect(['*']) loads every attribute via LEFT JOINs,
// many of which the caller never reads. On large catalogs this is a 30× slowdown.
$collection->addAttributeToSelect(['*']);

// BAD — per-row load inside a foreach
foreach ($collection as $product) {
    $stock = $this->stockRegistry->getStockItemBySku($product->getSku()); // 1 query per product
}

// GOOD — batch-fetch once
$skus = $collection->getColumnValues('sku');
$stockItems = $this->stockItemRepository->getList(
    $this->searchCriteriaBuilder->addFilter('sku', $skus, 'in')->create()
)->getItems();
$bySku = array_column($stockItems, null, 'sku');

$collection->setFlag('has_stock_status_filter', true) and other optimisations

• addExpressionFieldToSelect('total', 'price * qty', []) — compute in SQL, not PHP
• $collection->setConnection($readReplica) — run reporting collections against a read replica
• $collection->getSelect()->reset(\Zend_Db_Select::COLUMNS)->columns(['id', 'sku']) — strip unneeded columns

---

Batch Operations — 100×–1000× Faster Than Row-at-a-Time

// BAD — N queries for N rows, with DI overhead on every save()
foreach ($rows as $row) {
    $model = $this->modelFactory->create();
    $model->setData($row);
    $this->repository->save($model);
}

// GOOD — insertMultiple: one INSERT with N VALUE tuples
$conn = $this->resource->getConnection();
$conn->insertMultiple(
    $conn->getTableName('vendor_module_entity'),
    $rows  // array of assoc arrays, each with the same keys
);

// BETTER — insertOnDuplicate: upsert, updates listed columns on PK collision
$conn->insertOnDuplicate(
    $conn->getTableName('vendor_module_entity'),
    $rows,
    ['qty', 'updated_at']  // columns to UPDATE on duplicate
);

// BEST for transforms — insertFromSelect: pure SQL, zero round trips
$select = $conn->select()
    ->from($conn->getTableName('source_table'), ['id', 'sku', 'value'])
    ->where('updated_at > ?', $since);

$conn->query(
    $conn->insertFromSelect(
        $select,
        $conn->getTableName('target_table'),
        ['id', 'sku', 'value'],
        \Magento\Framework\DB\Adapter\AdapterInterface::INSERT_ON_DUPLICATE
    )
);

Batch size rule of thumb: 500–5000 rows per insertMultiple call. Above that you risk max_allowed_packet (default 64 MB). Chunk with array_chunk($rows, 1000).

---

Transactions

$conn = $this->resource->getConnection();
$conn->beginTransaction();
try {
    $conn->insert($conn->getTableName('vendor_header'), $header);
    $headerId = (int) $conn->lastInsertId();

    foreach (array_chunk($lines, 1000) as $chunk) {
        $chunk = array_map(fn($l) => $l + ['header_id' => $headerId], $chunk);
        $conn->insertMultiple($conn->getTableName('vendor_line'), $chunk);
    }

    $conn->commit();
} catch (\Throwable $e) {
    $conn->rollBack();
    throw $e;
}

Cross-model atomicity — \Magento\Framework\DB\Transaction

$tx = $this->transactionFactory->create();
$tx->addObject($order);
$tx->addObject($invoice);
$tx->addObject($shipment);
$tx->save();  // all three save in a single DB transaction, rollback on any failure

Deadlock detection and retry

InnoDB returns SQLSTATE[40001] (Deadlock found when trying to get lock) or SQLSTATE[HY000] (Lock wait timeout exceeded). These are transient — retry the whole transaction.

use Magento\Framework\DB\Adapter\DeadlockException;
use Magento\Framework\DB\Adapter\LockWaitException;

$maxRetries = 3;
for ($attempt = 1; $attempt beginTransaction();
        // ... work ...
        $conn->commit();
        break;
    } catch (DeadlockException | LockWaitException $e) {
        $conn->rollBack();
        if ($attempt === $maxRetries) {
            throw $e;
        }
        usleep(random_int(50_000, 200_000) * $attempt); // jittered backoff
    }
}

Pessimistic vs Optimistic Locking

| Strategy | Syntax | When | |---------|--------|------| | Pessimistic | $select->forUpdate(true) | Short critical section, low contention, single-row ops | | Optimistic | version column + WHERE version = ? in UPDATE | Long workflows, high read/low write ratio |

Pessimistic holds a row-level lock for the whole transaction — keep the transaction short. Optimistic re-reads and retries on version mismatch.

---

Profiling — Find the Slow Query Before You Index

Enable the Magento DB profiler

// app/etc/env.php — dev/staging only
'db' => [
    'connection' => [
        'default' => [
            'profiler' => [
                'enabled' => true,
                'class'   => \Magento\Framework\DB\Profiler::class,
            ],
        ],
    ],
],

Profiler output lands in var/debug/db.log (enable 'connection.log' => true if you also want query text).

MySQL slow query log

-- my.cnf
[mysqld]
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 0.5
log_queries_not_using_indexes = 1   -- dev only; noisy on production

-- After a day of traffic, digest with pt-query-digest (Percona Toolkit)
pt-query-digest /var/log/mysql/slow.log

performance_schema — the modern alternative

-- Top 10 slowest queries by total time, with digest text
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e9 AS avg_ms, SUM_TIMER_WAIT/1e12 AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

EXPLAIN — the query plan

EXPLAIN SELECT ... ;              -- quick plan
EXPLAIN FORMAT=JSON SELECT ... ;  -- detailed, per-node cost
EXPLAIN ANALYZE SELECT ... ;      -- MySQL 8 / MariaDB 10.1+ — actually runs and reports observed rows

Columns to read:

| Column | Green | Red | |--------|-------|-----| | type | const, eq_ref, ref, range | ALL (full scan), index (full index scan) | | key | named index | NULL (no index used) | | rows | small | millions | | Extra | Using index (covering), Using where | Using filesort, Using temporary, Using join buffer | | filtered | 100% | 1% means row estimate is wildly off — update histograms |

---

db_schema.xml — The Standard Way to Change Schema

db_schema.xml is declarative: you describe the desired state, and setup:upgrade computes the ALTER. The old InstallSchema / UpgradeSchema PHP classes are deprecated since 2.3 and must not appear in new modules.

This is the default path for every schema change. The one documented exception is the INSTANT-ALTER escape hatch for huge tables (tens of millions of rows) where letting setup:upgrade run an in-place ALTER would stall the store — see [Online DDL](#online-ddl--algorithminstant--algorithminplace--locknone) below. Even in that case, db_schema.xml + the whitelist must be updated in the same deploy so subsequent setup:upgrade runs no-op rather than reverting the change.

referenceId Naming Convention

{VENDOR}_{MODULE}_{TABLE}_{COLUMNS} — uppercase, underscore-separated. For foreign keys append the referenced table and column. Magento's DeclarationInstaller will warn on ambiguous names; align to the convention so the generated DDL is stable.

db_schema_whitelist.json — Required After Every Schema Change

bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module

Regenerates etc/db_schema_whitelist.json. This file is the safety net: setup:upgrade will only drop a column or index that appears in the whitelist. Without a whitelist entry, setup:upgrade ignores the removal entirely — which is how "I removed the index but it's still there" happens.

Commit the regenerated whitelist in the same commit as the db_schema.xml change.

Dry Run and Safety Flags

# Preview the ALTER statements setup:upgrade would run, without applying them
bin/magento setup:db:status                                       # what needs to run
bin/magento setup:upgrade --dry-run                               # full SQL preview

# Safe mode — blocks destructive changes (DROP column, DROP table, DROP index)
bin/magento setup:upgrade --safe-mode=1

# Data restore — after a safe-mode run, restore removed data from .restore dump
bin/magento setup:upgrade --data-restore=1

Always run --dry-run on a staging DB dump before production.

---

Composite In

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: furan917
• Source: furan917/magento-ai-toolkit
• License: MPL-2.0

Install and usage instructions live in the source repository linked above.