🔫

Laravel artisan で指定したテーブルのMermaid ER図を出力する(MySQL)

2022/05/09に公開

適当スクリプトをメモ

<?php

namespace App\Console\Commands;

use DB;
use Illuminate\Console\Command;
use stdClass;

class CreateDatabaseMermaid extends Command
{
    protected $signature = 'command:create-database-mermaid 
        { tables : 対象テーブル名をカンマ区切りで }
    ';

    protected $description = '指定されたテーブルのER図をMermaid形式で出力する';

    public function handle(): int
    {
        echo 'erDiagram' . "\n\n";

        $tables = collect(explode(',', $this->argument('tables')))
            ->map(fn(string $table) => trim($table))
            ->filter()
            ->values();

        $dbName = config('database.connections.mysql.database');
        $tables->each(function (string $table) use ($dbName) {
            $columns = collect(
                DB::select(sprintf('SHOW COLUMNS FROM `%s` IN `%s`;', $table, $dbName)),
            );
            echo sprintf(
                "%s {\n%s\n}",
                $table,
                $columns
                    ->map(
                        fn(stdClass $column) => sprintf(
                            '  %s %s',
                            str_replace(
                                'unsigned',
                                '',
                                preg_replace('/\(\d+\)/', '', $column->Type),
                            ),
                            $column->Field,
                        ),
                    )
                    ->implode("\n"),
            ) . "\n\n";
            $foreignKeys = DB::query()
                ->select([
                    'TABLE_NAME',
                    'COLUMN_NAME',
                    'CONSTRAINT_NAME',
                    'REFERENCED_TABLE_NAME',
                    'REFERENCED_COLUMN_NAME',
                ])
                ->fromRaw('INFORMATION_SCHEMA.KEY_COLUMN_USAGE')
                ->whereRaw('REFERENCED_TABLE_SCHEMA = ?', [$dbName])
                ->whereRaw('TABLE_NAME = ?', [$table])
                ->get();
            echo $foreignKeys
                ->map(
                    fn(stdClass $foreignKey) => sprintf(
                        '%s ||--o{ %s: "%s"',
                        $foreignKey->TABLE_NAME,
                        $foreignKey->REFERENCED_TABLE_NAME,
                        $foreignKey->CONSTRAINT_NAME,
                    ),
                )
                ->join("\n") . "\n\n";
        });

        return 0;
    }
}

Discussion