README

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.

Installation
Configuration
Usage
Quirks
- parallel-testing
- larastan
Testing
Roadmap
Disclaimer
Credits
License

Installation

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 bash

Install the package:

$ composer require smakecloud/skeema

Publish the config file:

$ php artisan vendor:publish --provider="SmakCloud\Skeema\SkeemaServiceProvider"

Configuration

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',
        ],
    ],
];

Usage

Run php artisan skeema -h to see all available commands and options.

Commands

Dumping the schema
Linting the schema
Diffing the schema
Pushing the schema
Pulling the schema
Migrating the schema

Dumping the schema

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.

Linting the schema

Lint the schema files with your configured rules.

Take a look at skeema linting documentation for more information.

$ php artisan skeema:lint

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

Diffing the schema

Diff the schema files against the database.

Take a look at skeema diffing documentation for more information.

$ php artisan skeema:diff

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

Pushing the schema

Push the schema files to the database.

Take a look at skeema pushing documentation for more information.

$ php artisan skeema:push

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

Pulling the schema

Pull the schema files from the database.

Take a look at skeema pulling documentation for more information.

$ php artisan skeema:pull

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

Deployment Checking

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-deployment

Laravel Migrations to Skeema "converting"

This 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 exist

php 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-migrations

Quirks

Parallel Testing

You 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');

            ...

Larastan

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
...

Testing

$ composer test

With coverage

$ composer test:coverage

Roadmap

GitHub Actions Examples
Deployment Instructions

Disclaimer

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.

License

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

Credits

Daursu - for the initial idea
Skeema - making all of this possible
GitHub - gh-ost
Percona - pt-online-schema-change
Smake® IT GmbH

smakecloud / skeema

Maintainers

Details