Maintaining the database

Database structure definition

Global definition

The database structure of PrestaShop can be found in install/data/db_structure.sql (8.0.x releases example).

It is used one time, during the installation of PrestaShop. It contains the structure of almost all tables. If a table needs to be added or modified, this the file you should open.

Table in DB but not in db_structure.sql

Some tables are now linked to Doctrine entities (i.e stocks). If their ObjectModel (= legacy) equivalent does not exist, the entity is probably only managed by Doctrine.

In that case, updating the table can be done by modifying the related entity stored in src/PrestaShopBundle/Entity/.

Database content

The default database content is stored in XML files in install/data/xml/. There is one file per entity (= table). XML files are structured following ObjectModel structure.

These files are used during the PrestaShop installation as well.

Another file is being used to load data during the install process: install/data/db_data.sql. Some versions of PrestaShop do not use it so you might not always find it in the ZIP archive.

Structure and content upgrades

Defining changes

Once PrestaShop is installed, the default structure and content files we saw are not used anymore.

If a new release of PrestaShop must bring changes to the existing database, an upgrade file must be created along the db_structure.sql update. This SQL file will be stored in the auto upgrade module in the folder /upgrade/sql/.

Its name is the PrestaShop version on which the change will be applied.

For instance, here is the file 8.0.0.sql, used by shops upgrading to 8.0.0 or later:

SET SESSION sql_mode='';
SET NAMES 'utf8mb4';

/* PHP:ps_800_add_security_tab(); */;

In there we can read the SQL queries to execute when upgrading to 8.0.0. Each of them alters the structure and/or modify the existing data. In case you have complex algorithms to run, you can call PHP code with the PHP: keyword.

To make the code callable, a dedicated file has to be created in /upgrade/php/ with a function in it. This file and function must have the same name as we saw in the SQL upgrade file.

If we reuse the previous example, we will find the corresponding file /upgrade/php/ps_800_add_security_tab.php:

function ps_800_add_security_tab()
  // Code inserting or updating values in database

PrestaShop does not expect these functions to return anything. It will always consider it was run without failure.

Applying changes

Applying the changes is covered in the upgrade page.