Database Migration

Introduction

Managing database changes by hand is risky and error-prone. Doppar's migration system takes the guesswork out of the equation by automating and organizing your schema changes — so you can focus more on building features and less on database headaches.

Doppar provides a seamless and structured way to manage your database migrations, making it easy to evolve your database schema over time. With Doppar’s migration system, you can track changes, collaborate with your team, and keep your development, staging, and production environments in sync.

Creating a Migration

To generate a new migration file in Doppar, use the make:migration command provided by the Pool Console. This command will scaffold a new migration class in the database/migrations directory.

php pool make:migration create

This will create a empty table name migration file inside your database/migration folder.

Now if you want to create a migration file with specific name including table name, follow this way.

php pool make:migration create_users_table --create=users

This commands

• create_users_table: The name of the migration file
• --create=users: Indicates that this migration is intended to create a users table

Once created, you can define the table structure inside the generated file using Doppar’s schema builder.

Define Basic Schema

Here, users is the table name. This command will generate a new migration file by returning an anonymous class instance. Now assume we are going to update this for define our database schema.

<?php

use Phaseolies\Support\Facades\Schema;
use Phaseolies\Database\Migration\Blueprint;
use Phaseolies\Database\Migration\Migration;

return new class extends Migration
{
    /**
     * Run the migrations
     *
     * @return void
     */
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password', 100);
            $table->string('remember_token', 100)->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

Run Migration

To migrate your all file or newly created migration files, run this migrate command

php pool migrate

This command will use your default database connection. You can run migration for a specific connection by running this command

php pool migrate --connection=mysql

This allows you to run a single migration file without affecting other migrations.

Add Column to Existing Table

In Doppar, you can extend your existing database tables by creating a migration that adds new columns. This is particularly useful when evolving your schema without interrupting current data.

Use the pool CLI tool to generate a migration that targets an existing table. Here in this command we are targeting users table.

php pool make:migration add_company_name --table=users

Doppar follows a convention-over-configuration approach, so naming migrations clearly helps organize your schema evolution.

Edit the generated migration file, typically found in the database/migrations/ directory. Use the Schema::table method to modify the table:

Schema::table('users', function (Blueprint $table) {
    $table->string('company_name', 50)->after('email');
});

💡 The after() function is not supported by the pgsql driver. If you use this, doppar will ignore this silently.

Now run migrate command to add company_name column to your users table.

WARNING

You must use the after() function when adding new columns using migration feature. This is a required constraint for Doppar to apply schema changes correctly. If you attempt to add multiple columns in a single migration, it will not be executed properly.

Run Specific Migration

Doppar allows you to run a specific migration file by using the --path option. This is useful when you only want to execute a particular migration without running the entire batch.

php pool migrate --path=/database/migrations/your_migration_file.php

Run a specific migration file

php pool migrate --path=/database/migrations/your_migration_file.php

Run a specific migration file on a specific connection

php pool migrate --connection=mysql --path=/your_migration_file.php

Refreshing Migrations

The php pool migrate:fresh command is a powerful tool in Doppar that allows you to reset and re-run all your migrations. This is particularly useful during development when you need to rebuild your database schema without manually rolling back and reapplying each migration.

When you run:

php pool migrate:fresh

Doppar performs the following actions

• Rolls back all existing migrations by executing the down() methods.
• Re-runs all migrations by executing the up() methods

This process effectively rebuilds your entire database schema.

Refresh the database for a specific connection

php pool migrate:fresh --connection=mysql

This command resets all tables and re-runs all migrations on the mysql connection, effectively refreshing the database to its initial migrated state.

Available Fields

Let's see all the available columns types and options

id()

Creates an auto-incrementing primary key id (unsigned big integer).

Schema::create('users', function (Blueprint $table) {
    $table->id();
    // with custom primary_key column name
    $table->id('user_id');
});

string()

Defines a column with the VARCHAR type, ideal for storing short strings like names, titles, or labels. By default, the maximum length is 255 characters.

Schema::create('users', function (Blueprint $table) {
    $table->string('title');
    // with custom length
    $table->string('title', 100);
});

char()

Defines a column with the CHAR type, ideal for storing short strings like names, titles, or labels. By default, the maximum length is 255 characters.

Schema::create('users', function (Blueprint $table) {
    $table->char('name');
});

tinyText()

Defines a column with the TINYTEXT type, ideal for storing small blocks of text such as summaries, excerpts, or content.

Schema::create('posts', function (Blueprint $table) {
    $table->tinyText('excerpt');
});

mediumText()

Defines a column with the MEDIUMTEXT type, ideal for storing longer blocks of text such as summaries, excerpts, or content that exceeds the 255-character limit of a string.

Schema::create('posts', function (Blueprint $table) {
    $table->mediumText('body');
});

text()

Defines a column with the TEXT type, ideal for storing longer blocks of text such as summaries, excerpts, or content that exceeds the 255-character limit of a string.

Schema::create('posts', function (Blueprint $table) {
    $table->text('body');
});

longText()

Defines a column with the LONGTEXT type, perfect for storing very large amounts of text, such as full articles, blog post content, or rich HTML.

Schema::create('posts', function (Blueprint $table) {
    $table->longText('description');
});

json()

Defines a column with the JSON type, ideal for storing structured data like arrays, objects, or key-value pairs. Useful when the data format is dynamic or flexible.

Schema::create('posts', function (Blueprint $table) {
    $table->json('attributes');
});

integer()

Defines a column with the INT type, ideal for storing whole numbers such as counts, IDs, or any data that doesn’t require decimal precision.

Schema::create('posts', function (Blueprint $table) {
    $table->integer('post_views');
});

boolean()

Defines a column with the BOOLEAN type, which stores binary values: true (1) or false (0). Typically used for flags or status indicators, such as whether a post is active or published.

Schema::create('posts', function (Blueprint $table) {
    $table->boolean('status');
});

timestamps()

Defines two columns: created_at and updated_at. These are automatically managed by Entity to track when a record is created and last updated.

Schema::create('posts', function (Blueprint $table) {
    $table->timestamps();
    $table->timestampsTz(); // Create timestamp columns with timezone
});

unique()

Enforces a unique constraint on a column, ensuring that no two rows can have the same value for that column. It's often used on columns like email addresses, slugs, or usernames to maintain data integrity.

Schema::create('posts', function (Blueprint $table) {
    $table->string('slug')->unique();
});

nullable()

Allows a column to accept null values. By default, columns in Doppar are required, but using nullable() makes the column optional, meaning it can store NULL values.

Schema::create('posts', function (Blueprint $table) {
    $table->string('excerpt')->nullable();
});

default()

Sets a default value for a column if no value is provided during the creation of a record. This is useful for ensuring a column has a predetermined value when it’s not explicitly set.

Schema::create('posts', function (Blueprint $table) {
    $table->boolean('status')->default(true);
});

Number Types

Doppar migration includes various numeric column types to handle different ranges and precisions of data. Here's a quick overview:

// Signed Integers:
$table->tinyInteger('tiny_int_column'); // 1 byte, range: -128 to 127
$table->smallInteger('small_int_column'); // 2 bytes, range: -32,768 to 32,767
$table->mediumInteger('medium_int_column'); // 3 bytes, range: -8,388,608 to 8,388,607
$table->integer('int_column'); // 4 bytes, range: -2,147,483,648 to 2,147,483,647
$table->bigInteger('big_int_column'); // 8 bytes, range: -9.2 quintillion to 9.2 quintillion

// Unsigned Integers (no negative values):
$table->unsignedInteger('unsigned_int_column'); // 0 to 255
$table->unsignedTinyInteger('unsigned_tiny_int_column'); // 0 to 65,535
$table->unsignedSmallInteger('unsigned_small_int_column'); // 0 to 16,777,215
$table->unsignedMediumInteger('unsigned_medium_int_column'); // 0 to 4,294,967,295

// Floating Point Types:
$table->float('float_column', 8, 2); // Approximate numeric, total 8 digits, 2 after decimal
$table->double('double_column', 15, 8); // Higher precision float, total 15 digits, 8 after decimal
$table->decimal('decimal_column', 10, 2); // Exact numeric, total 10 digits, 2 after decimal (ideal for currency)

Date/Time Types

Doppar migration includes a variety of date and time-related columns to cover different temporal data needs:

$table->date('date_column'); // Stores only the date (format: YYYY-MM-DD)
$table->dateTime('datetime_column'); //  Stores date and time (format: YYYY-MM-DD HH:MM:SS)
$table->dateTimeTz('datetime_tz_column'); // Like dateTime, but includes time zone support
$table->time('time_column'); // Stores only time (format: HH:MM:SS)

// Time Zone Aware Columns:
$table->timeTz('time_tz_column'); //  Like time, but with time zone awareness
$table->timestamp('timestamp_column'); // Stores date and time, often used for tracking created/updated times
$table->timestampTz('timestamp_tz_column'); // Time-stamped with time zone support

$table->year('year_column');
$table->softDeletes(); // Adds a deleted_at timestamp column

Binary Types

Doppar migration defines several binary and BLOB (Binary Large Object) column types to handle various sizes of binary data:

// Standard Binary:
$table->binary('binary_column'); // Creates a BLOB column suitable for storing small binary data (up to 65,535 bytes). 

// Extended BLOB Types (MySQL-specific):
$table->tinyBlob('tiny_blob_column'); // Stores up to 255 bytes.
$table->blob('blob_column'); // Stores up to 65,535 bytes.
$table->mediumBlob('medium_blob_column'); // Stores up to 16 MB.
$table->longBlob('long_blob_column'); // Stores up to 4 GB

Special Types

Doppar migration utilizes several specialized column types to handle unique data requirements:

// Defines a column with a set of predefined string  values. Commonly used for status indicators or categorical data.
$table->enum('enum_column', ['active', 'pending', 'cancelled']);
$table->enumNullable('role', ['admin', 'user']); // Nullable enum definition

// Allows storage of multiple values from a predefined list in a single column.
$table->set('set_column', ['red', 'green', 'blue']);

$table->uuid('uuid_column'); // Creates a column to store Universally Unique Identifiers (UUIDs).

$table->ipAddress('ip_address_column'); // Stores IPv4 and IPv6 addresses.
$table->macAddress('mac_address_column'); // Stores MAC addresses. Typically stored as strings in the format 00:00:00:00:00:00
$table->json('json_column'); // Stores JSON-formatted data. Supported in MySQL 5.7+
$table->jsonArray('preferences'); // Define array-compatible JSON column

$table->bit('is_active')->default(1); // Define bit column for status flags

Spatial Types (GIS)

Doppar migration utilizes several specialized column types to handle geospatial data, enabling advanced geographical queries and operations:

$table->geometry('geometry_column'); // Stores any type of geometry data.
$table->point('point_column'); // Represents a single location in coordinate space (latitude and longitude).
$table->lineString('line_string_column'); // Stores a sequence of points forming a continuous line.
$table->polygon('polygon_column'); // Defines a shape consisting of multiple points forming a closed loop.
$table->geometryCollection('geometry_collection_column'); // Stores a collection of geometry objects.
$table->multiPoint('multi_point_column'); // Stores multiple point geometries.
$table->multiLineString('multi_line_string_column'); // Stores multiple linestring geometries.
$table->multiPolygon('multi_polygon_column'); // Stores multiple polygon geometries.

Determining Whether a Table Exists

Before modifying or interacting with a database table, you may want to check if it exists. Doppar provides the hasTable() method via the Schema facade to perform this check.

use Phaseolies\Support\Facades\Schema;

public function up()
{
    if (Schema::hasTable('users')) {
        // do something
    }
}

Dropping a Table

Tables can be dropped quite easily using the drop() method.

use Phaseolies\Support\Facades\DB;

DB::table('users')->drop()

Truncate a Table

Tables can be Truncated easily using the truncate() method.

use Phaseolies\Support\Facades\DB;

DB::table('users')->truncate()
DB::table('users')->truncate(true) // passing true mean force reset auto increment

Enable Disable Foreign Key Constraints

You can easily disable and enable foreign key constraints using Schema facades by calling disableForeignKeyConstraints() method to disable and enableForeignKeyConstraints() method to enable like

use Phaseolies\Support\Facades\Schema;

Schema::disableForeignKeyConstraints();
Schema::enableForeignKeyConstraints();

Working With Foreign Keys

For creating foreign key constraints on your database tables. Let’s add a foreign key to an example table:

use Phaseolies\Support\Facades\Schema;
use App\Models\User;

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
        $table->foreignIdFor(User::class)->nullable();

        // or you can use like that
        $table->unsignedBigInteger('user_id');
        $table->foreign('user_id')->references('id')->on('users');
    })
}

In Doppar, when defining foreign key constraints within your migrations, you can specify actions to be taken when the referenced record is deleted. This ensures referential integrity and allows for automatic management of related records.

Setting Up CASCADE Actions

There are three primary ways to define CASCADE actions in Doppar migrations:

public function up()
{
     // true for onDeleteCascade and true for onUpdateCascade
     $table->foreignIdFor(User::class, true, true);

     // Or
     $table->unsignedBigInteger('user_id');
     $table->foreign('user_id')
         ->references('id')
         ->on('users')
         ->onDelete('CASCADE');

     // Using the cascadeOnDelete Shortcut:
     $table->unsignedBigInteger('user_id');
     $table->foreign('user_id')
         ->references('id')
         ->on('users')
         ->cascadeOnDelete();
}

You can also use this method cascadeOnDelete(), restrictOnDelete(), nullOnDelete(), cascadeOnUpdate(), restrictOnUpdate(), nullOnUpdate().

Running Migrations on Specific Connection

Doppar supports multi-database migrations out-of-the-box through its custom Schema and Blueprint classes. These allow you to define and execute schema changes (e.g., creating tables) on any configured database connection—not just the default one.

This is especially useful for:

• Modular applications where different modules use isolated databases
• Multi-tenant systems
• Reporting or archive databases

Creating a Table on mysql_second

Doppar’s Schema::connection() method returns a new instance of Schema configured to use the given database connection. Internally, this class uses the DB::connection() method to route all schema operations (create, drop, check, modify) to the appropriate database.

<?php

use Phaseolies\Support\Facades\Schema;
use Phaseolies\Database\Migration\Blueprint;
use Phaseolies\Database\Migration\Migration;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::connection('mysql_second')
            ->create('reports', function (Blueprint $table) {
                $table->id();
                $table->timestamps();
            });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::connection('mysql_second')->dropIfExists('reports');
    }
};

All schema operations become connection-aware simply by chaining connection('connection_name'). For example:

use Phaseolies\Support\Facades\Schema;

if (Schema::connection('mysql_second')->hasTable('user')) {
    // Perform some logic if the 'user' table exists in mysql_second
}

// Drop a table on a different connection
Schema::connection('archive_db')->dropIfExists('logs');

// Create a table with foreign key checks disabled
Schema::connection('tenant_db')->disableForeignKeyConstraints();

Schema::connection('tenant_db')
    ->create('orders', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
    });

Schema::connection('tenant_db')->enableForeignKeyConstraints();

The Schema::connection() method makes it simple to work with multiple databases in Doppar. Whether you're creating tables, checking schema state, or managing constraints, you can confidently direct all schema commands to the right database with clean and expressive syntax.