# SQL formatting

> One shortcut reflows the statement, or just the lines you've selected, in the mode and keyword case you choose.

Source: https://docs.querylab.io/writing-sql/sql-formatting/

**before**

```sql
select u.user_id,u.country,count(*) as total from `ace-analytics.warehouse.users` u join `ace-analytics.warehouse.events` e on u.user_id=e.user_id where u.is_active=true and e.event_date>='2024-01-01' group by u.user_id,u.country order by total desc limit 100
```

**after Format (Pretty, defaults)**

```sql
SELECT
  u.user_id,
  u.country,
  count(*) AS total
FROM
  `ace-analytics.warehouse.users` u JOIN `ace-analytics.warehouse.events` e ON u.user_id = e.user_id
WHERE u.is_active = true AND e.event_date >= '2024-01-01'
GROUP BY u.user_id, u.country
ORDER BY total DESC
LIMIT 100
```

## Formatting a query

- Press `⇧ ⌘ F` (`Ctrl Shift F` on Windows and Linux).
- Open the ⋮ menu at the top right of the editor and choose **Format**. The same command is **Format SQL** in the command palette and the right-click menu.

**editor ⋮ menu**

Formatting is one undo step: `⌘ Z` puts the original back. The cursor stays where it was. If the SQL is invalid, nothing changes.

### Formatting only a selection

Select part of the statement and press the same shortcut: only the selection is reformatted, the rest is untouched.

```sql
WITH active AS (
  «hl:select * from `ace-analytics.warehouse.users` where is_active=true»
),
recent AS (
  SELECT * FROM `ace-analytics.warehouse.events`
)
SELECT * FROM active
```

becomes

```sql
WITH active AS (
  SELECT
    *
  FROM `ace-analytics.warehouse.users`
  WHERE is_active = true
),
recent AS (
  SELECT * FROM `ace-analytics.warehouse.events`
)
SELECT * FROM active
```

### Changing keyword case only

The two _Convert keywords to …_ items in the ⋮ menu change keyword case without touching layout. Shortcuts: `⇧ ⌘ U` for UPPERCASE, `⌥ ⌘ L` for lowercase. Function names are left as written either way: `count(*)` stays `count(*)`.

## Choosing a formatting mode

Settings → Editor → SQL Formatting → Formatting Mode.

| Mode                 | Result                                                                            |
| -------------------- | --------------------------------------------------------------------------------- |
| **Pretty** (default) | One clause per line, lists indented (the example at the top)                      |
| **Compact**          | Single line, minimal whitespace; for logs and chat                                |
| **Preserve**         | Keeps your layout; only the options below (keyword case, quoting, AS) are applied |

**Compact**

```sql
SELECT u.user_id,u.country,count(*) AS total FROM `ace-analytics.warehouse.users` u JOIN `ace-analytics.warehouse.events` e ON u.user_id=e.user_id WHERE u.is_active = true AND e.event_date >= '2024-01-01' GROUP BY u.user_id,u.country ORDER BY total DESC LIMIT 100
```

## Tuning the output

All in Settings → Editor → SQL Formatting.

<div class="set-sub">Formatting Mode</div>
  - Preserve: Keep original formatting
  - Pretty (default) (selected): Multi-line with indentation
  - Compact: Single-line, minimal whitespace
  <div class="set-sub">Keyword Case</div>
  - UPPER (default) (selected): Convert keywords to uppercase (SELECT, FROM, WHERE)
  - lower: Convert keywords to lowercase (select, from, where)
  - preserve: Keep original keyword case
  <div class="set-sub">Indentation</div>
  - Indent Size: 2. Number of spaces or tab width for indentation
  - Spaces (selected): Use spaces or tabs for indentation
  <div class="set-sub">Layout Options</div>
  - Newline After Clause (selected): Add newline after major SQL clauses (SELECT, FROM, WHERE, etc.)
  - Max Line Length: 80. Maximum line length before wrapping (in characters)
  - Trailing Comma: Add comma after last item in lists (better for git diffs)
  <div class="set-sub">Quote Identifiers</div>
  - Preserve (default) (selected): Keep backticks as written
  - Always: Add backticks to all identifiers (e.g., `table`.`column`)
  - Never: Remove all backticks from identifiers
  <div class="set-sub">AS Keyword</div>
  - Preserve (default) (selected): Keep AS keywords as written in original SQL
  - Always: Always add AS keyword (e.g., id AS user_id)
  - Never: Never use AS keyword (e.g., id user_id)
  <div class="set-sub">Pipe Operator Style</div>
  - Standard (default) (selected): Normal indentation (pipes indented with content)
  - Left-Aligned: Pipes at column 0, keywords indented right
  <div class="set-sub">Format on Type</div>
  - Format on Type (selected): Automatically format SQL as you type (e.g., after pressing Enter)

Three options aren't about layout. **Quote Identifiers** on _Always_ backticks every table and column name (`` `table`.`column` ``) and on _Never_ strips them all; leave it on _Preserve_ unless you know no identifier needs quoting. **AS Keyword** on _Always_ writes `id AS user_id` for every alias and on _Never_ writes `id user_id`. **Pipe Operator Style** applies to [pipe syntax](https://docs.querylab.io/writing-sql/pipe-syntax/): _Standard_ indents `|>` with the content, _Left-Aligned_ puts `|>` at column 0 and indents the operator keywords.

**Format on Type** reformats as you go, for example after `Enter`. It's off by default.

> Syntax conversion sits next to **Format** in the ⋮ menu: `⌥`   `⌘`   `P` (`Ctrl`   `Alt`   `P` on Windows and Linux) converts the statement to [pipe   syntax](https://docs.querylab.io/writing-sql/pipe-syntax/), `⌥`   `⌘`   `S` (`Ctrl`   `Alt`   `S`) converts it back.