Database Migrations

Festi plugins ship SQL migration files that are applied with the festi-migrate CLI. This page covers the rules a migration must follow to be safe across multiple environments.

File Layout

Each plugin has an install/ directory with:

install/
├── install.pgsql.sql     # fresh install — full schema (PostgreSQL)
├── install.mysql.sql     # fresh install — full schema (MySQL)
├── install.mssql.sql     # fresh install — full schema (MSSQL)
├── update.pgsql.sql      # incremental migration applied to existing installs
├── update.mysql.sql
├── update.mssql.sql
└── demo.pgsql.sql        # optional demo data

The host project usually has its own dumps/updatesN.sql files that apply the same changes to environments that already have the plugin installed. Both files must be updated for any schema change.

Multi-Engine Rule

Every schema change goes in all three engine files at once. Forgetting MySQL or MSSQL only surfaces in CI weeks later when someone runs the test matrix. The CI pipeline runs the same suite against all three engines, so a divergence is always caught — but the round trip back to fix it is expensive.

When the SQL syntax differs between engines, write three versions in parallel rather than picking the lowest common denominator.

Idempotency Rules

update.*.sql files are sometimes re-applied (manual reruns, partial failures, fresh dumps with old updatesN.sql). Every statement must be safe to run twice.

New Tables

CREATE TABLE IF NOT EXISTS "sitemap_urls" (
    "id"         BIGSERIAL NOT NULL PRIMARY KEY,
    "url"        VARCHAR(255) NOT NULL,
    ...
);

New Columns

ALTER TABLE "contents"
    ADD COLUMN IF NOT EXISTS "mdate" TIMESTAMP DEFAULT NULL;

New Types (PostgreSQL)

CREATE TYPE is not idempotent, so wrap it in a DO block:

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_type WHERE typname = 'contents_tag_status'
    ) THEN
        CREATE TYPE "contents_tag_status" AS ENUM ('active', 'disabled');
    END IF;
END
$$;

Inserts into Festi Tables

URL rules, sections, section actions, and menu items must check for an existing row first. The standard pattern is INSERT ... SELECT ... WHERE NOT EXISTS:

INSERT INTO "festi_url_rules" ("plugin", "pattern", "method")
SELECT 'Contents', '~^/sitemap\.xml$~', 'onDisplaySitemapXml'
WHERE NOT EXISTS (
    SELECT 1 FROM "festi_url_rules"
    WHERE "method" = 'onDisplaySitemapXml'
);

INSERT INTO "festi_url_rules2areas" ("id_url_rule", "area")
SELECT currval('festi_url_rules_id_seq'), 'default'
WHERE NOT EXISTS (
    SELECT 1 FROM "festi_url_rules2areas"
    WHERE "id_url_rule" = (
        SELECT "id" FROM "festi_url_rules"
        WHERE "method" = 'onDisplaySitemapXml'
    )
    AND "area" = 'default'
);

A plain INSERT INTO festi_url_rules (...) VALUES (...) is not idempotent and will create duplicate rules on the second run. The same applies to festi_section_actions, festi_sections, festi_menus, and festi_sections_user_types_permission.

Updates

UPDATE "contents_tags"
SET "status" = 'active'
WHERE "status" IS NULL;

UPDATE is naturally idempotent — running it twice is a no-op when the predicate no longer matches. No special wrapping needed.

Transaction Wrapping

Wrap every migration in BEGIN; ... COMMIT; so a failure halfway through rolls back instead of leaving a half-applied schema. PostgreSQL supports transactional DDL, which makes this fully atomic.

BEGIN;

CREATE TABLE IF NOT EXISTS ...;
ALTER TABLE ...;
INSERT INTO ... WHERE NOT EXISTS ...;

COMMIT;

MySQL does not roll back DDL statements, so partial application is possible there. Keep MySQL migrations short and split a complex migration into several files if necessary.

Plugin Migration vs Project Migration

When you change a plugin's schema:

  1. Add the new statements to the plugin's install/install.*.sql (so fresh installs get the column).
  2. Add the same statements to the plugin's install/update.*.sql (so existing installs get the column when the plugin is upgraded).
  3. Add a project-level dumps/updatesN.sql in the host project so festi-migrate will apply the change in environments that already ran every previous migration.

Step 3 is easy to forget. The plugin upgrade alone is not enough — host projects only run their own dumps/ files via festi-migrate.

Naming and Ordering

Project migration files are applied in filename sort order. Use zero-padding or simple incrementing numbers (updates78.sql, updates79.sql, …) so the order stays stable. Never edit a previously applied migration; create a new one instead.

The plugin's install/update.*.sql is a single growing file — append new sections at the bottom and keep them idempotent so reruns are safe.

Common Review Comments

  • INSERT without WHERE NOT EXISTS for festi_url_rules, festi_section_actions, festi_menus.
  • ALTER TABLE ... ADD COLUMN without IF NOT EXISTS.
  • Schema change applied to one engine file but not the other two.
  • New plugin column added to install.*.sql but not to update.*.sql.
  • Plugin migration added but no matching dumps/updatesN.sql in the host project.
  • CREATE TYPE outside a DO ... IF NOT EXISTS block.
  • Editing an existing migration file instead of creating a new one.