laravel-postgresql-enhanced

PostgreSQL supports extended date formats like timestampTz (time zone aware) and millisecond precision. Standard Eloquent models may not handle these correctly without manual $dateFormat configuration. Use these traits to automate the process:

1. AutomaticDateFormat: Use this when your table contains ->timestampTz() columns.
2. AutomaticDateFormatWithMilliseconds: Use this when your table contains columns that store milliseconds (via ->timestamp() or ->timestampTz()).

CAUTION

If you mix columns with and without milliseconds in the same table, PostgreSQL may round values instead of truncating them. This can cause timestamps without milliseconds to appear as if they are in the future.

use Illuminate\Database\Eloquent\Model;
use Tpetry\PostgresqlEnhanced\Eloquent\Concerns\AutomaticDateFormat;

class Example extends Model
{
    use AutomaticDateFormat;
}

The package provides enhanced support for TimescaleDB features within Laravel migrations. You can enable the extension using Schema::createExtensionIfNotExists('timescaledb') and then use the $table->timescale(...) method on a standard table blueprint or the Schema::continuousAggregate(...) method for continuous aggregates.

Important: Indexes are not automatically created when creating hypertables or continuous aggregates; you must define them manually in your migration.

Supported TimescaleDB features include:

• Hypertables: CreateHypertable and ChangeChunkTimeInterval.
• Chunk Skipping: EnableChunkSkipping and DisableChunkSkipping.
• Columnstore: EnableColumnstore, DisableColumnstore, CreateColumnstorePolicy, DropColumnstorePolicy, ConvertToColumnstore, and ConvertToRowstore.
• Reordering: CreateReorderPolicy, CreateReorderPolicyByIndex, CreateReorderPolicyByUnique, DropReorderPolicy, and ReorderChunks.
• Data Retention: CreateRetentionPolicy, DropRetentionPolicy, and DropChunks.
• Tiered Storage: CreateTieringPolicy, DropTieringPolicy, TierChunks, and UntierChunks.
• Continuous Aggregates: CreateRefreshPolicy, DropRefreshPolicy, and RefreshData.

use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateColumnstorePolicy;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateHypertable;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateRefreshPolicy;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateRetentionPolicy;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\EnableChunkSkipping;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\EnableColumnstore;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\CaggBlueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;

return new class extends Migration
{
    public function up(): void
    {
        Schema::createExtensionIfNotExists('timescaledb');

        Schema::create('visits', function (Blueprint $table) {
            $table->identity();
            $table->bigInteger('website_id');
            $table->text('url');
            $table->float('duration');
            $table->timestampTz('created_at');

            $table->primary(['id', 'created_at']);
            $table->index(['website_id', 'created_at']);

            $table->timescale(
                new CreateHypertable('created_at', '1 day'),
                new EnableColumnstore(segmentBy: 'website_id'),
                new CreateColumnstorePolicy('3 days'),
                new CreateRetentionPolicy('1 year'),
                new EnableChunkSkipping('id'),
            );
        });

        Schema::continuousAggregate('visits_agg', function(CaggBlueprint $table) {
            $table->as("\n                SELECT\n                    time_bucket('1 hour', created_at) AS bucket,\n                    website_id,\n                    url,\n                    SUM(duration) AS duration\n                FROM visits\n                GROUP BY bucket, website_id, url\n            ");
            $table->realtime();
            $table->index(['website_id','url']);

            $table->timescale(
                new CreateRefreshPolicy('5 minutes', '1 days', '2 hours'),
                new EnableColumnstore(),
                new CreateColumnstorePolicy('2 days'),
            );
        });
    }
};

Domain types allow you to create application-specific types by aliasing an existing base type and adding an optional SQL validation condition. This is useful for enforcing rules like non-negative prices or specific formats (e.g., license plates) at the database level.

Create a Domain Type

Use Schema::createDomain() with the name, base type, and an optional condition. The condition can be a raw SQL string or a closure using a Builder instance.

Use Domain Types

In migrations, use the domain() method on the blueprint, passing the column name and the name of the domain type.

Altering Domain Types

You cannot change the base type of a domain, but you can update its validation condition using Schema::changeDomainConstraint() by passing a new SQL string or a closure. Passing null removes the constraint.

Dropping Domain Types

To remove a domain, first drop all columns using it. Then use Schema::dropDomain() or Schema::dropDomainIfExists(). You can drop multiple domains at once by passing multiple names.

// Create a domain with a closure-based constraint
Schema::createDomain('price', 'numeric(9,2)', fn (Builder $query) => $query->where('VALUE', '>=', 0));

// Use the domain in a migration
Schema::create('products', function (Blueprint $table): void {
  $table->domain('item_price', 'price');
});

// Change the constraint
Schema::changeDomainConstraint('price', 'VALUE > 0');

// Drop domains
Schema::dropDomain('price', 'license_plate');

For applications requiring 24/7 availability, you can mark migrations as zero-downtime to prevent long-running schema changes from locking tables indefinitely. By using the ZeroDowntimeMigration trait, the migration will be cancelled and the schema reset to its original state if it exceeds a specified time limit.

To configure timeouts, you can define:

• private float $timeout: A shared timeout for both up() and down() methods.
• private float $timeoutUp: A specific timeout for the up() method.
• private float $timeoutDown: A specific timeout for the down() method.

use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Schema\Concerns\ZeroDowntimeMigration;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

class Test123 extends Migration
{
    use ZeroDowntimeMigration;

    private float $timeoutUp = 5.0;

    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('user', function (Blueprint $table) {
            $table->string('name', 128)->change();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('user', function (Blueprint $table) {
            $table->string('name', 32)->change();
        });
    }
}