Database migration

[mbardelmeijer]

Is there a ready-to-go file available for the database migration required from V2 to V3? I've seen the changes listed on https://mailcoach.app/docs/v3/app/installation/upgrading but would be less error prone to have an official one.

[riasvdv]

This is a migration we used on a Mailcoach instance, we prefer to just list the changes since people might have made other changes in their own application.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Spatie\Mailcoach\Models\Campaign;

class UpdateMailcoachTables extends Migration
{
    public function up()
    {
        Schema::table('mailcoach_campaigns', function (Blueprint $table) {
            $table->string('reply_to_email')->nullable();
            $table->string('reply_to_name')->nullable();
            $table->timestamp('all_jobs_added_to_batch_at')->nullable();
            $table->string('send_batch_id')->nullable();
        });

        Schema::table('mailcoach_subscribers', function (Blueprint $table) {
            $table->uuid('imported_via_import_uuid')->nullable();
        });

        Schema::table('mailcoach_subscriber_imports', function (Blueprint $table) {
            $table->boolean('subscribe_unsubscribed')->default(false);
            $table->boolean('unsubscribe_others')->default(false);
            $table->text('subscribers_csv')->nullable();
            $table->uuid('uuid')->nullable();
        });

        Schema::table('mailcoach_email_lists', function (Blueprint $table) {
            $table->string('default_reply_to_email')->nullable();
            $table->string('default_reply_to_name')->nullable();
            $table->text('allowed_form_extra_attributes')->nullable();
        });

        Schema::table('mailcoach_sends', function (Blueprint $table) {
            $table->index('uuid');
            $table->index(['campaign_id', 'subscriber_id']);
        });

        Schema::table('webhook_calls', function (Blueprint $table) {
            $table->string('external_id')->nullable();
            $table->timestamp('processed_at')->nullable();
            $table->index('external_id');
        });

       // Update existing statistics to use new format
        Campaign::each(function (Campaign $campaign) {
            $campaign->update([
                'open_rate' => $campaign->open_rate * 100,
                'click_rate' => $campaign->click_rate * 100,
                'bounce_rate' => $campaign->bounce_rate * 100,
                'unsubscribe_rate' => $campaign->unsubscribe_rate * 100,
            ]);
        });

        if (DB::getDriverName() !== 'sqlite') {
           DB::statement('update webhook_calls set processed_at = NOW() where processed_at is null;');
        }
    }
}

[limenet]

@riasvdv thanks for providing this snippet! Unfortunately, it seems one column is missing: mailcoach_campaigns also needs $table->string('send_batch_id')->nullable();. Could you please add that to your snippet?

Additionally, while your snippet accounts for the changes to campaigns, the SQL query for webhooks is missing (update webhook_calls set processed_at = NOW() where processed_at is null). Would you mind adding that as well?

Thank you!

[jorenvh]

Below you can find the migration file that I used for my application.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;
use Spatie\Mailcoach\Models\Campaign;

class UpdateMailcoachTables extends Migration
{
    public function up()
    {
        Schema::table('mailcoach_campaigns', function (Blueprint $table) {
            $table->string('reply_to_email')->nullable();
            $table->string('reply_to_name')->nullable();
            $table->timestamp('all_jobs_added_to_batch_at')->nullable();
            $table->string('send_batch_id')->nullable();
        });

        Schema::table('mailcoach_subscribers', function (Blueprint $table) {
            $table->uuid('imported_via_import_uuid')->nullable();
        });

        Schema::table('mailcoach_subscriber_imports', function (Blueprint $table) {
            $table->boolean('subscribe_unsubscribed')->default(false);
            $table->boolean('unsubscribe_others')->default(false);
        });

        Schema::table('mailcoach_email_lists', function (Blueprint $table) {
            $table->string('default_reply_to_email')->nullable();
            $table->string('default_reply_to_name')->nullable();
            $table->text('allowed_form_extra_attributes')->nullable();
        });

        Schema::table('mailcoach_sends', function (Blueprint $table) {
            $table->index('uuid');
            $table->index(['campaign_id', 'subscriber_id']);
        });

        Schema::table('webhook_calls', function (Blueprint $table) {
            $table->string('external_id')->nullable();
            $table->timestamp('processed_at')->nullable();
            $table->index('external_id');
        });

        // Update existing statistics to use new format
        Campaign::each(function (Campaign $campaign) {
            $campaign->update([
                'open_rate' => $campaign->open_rate * 100,
                'click_rate' => $campaign->click_rate * 100,
                'bounce_rate' => $campaign->bounce_rate * 100,
                'unsubscribe_rate' => $campaign->unsubscribe_rate * 100,
            ]);
        });

        DB::statement('update webhook_calls set processed_at = NOW() where processed_at is null;');
    }
}

[freekmurze]

@limenet @riasvdv I've updated @riasvdv 's comment with your suggested changes.

[tdondich]

@freekmurze Line 16 is missing a semicolon.

Also, if this migration runs in sqlite (say using phpunit), then the last DB::statement fails. So can easily run a check using:

        if (DB::getDriverName() !== 'sqlite') {
            DB::statement('update webhook_calls set processed_at = NOW() where processed_at is null;');
        }

[freekmurze]

@tdondich Thanks for reporting. I'll fix those things.

[beberlei]

@freekmurze @riasvdv There is another change missing, I get Unknown column 'api_token' in 'where clause' (SQL: select * fromuserswhereapi_token= limit 1) shouldnt a migration for this be shipped with Laravel directly?

[beberlei]

It looks that happens because i use api:auth, as documented in the upgrade guide. But the spatie/mailcoach installation moved to another auth middleware that allows creating multiple tokens per user.

[beberlei]

@riasvdv the migration for subscriber imports is missing the uuid and subscribers_csv field. I assume it must be this:

Schema::table('mailcoach_subscriber_imports', function (Blueprint $table) {
            $table->boolean('subscribe_unsubscribed')->default(false);
            $table->boolean('unsubscribe_others')->default(false);
            $table->text('subscribers_csv')->nullable();
            $table->uuid('uuid')->nullable();
        });

[rrotteveel]

Schema::table('mailcoach_subscriber_imports', function (Blueprint $table) {
            $table->boolean('subscribe_unsubscribed')->default(false);
            $table->boolean('unsubscribe_others')->default(false);
            $table->text('subscribers_csv')->nullable();
            $table->uuid('uuid')->nullable();
        });

@beberlei

It seems to me, that the uuid cannot be null:

If you want to migrate data from an old database it gives an error.

[jonaswouters]

I really hope next time there will be an easier, out of the box upgrade path for people using mailcoach as is. 🤞