doctrine/sql-formatter

a PHP SQL highlighting library

1.5.1 2024-10-21 18:21 UTC

Requires

• php: ^8.1

Requires (Dev)

• doctrine/coding-standard: ^12
• ergebnis/phpunit-slow-test-detector: ^2.14
• phpstan/phpstan: ^1.10
• phpunit/phpunit: ^10.5
• vimeo/psalm: ^5.24

MIT

• Jeremy Dorn

• sql
• highlight

README

A lightweight php package for formatting sql statements.

It can automatically indent and add line breaks in addition to syntax highlighting.

History

This package is a fork from https://github.com/jdorn/sql-formatter Here is what the original History section says:

I found myself having to debug auto-generated SQL statements all the time and wanted some way to easily output formatted HTML without having to include a huge library or copy and paste into online formatters.

I was originally planning to extract the formatting code from PhpMyAdmin, but that was 10,000+ lines of code and used global variables.

I saw that other people had the same problem and used Stack Overflow user losif's answer as a starting point. http://stackoverflow.com/a/3924147

― @jdorn

Usage

The SqlFormatter class has a method format which takes an SQL string as input and returns a formatted block.

Sample usage:

<?php
require_once 'vendor/autoload.php';

use Doctrine\SqlFormatter\SqlFormatter;

$query = "SELECT count(*),`Column1`,`Testing`, `Testing Three` FROM `Table1`
    WHERE Column1 = 'testing' AND ( (`Column2` = `Column3` OR Column4 >= NOW()) )
    GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10";

echo (new SqlFormatter())->format($query);

Output:

When you run php under cli and instantiated SqlFormatter without argument, highlighted with CliHighlighter.

SqlFormatter constructor takes Highlighter implementations. HtmlHighlighter etc.

Formatting Only

If you don't want syntax highlighting and only want the indentations and line breaks, pass in a NullHighlighter instance as the second parameter.

This is useful for outputting to error logs or other non-html formats.

<?php

use Doctrine\SqlFormatter\NullHighlighter;
use Doctrine\SqlFormatter\SqlFormatter;

echo (new SqlFormatter(new NullHighlighter()))->format($query);

Output:

SELECT
  count(*),
  `Column1`,
  `Testing`,
  `Testing Three`
FROM
  `Table1`
WHERE
  Column1 = 'testing'
  AND (
    (
      `Column2` = `Column3`
      OR Column4 >= NOW()
    )
  )
GROUP BY
  Column1
ORDER BY
  Column3 DESC
LIMIT
  5, 10

Syntax Highlighting Only

There is a separate method highlight that preserves all original whitespace and just adds syntax highlighting.

This is useful for sql that is already well formatted and just needs to be a little easier to read.

<?php
echo (new SqlFormatter())->highlight($query);

Output:

Compress Query

The compress method removes all comments and compresses whitespace.

This is useful for outputting queries that can be copy pasted to the command line easily.

-- This is a comment
    SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;

echo (new SqlFormatter())->compress($query);

Output:

SELECT Id as temp, DateCreated as Created FROM MyTable;