Laravel's schema builder and migration system are great for managing tables and indexes---but offer no built-in support for other SQL entities, such as (materialized) views, procedures, functions, and triggers. These often get handled via raw SQL in migrations, making them hard to manage, prone to unknown conflicts, and difficult to track over time.
laravel-sql-entities solves this by offering:
- 📦 Class-based definitions: bringing views, functions, triggers, and more into your application code.
- 🧠 First-class source control: you can easily track changes, review diffs, and resolve conflicts.
- 🧱 Decoupled grammars: letting you support multiple drivers without needing dialect-specific SQL.
- 🔁 Lifecycle hooks: run logic at various points, enabling logging, auditing, and more.
- 🚀 Batch operations: easily create or drop all entities in a single command or lifecycle event.
- 🧪 Testability: definitions are just code so they’re easy to test, validate, and keep consistent.
Whether you're managing reporting views, business logic functions, or automation triggers, this package helps you treat SQL entities like real, versioned parts of your codebase---no more scattered SQL in migrations!
Note
Migration rollbacks are not supported since the definitions always reflect the latest state.
"We're never going backwards. You only go forward." -Taylor Otwell
First pull in the package using Composer:
composer require calebdw/laravel-sql-entitiesThe package looks for SQL entities under database/entities/ so you might need to add a namespace to your composer.json file, for example:
{ "autoload": { "psr-4": { "App\\": "app/", + "Database\\Entities\\": "database/entities/", "Database\\Factories\\": "database/factories/", "Database\\Seeders\\": "database/seeders/" } } }Tip
This package looks for any files matching database/entities in the application's base path. This means it should automatically work for a modular setup where the entities might be spread across multiple directories.
To get started, create a new class in a database/entities/ directory (structure is up to you) and extend the appropriate entity class (e.g. View, etc.).
For example, to create a view for recent orders, you might create the following class:
<?phpnamespaceDatabase\Entities\Views; useApp\Models\Order; useCalebDW\SqlEntities\View; useIlluminate\Database\Query\Builder; useOverride; // will create a view named `recent_orders_view`class RecentOrdersView extends View { #[Override] publicfunctiondefinition(): Builder|string { return Order::query() ->select(['id', 'customer_id', 'status', 'created_at']) ->where('created_at', '>=', now()->subDays(30)) ->toBase(); // could also use raw SQLreturn <<<'SQL' SELECT id, customer_id, status, created_at FROM orders WHERE created_at >= NOW() - INTERVAL '30 days' SQL; } }You can also override the name and connection:
<?phpclass RecentOrdersView extends View { protected ?string$name = 'other_name'; // also supports schemaprotected ?string$name = 'other_schema.other_name'; protected ?string$connection = 'other_connection'; }You can also use the provided lifecycle hooks to run logic before or after an entity is created or dropped. Returning false from the creating or dropping methods will prevent the entity from being created or dropped, respectively.
<?phpuseIlluminate\Database\Connection; class RecentOrdersView extends View { // ... #[Override] publicfunctioncreating(Connection$connection): bool { if (/** should not create */) { returnfalse; } /** other logic */returntrue; } #[Override] publicfunctioncreated(Connection$connection): void { $this->connection->statement(<<<SQL GRANT SELECT ON TABLE {$this->name()} TO other_user; SQL); } #[Override] publicfunctiondropping(Connection$connection): bool { if (/** should not drop */) { returnfalse; } /** other logic */returntrue; } #[Override] publicfunctiondropped(Connection$connection): void { /** logic */ } }Entities may depend on one another (e.g., a view that selects from another view). To support this, each entity can declare its dependencies using the dependencies() method:
<?phpclass RecentOrdersView extends View { #[Override] publicfunctiondependencies(): array { return [OrdersView::class]; } }The manager will ensure that dependencies are created in the correct order, using a topological sort behind the scenes. In the example above, OrdersView will be created before RecentOrdersView automatically.
The View class is used to create views in the database. In addition to the options above, you can use the following options to further customize the view:
<?phpclass RecentOrdersView extends View { // to create a recursive viewprotectedbool$recursive = true; // adds a `WITH CHECK OPTION` clause to the viewprotectedstring|true|null$checkOption = 'cascaded'; // can provide explicit column listingprotected ?array$columns = ['id', 'customer_id', 'status', 'created_at']; }The Function_ class is used to create functions in the database.
Tip
The class is named Function_ as function is a reserved keyword in PHP.
In addition to the options above, you can use the following options to further customize the function:
<?phpnamespaceDatabase\Entities\Functions; useCalebDW\SqlEntities\Function_; class Add extends Function_ { /** If the function aggregates. */protectedbool$aggregate = false; protectedarray$arguments = [ 'integer', 'integer', ]; /** The language the function is written in. */protectedstring$language = 'SQL'; /** The function return type. */protectedstring$returns = 'integer'; #[Override] publicfunctiondefinition(): string { return <<<'SQL' RETURN $1 + $2; SQL; } }Loadable functions are also supported:
<?phpnamespaceDatabase\Entities\Functions; useCalebDW\SqlEntities\Function_; class Add extends Function_ { protectedarray$arguments = [ 'integer', 'integer', ]; /** The language the function is written in. */protectedstring$language = 'c'; protectedbool$loadable = true; /** The function return type. */protectedstring$returns = 'integer'; #[Override] publicfunctiondefinition(): string { return'c_add'; } }The Trigger class is used to create triggers in the database. In addition to the options above, you can use the following options to further customize the trigger:
<?phpnamespaceDatabase\Entities\Triggers; useCalebDW\SqlEntities\Trigger; class AccountAuditTrigger extends Trigger { // if the trigger is a constraint trigger// PostgreSQL onlyprotectedbool$constraint = false; protectedstring$timing = 'AFTER'; protectedarray$events = ['UPDATE']; protectedstring$table = 'accounts'; #[Override] publicfunctiondefinition(): string { return$this->definition ?? <<<'SQL' EXECUTE FUNCTION record_account_audit(); SQL; } }The SqlEntityManager singleton is responsible for creating and dropping SQL entities at runtime. You can interact with it directly, or use the SqlEntity facade for convenience.
<?phpuseCalebDW\SqlEntities\Facades\SqlEntity; useCalebDW\SqlEntities\SqlEntityManager; useCalebDW\SqlEntities\View; // Create a single entity by class or instance SqlEntity::create(RecentOrdersView::class); resolve(SqlEntityManager::class)->create(RecentOrdersView::class); resolve('sql-entities')->create(newRecentOrdersView()); // Similarly, you can drop a single entity using the class or instance SqlEntity::drop(RecentOrdersView::class); // Create or drop all entities SqlEntity::createAll(); SqlEntity::dropAll(); // You can also filter by type or connection SqlEntity::createAll(types: View::class, connections: 'reporting'); SqlEntity::dropAll(types: View::class, connections: 'reporting');Sometimes you need to run a block of logic (like renaming a table column) without certain SQL entities present. The withoutEntities() method temporarily drops the selected entities, executes your callback, and then recreates them afterward.
If the database connection supports schema transactions, the entire operation is wrapped in one.
<?phpuseCalebDW\SqlEntities\Facades\SqlEntity; useIlluminate\Database\Connection; SqlEntity::withoutEntities(function (Connection$connection) { $connection->getSchemaBuilder()->table('orders', function ($table) { $table->renameColumn('old_customer_id', 'customer_id'); }); });You can also restrict the scope to certain entity types or connections:
<?phpuseCalebDW\SqlEntities\Facades\SqlEntity; useIlluminate\Database\Connection; SqlEntity::withoutEntities( callback: function (Connection$connection) { $connection->getSchemaBuilder()->table('orders', function ($table) { $table->renameColumn('old_customer_id', 'customer_id'); }); }, types: [RecentOrdersView::class, RecentHighValueOrdersView::class], connections: ['reporting'], );After the callback, all affected entities are automatically recreated in dependency order.
The package provides console commands to create and drop your SQL entities.
php artisan sql-entities:create [entities] [--connection=CONNECTION ...] # Create all entities php artisan sql-entities:create # Create a specific entity php artisan sql-entities:create 'Database\Entities\Views\RecentOrdersView'# Create all entities on a specific connection php artisan sql-entities:create -c reporting # Similarly, drop all entities php artisan sql-entities:dropYou may want to automatically drop all SQL entities before migrating, and then recreate them after the migrations are complete. This is helpful when the entities depend on schema changes. To do this, register the built-in subscriber in a service provider:
<?phpuseCalebDW\SqlEntities\Listeners\SyncSqlEntities; useIlluminate\Support\Facades\Event; useIlluminate\Support\ServiceProvider; class AppServiceProvider extends ServiceProvider { publicfunctionboot(): void { Event::subscribe(SyncSqlEntities::class); } }The listener will also create all entities if there's no pending migrations, ensuring any new entities are created automatically.
Thank you for considering contributing! You can read the contribution guide here.
This is open-sourced software licensed under the MIT license.
