Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 30 ยท ObjectModel and Data

Tables, Installers and Upgrades

Create tables when a PrestaShop module installs, ship upgrade-x.y.z.php files, handle the table prefix and clean up on uninstall, with a runnable migration stand-in.

Most useful modules store data of their own: notes, wishlists, delivery rules, log lines. PrestaShop does not create those tables for you. Your module has to create them when it is installed, change them when a new version needs a different shape, and remove them when the merchant uninstalls it. Getting these three moments right is what separates a module you can ship from one that works only on your laptop.

This lesson covers the install script, the table prefix, upgrade files named upgrade-x.y.z.php, how versions are compared, and a clean uninstall. The real module code is read-only on this site. The runnable examples are stand-ins: a small migration runner on SQLite that follows the same rules, so you can see the ordering and the failure behavior. Models that sit on top of these tables are covered in ObjectModel and Entities.

The three moments

flowchart LR
    I["install()<br/>create tables, register hooks,<br/>set default configuration"] --> U["upgrade files<br/>run when the code version<br/>is newer than the stored version"]
    U --> D["uninstall()<br/>drop tables, delete<br/>configuration, remove tabs"]

A module's database life: created once, changed many times, removed once.

PrestaShop stores the installed version of every module in its module table. Your main class declares the version of the code in $this->version. When the two differ, PrestaShop knows an upgrade is pending.

The table prefix

A PrestaShop database has a prefix in front of every table name. The default is ps_, but the merchant (or the host) can choose anything, and several shops can share one database with different prefixes. The prefix is chosen at install time and is available in code as the constant _DB_PREFIX_.

So you never write ps_acme_note in a module. You write _DB_PREFIX_ . 'acme_note'. A hard-coded ps_ works on your machine and fails on every shop with another prefix. Similarly, _MYSQL_ENGINE_ holds the storage engine PrestaShop was installed with (normally InnoDB), and you should use it instead of typing ENGINE=InnoDB.

Name your tables with your own short vendor or module prefix after the shop prefix (acme_note, not note), so you never clash with a core table or another module.

The install script

A module's install() method must call parent::install() first, because that registers the module in the database. Then it adds its own work and returns true only if everything succeeded. A common pattern is to keep each concern in its own method:

PHP
// modules/acmenote/acmenote.php (read-only, shortened)
public function install(): bool
{
    return parent::install()
        && $this->installDatabase()
        && $this->registerHook('displayProductAdditionalInfo')
        && Configuration::updateValue('ACMENOTE_MAX_LENGTH', 200);
}

private function installDatabase(): bool
{
    $sql = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'acme_note` (
        `id_acme_note` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_customer` INT(10) UNSIGNED NOT NULL,
        `content` TEXT NOT NULL,
        `date_add` DATETIME NOT NULL,
        PRIMARY KEY (`id_acme_note`),
        KEY `id_customer` (`id_customer`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;';

    return Db::getInstance()->execute($sql);
}

Points worth noticing:

  • The chain with && stops at the first step that returns false, and install() then fails. The merchant sees an error instead of a half-working module.
  • CREATE TABLE IF NOT EXISTS makes the step safe to repeat, for example if someone uninstalled the module without data removal and installs it again.
  • The primary key follows the core convention id_<table>, which is what ObjectModel expects.
  • Use utf8mb4, so emoji and all scripts work.
  • Db::getInstance()->execute() runs one statement and returns a boolean. It does not split a file with several statements, so run them one at a time.

Many generated modules keep SQL in a file such as sql/install.php that builds an array of statements and loops over it. That is only an organizing choice. You can also keep the statements in the main class.

For tables with translated text or per-shop values, the core convention adds <table>_lang and <table>_shop tables. Their layout is explained in the ObjectModel lesson.

Upgrade files

Once a module has shipped, its tables are on real shops with real data. You cannot drop and re-create them. Instead you ship an upgrade file for each version that changes something. By convention the files live in an upgrade/ folder in the module and are named upgrade-<version>.php:

PHP
// modules/acmenote/upgrade/upgrade-1.1.0.php (read-only)
<?php
if (!defined('_PS_VERSION_')) {
    exit;
}

function upgrade_module_1_1_0($module)
{
    $table = _DB_PREFIX_ . 'acme_note';

    $exists = Db::getInstance()->executeS(
        'SHOW COLUMNS FROM `' . $table . '` LIKE \'color\''
    );
    if ($exists) {
        return true;
    }

    return Db::getInstance()->execute(
        'ALTER TABLE `' . $table . '` ADD `color` VARCHAR(16) NOT NULL DEFAULT \'yellow\''
    );
}

The function name is upgrade_module_ plus the version with dots replaced by underscores, so 1.1.0 becomes upgrade_module_1_1_0. It receives the module instance and returns true on success. Returning false signals a failed upgrade, and PrestaShop reports an error instead of treating the module as upgraded.

Rules to follow:

  1. One file per version that changes data. You do not need a file for versions with only code changes.
  2. Never edit a released file. Shops that ran it will not run it again. Add a new version.
  3. Make each step safe to repeat. The example above checks that the column does not already exist. A step can run on a database that someone already changed by hand or that failed halfway last time.
  4. A fresh install does not run upgrade files. install() must create the latest structure itself. When you add the color column in 1.1.0, you also add it to installDatabase(), otherwise new shops would miss it.
  5. Bump $this->version in the main class (and in config.xml if you keep one) in the same release.

The point about fresh installs causes many bugs, because it means the structure is described twice: once in install() and once as the sum of the upgrade files. Test both paths: install the latest version on an empty shop, and upgrade from the oldest version you support, then compare the tables.

How versions are compared

Versions are compared with PHP's version_compare(), not as strings and not as decimals. That matters because 1.10.0 is newer than 1.9.0, although as a string it sorts before it. PrestaShop collects the upgrade files whose version is higher than the stored one and not higher than the new one, and runs them in ascending version order. If a shop jumps from 1.0.0 to 1.3.0, every file in between runs, in order.

Use plain major.minor.patch versions. Suffixes like -beta work with version_compare, but keep release numbers boring to avoid surprises.

A migration runner you can run

The stand-in below mimics the logic: a modules table stores the installed version, install() creates the latest structure, and an upgrader runs the steps between the stored and the target version. It follows the rules above. What it does not do is find files on disk or call real PrestaShop classes; the steps are PHP closures in an array, and SQLite replaces MySQL.

PHP Stand-in: install the latest schema, then upgrade older shops

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Both shops end with the same columns and index, although they got there differently. If they ever differ, either installDatabase() or one of the upgrade files is wrong, and a test that compares them would catch it. The second upgrade call on shop A does nothing, because the stored version already matches.

Ordering and failure

Two details deserve their own demonstration: the order is numeric by version, and a failing step stops the chain. In this stand-in the version is stored after each successful step, so after a failure the shop sits at the last good version and the next attempt resumes there. Treat that as a design you should be able to explain for your own modules; check how your target PrestaShop version records progress before relying on it.

The same example also performs a clean uninstall: it removes the tables whose names carry the shop prefix and the module's configuration rows.

PHP Stand-in: version order, a failing step and uninstall cleanup

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The string sort puts 1.10.0 before 1.2.0, which would run steps in the wrong order. The cleanup deleted only rows and tables belonging to the module and left PS_SHOP_NAME alone: never delete by a broad pattern.

Uninstall cleanup

PrestaShop calls your uninstall() when the merchant removes the module. Call parent::uninstall() and undo what install() did:

PHP
// modules/acmenote/acmenote.php (read-only, shortened)
public function uninstall(): bool
{
    return $this->uninstallDatabase()
        && Configuration::deleteByName('ACMENOTE_MAX_LENGTH')
        && parent::uninstall();
}

private function uninstallDatabase(): bool
{
    return Db::getInstance()->execute(
        'DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'acme_note`'
    );
}

Things to think about before you write DROP TABLE:

  • Is it safe to delete merchant data? Uninstalling is not always a decision to destroy data; a merchant may reinstall later. Some modules drop everything, others keep the tables and offer a "delete data" setting. Whichever you choose, document it.
  • Remove every trace: configuration keys, back office tabs, registered custom hooks you added, uploaded files in the module's own folder.
  • Never touch core tables. Remove only rows that belong to you.
  • Disabling is not uninstalling. disable() should keep tables and data.

Disabled or reset modules also interact with caches; after install and upgrade runs, clear the cache as described in Setting Up a Development Environment.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How should a module refer to its own table in SQL?

    The prefix is chosen by each shop at install time, so DB_PREFIX must be used. A hard-coded ps_ breaks on shops with another prefix.
  2. 2. A shop at version 1.0.0 updates to code version 1.3.0. Which upgrade files run, and in what order?

    Every upgrade file with a version above the stored one and up to the new one runs, sorted with version_compare. That is why each step can assume the previous ones ran.
  3. 3. You add a color column in upgrade-1.1.0.php. What else must you do for new shops?

    A fresh install does not run upgrade files, so install() must create the latest structure itself. Otherwise new shops would lack the column.
  4. 4. Why should an upgrade step check whether a column already exists before adding it?

    Steps may be retried or meet a database that already has the change. Making them safe to repeat avoids errors such as a duplicate column.
  5. 5. Which sorting puts 1.10.0 after 1.9.0?

    version_compare() compares the numeric parts, so 1.10.0 is newer than 1.9.0. A string sort would place 1.10.0 first.

Summary

  • Create tables in install() with _DB_PREFIX_ and _MYSQL_ENGINE_, chain steps with && and return false on any failure.
  • Ship upgrade/upgrade-x.y.z.php files containing upgrade_module_x_y_z($module); they run in version_compare order for versions above the stored one.
  • Make every step safe to repeat, never edit a released file, and always update install() to the latest structure too.
  • Test two paths: fresh install of the latest version, and upgrade from the oldest supported version.
  • In uninstall(), remove only what belongs to the module (tables, configuration, tabs), and decide consciously about merchant data.

Next: Working with Products, Carts and Orders