I’m becoming a .NET developer. Yes, I can’t believe it either, but it’s happening! 🚀
Here we go: github.com/jonatas/grepsql
Since I joined Bax Energy, I’ve been diving deep into the .NET ecosystem. To leverage my learning process, I decided to port one of my favorite Ruby projects - the SQL AST matcher from Fast - to C#.
This journey has been incredible. I’ve been “vibe coding” with an insane amount of help from AI, both for development and learning .NET fundamentals. What’s mind-blowing is how AI has accelerated the learning curve. I probably spent at least 200 hours building and debugging AST patterns for the Ruby version, but now I’m making rapid progress with intelligent assistance.
As someone who spent the last few years at Timescale in a role where programming wasn’t the main activity, I’m incredibly excited about how much progress I’ve made building this .NET tool from scratch. If you’re interested in the broader concepts behind SQL AST pattern matching, check out my previous post on grepping SQL code like a boss.
What is GrepSQL?
GrepSQL is a .NET wrapper for libpg_query that provides PostgreSQL query parsing and advanced SQL pattern matching capabilities. Think of it as “grep for SQL” but with the power of Abstract Syntax Tree (AST) navigation.
Instead of simple string matching, GrepSQL understands the structure of your SQL queries and lets you search for patterns at the semantic level. Want to find all SELECT statements that query a specific table? Or locate hardcoded passwords in your SQL files? GrepSQL makes these tasks trivial.
Core Features
1. Command Line Power Tool
The command-line interface makes SQL analysis accessible to everyone:
# Find all SELECT statements
./grepsql.sh "SelectStmt" --from-sql "SELECT id FROM users"
# Find table references with highlighting
./grepsql.sh "(relname \"users\")" --from-sql "SELECT * FROM users JOIN products ON users.id = products.user_id" --highlight
# Show AST structure for debugging
./grepsql.sh "SelectStmt" --from-sql "SELECT id FROM users" --tree
2. S-Expression Pattern Language
GrepSQL uses a LISP-inspired s-expression syntax for pattern matching. This might look unfamiliar at first, but it’s incredibly powerful:
# Basic node type matching
SelectStmt # Any SELECT statement
InsertStmt # Any INSERT statement
# Field-specific matching
(relname "users") # Table named "users"
(relname _) # Any table name
(sval "admin") # String constant "admin"
(ival 42) # Integer constant 42
# Set matching (OR logic)
(relname {users orders products}) # Any of these tables
(sval {admin user guest}) # Any of these strings
# Nested patterns
(RangeVar (relname "users")) # Table reference to "users"
3. Advanced Pattern Matching
Here’s where GrepSQL shines. Let’s look at practical examples:
Finding Security Issues
# Find hardcoded credentials
./grepsql.sh "(sval \"password\")" *.sql
./grepsql.sh "(sval \"admin\")" *.sql --highlight
# Find sensitive data patterns
./grepsql.sh "(sval {password secret key token})" *.sql
Code Quality Analysis
# Find magic numbers
./grepsql.sh "(ival _)" *.sql
# Find SELECT * patterns (potential performance issues)
./grepsql.sh "A_Star" *.sql --highlight
# Find all table references for migration planning
./grepsql.sh "(relname _)" migration.sql
Complex Structural Patterns
# Find SELECTs with WHERE clauses containing comparisons
./grepsql.sh "(SelectStmt ... (whereClause (A_Expr ...)))" *.sql
# Find any query containing a specific table, regardless of context
./grepsql.sh "(... (relname \"users\"))" *.sql
# Find JOIN operations
./grepsql.sh "JoinExpr" *.sql --tree
4. .NET API Integration
For developers who want to integrate GrepSQL into their applications:
using GrepSQL.SQL;
// Basic pattern matching
var sql = "SELECT name FROM users WHERE age > 18";
var matches = PatternMatcher.Search("(relname _)", sql);
Console.WriteLine($"Found {matches.Count} table references");
// Parse SQL into AST
var result = Postgres.ParseSql(sql);
Console.WriteLine(TreePrinter.Print(result.ParseTree));
// Check if pattern matches
bool hasUserTable = PatternMatcher.Match("(relname \"users\")", sql);
// Advanced analysis
PatternMatcher.SetDebug(true);
var analysis = PatternMatcher.Analyze("(relname _)", sql);
Understanding the S-Expression Syntax
The pattern language is inspired by LISP’s s-expressions. Here’s a quick reference:
Basic Structure: (head children...)
(SelectStmt ...) # Any SELECT statement
(SelectStmt (targetList ...)) # SELECT with specific target list
(A_Const (ival _)) # Any integer constant
Wildcards and Logic
# Wildcards
_ # Match any single node
nil # Match exactly null/empty
... # Match any node with children
# Logical operators
{a b c} # OR: match any of a, b, or c
[a b c] # AND: all conditions must be true
!pattern # NOT: pattern must not match
?pattern # MAYBE: optional pattern
Ellipsis Navigation
The ellipsis (...) provides structured traversal:
# Find pattern anywhere in subtree
(SelectStmt ... (relname "users")) # SELECT containing table "users"
(... (whereClause (A_Expr ...))) # Any query with WHERE expression
(SelectStmt ... (A_Const (ival 42))) # SELECT containing integer 42
Real-World Examples
Let me share some practical scenarios where GrepSQL shines:
Database Migration Analysis
When planning database migrations, you need to understand how tables are used:
# Find all references to a table you're planning to rename
./grepsql.sh "(relname \"old_table_name\")" *.sql
# Find all schema references
./grepsql.sh "(schemaname _)" *.sql
# Find all foreign key references
./grepsql.sh "FkConstraint" *.sql --tree
Security Auditing
# Find potential SQL injection risks (string concatenation patterns)
./grepsql.sh "(sval _)" *.sql | grep -E "(SELECT|INSERT|UPDATE|DELETE)"
# Find administrative access patterns
./grepsql.sh "(sval {admin root superuser})" *.sql --highlight
# Identify hardcoded connection strings
./grepsql.sh "(sval {localhost 127.0.0.1 password})" *.sql
Performance Optimization
# Find queries that might benefit from indexing
./grepsql.sh "(... (whereClause ...))" *.sql
# Find potentially slow SELECT * queries
./grepsql.sh "(SelectStmt (targetList (ResTarget (val (ColumnRef (fields))))))" *.sql
# Find subqueries that could be optimized
./grepsql.sh "SubLink" *.sql --tree
The Development Journey
Building GrepSQL has been an intro to the .NET ecosystem. Coming from a Ruby background, I was amazed by:
- The Tooling: Visual Studio Code with C# extensions provides incredible IntelliSense and debugging capabilities
- Performance: The compiled nature of C# delivers impressive performance compared to interpreted Ruby
- Ecosystem: NuGet package management and the rich .NET ecosystem made integrating libpg_query straightforward
- Cross-Platform: Building for Linux, macOS, and Windows from a single codebase is seamless
The most challenging part was understanding the libpg_query C bindings and how to properly wrap them in C#. The Google Protocol Buffers integration required careful marshaling of data between C and managed code.
Error Handling and Debugging
GrepSQL provides robust error handling with detailed information:
try
{
var result = Postgres.ParseSql("SELECT * FROM");
}
catch (PgQueryException ex)
{
Console.WriteLine($"Error: {ex.Message}");
Console.WriteLine($"Position: {ex.CursorPosition}");
}
For debugging patterns, use:
# Show expression tree for pattern debugging
./grepsql.sh "SelectStmt" --only-exp
# Enable verbose debug output
./grepsql.sh "pattern" *.sql --debug --verbose
Contributing and Future Plans
GrepSQL follows modern .NET development practices and welcomes contributions! The codebase is organized with:
- Clean separation between SQL parsing and pattern matching
- Automated CI/CD pipeline
- Cross-platform compatibility
My next goals include:
- Enhanced Pattern Syntax: More logical operators and syntactic sugar
- IDE Integration: Visual Studio Code extension for real-time SQL analysis
- Additional Output Formats: JSON, XML, and structured data formats
- Performance Optimizations: Caching and parallel processing for large codebases
Acknowledgments
This project wouldn’t exist without the incredible work of:
- libpg_query - The PostgreSQL query parsing foundation 🫶🏼
- Fast - My Ruby AST pattern matching inspiration
- The .NET and C# communities for their excellent documentation and tooling
Try It Yourself!
I encourage you to download GrepSQL and experiment with your own SQL files. Whether you’re doing security audits, planning migrations, or just exploring your SQL codebase, GrepSQL can provide insights that traditional grep simply can’t match.
The intersection of AST parsing, pattern matching, and modern tooling creates powerful possibilities for code analysis. As AI continues to accelerate development workflows, tools like GrepSQL become even more valuable for understanding and maintaining large codebases.
Ready to grep your SQL like a boss? Check out the project and give it a try!
Happy coding! 🚀
Follow me on LinkedIn or open an issue on GitHub if you have questions or ideas to share!
