Laravel Command to Generate Database Schema Documentation for LLM Prompts

When working with Large Language Models (LLMs) like ChatGPT or Claude to help with Laravel development, you often need to share your database schema structure. While Laravel provides some built-in commands like php artisan model:show or php artisan db:table, none of them produce output that's particularly well-suited for LLM prompts. Let's build a custom artisan command that generates a clean, markdown-formatted overview of your entire database schema.

The Goal

We want a command that:

• Lists all tables in the database
• Shows detailed column information including types, nullability, and defaults
• Includes foreign key relationships
• Shows index information
• Outputs everything in a clean markdown format that's perfect for LLM prompts
• Can optionally save the output to a file

Creating the Command

First, create the command using artisan:

php artisan make:command GenerateDatabaseSchema --command=db:schema-overview

This creates a new command file at app/Console/Commands/GenerateDatabaseSchema.php. Now let's implement our schema overview generator.

Implementation

Here's the full code for the command:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

class GenerateDatabaseSchema extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:schema-overview';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Generate a markdown formatted overview of the database schema';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        // Get all tables using raw query
        $tables = DB::select('SHOW TABLES');
        $output = "# Database Schema Overview\n\n";

        foreach ($tables as $table) {
            // Get the table name (first property of the object)
            $tableName = current((array)$table);

            // Get columns info
            $columns = Schema::getColumnListing($tableName);
            $columnDetails = [];

            foreach ($columns as $column) {
                $type = Schema::getColumnType($tableName, $column);
                $columnDetails[$column] = $this->getColumnDetails($tableName, $column, $type);
            }

            // Generate table markdown
            $output .= $this->generateTableMarkdown($tableName, $columnDetails);
        }

        $this->line($output);

        // Optionally save to file
        if ($this->confirm('Would you like to save this overview to a file?')) {
            $path = base_path('database-schema.md');
            file_put_contents($path, $output);
            $this->info("Schema overview saved to: $path");
        }
    }

    private function getColumnDetails($table, $column, $type)
    {
        // Use raw database queries for column details
        $columnInfo = DB::select("SHOW COLUMNS FROM {$table} WHERE Field = ?", [$column])[0];

        return [
            'type' => $type,
            'nullable' => $columnInfo->Null === 'YES' ? 'Yes' : 'No',
            'default' => $columnInfo->Default,
            'extra' => $columnInfo->Extra,
        ];
    }

    private function generateTableMarkdown($table, $columns)
    {
        $output = "## Table: `$table`\n\n";

        // Columns section
        $output .= "### Columns\n\n";
        $output .= "| Column | Type | Nullable | Default | Extra |\n";
        $output .= "|--------|------|----------|---------|--------|\n";

        foreach ($columns as $column => $details) {
            $default = $details['default'] === null ? 'NULL' : $details['default'];
            $extra = $details['extra'] ?? '';
            $output .= "| `$column` | {$details['type']} | {$details['nullable']} | $default | $extra |\n";
        }

        // Get and add foreign keys using raw query
        $foreignKeys = DB::select("
            SELECT 
                COLUMN_NAME as column_name,
                REFERENCED_TABLE_NAME as referenced_table,
                REFERENCED_COLUMN_NAME as referenced_column
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE 
                TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = ?
                AND REFERENCED_TABLE_NAME IS NOT NULL
        ", [$table]);

        if (!empty($foreignKeys)) {
            $output .= "\n### Foreign Keys\n\n";
            $output .= "| Column | References |\n";
            $output .= "|--------|------------|\n";

            foreach ($foreignKeys as $fk) {
                $output .= "| `{$fk->column_name}` | `{$fk->referenced_table}`.`{$fk->referenced_column}` |\n";
            }
        }

        // Get and add indexes using raw query
        $indexes = DB::select("SHOW INDEXES FROM {$table}");

        if (!empty($indexes)) {
            $output .= "\n### Indexes\n\n";
            $output .= "| Name | Type | Column |\n";
            $output .= "|------|------|--------|\n";

            $processedIndexes = [];
            foreach ($indexes as $index) {
                $type = $index->Key_name === 'PRIMARY' ? 'PRIMARY' : 
                       ($index->Non_unique == 0 ? 'UNIQUE' : 'INDEX');

                $output .= "| {$index->Key_name} | {$type} | `{$index->Column_name}` |\n";
            }
        }

        $output .= "\n---\n\n";
        return $output;
    }
}

Using the Command

After adding the command to your Laravel application, you can run it using:

php artisan db:schema-overview

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com