dna-query-magic

A VS Code extension for SQL cleanup, formatting, and batch file processing — built around a team-specific style guide.

Built on sql-formatter with additional post-processing rules for consistent output across Hive, SparkSQL, and Snowflake dialects.

---

Contents

• Quick Start
• DnA Style Guide Specifics
• Core Features
  • Batch File Processing
  • How It Works
  • Editor Formatting
  • Custom Find/Replace
  • Compare Two SQL Files
  • File Utilities
• Appendix

---

Quick Start

1. Right-click SQL file(s) in Explorer → DnA: SQL → DnA Style Guide
2. Choose Transform in place or Create copy with prefix
3. Done.

Works on single files or batch selections.

---

DnA Style Guide Specifics

• Keywords uppercase (SELECT, FROM, WHERE)
• Identifiers lowercase
• 4-space indentation
• Keywords on their own lines
• CASE statements expanded for readability
• Handles Hive/SparkSQL/Snowflake syntax fairly consistently and gracefully

---

Core Features

Batch File Processing

Select multiple files in Explorer → Right-click → DnA: SQL → DnA Style Guide

When processing files, you're prompted:

Option	What happens
Transform in place	Overwrites original files
Create copy with prefix	Preserves originals, creates prefixed copies (e.g., formatted-myfile.sql)

This works across any number of files - format an entire folder of SQL at once.

---

How It Works

Input SQL → Normalize → Protect Variables → sql-formatter → Apply Rules → Restore → Output SQL

The multi-stage pipeline handles edge cases that trip up simpler formatters - nested CASE statements, mixed dialects, template variables, and legacy code with inconsistent formatting.

---

Editor Formatting

Format SQL directly in an open file:

1. Select text (or Ctrl+A for entire file)
2. Right-click → DnA: SQL → DnA Style Guide
3. Selection is formatted in-place

---

Custom Find/Replace

Create reusable text transformations for common SQL modifications. See Appendix G for detailed documentation.

Create: Right-click → DnA: Utilities → ➕ Create Custom Find/Replace

Run on files: Right-click file(s) in Explorer → DnA: Utilities → Run Custom Find/Replace

Run on selection: Highlight text in editor → Right-click → Run Custom Find/Replace

Transformation types:

• 🔤 Simple Find/Replace — Basic text replacement
• 🎯 Smart Pattern Find/Replace — Replace specific occurrence or apply regex
• ⛓️ Chain Find/Replace — Run multiple Find/Replaces in sequence
• 🔗 Text Builder — Comma-separated lists, URL strings, etc. (clipboard only)

---

Compare Two SQL Files

Verify that two SQL files are functionally equivalent — useful for validating production changes with confidence.

Access: Right-click → DnA: Utilities → Compare Two SQL Files

How it works: Both files are normalized to Prettier format in the background (comments and whitespace removed), then compared. If identical, you get a success message. If different, VS Code's diff view opens.

---

File Utilities

Operation	Access
Copy files with prefix	DnA: Utilities → Copy Files
Remove file prefixes	DnA: Utilities → Remove File Prefixes

---

Appendix Index

• A: Team-Specific Conventions
• B: Formatting Rules Reference
• C: Protected Patterns
• D: Multi-Dialect Support
• E: About sql-formatter
• F: Optional Features
• G: Custom Find/Replace Reference

---

Appendix A: Team-Specific Conventions

⚠️ This formatter implements a specific SQL style guide

The conventions include:

• Keywords on their own lines (not inline)
• Explicit JOIN types (INNER JOIN, not JOIN)
• CASE statements with WHEN/THEN/ELSE on separate lines
• 4-space indentation (not 2)

If these don't match your needs:

1. Use the Prettier formatter (enable in settings) in this extension for lighter formatting
2. Consider SQL Formatter VSCode

---

Appendix B: Formatting Rules Reference

Rules execute in priority order (higher priority runs first).

#	Priority	Rule	Description
1	100	IDENT-001	Lowercase identifiers
2	90	KEYWORD-001	Uppercase keywords
3	85	BUGFIX-001	Fix sql-formatter edge cases
4	80	INDENT-001	4-space indentation
5	75	JOIN-001	JOIN clause formatting
6	55	WINDOW-001	Window function formatting
7	50	FUNC-001	Function call spacing
8	49	FUNC-002	Closing parenthesis placement
9	48	FUNC-003	Simple function calls
10	47	POLISH-008	Fix Hive dialect quirks (SET/INSERT)
11	45	POLISH-003	Fix orphaned commas
12	40	POLISH-004	Fix IN list indentation
13	38	POLISH-006	Fix spacing before parentheses
14	35	POLISH-005	Format CASE statements
15	25	POLISH-009	Case-insensitive comparisons (opt-in)
16	15	POLISH-007	Blank lines around UNION
17	12	POLISH-002	Remove excessive blank lines
18	10	POLISH-001	Final cleanup

---

Appendix C: Protected Patterns

These patterns are preserved during formatting (not mangled by the formatter):

Pattern	Example
Hive variables	${hiveconf:variable}
Generic variables	${variable}
Jinja expressions	{{ expression }}
Jinja blocks	{% block %}

---

Appendix D: Multi-Dialect Support

The formatter tries multiple sql-formatter dialect modes in sequence:

1. PostgreSQL - Strictest, best for Snowflake-style SQL
2. Spark - Handles HiveQL without weird line breaks
3. Hive - Fallback for edge cases
4. Generic SQL - Last resort

If one dialect mode fails to parse the SQL, the next is tried automatically. This prevents silent failures where the formatter returns unformatted SQL. If you see leading commas unchanged after formatting, the file likely has syntax that none of the dialect modes could handle.

---

Appendix E: About sql-formatter

This extension is built on sql-formatter, an open-source SQL formatting library that supports multiple dialects.

Related Extension

If you're looking for a general-purpose SQL formatter without team-specific rules, consider SQL Formatter VSCode - a VS Code extension that provides direct access to sql-formatter with configurable options.

Note: SQL Formatter VSCode is the direct successor to the Prettier SQL VSCode extension, which is no longer maintained.

Why We Built On Top

Rather than using sql-formatter directly, this extension adds:

• Post-processing rules for team-specific conventions
• Multi-dialect fallback for mixed codebases
• Protected pattern handling for Hive/Jinja variables
• Batch file processing with copy/transform options
• Custom find/replace transformations

If you just need standard SQL formatting, the SQL Formatter VSCode extension may be a better fit.

---

Appendix F: Optional Features

One feature is disabled by default. Enable in VS Code settings (Ctrl+, → search for queryMagic).

Prettier Formatter

The base sql-formatter with minimal modifications. Useful for comparison or lighter formatting.

"queryMagic.enablePrettierFormatter": true

Adds DnA: SQL → Prettier to context menus.

---

Appendix G: Custom Find/Replace Reference

Create reusable transformations for common text modifications. Config stored in ~/.query-magic/customFindReplace.toml (auto-created on extension activation).

Commands:

• DnA: Run Custom Find/Replace — run a transformation
• DnA: Create Custom Find/Replace — create new transformation
• DnA: Manage Custom Find/Replace — edit, delete, enable/disable
• DnA: Edit Custom Find/Replace Config — open TOML file directly

Wizard types (in order):

1. 🔤 Simple Find/Replace — Basic text replacement
2. 🎯 Smart Pattern Find/Replace — Replace specific occurrence or apply regex
3. ⛓️ Chain Find/Replace — Run multiple Find/Replaces in sequence
4. 🔗 Text Builder — Comma-separated lists, URL strings, etc.

---

1. Simple Find/Replace

Basic text replacement. Most common use case.

Example: Replace schema prefix

1. Create → Simple Find/Replace
2. Name: staging to prod
3. Find: staging.
4. Replace: prod.

---

2. Smart Pattern Find/Replace

Replace specific occurrences with optional conditions. Two modes: non-regex and regex.

Non-regex example: Convert database prefix

Converts prod_schema_table → prod.schema_table (only first underscore, only for specific prefixes)

1. Create → Smart Pattern Find/Replace
2. Name: convert db prefix
3. Use regex: No
4. Find: _
5. Replace: .
6. Occurrence: 1st
7. Direction: Left to Right
8. Condition: Yes → Starts with → prod|staging|dev

Regex example: Same conversion using regex

1. Create → Smart Pattern Find/Replace
2. Name: convert db prefix (regex)
3. Use regex: Yes
4. Find: \b(prod[a-zA-Z0-9]*|staging[a-zA-Z0-9]*|dev[a-zA-Z0-9]*)_
5. Replace: $1.

Regex example: Convert HTTPS URL to SSH

Converts https://gitlab.example.com/group/repo → git@gitlab.example.com:group/repo.git

1. Create → Smart Pattern Find/Replace
2. Name: HTTPS to SSH
3. Use regex: Yes
4. Find: https://([^/]+)/(.*)$
5. Replace: git@$1:$2.git

Note: In the wizard, type single backslashes (e.g., \b). The TOML file shows escaped backslashes (\\b) — this is normal.

---

3. Chain Find/Replace

Run multiple transformations in sequence. Create "building block" transformations, then chain them.

Example: Multi-step environment swap

First, create two simple transformations:

Step 1: Create → Simple Find/Replace

• Name: temp to permanent
• Find: TEMPORARY TABLE
• Replace: TABLE

Step 2: Create → Simple Find/Replace

• Name: staging to dev schema
• Find: staging.
• Replace: dev_myuser.

Step 3: Create the chain:

1. Create → Chain Find/Replace
2. Name: prod to dev conversion
3. Select both transformations above
4. Order: temp to permanent first, schema second
5. Disable from picker: Yes (hides building blocks from main menu)

---

4. Text Builder

Build formatted text from selections. Two subtypes: Delimiter List and URL Builder.

Delimiter List example: Build IN clause

Convert column of values to comma-separated list for SQL IN clause.

1. Create → Text Builder → Delimiter Separated List
2. Name: values to IN clause
3. Quote style: Single quotes
4. Delimiter: ,
5. Output format: Single line

Usage: Select values (one per line), run transformation, paste result.

apple        →    'apple', 'banana', 'cherry'
banana
cherry

URL Builder example: Generate report links

1. Create → Text Builder → URL Builder
2. Name: dashboard link
3. Base URL: https://reports.example.com/dashboard?id=
4. Param: item_id
5. Separator: ,
6. Suffix: &view=detail

---

Configuration File

Location: ~/.query-magic/customFindReplace.toml (auto-created on activation)

Edit directly: Command Palette → DnA: Edit Custom Find/Replace Config

Format:

# Query Magic Custom Transformations

[[transformations]]
id = "custom-123"
type = "simple"
name = "Example"
find = "old_value"
replace = "new_value"
enabled = true

Custom path: Set queryMagic.customFindReplaceFile in VS Code settings to override default location

---

License

MIT