Shopware 6 SQL Guide

- 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 

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.

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:

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.

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.

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 followsCreate a temporary backup table to save the current status of the extensions:

Deactivate the extensions:

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

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

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:

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).

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:

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

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. 

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

Explore Shopware

'