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.
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
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
Key | Description | Value options |
---|---|---|
language | The SQL dialect to use | sql , bigquery , db2 , hive , mariadb , mysql , n1ql , plsql , postgresql , redshift , singlestoredb , spark , sqlite , transactsql , trino |
tabWidth | Amount of indentation to use (option ignored when useTabs option is enabled) | 2 or other numeric quantities or the string "\t" for tabs |
useTabs | To use tabs for indentation | false , true |
keywordCase | Uppercases or lowercases keywords | preserve (original casing), upper , lower |
indentStyle | Defines overall indentation style | standard (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) |
logicalOperatorNewline | Newline before or after boolean operator (AND , OR , XOR ) | before , after |
tabulateAlias | Aligns column aliases vertically | false , true |
commaPosition | Where to place the comma in column lists | after , before , tabular |
expressionWidth | Maximum number of characters in parenthesized expressions to be kept on single line | 50 characters or some other numeric quantity |
linesBetweenQueries | How many newlines to insert between queries | 1 or some other positive integer (or boolean false for 0 ) |
denseOperators | Packs operators densely without spaces | false , true |
newlineBeforeSemicolon | Places semicolon on separate line | false , true |
params | Collection of values for placeholder replacement | "" (default); Array (position placeholders) or Object (named placeholders); see reference |
paramTypes | Specifies 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
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:
- Copy the query to be formatted.
- Run the
qtf
alias in your shell of choice (i.e., bash or fish). - Paste the newly formatted query to its desired target location.
ghead
to strip trailing newlines from formatted queriesBoth 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
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
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'