Dedicated database host for stored_events?

[ahoiroman]

Hi there,

to discuss this issue #155 I'd like to ask if anybody ever tried to provide a single MariaDB-database host for the stored_events-table?

As my database is getting huuuuge, I'd like to separate the db from laravel's "main" db.

[rapkis]

Hi! Here's how I did it:

• Defined a new database connection in config/database.php. I've made it basically the same as the default mysql entry. Here's how it may look:

    'event_sourcing_mysql' => [
            'driver' => 'mysql',
            'url' => env('EVENT_SOURCING_DATABASE_URL'),
            'host' => env('EVENT_SOURCING_DB_HOST', '127.0.0.1'),
            'port' => env('EVENT_SOURCING_DB_PORT', '3306'),
            'database' => env('EVENT_SOURCING_DB_DATABASE', 'forge'),
            'username' => env('EVENT_SOURCING_DB_USERNAME', 'forge'),
            'password' => env('EVENT_SOURCING_DB_PASSWORD', ''),
            'unix_socket' => env('EVENT_SOURCING_DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

• I've changed the event-sourcing.connection config value to refer to the connection. I've used ENV variables here, which helps to have two separate databases in production but only one in the local environment.
• Then I needed to override this connection for the event-sourcing.stored_event_model and event-sourcing.snapshot_model values. I've just extended the original classes and overrode the required methods:

<?php

namespace App\EventSourcing\Models;

use Spatie\EventSourcing\Snapshots\EloquentSnapshot;

class Snapshot extends EloquentSnapshot
{
    public function getConnectionName(): string|null
    {
        return config('event-sourcing.connection');
    }
}

// ------

<?php

namespace App\EventSourcing\Models;

use Spatie\EventSourcing\StoredEvents\Models\EloquentStoredEvent;

class StoredEvent extends EloquentStoredEvent
{
    public function getConnectionName(): string|null
    {
        return config('event-sourcing.connection');
    }
}

• Modified the migration files, to ensure the same connection is used:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

return new class extends Migration
{
    public function up(): void
    {
        Schema::connection(config('event-sourcing.connection'))
            ->create('stored_events', function (Blueprint $table) {
                // 
        });
    }
};

// ---

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

return new class extends Migration
{
    public function up(): void
    {
        Schema::connection(config('event-sourcing.connection'))
            ->create('snapshots', function (Blueprint $table) {
                //
        });
    }
};

• Added the two new variables in my phpunit.xml, to ensure that the same database connection is used in my tests:

<server name="DB_CONNECTION" value="sqlite"/>
<server name="DB_DATABASE" value=":memory:"/>
<server name="EVENT_SOURCING_DB_CONNECTION" value="sqlite"/>
<server name="EVENT_SOURCING_DB_DATABASE" value=":memory:"/>

DISCLAIMER: I made these changes prior to deploying my application to production. If you already have events in your production environment, you might want to have a migration strategy in place to ensure your old events are migrated to the new instance.

Hope this helps! ✌️