This laravel package provides a set of commands to help you manage your database schema during development and CI/CD pipelines using Skeema.
Skeema is a schema management system for MySQL and MariaDB. It enables management of table definitions and schema changes in a declarative fashion using pure SQL.
✅ Avoid downtimes during migrations.
✅ Lint your schema files with customizable rulests.
✅ Diff your schema files against your database.
✅ Easy to integrate with your CI/CD pipeline.
✅ Utility commands to help you moving from laravel migrations to skeema schema files.
✅ Manage your database schema in a more declarative way.
Use the install.sh script to install skeema and gh-ost.
$ curl -s https://raw.githubusercontent.com/smakecloud/skeema/master/install.sh | SKEEMA_VERSION=1.10.1 GH_OST_VERSION=1.1.5 bashInstall the package:
$ composer require smakecloud/skeemaPublish the config file:
$ php artisan vendor:publish --provider="Smakecloud\Skeema\ServiceProvider"The package will use the default configuration file config/skeema.php to run the skeema commands.
Checkout the Skeema documentation for more information about the different configuration options.
Default skeema.php config file
<?php
return [
/*
* The path to the skeema binary.
*/
'bin' => env('SKEEMA_BIN', 'skeema'),
/*
* The directory where the schema files will be stored.
*/
'dir' => 'database/skeema',
/*
* The connection to use when dumping the schema.
*/
'connection' => env('DB_CONNECTION', 'mysql'),
/**
* Alter Wrapper
*/
'alter_wrapper' => [
/*
* Enable the alter wrapper.
*/
'enabled' => env('SKEEMA_WRAPPER_ENABLED', false),
/*
* The path to the wrapper binary.
*/
'bin' => env('SKEEMA_WRAPPER_BIN', 'gh-ost'),
/**
* Any table smaller than this size (in bytes) will ignore the alter-wrapper option. This permits skipping the overhead of external OSC tools when altering small tables.
*/
'min_size' => '0',
/**
* This is how we do it at Smake.
* We highly recommend you to read documentation of
* gh-ost or pt-online-schema-change.
* https://github.com/github/gh-ost/blob/master/doc/command-line-flags.md
* https://docs.percona.com/percona-toolkit/pt-online-schema-change.html
*/
'params' => [
'--max-load=Threads_running=25',
'--critical-load=Threads_running=1000',
'--chunk-size=1000',
'--throttle-control-replicas='.env('DB_REPLICAS'),
'--max-lag-millis=1500',
'--verbose',
'--assume-rbr',
'--allow-on-master',
'--cut-over=default',
'--exact-rowcount',
'--concurrent-rowcount',
'--default-retries=120',
'--timestamp-old-table',
// https://github.com/github/gh-ost/blob/master/doc/command-line-flags.md#postpone-cut-over-flag-file
'--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag',
],
],
/**
* Linter specific config
* lint, diff, push
*/
'lint' => [
/**
* Linting rules for all supported cmds
*/
'rules' => [
\Smakecloud\Skeema\Lint\AutoIncRule::class => 'warning',
\Smakecloud\Skeema\Lint\CharsetRule::class => 'warning',
\Smakecloud\Skeema\Lint\CompressionRule::class => 'warning',
\Smakecloud\Skeema\Lint\DefinerRule::class => 'error',
\Smakecloud\Skeema\Lint\DisplayWidthRule::class => 'warning',
\Smakecloud\Skeema\Lint\DupeIndexRule::class => 'error',
\Smakecloud\Skeema\Lint\EngineRule::class => 'warning',
\Smakecloud\Skeema\Lint\HasEnumRule::class => 'ignore',
\Smakecloud\Skeema\Lint\HasFkRule::class => 'ignore',
\Smakecloud\Skeema\Lint\HasFloatRule::class => 'ignore',
\Smakecloud\Skeema\Lint\HasRoutineRule::class => 'ignore',
\Smakecloud\Skeema\Lint\HasTimeRule::class => 'ignore',
\Smakecloud\Skeema\Lint\NameCaseRule::class => 'ignore',
\Smakecloud\Skeema\Lint\PkRule::class => 'warning',
\Smakecloud\Skeema\Lint\ZeroDateRule::class => 'warning',
/**
* These rules are disabled by default
* because they are not available in the Community edition of Skeema
*
* https://www.skeema.io/download/
*/
// \Smakecloud\Skeema\Lint\HasTriggerRule::class => 'error',
// \Smakecloud\Skeema\Lint\HasViewRule::class => 'error',
],
/**
* Linting rules for diff
* Set to false to disable linting for diff
* See https://www.skeema.io/docs/commands/diff
*/
'diff' => [
// \Smakecloud\Skeema\Lint\ZeroDateRule::class => 'error',
],
/**
* Linting rules for push
* Set to false to disable linting for push
* See https://www.skeema.io/docs/commands/push
*/
'push' => [
// \Smakecloud\Skeema\Lint\ZeroDateRule::class => 'error',
],
],
];Run php artisan skeema -h to see all available commands and options.
Commands
SetUp function, run it once, push to version control
$ php artisan skeema:init {--force} {--connection[=CONNECTION]}Check the generated skeema dir ( database/skeema by default ) after running the command to make sure it's correct.
Lint the schema files with your configured rules.
Take a look at skeema linting documentation for more information.
$ php artisan skeema:lintDetails
Description:
Lint the database schema
Usage:
skeema:lint [options]
Options:
--skip-format Skip formatting the schema files
--strip-definer[=STRIP-DEFINER] Remove DEFINER clauses from *.sql files
--strip-partitioning Remove PARTITION BY clauses from *.sql files
--allow-auto-inc[=ALLOW-AUTO-INC] List of allowed auto_increment column data types for lint-auto-inc
--allow-charset[=ALLOW-CHARSET] List of allowed character sets for lint-charset
--allow-compression[=ALLOW-COMPRESSION] List of allowed compression settings for lint-compression
--allow-definer[=ALLOW-DEFINER] List of allowed routine definers for lint-definer
--allow-engine[=ALLOW-ENGINE] List of allowed storage engines for lint-engine
--update-views Reformat views in canonical single-line form
--ignore-warnings Exit with status 0 even if warnings are found
--output-format[=OUTPUT-FORMAT] Output format for lint results. Valid values: default, github, quiet
--connection[=CONNECTION]
-h, --help Display help for the given command. When no command is given display help for the list command
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi|--no-ansi Force (or disable --no-ansi) ANSI output
-n, --no-interaction Do not ask any interactive question
--env[=ENV] The environment the command should run under
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Diff the schema files against the database.
Take a look at skeema diffing documentation for more information.
$ php artisan skeema:diffDetails
Description:
Diff the database schema
Usage:
skeema:diff [options]
Options:
--ignore-warnings No error will be thrown if there are warnings
--alter-algorithm[=ALTER-ALGORITHM] The algorithm to use for ALTER TABLE statements
--alter-lock[=ALTER-LOCK] The lock to use for ALTER TABLE statements
--alter-validate-virtual Apply a WITH VALIDATION clause to ALTER TABLEs affecting virtual columns
--compare-metadata For stored programs, detect changes to creation-time sql_mode or DB collation
--exact-match Follow *.sql table definitions exactly, even for differences with no functional impact
--partitioning[=PARTITIONING] Specify handling of partitioning status on the database side
--strip-definer[=STRIP-DEFINER] Ignore DEFINER clauses when comparing procs, funcs, views, or triggers
--allow-auto-inc[=ALLOW-AUTO-INC] List of allowed auto_increment column data types for lint-auto-inc
--allow-charset[=ALLOW-CHARSET] List of allowed character sets for lint-charset
--allow-compression[=ALLOW-COMPRESSION] List of allowed compression settings for lint-compression
--allow-definer[=ALLOW-DEFINER] List of allowed routine definers for lint-definer
--allow-engine[=ALLOW-ENGINE] List of allowed storage engines for lint-engine
--allow-unsafe Permit generating ALTER or DROP operations that are potentially destructive
--safe-below-size[=SAFE-BELOW-SIZE] Always permit generating destructive operations for tables below this size in bytes
--skip-verify Skip Test all generated ALTER statements on temp schema to verify correctness
--connection[=CONNECTION]
-h, --help Display help for the given command. When no command is given display help for the list command
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi|--no-ansi Force (or disable --no-ansi) ANSI output
-n, --no-interaction Do not ask any interactive question
--env[=ENV] The environment the command should run under
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Push the schema files to the database.
Take a look at skeema pushing documentation for more information.
$ php artisan skeema:pushDetails
Description:
Push the database schema
Usage:
skeema:push [options]
Options:
--alter-algorithm[=ALTER-ALGORITHM] Apply an ALGORITHM clause to all ALTER TABLEs
--alter-lock[=ALTER-LOCK] Apply a LOCK clause to all ALTER TABLEs
--alter-validate-virtual Apply a WITH VALIDATION clause to ALTER TABLEs affecting virtual columns
--compare-metadata For stored programs, detect changes to creation-time sql_mode or DB collation
--exact-match Follow *.sql table definitions exactly, even for differences with no functional impact
--partitioning[=PARTITIONING] Specify handling of partitioning status on the database side
--strip-definer[=STRIP-DEFINER] Ignore DEFINER clauses when comparing procs, funcs, views, or triggers
--allow-auto-inc[=ALLOW-AUTO-INC] List of allowed auto_increment column data types for lint-auto-inc
--allow-charset[=ALLOW-CHARSET] List of allowed character sets for lint-charset
--allow-compression[=ALLOW-COMPRESSION] List of allowed compression settings for lint-compression
--allow-definer[=ALLOW-DEFINER] List of allowed routine definers for lint-definer
--allow-engine[=ALLOW-ENGINE] List of allowed storage engines for lint-engine
--allow-unsafe Permit generating ALTER or DROP operations that are potentially destructive
--safe-below-size[=SAFE-BELOW-SIZE] Always permit generating destructive operations for tables below this size in bytes
--skip-verify Skip Test all generated ALTER statements on temp schema to verify correctness
--dry-run Output DDL but don’t run it; equivalent to skeema diff
--foreign-key-checks Force the server to check referential integrity of any new foreign key
--force
--connection[=CONNECTION]
-h, --help Display help for the given command. When no command is given display help for the list command
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi|--no-ansi Force (or disable --no-ansi) ANSI output
-n, --no-interaction Do not ask any interactive question
--env[=ENV] The environment the command should run under
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Pull the schema files from the database.
Take a look at skeema pulling documentation for more information.
$ php artisan skeema:pullDetails
Description:
Pull the database schema
Usage:
skeema:pull [options]
Options:
--skip-format Skip Reformat SQL statements to match canonical SHOW CREATE
--include-auto-inc Include starting auto-inc values in new table files, and update in existing files
--new-schemas Detect any new schemas and populate new dirs for them (enabled by default; disable with skip-new-schemas)
--strip-definer[=STRIP-DEFINER] Omit DEFINER clauses when writing procs, funcs, views, and triggers to filesystem
--strip-partitioning Omit PARTITION BY clause when writing partitioned tables to filesystem
--update-views Update definitions of existing views, using canonical form
--update-partitioning Update PARTITION BY clauses in existing table files
--connection[=CONNECTION]
-h, --help Display help for the given command. When no command is given display help for the list command
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi|--no-ansi Force (or disable --no-ansi) ANSI output
-n, --no-interaction Do not ask any interactive question
--env[=ENV] The environment the command should run under
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
This should not be used in production environments, run it in a dedicated CI environment !
This custom command checks for existing laravel migrations, mysql-dump files, or running gh-ost migrations.
This can be usefull for pre-deployment checks ( in CI/CD pipelines ).
$ php artisan skeema:check-deploymentThis should not be used in production environments, run it in development environments only !
Does not work with Laravel squashed schema dumps out of the box!
Instead:
# Make sure skeema:init has been run.
# database/skeema should existphp artisan skeema:pull to pull the schema from the database. ( After making sure that the database is up to date )
This custom command "converts" existing laravel migrations to skeema schema files. This is achieved by executing the following steps:
- Force pushing the current skeema files to the database ( Skippable with
--no-push) - Looping through existing laravel migrations
- If the have been executed already, they will be deleted
- If they haven't been executed yet, they will be executed and then deleted
- Pulling the new skeema files from the database
$ php artisan skeema:convert-migrationsYou have to limit the skeema option tmp-schema-threads to 1.
For example:
tests/Concerns/ResetsData.php
...
protected function resetDatabase(): void
{
if (! isset(static::$initializedDatabases[$this->parallelToken()])) {
...
config()->set('skeema.alter_wrapper.enabled', false);
$this->artisan('skeema:push --allow-unsafe --force --temp-schema-threads=1');
...To use this package in combination with Larastan you have to add the skeema dumpfiles dir to the phpstan paramaters like this:
phpstan.neon.dist
...
parameters:
squashedMigrationsPath:
- database/skeema
...
$ composer testWith coverage
$ composer test:coverage- GitHub Actions Examples
- Deployment Instructions
This package is not affiliated with Skeema in any way.
Read the documentation of Skeema before using this package !
We don't take any responsibility for any damage caused by this package.
The MIT License (MIT). Please see License File for more information.
- Daursu - for the initial idea
- Skeema - making all of this possible
- GitHub - gh-ost
- Percona - pt-online-schema-change
- Smake® IT GmbH