dna-query-magic

🎯 This extension streamlines SQL cleanup, standardization, and functional comparison – with support for both batch (multi-file) and in-file formatting. Built on top of the sql-formatter parser, it is designed for efficiency and ease of use. It features a powerful before and after compare tool for file transformations, along with flexible custom find/replace transformations. Whether processing entire repositories or editing inline, this tool helps maintain clean, consistent SQL – reducing human error and making code reviews smoother.

Index

• VS Code Explorer Right-Click Operations

  • DnA-1: Format SQL
  • DnA-2: Custom Transformations

• Editor Operations (Right-Click in Open File)

• Appendix

  • SQL Comparison
  • Custom Find/Replace
  • SQL Formatter Parser

• Technical

VS Code Explorer Right-Click Operations

DnA-1: Format SQL

• SQL → Prettier Format - Base sql-formatter parser with minimal regex enhancements
• SQL → DnA Style Guide - Applies specific style guide rules on top of sql-formatter parser
• Compare Two SQL Files - Compares files for functional equivalence after transformation (see details)

File Operation Options: When transforming files, you can:

• Transform in place (overwrites original)
• Create copy with prefix (preserves original)

DnA-2: Custom Transformations

• Copy Files - Duplicate files with custom prefix
• Remove File Prefixes - Batch remove prefixes from filenames
• Run Custom Find/Replace - Apply saved transformations
• ➕ Create Custom Find/Replace - Wizard to create new transformations (see details)
• ⚙️ Manage Transformations - Edit, delete, or disable saved transformations

Note: All commands support batch processing on multiple selected files.

Editor Operations (Right-Click in Open File)

Format selected text or entire file:

• DnA: SQL → DnA Style Guide
• DnA: SQL → Prettier

Note: Select all (Ctrl+A / Cmd+A) or highlight individual queries to format specific sections.

Appendix

SQL Comparison

Determines if two files are functionally equivalent after transformation.

Typical workflow:

1. 📋 Copy an existing file
2. ✏️ Transform the copy
3. 🔍 Compare original and transformed files

Comparison process:

• Both files are formatted using the Prettier formatter
• Comments, whitespace, and SET statements are removed
• Formatting and case are normalized
• Temporary formatted versions stored in memory for comparison
• If functionally identical, success message shown
• If different, VS Code diff view opens showing differences

This ensures apples-to-apples comparison by normalizing both files identically before comparing.

Custom Find/Replace

Create reusable find/replace transformations through a 3-step wizard:

1. 📝 Name the transformation
2. 🔎 Specify find text
3. ✏️ Specify replacement text

Transformations are stored in queryMagic.customTransformations in VS Code user settings but should be managed through the UI commands.

Common use cases:

• Environment transformations: stg. → tmp_
• Dialect conversions: CREATE TEMPORARY TABLE → CREATE OR REPLACE TEMP VIEW
• Schema replacements: old_schema. → new_schema.

Quick prefix tip: When creating copies, type a prefix directly in the dialog (e.g., "backup", "test") instead of selecting from options.

SQL Formatter Parser

This extension is built on top of the open-source sql-formatter parser, which provides reliable and flexible SQL formatting across multiple dialects. For more details or to contribute, see the sql-formatter GitHub project.

Technical

• Built with sql-formatter, TypeScript, VS Code Extension API
• Works on any text file (not limited to .sql extensions)
• Cross-platform (Windows, WSL/Linux, macOS)
• License: MIT