Database Migrations and Operations

Table of contents

  1. Overview
  2. Migration
  3. Creating and Managing Migrations
  4. Supported Field Types & Modifiers for Migrations
  5. Tips and Common Pitfalls
  6. DB Class Reference


1. Overview Table of Contents

Chappy.php supports full migration-based database management using its built-in CLI. This includes:

  • Creating new tables
  • Updating existing tables
  • Dropping all tables
  • Refreshing the schema

Migrations are managed using the migrations table, which keeps track of which files have been executed. This ensures that only new migration files are applied each time you run php console migrate.


2. Migration Table of Contents

Performing a database migration is one of the first tasks that is completed when setting up a project. By default, the .env file is configured to use SQLite. If you want to use a MySQL or MariaDB as your database you will have to update the .env file. An example is shown below:

# Set to mysql or mariadb for production
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
# Set to your database name for production
DB_DATABASE=my_db
DB_USER=root
DB_PASSWORD=my_secure-password

Next, create the database using your preferred method. We like to use phpMyAdmin and Adminer.

Finally, you can run the migrate command shown below:

php console migrate

Common Commands

# Run all pending migrations
php console migrate

# Refresh all tables (drop and re-run)
php console migrate:refresh

# Drop all tables without rerunning
php console migrate:drop-all

💡 The migrate:refresh command is great during local development when you want a clean slate.


3. Creating and Managing Migrations Table of Contents

A. Creating a New Table

Create a migration by running the make:migration command. An example is shown below for a table called foo:

php console make:migration foo

Once you perform this action a migration class is created with two functions called up and down. Up is used to create a new table or update an existing one. Down drops an existing table. We usually don’t modify the down function. The output from the previous command is shown below:

namespace Database\Migrations;
use Core\Lib\Database\Schema;
use Core\Lib\Database\Blueprint;
use Core\Lib\Database\Migration;

/**
 * Migration class for the foo table.
 */
class Migration1741215401 extends Migration {
    /**
     * Performs a migration.
     *
     * @return void
     */
    public function up(): void {
        Schema::create('foo', function (Blueprint $table) {
          $table->id();

      });
    }

    /**
     * Undo a migration task.
     *
     * @return void
     */
    public function down(): void {
        Schema::dropIfExists('foo');
    }
}

The up function automatically creates a $table variable set to the value you entered when you ran the make:migration command along with a function call to create the table. In the code snippet below we added some fields.

namespace Database\Migrations;
use Core\Lib\Database\Schema;
use Core\Lib\Database\Blueprint;
use Core\Lib\Database\Migration;

/**
 * Migration class for the foo table.
 */
class Migration1741215401 extends Migration {
    /**
     * Performs a migration.
     *
     * @return void
     */
    public function up(): void {
        Schema::create('foo', function (Blueprint $table) {
            $table->id();
            $table->string('bar', 150)->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->integer('user_id');
            $table->index('user_id');
      });
    }

    /**
     * Undo a migration task.
     *
     * @return void
     */
    public function down(): void {
        Schema::dropIfExists('foo');
    }
}

Common Field Methods

  • $table->id() — Creates an id column (auto-increment primary key)
  • $table->string('name', 255) — Varchar column
  • $table->timestamps() — Adds created_at and updated_at
  • $table->softDeletes() — Adds deleted_at for soft deletion
  • $table->index('user_id') — Adds an index
  • $table->string('bar', 25)->unique() - Chaining the unique function makes a field unique.

Run the migration and the console output, if successful, will be shown below:

Migrate output example

Figure 1: Console output after running the migrate command.

Open your database management software package and you will see that the table has been created.

New database table

Figure 2 - New database table after migration was performed


B. Updating an Existing Table

To add columns:

php console make:migration foo --update

Configure migration for update:

public function up(): void {
    Schema::table('foo', function (Blueprint $table) {
        $table->string('bar', 150)->nullable()->default('Pending');
        $table->index('bar');
    });
}

🔄 Adding the --update flag generates a migration file for updating your table.


C. Renaming an Existing Table

To rename an existing table:

php console make:migration bar --rename=foo

Resulting up function:

public function up(): void {
    Schema::rename('bar', 'foo');
}

🔄 Adding the --rename=foo flag generates a migration file for renaming the table to foo.


D. Dropping a Table

You can drop a table manually using:

Schema::dropIfExists('foo');

This is automatically included in your migrations class.


E. Renaming a Column

To rename an existing column use the renameColumn function:

public function up(): void {
    Schema::table('test', function (Blueprint $table) {
        $table->renameColumn('foo', 'bar');
    });
}

All renaming functions accepts two arguments:

  • $from - The column’s original name
  • $to - The column’s new name

Renaming indexes

To rename a column that is indexed use the renameIndex function:

public function up(): void {
    Schema::table('test', function (Blueprint $table) {

        $table->renameIndex('my_index', 'blah');
    });
}

Renaming Primary Keys

To rename a table’s primary key use the renamePrimaryKey function:

public function up(): void {
    Schema::table('test', function (Blueprint $table) {
        $table->renamePrimaryKey('id', 'id_new');
    });
}

Rename Unique Constrained Columns

To rename a column with a unique constraint use the renameUnique function:

public function up(): void {
    Schema::table('test', function (Blueprint $table) {
        $table->renameUnique('bar', 'blah');
    });
}

Rename Foreign Keys

To rename a column with a foreign key constraint use the renameForeign function:

public function up(): void {
    Schema::table('test', function (Blueprint $table) {
        $table->renameForeign('foreign_key', 'new_fk');
    });
}


F. Dropping Columns

You can drop an individual column using the dropColumns function.

$table->dropColumns('bar');

Multiple columns can be dropped by providing the column names as an array.

$table->dropColumns(['foo', 'bar']);

This function checks if column is an index or primary key. If these conditions are detected a warning is printed to the console and that field is skipped.

Dropping Primary Keys

To drop a primary key use the dropPrimaryKey function.

public function up(): void {
    Schema::table('test', function (Blueprint $table) {
        $table->dropPrimaryKey('id');
    });
}

This function accepts two arguments:

  • $column - The name of the column to be dropped.
  • $preserveColumn - A boolean flag that defaults to true. Set to true if you want to keep the column and drop only the foreign key constraint. The default value is true.

Dropping Indexes

To drop an indexed value use the dropIndex function.

public function up(): void {
    Schema::table('test', function (Blueprint $table) {
        $table->dropIndex('my_index', true);
    });
}

This function accepts two arguments:

  • $column - The name of the column to be dropped.
  • $preserveColumn - A boolean flag that defaults to true. Set to true if you want to keep the column and drop only the primary key constraint. The default value is true.

Dropping Unique Constrained Columns

To drop a column with the unique constraint use the dropUnique function.

public function up(): void {
    Schema::table('test', function (Blueprint $table) {
        $table->dropUnique('bar', true);
    });
}

This function accepts two arguments:

  • $column - The name of the column to be dropped.
  • $preserveColumn - A boolean flag that defaults to true. Set to true if you want to keep the column and drop only the unique index constraint. The default value is true.

Dropping Foreign Keys

To drop a foreign key use the dropForeign function (MySQL only).

public function up(): void {
    Schema::table('test', function (Blueprint $table) {
        $table->dropForeign('my_foreign_key', true);
    });
}

This function accepts two arguments:

  • $column - The name of the column to be dropped.
  • $preserveColumn - A boolean flag that defaults to true. Set to true if you want to keep the column and drop only the foreign key constraint. The default value is true.


F. Migrations With Raw SQL Queries

You are able to perform raw SQL queries within a migration. You can create or update a table and then use SQL queries to add values. This is useful if your database table already has data.

Example:

public function up(): void {
    $sql = "UPDATE products SET `has_options` = 0, `inventory` = 0";
    DB::getInstance()->query($sql);
}

As shown above, create your SQL statement and then chain the query function to a static call to the getInstance variable. You can also do this after a call to the Schema::create or Schema::table static function calls.


G. Migration CLI Commands

1. make:migration

Generates a new migration class.

php console make:migration foo

Rename Example

Generates a migration class for renaming a table.

php console make:migration foo --rename=bar

foo is the original and bar will be its new name.

Update Example

Generates a migration class for updating a table.

php console make:migration foo--update


2. migrate

Performs all pending migrations.

Seed Example

Use the --seed flag to seed your database after migrations have completed.

php console migrate:fresh --seed


3. migrate:drop-all

Drops all tables.


4. migrate:fresh

Drops all tables and performs migration.

Seed Example

Use the --seed flag to seed your database after migrations have completed.

php console migrate:fresh --seed


5. migrate:refresh

Drops all tables one at a time and performs migration.

Seed Example

Use the --seed flag to seed your database after migrations have completed.

php console migrate:refresh --seed

Step Example

php console migrate:refresh --step=2

Undo last 2 previous migration and then runs migration against all pending migrations.


6. migrate:rollback

Performs a roll back of the last batch of migrations.

php console rollback

Batch Example

php console migrate:rollback --batch=2

Roll back migrations for batch number 2

Step Example

php console migrate:rollback --step=2

Undo last 2 previous migrations.


7. migrate:status

Displays status of ran and pending migrations.


4. Supported Field Types & Modifiers for Migrations Table of Contents

Chappy.php’s migration system includes a flexible schema builder via the Blueprint class. It supports most standard SQL column types, modifiers, and constraints across both MySQL and SQLite databases. The table below outlines supported fields and how to define them.


A. Field Types

Modifier Description
id() Adds a primary key (AUTO_INCREMENT or AUTOINCREMENT)
string('name', 255) Adds a VARCHAR field (or TEXT for SQLite)
text('description') Adds a TEXT field
integer('age') Adds an INT (or INTEGER on SQLite)
bigInteger('views') Adds a BIGINT field
mediumInteger('count') Adds a MEDIUMINT field
smallInteger('flag') Adds a SMALLINT field
tinyInteger('bool_flag') Adds a TINYINT (or INTEGER on SQLite)
unsignedInteger('num') Adds UNSIGNED INT (MySQL only) or INTEGER (SQLite)
decimal('amount', 10, 2) Adds a DECIMAL field with precision and scale
float('ratio', 8, 2) Adds a FLOAT field
double('rate', 16, 4) Adds a DOUBLE field
boolean('active') Adds a TINYINT(1) to represent boolean values
date('start_date') Adds a DATE field
datetime('event_time') Adds a DATETIME field
time('alarm') Adds a TIME field
timestamp('published_at') Adds a TIMESTAMP field
timestamps() Adds created_at and updated_at fields
softDeletes() Adds a soft delete deleted field
enum('status', [...]) Adds an ENUM (MySQL only, falls back to TEXT in SQLite)
uuid('uuid') Adds a CHAR(36) for UUIDs (or TEXT in SQLite)


B. Column Modifiers

Modifier Description
nullable() Makes the column NULL-able
default('value') Assigns a default value to the most recent column
index('column') Adds an index to the specified column
foreign('col', 'id', 'table') Adds a foreign key constraint (MySQL only)


C. Notes on Compatibility

  • 🐬 MySQL: All features are supported, including foreign keys and ENUM.
  • 🐘 SQLite: Lacks native support for ENUM, foreign keys (unless enabled), and strict UNSIGNED types. Your migration code gracefully degrades in these cases.


D. Example Using Many Field Types 

Schema::create('products', function(Blueprint $table) {
    $table->id();
    $table->string('name', 255)->default('Unnamed');
    $table->decimal('price', 10, 2)->nullable();
    $table->boolean('in_stock')->default(true);
    $table->unsignedInteger('category_id');
    $table->foreign('category_id', 'id', 'categories');
    $table->timestamps();
    $table->softDeletes();
});

🔐 Reminder: Use foreign keys only when using MySQL. They’ll be ignored silently on SQLite.


5. Tips and Common Pitfalls Table of Contents

✅ Use nullable()->default() to safely add optional fields. ⚠️ Foreign keys and ENUM types are only supported in MySQL. 🐘 SQLite ignores unsupported column modifiers silently. 🧪 Always verify your migrations using a database viewer like phpMyAdmin or SQLiteBrowser. 📄 Log messages in the CLI will show SUCCESS: Adding Column... or SUCCESS: Creating Table....


6. DB Class Reference Table of Contents

The DB class in Chappy.php is a singleton-based database utility that wraps PDO functionality and adds query building, logging, and utility helpers.

A. 🔁 getInstance() 

DB::getInstance(): DB

Returns a singleton instance of the DB class. Used to initiate or access the shared database connection.

Example:

$db = DB::getInstance();


B. 🔗 getPDO() 

getPDO(): PDO

Returns the raw PDO instance. Useful for advanced operations or custom queries not handled by the built-in query method.

Example

$dbDriver = DB::getInstance()->getPDO()->getAttribute(\PDO::ATTR_DRIVER_NAME);

Here we chain the getPDO function to getInstance. Then we chain the getAttribute function to getPDO to retrieve the name of the current DB driver.


C. 🧱 query($sql, $params = [], $class = false)

Prepares, binds, and executes a SQL query.

Parameters:

  • $sql - Raw SQL string
  • $params - Array of bound values
  • $class - Optional class name to map results

Returns: DB instance with result data loaded


D. 🔄 groupByColumn($column)

Formats a column name for use in a GROUP BY clause. On MySQL or MariaDB, wraps the column in ANY_VALUE() to prevent ONLY_FULL_GROUP_BY errors.

Example:

$col = DB::groupByColumn('users.name');


E. 📋 results() and first()

  • results() — Returns all rows from the last query
  • first() — Returns only the first row of the last query


F. 🔢 count() and lastID()

  • count() — Number of rows affected or returned by the last query
  • lastID() — Returns the ID from the last INSERT operation


G. 🔍 find(), findFirst(), and findTotal()

  • find() — Runs a flexible SELECT query
  • findFirst() — Same as find(), but returns only the first result
  • findTotal() — Returns a count for the specified table and conditions


H. 🧪 valueExistsInColumn()

Checks if a value exists inside a JSON or string column. Compatible with both MySQL (uses JSON_CONTAINS) and SQLite (uses LIKE).