Module tables

Key-value settings are not always enough: reviews, requests and history are kept in a table of their own. PrestaShop has no migrations like frameworks do. A table is created with an SQL query in the install() method, and structure changes between module versions are described in upgrade scripts.

Creating on install

Queries are run by the Db class. Put the table creation into a separate method of the main class:

// modules/acmehello/acmehello.php
private function createTables(): bool
{
    $sql = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'acmehello_review` (
        `id_review` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_product` INT UNSIGNED NOT NULL,
        `author` VARCHAR(100) NOT NULL,
        `rating` TINYINT UNSIGNED NOT NULL DEFAULT 5,
        `content` TEXT,
        `date_add` DATETIME NOT NULL,
        PRIMARY KEY (`id_review`),
        KEY `id_product` (`id_product`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4';
 
    return Db::getInstance()->execute($sql);
}

Db::getInstance() returns the connection to the shop database, execute() runs a query and returns true or false. The _DB_PREFIX_ constant is the table prefix, usually ps_, and _MYSQL_ENGINE_ is the table engine, usually InnoDB.

Names in PrestaShop follow a convention: a table is the module name plus the entity in the singular, the primary key is id_ plus the entity.

Calling it from install and uninstall

// modules/acmehello/acmehello.php
public function install(): bool
{
    return parent::install()
        && $this->createTables()
        && $this->registerHook('displayHome')
        && $this->registerHook('displayFooterProduct');
        // the other hooks and settings as before
}
 
public function uninstall(): bool
{
    return Db::getInstance()->execute('DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'acmehello_review`')
        && Configuration::deleteByName('ACMEHELLO_GREETING')
        && parent::uninstall();
}

Reset the module and check that the table exists:

php bin/console prestashop:module reset acmehello

SHOW TABLES LIKE 'ps_acmehello%';

The query returns one row — ps_acmehello_review.

Why upgrade scripts are needed

Version 1.0.0 of the module is already installed in shops, and the table holds reviews. Version 1.1.0 needs a new id_customer column. Changing CREATE TABLE is not enough: install() does not run for an installed module, and a reset would delete all reviews. This is what upgrade scripts are for.

An upgrade script

Create the upgrade folder by hand and a file with the new version number in its name:

modules/acmehello/
├── acmehello.php
├── upgrade/
│   └── upgrade-1.1.0.php
└── views/

<?php
// modules/acmehello/upgrade/upgrade-1.1.0.php
if (!defined('_PS_VERSION_')) {
    exit;
}
 
function upgrade_module_1_1_0($module)
{
    return Db::getInstance()->execute(
        'ALTER TABLE `' . _DB_PREFIX_ . 'acmehello_review`
         ADD `id_customer` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `id_product`'
    );
}

The function name is upgrade_module_ plus the version with underscores instead of dots. $module receives the module object: through it you can, for example, subscribe to a new hook. The function returns true on success.

Running the upgrade

1. Raise the version in the constructor: $this->version = '1.1.0';.
2. Add the id_customer column to CREATE TABLE as well — for those who install the module for the first time.
3. Run the upgrade:

php bin/console prestashop:module upgrade acmehello

The engine compares the version in the code with the version stored in the database and runs all scripts between them in order. The reviews stay in place, and the table gets the new column. In the back office the same happens on the "Module Manager → Updates" page.

Common mistake

A new column is added only to the upgrade script and forgotten in CREATE TABLE in install(). Old shops work fine, but a fresh install has no such column, and the module fails on the first query. Every structure change goes into two places: the upgrade script and the table creation query.