Database Management with Shopware 6

Inevitably, at one point you’ll have the need to access the database directly. Some practical use cases can be found in the docs: https://docs.shopware.com/en/shopware-6-en/tutorials-and-faq/sql-tips-tricks.

So, what’s the best tool then to use? The particular challenge when it comes to Shopware 6 arises from its use of binary value UUIDs – they are just not “human readable” by default. Many database manage tools however are able to display UUIDs in a human readable format – the so-called hexadecimal or, short, hex format.

Shopware as of today recommends the use of Adminer, an open source database administration tool. Read here why putting Adminer on a production server is not a good idea, and what is a better alternative. ___STEADY_PAYWALL___

Why you should not even think of using Adminer on a production server

First, Adminer on the server exposes the database to everyone knowing the database credentials, even though the database access itself may be restricted to local users (as is common practice). I have seen credentials like “shopware” as a user and “MyShop2021” on production servers. Not an issue as long as access to the server is restricted by SSH, but with Adminer becomes a too easy target for brute-force-attacks.

Second, Adminer itself has some vulnerabilities even in recent versions – see https://www.cvedetails.com/vulnerability-list/vendor_id-17755/Adminer.html for details.

DBeaver

A local database management tool most certainly poses less security risks to your server, if set up properly. In any event, the connection to the server should be made by means of an SSH tunnel using a private/public key pair.

My personal choice for Shopware 6 is DBeaver. DBeaver is a smart database management tool available for Windows, Linux and Macs. I use the free community edition on my Ubuntu 20.04. Unfortunately, there is no repo for it, so you’d have to to download the .deb installer.

Setting up a database connection is straight-forward. For accessing a database on a server, just use its SSH tunnel option.

How to display binary values in human readable format?

To change the display format for binary values, open any table and right click on any field in a column with binary values. Select View/Format, Binary format and change it to hex. That’s it. DBeaver will remember this setting for all tables and columns in the database.

Before – binary values as strings
After – binary values in hex format

DBeaver has an abundance of nice and useful features – like with just one click you can navigate to a foreign-key related row of the related table table.

Leave a Reply

Your email address will not be published.