PHP Laravel Tinker

Show SQL queries on Tinker

by Daniel Loureiro on 3 min read

If you want to echo the SQL queries being executed on Tinker:

Add .psysh.php to your project:

<?php
DB::listen(function ($query) {
    dump("[{$query->time}ms] {$query->sql}");
    if ($query->bindings) {
        dump($query->bindings);
    }
});

Now, whenever Tinker executes a query, it logs on screen: Tinker showing query

# Improved version

If you want this improved version SQL with colors

Use this snippet instead:

<?php
use Symfony\Component\Console\Output\ConsoleOutput;
use Symfony\Component\Console\Formatter\OutputFormatterStyle;

function generate_color_console() {
    /** Reference: https://symfony.com/doc/current/console/coloring.html */
    $output       = new ConsoleOutput();

    $redStyle     = new OutputFormatterStyle('#ff0000', '', ['bold']);
    $cyanStyle    = new OutputFormatterStyle('cyan', '', ['bold']);
    $purpleStyle  = new OutputFormatterStyle('#9b6bdf', '', ['bold']);
    $yellowStyle  = new OutputFormatterStyle('yellow', '', ['bold']);
    $greenStyle   = new OutputFormatterStyle('green', '', ['bold']);
    $magentaStyle = new OutputFormatterStyle('#ff00ff', '', ['bold']);
    $whiteStyle   = new OutputFormatterStyle('#ffffff', '', []);

    $output->getFormatter()->setStyle('label', $cyanStyle);
    $output->getFormatter()->setStyle('select', $purpleStyle);
    $output->getFormatter()->setStyle('update', $yellowStyle);
    $output->getFormatter()->setStyle('insert', $greenStyle);
    $output->getFormatter()->setStyle('delete', $magentaStyle);
    $output->getFormatter()->setStyle('default', $redStyle);
    $output->getFormatter()->setStyle('params', $whiteStyle);

    return $output;
}

/**
 * Given an INSERT query, return the column name of each `?`.
 *
 * @param string $sql Example:
 *   `INSERT INTO users(id, `name`, created_at) VALUES(?, ?, ?)`
 *
 * @return string[] Example: `['id', 'name', 'date']`
 *
 */
function get_binding_names_from_insert($sql) {
    // Get column names. Ex.: "'id, `name`, created_at'".
    $matches = [];
    $success = preg_match('/INSERT *INTO *[^(]*\(([^)]+)\)/i', $sql, $matches);
    if (0 === $success) {
        return false;
    }
    $names_str = array_pop($matches);

    // Split: ['id, 'name', created_at']
    preg_match_all('/([a-z0-9_]+)/i', $names_str, $matches);

    return array_shift($matches);
}

/**
 * Given a SQL, return the column name of each `?`.
 *
 * @param string $sql Example:
 *   `SELECT * FROM users
 *    WHERE `users`.`id` = ? AND created_at <= ? LIMIT ?`
 *
 * @return string[] Example: `['id', 'created_at', 'LIMIT']`
 *
 */
function get_binding_names_from_expressions($sql) {
    // For each `?`, get the two previous words.
    $matches = [];
    preg_match_all('/([\S]*) *([\S]*) *\?/', $sql, $matches);

    // Sample: ["`users`.`id` = ?", "`users`.`deleted_at` >= ?", " LIMIT ?"]
    $expressions = array_shift($matches);

    // Get the first word of each expression. Sample: ["`users`.`id`", "`users`.`deleted_at`", "LIMIT"]
    $first_words = array_map(function ($str) {
        $words = explode(' ', trim($str), 2);
        return array_shift($words);
    }, $expressions);

    // Extract "column" from "`table`.`column`".
    $names = array_map(function ($str) {
        $matches = [];
        preg_match_all('/`(.*?)`/', $str, $matches);
        $match = array_pop($matches);
        $name = array_pop($match);
        return $name ? $name : $str;
    }, $first_words);

    return $names;
}

$output = generate_color_console();
DB::listen(function ($query) use ($output) {
   // Change reserved words to uppercase (not the full list, just some main ones).
    $reserved = [
        'SELECT ', 'UPDATE ', 'INSERT ', 'DELETE ',
        'BEGIN', 'COMMIT', 'ROLLBACK', ' FROM ',
        ' LEFT JOIN ', ' RIGHT JOIN ', ' FULL JOIN ',
        ' INNER JOIN ', ' OUTER JOIN ', ' JOIN ',
        ' WHERE ', ' LIMIT ', ' ORDER BY ', ' SET ',
        ' UNION ', ' LIKE ', ' IS NULL ', ' IS NOT NULL ',
        'INSERT INTO ', ' IN ', 'CREATE ', 'DROP ',
        'ALTER ', ' VALUES ',
    ];
    $sql = str_ireplace($reserved, $reserved, $query->sql);

    // Each statement type has a different color.
    $statements = ['SELECT', 'UPDATE', 'INSERT', 'DELETE'];
    $words      = explode(' ', trim($sql));
    $first_word = strtoupper(array_shift($words));

    // Wrap SQL in a color tag (ex. <select>, <update>, etc).
    $tag = (in_array($first_word, $statements)) ? $first_word : 'default';
    $tag = strtolower($tag);

    // Margin + "SQL" label + Timing + SQL.
    $str = "  <label>SQL ({$query->time}ms)<label>  <$tag>{$sql}</$tag>";

    // Add Bindings.
    if ($query->bindings) {
        $names = get_binding_names_from_insert($sql);
        if (false === $names) {
            $names = get_binding_names_from_expressions($sql);
        }
        if (false === $names) {
            $names = array_keys($query->bindings);
        }

        $params = [];
        foreach ($query->bindings as $index => $value) {
            $name = $names[$index];
            $params[] = "['$name', '$value']";
        }

        $params_str = join(', ', $params);
        $str = "$str  <params>[$params_str]</params>";
    }

    // Output final string.
    $output->writeln($str);
});

Comments

Copyright 2021 - Daniel Loureiro