maggomann / skeema-1

Laravel DB Schema Management through Skeema

Laravel Skeema

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.

Table of Contents

• Installation
• Configuration
• Usage
  • Dumping the Schema
  • Diffing the Schema
  • Linting the Schema
  • Pushing the Schema
  • Pulling the Schema
  • Deployment Checking
  • Laravel Migrations to Skeema "converting"
• 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:

1. Force pushing the current skeema files to the database ( Skippable with --no-push )
2. 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
3. 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