Evolving Database Schemas with PostgreSQL Compatibility in Mind

When working on Reimpact's platform, we often encounter scenarios where database schema changes are necessary to accommodate new features or optimize existing functionalities. Ensuring compatibility across different database systems, especially PostgreSQL, requires careful consideration and attention to detail.

The Challenge of Foreign Keys in PostgreSQL

PostgreSQL enforces strict type matching for foreign key relationships. This means that the data type of a foreign key column must exactly match the data type of the primary key column it references. This can pose a challenge when migrating schemas from other systems where such strictness might not be enforced.

Addressing UUIDs for Foreign Keys

Consider a scenario where we have a rates table that needs to reference a companies table and priority_products table. Originally, the migration might have defined the foreign key columns (company_id, priority_product_id) as strings. However, if the primary key columns in the referenced tables are UUIDs, PostgreSQL will raise an error. To resolve this, we must ensure that the foreign key columns in the rates table are also defined as UUIDs.

Here's an illustrative example of how to correct the migration:

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

class UpdateRatesTable extends Migration
{
    public function up()
    {
        Schema::table('rates', function (Blueprint $table) {
            $table->uuid('company_id')->change();
            $table->uuid('priority_product_id')->change();
        });
    }

    public function down()
    {
        Schema::table('rates', function (Blueprint $table) {
            $table->string('company_id')->change();
            $table->string('priority_product_id')->change();
        });
    }
}

Removing Unsupported Functionality

Another PostgreSQL incompatibility arises from the use of functions like after() in column definitions. While some database systems might support this to specify the order of columns, PostgreSQL does not. Attempting to use after() in a migration will result in an error. The solution is to remove the after() call and rely on the default column ordering.

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

class CreateRatesTable extends Migration
{
    public function up()
    {
        Schema::create('rates', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->uuid('company_id');
            $table->uuid('priority_product_id');
            $table->decimal('rate');
            $table->timestamps();

            $table->foreign('company_id')->references('id')->on('companies');
            $table->foreign('priority_product_id')->references('id')->on('priority_products');
        });
    }

    public function down()
    {
        Schema::dropIfExists('rates');
    }
}

Actionable Takeaway

When designing database schemas for cross-platform compatibility, especially with PostgreSQL, always ensure that foreign key data types match and avoid using database-specific functions that may not be universally supported. This proactive approach will save time and prevent unexpected errors during deployment.