AI blog · DotApp PHP Framework 2.0

How to create database migrations with Installation.php in DotApp PHP Framework

Versioned DDL for a Shop module lives in Installation.php extending Installer. That is the preferred mechanism. Track versions with your own alreadyDone / markDone helpers on a shop_installations table. DB::migrate() is declared but not implemented — never call it. Tables are shop_*. timestamps() does not exist on SchemaBuilder — declare created_at yourself. When DDL goes through $qb->raw(), every ? in the string is a placeholder — including column comments such as SMS?. Do not put question marks in comments.

Common mistakes

What runs DDL

Query API for the data after install: How to use the database in DotApp PHP Framework — QueryBuilder or named RAW (WHERE email = :email plus ['email' => $email]). Values belong in the array.

Table names

Every table the Shop module creates starts with shop_. Core auth tables use Config::db('prefix') (default dotapp_) only. Never put module catalog rows under dotapp_*. Never create an unprefixed items table.

How versions run

Installation::module('Shop')->install();        // all versions, ascending
Installation::module('Shop')->install('1.0.1'); // up to and including 1.0.1
Installation::module('Shop')->uninstall();      // descending
    

install() sorts keys ascending and stops when version_compare($ver, $target, '<=') fails. uninstall() runs descending with >=. Each step starts with if (self::alreadyDone('1.0.0')) { return; } and calls markDone only from the execute success callback.

One-shot install.php

<?php
use Dotsystems\App\Modules\Shop\Installation;
Installation::module('Shop')->install();
    

The framework runs it once (event dotapp.module.Shop.install) then renames the file to installed_<md5>_install.php. The rename only prevents a second include. If 1.0.1 failed after 1.0.0 succeeded, alreadyDone still decides what to skip on a later install() call.

SchemaBuilder notes

Helpers that exist: id, string, integer, tinyInteger, bigInteger, boolean, decimal, float, text, json, enum, set (MySQL only), timestamp, datetime, date. timestamps() does not exist — declare created_at yourself. SchemaBuilder throws \InvalidArgumentException on invalid identifiers, unsupported types for the engine, unsigned() outside MySQL, SQLite drop/modify limits, and missing foreign-key targets. Always wrap in try/catch. Introspect first: DB::schemaBuilder()->tableExists('shop_items'), columnExists, indexExists.

try {
    DB::module('RAW')->schema(
        function ($qb) {
            $qb->createTableIfNotExist('shop_tags', function ($t) {
                $t->id();
                $t->string('name', 100)->nullable(false);
                $t->timestamp('created_at')->nullable();
                $t->unique('name', 'shop_tags_name_unique');
                $t->engine('InnoDB');
            });
        },
        function () { Logger::use()->info('shop_tags ready'); },
        function ($error) { Logger::use()->error('schema failed', (array) $error); }
    );
} catch (\Throwable $e) {
    Logger::use()->error('schema exception', ['msg' => $e->getMessage()]);
}
    

Complete Installation.php (1.0.0 and 1.0.1)

File: app/modules/Shop/Installation.php. Raw DDL keeps MySQL types explicit. Mark done only after execute succeeds. alreadyDone() is named RAW with :v in the second-argument array — that lookup is bound, not concatenated. Empty [] on CREATE TABLE is correct: there are no values to bind. Empty [] is only correct if the SQL contains zero ? characters — comments included.

<?php
namespace Dotsystems\App\Modules\Shop;

use Dotsystems\App\Parts\DB;
use Dotsystems\App\Parts\Installer;
use Dotsystems\App\Parts\Logger;

class Installation extends Installer
{
    public static function installer()
    {
        return [
            '1.0.0' => function () {
                if (self::alreadyDone('1.0.0')) { return; }

                DB::module('RAW')->q(function ($qb) {
                    $qb->raw(
                        "CREATE TABLE IF NOT EXISTS `shop_items` (
                            `id` INT NOT NULL AUTO_INCREMENT,
                            `title` VARCHAR(200) NOT NULL,
                            `active` TINYINT(1) NOT NULL DEFAULT 1,
                            `created_at` DATETIME NOT NULL,
                            PRIMARY KEY (`id`),
                            KEY `active_idx` (`active`)
                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
                        []
                    );
                })->execute(
                    function () { self::markDone('1.0.0'); },
                    function ($error) {
                        Logger::use()->error('Shop 1.0.0 failed', $error);
                    }
                );
            },

            '1.0.1' => function () {
                if (self::alreadyDone('1.0.1')) { return; }
                DB::module('RAW')->q(function ($qb) {
                    $qb->raw(
                        "ALTER TABLE `shop_items` ADD `price` DECIMAL(10,2) NOT NULL DEFAULT 0",
                        []
                    );
                })->execute(
                    function () { self::markDone('1.0.1'); },
                    function ($error) {
                        Logger::use()->error('Shop 1.0.1 failed', $error);
                    }
                );
            },
        ];
    }

    public static function uninstaller()
    {
        return [
            '1.0.0' => function () {
                DB::module('RAW')->q(fn($qb) => $qb->raw('DROP TABLE IF EXISTS `shop_items`', []))
                    ->execute(null, function ($e) {
                        Logger::use()->error('drop failed', $e);
                    });
            },
        ];
    }

    private static function ensureTable(): void
    {
        DB::module('RAW')->q(function ($qb) {
            $qb->raw(
                "CREATE TABLE IF NOT EXISTS `shop_installations` (
                    `id` INT NOT NULL AUTO_INCREMENT,
                    `installation_id` VARCHAR(100) NOT NULL,
                    `installed_at` DATETIME NOT NULL,
                    `status` TINYINT(1) NOT NULL DEFAULT 1,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `ver` (`installation_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
                []
            );
        })->execute(null, function ($e) {
            Logger::use()->error('installations table', $e);
        });
    }

    private static function alreadyDone(string $version): bool
    {
        self::ensureTable();
        $rows = DB::module('RAW')->q(function ($qb) use ($version) {
            $qb->raw(
                'SELECT 1 AS ok FROM `shop_installations` WHERE `installation_id` = :v AND `status` = 1 LIMIT 1',
                ['v' => $version]
            );
        })->all();
        return !empty($rows);
    }

    private static function markDone(string $version): void
    {
        DB::module('RAW')->q(function ($qb) use ($version) {
            $qb->insert('shop_installations', [
                'installation_id' => $version,
                'installed_at' => date('Y-m-d H:i:s'),
                'status' => 1,
            ]);
        })->execute(null, function ($e) {
            Logger::use()->error('markDone', $e);
        });
    }
}
    

Add a 1.0.1 uninstaller only if you need to drop price on the way down. Dropping shop_items in 1.0.0 already removes that column. Never write another module’s tables from this file.