How to Create a Laravel Command to Display Columns of a Database Table or Model

When developing a Laravel application, you might occasionally need to inspect the structure of your database tables. Having a quick way to list all the columns of a table or a model can be immensely helpful. In this article, we’ll walk through the process of creating a custom Laravel Artisan command that can:

1. Check if the given argument is a model class.
2. If it’s a model, determine the associated table and retrieve its columns.
3. If it’s not a model, treat it as a table name and list the columns directly.

Let’s dive in!

Step 1: Generate the Command

First, create the command using Artisan:

php artisan make:command GetColumns

This will generate a new command file at app/Console/Commands/GetColumns.php. Open this file and replace its content with the following:

namespace App\Console\Commands;

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

class GetColumns extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:get-columns {name : The model class or table name} {--with-types : Include column types in the output}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Get all columns from the database for a given model or table';

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $name = $this->argument('name');
        $withTypes = $this->option('with-types');

        // Check if it's a valid model
        if (class_exists($name) && is_subclass_of($name, 'Illuminate\Database\Eloquent\Model')) {
            $model = new $name();
            $table = $model->getTable();
        } else {
            $table = $name;
        }

        // Check if the table exists in the database
        if (!DB::getSchemaBuilder()->hasTable($table)) {
            $this->error("The table '{$table}' does not exist.");
            return Command::FAILURE;
        }

        // Retrieve the columns of the table
        $columns = DB::getSchemaBuilder()->getColumnListing($table);

        if (empty($columns)) {
            $this->info("No columns found for the table '{$table}'.");
            return Command::SUCCESS;
        }

        // Output the columns
        $this->info("Columns for '{$table}':");
        foreach ($columns as $column) {
            if ($withTypes) {
                $type = DB::getSchemaBuilder()->getConnection()->getDoctrineColumn($table, $column)->getType()->getName();
                $this->line("- $column ($type)");
            } else {
                $this->line("- $column");
            }
        }

        return Command::SUCCESS;
    }
}

Step 2: Register the Command

To ensure the command is available, register it in the commands array in app/Console/Kernel.php:

protected $commands = [
    \App\Console\Commands\GetColumns::class,
];

Step 3: Running the Command

Once the command is registered, you can use it to get the columns of a table or model.

Example 1: Using a Model

If you have a model named User located in App\Models\User, run the following command:

php artisan db:get-columns "App\Models\User"

Output:

Columns for 'users':
id
name
email
password
created_at
updated_at

Example 2: Using a Table Name

You can also specify a table name directly:

php artisan db:get-columns "orders"

Output:

Columns for 'orders':
id
user_id
total
status
created_at
updated_at

Example 3: Using the --with-types Option

To include the data types of the columns, use the --with-types option:

php artisan db:get-columns "App\Models\User" --with-types

Output:

Columns for 'users':
id (integer)
name (string)
email (string)
password (string)
created_at (datetime)
updated_at (datetime)

Example 4: Handling Invalid Input

If the table or model doesn’t exist, the command will inform you:

php artisan db:get-columns "non_existent_table"

Output:

The table 'non_existent_table' does not exist.

How It Works

1. Checking for a Model: The command first checks if the argument is a valid class that extends Illuminate\Database\Eloquent\Model. If true, it instantiates the model and retrieves its associated table using $model->getTable().

2. Checking the Table Existence: If the argument is not a model, the command treats it as a table name and verifies its existence using DB::getSchemaBuilder()->hasTable($table).

3. Listing Columns: Once the table is determined, its columns are fetched using DB::getSchemaBuilder()->getColumnListing($table). If the --with-types option is used, the command also retrieves the type of each column using Doctrine DBAL.

Why Use This Command?

• Efficiency: Quickly inspect your database structure directly from the terminal.
• Flexibility: Supports both Eloquent models and raw table names.
• Automation: Useful in scripts or workflows where you need to programmatically access database schema information.

Conclusion

This command is a handy tool for developers working with Laravel and databases. Whether you’re debugging, building features, or simply exploring the schema, having this capability at your fingertips can save time and effort. Try it out and customize it further to suit your needs!