Format and pretty print SQL queries

Contents

• Installation
• Example Output
• Mix Formatter
• Supporting SqlFmt
• Attribution

Installation

Available in Hex, the package can be installed by adding sql_fmt to your list of dependencies in mix.exs:

def deps do
  [
    {:sql_fmt, "~> 0.3.0"}
  ]
end

Documentation can be found at https://hexdocs.pm/sql_fmt.

Example Output

After setting up SqlFmt in your application you can use the SqlFmt functions in order to format queries. Here are a couple examples of queries with having parameters inline and with passing in the parameters separately:

iex(1)> {:ok, formatted_sql} = SqlFmt.format_query("select * from businesses where id in ('c6f5c5f1-a1fc-4c9a-91f7-6aa40f1e233d', 'f339d4ce-96b6-4440-a541-28a0fb611139');")
{:ok, "SELECT\n  *\nFROM\n  businesses\nWHERE\n  id IN (\n    'c6f5c5f1-a1fc-4c9a-91f7-6aa40f1e233d',\n    'f339d4ce-96b6-4440-a541-28a0fb611139'\n  );"}

iex(2)> IO.puts(formatted_sql)
SELECT
  *
FROM
  businesses
WHERE
  id IN (
    'c6f5c5f1-a1fc-4c9a-91f7-6aa40f1e233d',
    'f339d4ce-96b6-4440-a541-28a0fb611139'
  );
:ok

iex(1)> {:ok, formatted_sql} = SqlFmt.format_query_with_params("select * from help where help.\"col\" in $1;", ["'asdf'"])
{:ok, "SELECT\n  *\nFROM\n  help\nWHERE\n  help.\"col\" IN 'asdf';"}

iex(2)> IO.puts(formatted_sql)
SELECT
  *
FROM
  help
WHERE
  help."col" IN 'asdf';
:ok

Be sure to checkout the HexDocs as you can also provide formatting options to the functions to tailor the output to your liking.

Mix Formatter

SqlFmt also provides you with the ~SQL sigil that can be used to format SQL via Mix Formatter plugin. To set up the Mix Formatter plugin, simply install this package and add update your .formatter.exs file as follows:

[
  plugins: [SqlFmt.MixFormatter],
  inputs: ["**/*.sql"],
  # ...
]

With this configuration, the SqlFmt Mix Format plugin will now format all ~SQL sigils and all files ending in .sql. This can be particularly useful in Ecto migrations where you have large execute statements and you want to make sure that your code is readable. Check out the SqlFmt.MixFormatter module docs for more information.

Supporting SqlFmt

If you rely on this library help you debug your Ecto/SQL queries, it would much appreciated if you can give back to the project in order to help ensure its continued development.

Checkout my GitHub Sponsorship page if you want to help out!

Gold Sponsors

Silver Sponsors

Bronze Sponsors

Attribution

• The logo for the project is an edited version of an SVG image from the unDraw project.
• The SqlFmt library leans on the Rust library sqlformat-rs for SQL statement formatting.