Is using the database directly such a sin?

25.02.202126.02.2021 by Jan Schmidak

Normally, we use repositories in Shopware 6 to read and write data from or to the database. In 99% of cases, this is the best way to do it. However, as I know from experience, there are some select cases, in which you may want to or even have to connect to the database directly. It is not such a cardinal sin to do this, if you know, what are you doing. In comparison to some other e-commerce software, like Magento, which uses EAV model (entity, attribute, value), Shopware’s database structure is still pretty simple and intuitive.. mostly..

So in this article, you will get to know, how to be able to run SQL queries directly in your PHP code, when to use them and some additional tips.

How to work with tables directly in Shopware 6?

You can always use native PHP functions for connecting to the database, but you know that, right? That is not why you are here. And besides, there is no reason to do this, when you can use a class, that exists in Shopware 6 for our purpose. It is called simply Connection and you can inject it into your class and use it right away like this:

<?php
namespace SomePlugin;

use Doctrine\DBAL\Connection;

class SomeClass
{
    private $connection;

    //inject the connection into your class constructor
    public function __construct (
        Connection $connection
    )
    {
        $this->connection = $connection;
    }

    public function someFunction ()
    {
        //define the SQL query
        $sqlQuery = "SELECT * FROM `some_table`";

        //execute the query and fetch the results as an array
        $sqlResult = $this->connection->executeQuery($sqlQuery)->fetchAll();

        //..or execute the query and get the results row by row
        $sqlResult = $this->connection->executeQuery($sqlQuery);
        foreach ($sqlResult as $sqlResultRow) {
            //do something with this row
        }

    }

}

<?php

namespace SomePlugin;

use Doctrine\DBAL\Connection;

class SomeClass

{

private $connection;

//inject the connection into your class constructor

public function __construct (

Connection $connection

)

{

$this->connection = $connection;

}

public function someFunction ()

{

//define the SQL query

$sqlQuery = "SELECT * FROM `some_table`";

//execute the query and fetch the results as an array

$sqlResult = $this->connection->executeQuery($sqlQuery)->fetchAll();

//..or execute the query and get the results row by row

$sqlResult = $this->connection->executeQuery($sqlQuery);

foreach ($sqlResult as $sqlResultRow) {

//do something with this row

}

That was a SELECT query. Theoretically you can run all SQL queries with executeQuery method, but Shopware offers also executeUpdate query, so why not use it? It returns the number of affected rows, which is really useful for INSERT, UPDATE and DELETE SQL statements.

<?php
namespace SomePlugin;

use Doctrine\DBAL\Connection;

class SomeClass
{
    private $connection;

    //inject the connection into your class constructor
    public function __construct (
        Connection $connection
    )
    {
        $this->connection = $connection;
    }

    public function someFunction ()
    {
        //define the SQL query
        $sqlQuery = "SELECT * FROM `some_table`";

        //execute the query
        $sqlQuery = "UPDATE `table` SET column='something' WHERE id=UNHEX('xxx'))";
        
        //get the number of affected rows
        $sqlResult = $this->connection->executeUpdate($sqlQuery);

    }

}

<?php

namespace SomePlugin;

use Doctrine\DBAL\Connection;

class SomeClass

{

private $connection;

//inject the connection into your class constructor

public function __construct (

Connection $connection

)

{

$this->connection = $connection;

}

public function someFunction ()

{

//define the SQL query

$sqlQuery = "SELECT * FROM `some_table`";

//execute the query

$sqlQuery = "UPDATE `table` SET column='something' WHERE id=UNHEX('xxx'))";

//get the number of affected rows

$sqlResult = $this->connection->executeUpdate($sqlQuery);

}

Just a sidenote here, regarding working with the IDs in Shopware 6 database. You have probably noticed the UNHEX function in the SQL code example above. As you probably know, Shopware 6 uses UUID string as a unique row identifier, instead of a traditional autoincremented numerical ID. When you use repositories, you don’t have to worry about that. However in this case, it is important to use the HEX and UNHEX functions. When you read the UUID from the database and want to use it either in PHP code or as a human-readable value in general, use HEX to obtain its value. And when you want to write it back to the database, use UNHEX on it. SQL code example:

SELECT HEX(id) FROM table;

UPDATE table SET id = UNHEX('xxx');

SELECT HEX(id) FROM table;

UPDATE table SET id = UNHEX('xxx');

When to use SQL instead of repositories?

So the golden rule is: when you can use repositories, do so. And when not? Well, then just be careful with your SQL and make backups often. 🙂

These were my cases so far, when I preferred the direct DB approach:

Migration of large data to Shopware 6

Yes, there are some third party plugins, that promise you to migrate your data from another e-commerce systems seamlessly, but at least in my case, a Magento 1 store, that did not work that well. Especially when migrating data from larger or heavily customized stores, they can not handle all the additional data, that are in many cases spread across multiple tables.

So I have created a standalone PHP script, that worked with the data, that were copied from Magento to a local database. The data was processed locally, converting them to Shopware format and then copied to the Shopware database, where a simple SQL script copied them to the appropriate tables. Using repositories in this case would have been possible, but probably much clumsier, because the conversion tables would have to be implemented as entities in order to use them as repositories. And that is not so simple, because the whole structure must be defined in PHP classes.

So my advice is: if you need a one-time import of a lot of data, that requires some conversion, intermediary / temporary tables or pre-processing, consider using the direct approach, at least for the part of the logic, that does not have to work with Shopware 6 tables yet.

When repositories run out of memory

This has happened to me, while I was creating a regularly run import, that worked with large amounts of data. The import was a command, that always just ended up with ‘Killed’, when I selected a couple of thousand rows from my custom table. Apparently, the repositories do not come without a price. It was frustrating! I wanted to do it ‘the right way’, so I have tried to select the data in batches, to limit the columns selcted and some other ‘tricks’. I might have even succeeded in ‘hacking’ this, but in the end I have realized, that for a simple select, using SQL is perfectly fine. The import works swiftly and smoothly to this day..

So if you encounter some memory issues or similar problems, when you are selecting data from a repository, don’t torture yourself with trying to find a perfect solution and just get the job done by SQL. Or not – the choice is always yours.

The caveats of using SQL instead of repositories

You can run into some problems, when you decide to use the tables directly. I would say 90% of them would appear when writing and the rest when reading.

Foreign key checks

First of all, there are foreign key checks or constraints. Shopware database is full of them. They take care of data integrity, meaning that they prevent you from deleting or changing things in one table, if it would mean leaving a non-functional reference in another table. Your script can easily end up with errors, preventing it from finishing its job.

There is a way around it, basically turning off the foreign key checks, but make sure you know, what are you doing – this can make a real mess in the database. I have “managed” to do this on one or two occasions and the cause of the troubles can be hard to spot later on. In my cases, the Shopware Administration stopped to load any products and customers in their respective listings, because I have deleted some rows in underlying tables and it took me some time to put it back together. I had to literally generate the expected rows back, so that the Shopware’s internal queries could work again.

So here is how you turn off the foreign key and other checks – on your own risk:

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

# your SQL statements here

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;

SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;

SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;

SET NAMES utf8;

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

# your SQL statements here

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;

SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;

SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;

SET SQL_NOTES=@OLD_SQL_NOTES;

Special characters

If you work with english language only, then this probably does not apply to you. Otherwise, you can get into trouble, when you try to write special characters from languages like german, czech and others. I am not exactly sure, why it happens, because theoretically everything in the database is in UTF-8 encoding, but it does happen, especially when writing into the JSON custom fields. My recommendation is to switch to repositories, if you encounter such problems, or you can use a method, that converts the special characters. Not a pretty solution, but if you are desperate enough, it can help. Here is my function for the german language special characters:

/**
 * Method for correcting german language characters
 *
 * @param string $string
 * @return string
 */
private function correctGermanChars (string $string) {
    $string = str_replace('u00f6', 'ö', $string);
    $string = str_replace('u00d6', 'Ö', $string);
    $string = str_replace('u00fc', 'ü', $string);
    $string = str_replace('u00dc', 'Ü', $string);
    $string = str_replace('u00e4', 'ä', $string);
    $string = str_replace('u00c4', 'Ä', $string);
    $string = str_replace('u00df', 'ß', $string);
    return $string;
}

/**

* Method for correcting german language characters

* @param string $string

* @return string

private function correctGermanChars (string $string) {

$string = str_replace('u00f6', 'ö', $string);

$string = str_replace('u00d6', 'Ö', $string);

$string = str_replace('u00fc', 'ü', $string);

$string = str_replace('u00dc', 'Ü', $string);

$string = str_replace('u00e4', 'ä', $string);

$string = str_replace('u00c4', 'Ä', $string);

$string = str_replace('u00df', 'ß', $string);

return $string;

}

Shopwarian.com