write-upgrade-sql-script

write-upgrade-sql-script

Heads-up — cross-repo

Since PrestaShop 9.x, upgrade SQL scripts are not in the core repository. They live in the autoupgrade module: https://github.com/PrestaShop/autoupgrade (see upgrade/sql/{version}.sql). This skill describes what to write; the file lands in autoupgrade. Agents working in core should hand the change off to a maintainer or open the PR against autoupgrade directly.

When to use this skill

Whenever a change merged into core needs an existing shop to apply a database modification at upgrade time. Common cases:

1. Schema change — new table, new column, new index, type change.
2. Default fixtures / configuration update — a new row in ps_configuration, a new row in ps_feature_flag, etc., that newly-installed shops get from the XML/install fixtures but existing shops do not.
3. Feature-flag state transition — promoting a flag from beta to stable and enabling it for existing installs.

A new install reads the install fixtures directly; only upgrading shops need this script.

Instructions

1. Determine the target PrestaShop version (e.g., 9.1.0).
2. Open or create upgrade/sql/{version}.sql in the autoupgrade module repo.
3. Append the SQL needed for your change. Use the section that matches your case below.
4. Make every statement idempotent — the script may be run more than once and must not error or corrupt data on re-run.

Case (a) — schema change

-- Add column only if it doesn't exist (MySQL 8.0+ supports IF NOT EXISTS on ALTER TABLE ADD COLUMN)
ALTER TABLE `PREFIX_xxx`
    ADD COLUMN IF NOT EXISTS `new_field` INT(11) NOT NULL DEFAULT 0;

-- Create table only if it doesn't exist
CREATE TABLE IF NOT EXISTS `PREFIX_xxx_yyy` (
    `id_xxx` INT(11) UNSIGNED NOT NULL,
    `id_yyy` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`id_xxx`, `id_yyy`)
) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8mb4;

Case (b) — default fixtures / configuration update

-- Insert configuration row only if not present (preserves merchant overrides)
INSERT INTO `PREFIX_configuration` (`name`, `value`, `date_add`, `date_upd`)
SELECT 'PS_NEW_OPTION', '1', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `PREFIX_configuration` WHERE `name` = 'PS_NEW_OPTION');