Skip to main content

Automated SQL query formatting via the clipboard (Mac)

Daniel Farlow

This post details how to automate SQL query formatting via the clipboard on a Mac. Want to copy a SQL query, run a command, and then have a nicely formatted query to paste elsewhere? This is the post for you.

Try out the formatter before installing anything

This post highlights the sql-formatter Node.js package which has a demo site you can interactively explore to determine whether or not this formatter is for you.

Prerequisites

Development on a Mac

This post was developed on a Mac. All ideas presented should be applicable elsewhere, but you may run into issues (e.g., pbcopy on MacOS does not exist as a standard Windows utility, but clip does exist and is the rough equivalent).

The functionalities remarked on in this post rely on the Node.js sql-formatter package; consequently, this post assumes you have Node.js installed on your system (I was using Node.js v16.15.1 while writing and testing the contents of this article). This post also uses npx, an npm package runner, for simplicity. If you use npm 5.1 or earlier, then you cannot use npx; instead, you will need to install sql-formatter globally:

npm i -g sql-formatter

Finally, there is an option later in this post to use ghead (i.e., head from GNU coreutils). If you want to use this option, which is remarked on further below in the body of this post, then you may install GNU coreutils as follows (assuming you are using Homebrew as your package manager on your Mac):

brew install coreutils

Package details

Each of the "Key" options below links out to the package documentation, but brief descriptions are given below. Additionally, the first value listed in the "Value options" table is the default value used when no value is explicitly provided.

Configuration options

KeyDescriptionValue options
languageThe SQL dialect to usesql, bigquery, db2, hive, mariadb, mysql, n1ql, plsql, postgresql, redshift, singlestoredb, spark, sqlite, transactsql, trino
tabWidthAmount of indentation to use (option ignored when useTabs option is enabled)2 or other numeric quantities or the string "\t" for tabs
useTabsTo use tabs for indentationfalse, true
keywordCaseUppercases or lowercases keywordspreserve (original casing), upper, lower
indentStyleDefines overall indentation stylestandard (indents code by the amount specified by tabWidth option); tabularLeft (indents in tabular style with 10 spaces, aligning keywords to left); tabularRight (indents in tabular style with 10 spaces, aligning keywords to right)
logicalOperatorNewlineNewline before or after boolean operator (AND, OR, XOR)before, after
tabulateAliasAligns column aliases verticallyfalse, true
commaPositionWhere to place the comma in column listsafter, before, tabular
expressionWidthMaximum number of characters in parenthesized expressions to be kept on single line50 characters or some other numeric quantity
linesBetweenQueriesHow many newlines to insert between queries1 or some other positive integer (or boolean false for 0)
denseOperatorsPacks operators densely without spacesfalse, true
newlineBeforeSemicolonPlaces semicolon on separate linefalse, true
paramsCollection of values for placeholder replacement"" (default); Array (position placeholders) or Object (named placeholders); see reference
paramTypesSpecifies parameter placeholders types to support"" (default), positional, numbered, named, quoted; see reference

Example (personal configuration)

I personally use the following configuration options:

{
"language": "mysql",
"tabWidth": 2,
"useTabs": false,
"keywordCase": "upper",
"indentStyle": "standard",
"logicalOperatorNewline": "before",
"tabulateAlias": false,
"commaPosition": "after",
"expressionWidth": 50,
"linesBetweenQueries": 1,
"denseOperators": false,
"newlineBeforeSemicolon": false
}

This results in a query such as

select supplier_name,city from
(select * from suppliers join addresses on suppliers.address_id=addresses.id)
as suppliers
where supplier_id>500
order by supplier_name asc,city desc;

being transformed into

SELECT
supplier_name,
city
FROM
(
SELECT
*
FROM
suppliers
JOIN addresses ON suppliers.address_id = addresses.id
) AS suppliers
WHERE
supplier_id > 500
ORDER BY
supplier_name ASC,
city DESC;

Script version

Node.js script for using sql-formatter
const { format } = require('sql-formatter');

// for copying formatted query to clipboard
function pbcopy(data) {
const proc = require('child_process').spawn('pbcopy');
proc.stdin.write(data); proc.stdin.end();
}

const QUERY_FORMAT_CONFIG = {
"language": "mysql",
"tabWidth": 2,
"useTabs": false,
"keywordCase": "upper",
"indentStyle": "standard",
"logicalOperatorNewline": "before",
"tabulateAlias": false,
"commaPosition": "after",
"expressionWidth": 50,
"linesBetweenQueries": 1,
"denseOperators": false,
"newlineBeforeSemicolon": false
}

const QUERY_STR = `
select supplier_name,city from
(select * from suppliers join addresses on suppliers.address_id=addresses.id)
as suppliers
where supplier_id>500
order by supplier_name asc,city desc;
`

const FORMATTED_QUERY = format(QUERY_STR, QUERY_FORMAT_CONFIG);
pbcopy(FORMATTED_QUERY) // copies formatted query to clipboard

Above, FORMATTED_QUERY is the actual formatted query. Using pbcopy, as defined at the beginning of the script, makes it possible to copy FORMATTED_QUERY to the clipboard.

If you do not wish to execute a script and update the FORMATTED_QUERY value every single time (likely), then a shell-based solution may be desirable.

Shell versions

As alluded to above as well as in this blog post's description, the ultimate goal would be to not have to run a file script with contents we would have to update whenever we wanted to format a SQL query. Specifically, it would be nice to be able to copy whatever query we wanted to format, use a shell alias to run a script on this copied content, and then automatically have the formatted output available for us to past wherever we wanted.

This section presents such solutions for the bash shell (place the alias in your bash profile) and fish shell (place the alias in your fish config). Both solutions make use of the personal package configurations provided previously. The qtf alias, which stands for query to format, can be used quite simply:

  1. Copy the query to be formatted.
  2. Run the qtf alias in your shell of choice (i.e., bash or fish).
  3. Paste the newly formatted query to its desired target location.
Solutions use ghead to strip trailing newlines from formatted queries

Both solutions below make use of the ghead utility from GNU coreutils (see beginning of post for installation notes), but usage of this utility is not critical.

Specifically, ghead is used to strip formatted queries of trailing newlines before the result is copied to the clipboard:

... | ghead -c -1 | pbcopy

For example, the query SELECT * from geezers WHERE age<14 results in the following formatted query when | ghead -c -1 precedes | pbcopy as depicted above (and as included in both solutions provided below):

SELECT
*
FROM
geezers
WHERE
age < 14

If | ghead -c -1 is removed, then we will get the following formatted query:

SELECT
*
FROM
geezers
WHERE
age < 14

Note the presence of the trailing newline. I personally do not like this and choose to strip it away by means of ghead. If you do not mind its presence, then simply remove | ghead -c -1 from both solutions below.

bash shell

/Users/danielfarlow/.bash_profile
alias qtf='pbpaste | npx sql-formatter --config <(echo \"{ "language": "mysql", "tabWidth": 2, "useTabs": false, "keywordCase": "upper", "indentStyle": "standard", "logicalOperatorNewline": "before", "tabulateAlias": false, "commaPosition": "after", "expressionWidth": 50, "linesBetweenQueries": 1, "denseOperators": false, "newlineBeforeSemicolon": false }\") | ghead -c -1 | pbcopy'

fish shell

/Users/danielfarlow/.config/fish/config.fish
alias qtf='pbpaste | npx sql-formatter --config (echo \'{ "language": "mysql", "tabWidth": 2, "useTabs": false, "keywordCase": "upper", "indentStyle": "standard", "logicalOperatorNewline": "before", "tabulateAlias": false, "commaPosition": "after", "expressionWidth": 50, "linesBetweenQueries": 1, "denseOperators": false, "newlineBeforeSemicolon": false }\' | psub -f) | ghead -c -1 | pbcopy'