Shopware 6 Data Persistence: Choosing Between DAL and Direct SQL

The Persistence Dilemma: Consistency vs. Performance

When building custom extensions in Shopware 6, you must decide how to commit data to the database. The primary conflict is between the Data Abstraction Layer (DAL)—which ensures the system remains synchronized—and Direct SQL (DBAL), which offers raw speed by bypassing the application logic.

The critical takeaway: Use the DAL for almost all write operations to ensure that search indexes, cache invalidation, and plugin events trigger correctly. Reserve direct SQL for read-only reporting or massive bulk updates where the DAL overhead would cause request timeouts.

Comparison of Persistence Methods

Feature	DAL Repository	Direct SQL (DBAL)
Event Triggering	Automatic (EntityWrittenEvent)	None
Indexing	Automatic	Manual rebuild required
API Integration	Exposed to Admin API	Hidden from API
Performance	Moderate (Object overhead)	High (Direct execution)
Complexity	Requires EntityDefinition	Requires raw SQL knowledge

Trade-offs and Risks

The DAL is not merely a wrapper; it is the engine that manages Shopware's complex data relationships. When you use a repository to save an entity, Shopware automatically handles Translations (mapping data to the correct language) and Visibility (ensuring products only show in specific sales channels).

If you bypass the DAL using the Connection object (DBAL), you introduce three primary risks:

• Stale Data: The Elasticsearch or MySQL search indexes will not know the data has changed, meaning the storefront will show outdated information.
• Plugin Breakage: Other developers often hook into EntityWrittenEvent. Direct SQL skips these hooks, potentially breaking third-party integrations.
• Schema Fragility: Direct SQL relies on hardcoded table names. If a Shopware update changes the schema, your raw queries will fail.

Implementation: Defining a Custom Entity for DAL

To use the DAL, you must first define your data structure. This allows the system to generate the necessary repositories and API endpoints.

Step 1: Create the EntityDefinition
Run this in your plugin's src/Core/Content/CustomEntity/CustomEntityDefinition.php. You must extend EntityDefinition and define the fields.

public function defineFields(): FieldCollection
{
    return new FieldCollection([
        (new IdField('id', 'id'))->addFlags(new PrimaryKey(), new Required()),
        (new StringField('custom_name', 'customName'))->addFlags(new Required()),
        (new IntField('custom_value', 'customValue')),
    ]);
}

Step 2: Writing Data via Repository
Inject the repository for your entity into your service. This operation should be run within the Shopware application context with appropriate service permissions.

// In your service class
public function saveCustomData(string $id, string $name, int $value, Context $context): void
{
    $this->customEntityRepository->create([
        ['id' => $id, 'customName' => $name, 'customValue' => $value]
    ], $context);
}

Validation and Verification

To verify that your persistence choice is working as intended, perform the following checks:

1. Event Verification: Create a subscriber that listens for EntityWrittenEvent for your custom entity. If the subscriber does not trigger during a repository->create() call, your definition is misconfigured.
2. Admin Visibility: Check the Shopware Administration panel. Data inserted via DAL should appear immediately (if an API resource is defined). Data inserted via direct SQL will not appear until the cache is cleared or the index is manually rebuilt.
3. Performance Benchmarking: For datasets exceeding 10,000 records, compare the execution time of a DAL create()` loop versus a single DBAL INSERT INTO ... VALUES ... statement. If the DAL exceeds your server's max_execution_time, consider a hybrid approach: use DBAL for the bulk insert, then manually trigger the EntityIndexerRegistry to refresh the indexes.

Rollback Strategy

Because these operations change the database state, ensure you have a backup before running bulk SQL scripts. To revert a DAL-based implementation, use the repository's delete() method using the primary keys of the created records to ensure associated versioning data is also removed.