Shopware 6 SQL Guide

January 13, 2022 - E-Commerce, Shopware

Below, you will learn how to manage your Shopware data with the help of SQL. We describe what SQL queries to use to delete products, properties, customer data, and orders in Shopware 6. Also, you will find out how to disable extensions, customize database collation, and restore the default customer group in Shopware 6 using SQL. In addition to this Shopware 6 SQL guide, you can find other Shopware 6 tutorials in our cookbook.

Note that we provide Shopware integration with external platforms. Contact our support for further information or try our Improved Import, Export & Mass Actions.

Get Improved Import, Export & Mass Actions for Shopware by Firebear Studio

Explore Shopware

Table of contents

1 Shopware 6 SQL Warning!
2 Shopware 6 SQL Defined
3 Shopware 6 SQL Database Management
- 3.1 Catalog
- 3.2 System
4 Shopware 6 Essentials FAQ

Shopware 6 SQL Warning!

The article below is aimed at experienced developers. It is strongly advised to follow the tactics described in this Shopware 6 SQL tutorial only with the necessary expertise and at your own risk. Before going any further, make a backup of your Shopware 6 website to prevent the inability to restore it if anything goes wrong or you are dissatisfied with the results. Note that the queries below may become outdated by the time you read this material. Also, note that all the SQL manipulations described here are not officially supported, although you can find a similar guide in the official documentation.

Shopware 6 SQL Defined

SQL (Structured Query Language) is a standardized programming language used for relational database management. As for SQL databases in relational systems, they consist of multiple tables containing data in rows and columns. And it is the way Shopware 6 treats its data. Thus, you get the ability to use various SQL queries to manage information stored in your website’s database. So, what are the most common data manipulations?

Shopware 6 SQL Database Management

Below, we provide two sections of SQL queries: Catalog and System. The first one contains SQL queries that delete catalog data in Shopware 6. The second section illustrates various system manipulations that you can apply to your Shopware 6 website via SQL.

Catalog

How to delete products via SQL in Shopware 6

Use the following SQL query to delete all created products in Shopware 6. It wipes the product overview completely.

delete pv.*, pt2.*, pt.*, psk.*, pr.*, pp2.*, pp.*, po.*, pm.*, pcfs.*, pcst.*, pcsap.*, pcs2.*, pcs.*, pct.*, pc.*, p.* from product p
left join product_category pc on p.id = pc.product_id 
left join product_category_tree pct on p.id = pct.product_id 
left join product_configurator_setting pcs on p.id = pcs.product_id 
left join product_cross_selling pcs2 on p.id = pcs2.product_id 
left join product_cross_selling_assigned_products pcsap on p.id = pcsap.product_id 
left join product_cross_selling_translation pcst on pcs2.id = pcst.product_cross_selling_id 
left join product_custom_field_set pcfs on p.id = pcfs.product_id 
left join product_media pm on p.id = pm.product_id 
left join product_option po on p.id = po.product_id 
left join product_price pp on p.id = pp.product_id 
left join product_property pp2 on p.id = pp2.product_id 
left join product_review pr on p.id = pr.product_id 
left join product_search_keyword psk on p.id = psk.product_id 
left join product_tag pt on p.id = pt.product_id 
left join product_translation pt2 on p.id = pt2.product_id 
left join product_visibility pv on p.id = pv.product_id;

delete pv.*, pt2.*, pt.*, psk.*, pr.*, pp2.*, pp.*, po.*, pm.*, pcfs.*, pcst.*, pcsap.*, pcs2.*, pcs.*, pct.*, pc.*, p.* from product p

left join product_category pc on p.id = pc.product_id

left join product_category_tree pct on p.id = pct.product_id

left join product_configurator_setting pcs on p.id = pcs.product_id

left join product_cross_selling pcs2 on p.id = pcs2.product_id

left join product_cross_selling_assigned_products pcsap on p.id = pcsap.product_id

left join product_cross_selling_translation pcst on pcs2.id = pcst.product_cross_selling_id

left join product_custom_field_set pcfs on p.id = pcfs.product_id

left join product_media pm on p.id = pm.product_id

left join product_option po on p.id = po.product_id

left join product_price pp on p.id = pp.product_id

left join product_property pp2 on p.id = pp2.product_id

left join product_review pr on p.id = pr.product_id

left join product_search_keyword psk on p.id = psk.product_id

left join product_tag pt on p.id = pt.product_id

left join product_translation pt2 on p.id = pt2.product_id

left join product_visibility pv on p.id = pv.product_id;

How to delete properties via SQL in Shopware 6

Since properties exist apart from products in Shopware 6, you need a separate query to eliminate them. Use the following SQL manipulation to do that:

delete pgt.*, pgot.*, pgo.*, pg.* from property_group pg 
join property_group_option pgo on pg.id = pgo.property_group_id 
join property_group_option_translation pgot on pgo.id = pgot.property_group_option_id 
join property_group_translation pgt on pg.id = pgt.property_group_id;

delete pgt.*, pgot.*, pgo.*, pg.* from property_group pg

join property_group_option pgo on pg.id = pgo.property_group_id

join property_group_option_translation pgot on pgo.id = pgot.property_group_option_id

join property_group_translation pgt on pg.id = pgt.property_group_id;

How to delete customer data via SQL in Shopware 6

Use the following SQL query to delete all customers in Shopware 6. Never apply it to your live store since you will lose vital data associated with e-commerce activities.

delete cwp.*, cw.*, ct.*, cr.*, ca.*, c.* from customer c 
left join customer_address ca on c.id = ca.customer_id 
left join customer_recovery cr on c.id = cr.customer_id 
left join customer_tag ct ON c.id = ct.customer_id 
left join customer_wishlist cw on c.id = cw.customer_id 
left join customer_wishlist_product cwp on cw.id = cwp.customer_wishlist_id;

delete cwp.*, cw.*, ct.*, cr.*, ca.*, c.* from customer c

left join customer_address ca on c.id = ca.customer_id

left join customer_recovery cr on c.id = cr.customer_id

left join customer_tag ct ON c.id = ct.customer_id

left join customer_wishlist cw on c.id = cw.customer_id

left join customer_wishlist_product cwp on cw.id = cwp.customer_wishlist_id;

How to delete orders via SQL in Shopware 6

Note that you can apply the following SQL query before going live with your store since orders with receipts or receipts should never be deleted. You can still use it to delete all demo orders in Shopware 6.

delete ot2.*, ot.*, oli.*, odp.*, od.*, oc.*, oa.*, o.* from `order` o 
left join  order_address oa on o.id = oa.order_id 
left join  order_customer oc on o.id = oc.order_id 
left join  order_delivery od on o.id = od.order_id 
left join  order_delivery_position odp on od.id = odp.order_delivery_id 
left join  order_line_item oli on o.id = oli.order_id 
left join  order_tag ot on o.id = ot.order_id 
left join  order_transaction ot2 on o.id = ot2.order_id;

delete ot2.*, ot.*, oli.*, odp.*, od.*, oc.*, oa.*, o.* from `order` o

left join order_address oa on o.id = oa.order_id

left join order_customer oc on o.id = oc.order_id

left join order_delivery od on o.id = od.order_id

left join order_delivery_position odp on od.id = odp.order_delivery_id

left join order_line_item oli on o.id = oli.order_id

left join order_tag ot on o.id = ot.order_id

left join order_transaction ot2 on o.id = ot2.order_id;

System

How to disable non-standard extensions via SQL in Shopware 6

Use the following SQL query to deactivate extensions that are not a part of the default Shopware installation. This technique is especially useful for debugging errors since you apply the query and reduce the impact of extensions to zero.

Also, note that using this SQL query with a theme integrated via a plugin may cause problems if the theme is assigned to a sales channel. Calling the appropriate sales channels will be problem-plagued. Therefore, ensure that your theme is not assigned to a sales channel.

You disable non-standard extensions via SQL in Shopware 6 as follows. Create a temporary backup table to save the current status of the extensions:

CREATE TABLE plugin_tmp LIKE plugin;
INSERT INTO `plugin_tmp` SELECT * FROM `plugin`;

1 2	CREATE TABLE plugin_tmp LIKE plugin; INSERT INTO `plugin_tmp` SELECT * FROM `plugin`;

Deactivate the extensions:

UPDATE `plugin` SET `active`= 0 WHERE (author <> 'shopware AG' AND author <> 'Shopware') OR (author IS NULL);

1	UPDATE `plugin` SET `active`= 0 WHERE (author <> 'shopware AG' AND author <> 'Shopware') OR (author IS NULL);

You can restore the original state of the extensions with another SQL query:

UPDATE plugin AS p JOIN plugin_tmp AS pt ON p.id = pt.id SET p.active = pt.active;

1	UPDATE plugin AS p JOIN plugin_tmp AS pt ON p.id = pt.id SET p.active = pt.active;

After that, delete the temporary backup table using this SQL query:

DROP TABLE plugin_tmp;

1	DROP TABLE plugin_tmp;

How to update database collation via SQL in Shopware 6

Showpare utilizes “utf8mb4_unicode_ci” as a default collation for tables. If the collation for individual tables is not set correctly, you can use an SQL query to update it.

This technique will help you avoid errors caused by comparing strings of incompatible collations or selecting data from columns with different collations.

In log files, the following entry will help you recognize the issue:

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

1	SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

Use the following SQL query to check if a wrong collation is used in a Shopware table. Insert the table and database name into the marked positions (TableName & DatabaseName).

SELECT table_schema, table_name, column_name, character_set_name, collation_name
 FROM information_schema.columns
 WHERE (collation_name = 'utf8mb4_unicode_ci' or collation_name = 'utf8_general_ci ') 
 and table_name = '' --TableName
 and table_schema = '' --DatabaseName 
 ORDER BY table_schema, table_name,ordinal_position;

SELECT table_schema, table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE (collation_name = 'utf8mb4_unicode_ci' or collation_name = 'utf8_general_ci ')

and table_name = '' --TableName

and table_schema = '' --DatabaseName

ORDER BY table_schema, table_name,ordinal_position;

Create a full backup before applying changes to the database. Next, update the collation. Use this SQL query to update the entire Shopware 6 database:

ALTER DATABASE --DatabaseName CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

1	ALTER DATABASE --DatabaseName CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

If you want to update a single table in your Shopware 6 database, the following query is at your service

ALTER TABLE --TableName CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci';

1	ALTER TABLE --TableName CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci';

How to restore the default customer group via SQL in Shopware 6

The following SQL query restores the default customer group in Shopware 6. Note that you should maintain the corresponding translations manually in the admin.

INSERT INTO `customer_group` (`id`, `display_gross`, `registration_active`, `created_at`, `updated_at`) VALUES
(UNHEX('CFBD5018D38D41D8ADCA10D94FC8BDD6'), 1, 0, '2021-01-01 00:00:00.00', NULL);

1 2	INSERT INTO `customer_group` (`id`, `display_gross`, `registration_active`, `created_at`, `updated_at`) VALUES (UNHEX('CFBD5018D38D41D8ADCA10D94FC8BDD6'), 1, 0, '2021-01-01 00:00:00.00', NULL);

Shopware 6 Essentials FAQ

How to create a Shopware account?

You need a Shopware account to create an e-commerce website to apply SQL queries to it. Visit the registration page, hit the Register Now link, and fill in the form to create a new account. After receiving a confirmation email, follow the instructions provided in it.

What about a Shopware ID?

You will get your Shopware ID upon registration without extra steps.

What is Shopware master data?

All the information about your company and business required in your Shopware account is called master data.

How to add a new Shopware shop?

You need to create a Shopware shop to apply SQL queries. It is possible to add it to your merchant area. Click the corresponding link, choose the type of your new shop (a cloud storefront or an on-premise installation), and provide all the information the system requires.

How to add a payment method to your Shopware account?

A payment method is required for purchasing plugins or non-standard extensions that can be disabled via SQL. You can add the method under Basic Information -> Accounting. Select PayPal, credit card, or direct debit as a way to transfer funds to your account.

How to create a deposit in Shopware?

A working payment method lets you create a deposit for purchasing extensions and themes. Go to your Merchant Area -> Shops, choose a shop to provide funds to, click Open Account Details, and transfer a deposit that covers the amount of your purchase.

How to purchase Shopware extensions?

You can buy extensions in the Plugin Manager or on the Shopware Marketplace. As we’ve already mentioned, it is necessary to create a deposit that equals the amount of your future purchase. Next, select a tool you want to purchase and complete the checkout.

How to get support in Shopware?

Send accounting and financial inquiries to financial.services@shopware.com and +49 2555 9288 510. Shopware also accepts inquiries regarding license issues here: info@shopware.com or call +49 2555 9288 50.

How to automate import and export processes in Shopware 6?

You can implement the automation of recurring data transfers with the Improved Import, Export & Mass Actions extension. The module allows the creation of schedules so that the corresponding import and export processes are launched automatically.

How to migrate to Shopware 6?

The Improved Import, Export & Mass Actions extension can transfer data from other e-commerce systems or Shopware’s previous versions. Note that we also offer export solutions for systems like Magento so that you can simplify your data transfers between them and Shopware. The tool mentioned above is also suitable for migration to Shopware 6. Contact us for more information.

How to integrate Shopware 6 with external systems?

If you need to connect your Shopware 6 website to ERPs, CRMs, accounting tools, and other similar platforms, heed the Improved Import, Export & Mass Actions plugin. As we’ve already mentioned above, the module can help you automate repetitive import and export processes. Besides, it introduces mapping presets that modify data according to the requirements of your Shopware store during import and help you follow the rules of the connected platform during export.

Get Improved Import & Export for Shopware by Firebear Studio