sql_formatter 0.1.0

A pure-Dart SQL pretty-printer supporting standard SQL, PostgreSQL, MySQL and SQLite dialects. A faithful port of the JS sql-formatter library.

sql_formatter

A pure-Dart SQL pretty-printer (beautifier). Turns a one-line or messy SQL string into clean, indented, keyword-cased SQL. A faithful port of the popular JavaScript sql-formatter library.

• Zero runtime dependencies — pure Dart, works on Flutter, server, CLI and web.
• Multi-dialect — standard SQL, PostgreSQL, MySQL/MariaDB and SQLite.
• Deterministic & idempotent — format(format(x)) == format(x).

Install

dependencies:
  sql_formatter: ^0.1.0

Usage

import 'package:sql_formatter/sql_formatter.dart';

void main() {
  const sql =
      'select id, name, count(*) from users u left join orders o on o.uid=u.id '
      'where u.active=1 and o.total>100 group by id order by name limit 10';

  print(format(sql));
}

Output:

SELECT
  id,
  name,
  COUNT(*)
FROM
  users u
  LEFT JOIN orders o ON o.uid = u.id
WHERE
  u.active = 1
  AND o.total > 100
GROUP BY
  id
ORDER BY
  name
LIMIT
  10

Dialects

Pass a SqlDialect to handle dialect-specific syntax (identifier quoting, operators, placeholders, comment styles and keyword sets):

format(r'select data->>'"'"'k'"'"' from t where id = $1', dialect: SqlDialect.postgresql);
format('select `col` from t -- mysql # comment styles', dialect: SqlDialect.mysql);
format('select [My Col] from t', dialect: SqlDialect.sqlite);

Dialect	Identifiers	Strings	Line comments	Placeholders	Notable operators
standard	"id"	'...'	--	? :name	<= >= <> != ||
postgresql	"id"	'...', $$...$$, $tag$...$tag$	--	$1, :name	:: -> ->> #> #>> @> <@
mysql	`id`	'...', "..."	--, #	?	<=> := << >> && ||
sqlite	"id", `id`, [id]	'...'	--	? : @ $	|| == << >>

Options

format(
  sql,
  dialect: SqlDialect.standard,
  options: const FormatOptions(
    indent: '    ',                 // default: two spaces
    keywordCase: KeywordCase.upper, // upper | lower | preserve (default: upper)
    linesBetweenQueries: 1,         // blank lines between statements (default: 1)
  ),
);

What it handles

SELECT/INSERT/UPDATE/DELETE/CREATE TABLE, all JOIN flavours with inline ON, subqueries and CTEs, CASE … WHEN … THEN … ELSE … END, UNION/EXCEPT/INTERSECT, inline function calls (COUNT(*), COALESCE(a, b)), line/block comments, string literals (left untouched), and ON CONFLICT … DO NOTHING/DO UPDATE. Malformed SQL is handled best-effort without throwing.

Not (yet) supported

• expressionWidth-style auto line-wrapping of long expressions. Our line-breaking model differs from the JS sql-formatter: we break on clause and list boundaries rather than wrapping a long expression to a target column width.
• MERGE is best-effort. MERGE … WHEN MATCHED … WHEN NOT MATCHED … is laid out reasonably but is not guaranteed to match every dialect's canonical layout.

Differences from sql-formatter (JS)

• One keywordCase knob. It cases keywords, function names and data types together. The JS library splits these into separate keywordCase / functionCase / dataTypeCase options (the latter two default to preserve), so under KeywordCase.upper this package also upper-cases INT, TEXT, COUNT, etc.
• Opinionated default. keywordCase defaults to KeywordCase.upper here; the JS default is preserve. Pass KeywordCase.preserve to keep the original casing.

See CHANGELOG.md for version history.

Issues & contributions welcome

Found a layout you don't like, or a dialect quirk we miss? Please open an issue or PR at https://github.com/ultramcu/sql_formatter/issues.

Credits

Behaviour and keyword lists are ported from sql-formatter (MIT). See NOTICE.md.

License

MIT — see LICENSE.