🐥 Using Laravel migration with different databases

At work, I recently had the need to use the Laravel migrations for multiple databases. Long story short, our main database is MySQL but we also use PostgreSQL as an AI vector store. I wanted to use the Laravel migrations to create the tables in both databases.

It turns out to be really straightforward to do this.

The first step is to ensure you have the database connections set up in your config/database.php file:

return [
    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', ''),
            'port' => env('DB_PORT', ''),
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
        ],

        'vectordb' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', ''),
            'port' => env('DB_PORT', ''),
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
        ],
    ],
];

As you can see, the default one points to MySQL, the other one points to PostgreSQL.

Next, you need to create a new migration file. You can do this by running the following command:

php artisan make:migration add_vectordb_tables

Then, in there, you can do this:

Schema::connection('vectordb')->create('embeddings', function (Blueprint $table) {
    $table->id();
});

By using Schema::connection('vectordb'), you can specify which connection to use for the migration.

Finally, you can run the migration like this:

php artisan migrate

The `migrations' table will be stored in the main database by the way.

You can read more about this in the Laravel documetation.