README

This packages allows you to add native check constraints to your database tables.

You can read more about check constraints in the official documentations of MySQL, PostrgeSQL, SQLite and SQL Server.

Currently, this package does not add check constraints to the SQLite driver, but you should be fine if you only use SQLite for running tests (see below).

Installation

You can install the package via composer:

composer require ditscheri/laravel-check-constraints

Usage

Schema::create('events', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->datetime('starts_at');
    $table->datetime('ends_at');

    // This is the new part:
    $table->check('starts_at < ends_at');
});

That last statement will produce the following SQL:

alter table `events` add constraint `events_starts_at_ends_at_check` check (starts_at < ends_at);

Now your database will only allow inserts and updates for rows with a valid date range.

If you try to insert or update a row that violates the check, an \Illuminate\Database\QueryException will be thrown:

Event::first()->update([
    'starts_at' => '2022-02-19 20:00:00',
    'end_at'    => '2022-02-19 18:00:00', // this one would be over before it even started?!
]); 
 
// Illuminate\Database\QueryException with message
// SQLSTATE[HY000]: General error: 3819 
// Check constraint 'events_starts_at_ends_at_check' is violated.

Another simple yet typical use case is with prices and discounts:

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->unsignedInteger('price');
    $table->unsignedInteger('discounted_price');

    // Ensure that discounts are lower than the regular price:
    $table->check('discounted_price <= price');
});

Of course you will still want to validate your data within the application code and detect such things before even reaching out to the database. But sometimes it is useful to have an additional layer of integrity checks right in your database itself.

Especially when you read data back from your database, your code may now safely assume that all the defined checks are guaranteed.

You can also add checks to existing tables:

Schema::table('users', function (Blueprint $table) {
    $table->check('age > 18');
});

Use the second parameter for an optional custom constraint name:

Schema::table('users', function (Blueprint $table) {
    $table->check('age > 18', 'require_min_age');
    $table->check('is_admin=1 OR company_id IS NOT NULL', 'non_admins_require_company');
});

You can drop check constraints by their name:

Schema::table('users', function (Blueprint $table) {
    $table->dropCheck('require_min_age');
});

A note about SQLite

While SQLite does support check constraints within create table statements, there are a number of limitions:

SQLite cannot add check constraints to existing tables.
SQLite cannot drop check constraints.

Since this package only relies on macros, it currently does not support the SQLite driver at all.

Instead, you can use the config check-constraints.sqlite.throw to define wether to throw a RuntimeException or to fail silently when using SQLite.

If you only use SQLite in your tests, you might be fine with setting the option to false. This gives you all the benefits of check constraints for your production environment, while your tests can still run using SQLite, where the calls to $table->check() will just be skipped.

Configuration

You can publish the config file with:

php artisan vendor:publish --tag="check-constraints-config"

This is the contents of the published config file:

return [
    'sqlite' => [
        'throw' => true,
    ],
];

Testing

composer test

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

License

The MIT License (MIT). Please see License File for more information.

ditscheri / laravel-check-constraints

Maintainers

Details