Two years ago, I gave a talk at Lambda Days 2024 titled “Grepping SQL Code like a boss”. In that talk, I explored how we could use the fast gem—originally designed for Ruby ASTs—to parse, navigate, and refactor SQL code using PostgreSQL’s native parser.
Since then, the AI landscape has shifted massively, and the tools we use to navigate codebases have evolved. Today, I’m thrilled to announce a game-changing update to the fast gem: Model Context Protocol (MCP) Support for SQL.
Expanding Fast with MCP
We recently integrated full MCP support directly into the fast CLI. This allows modern AI assistants (like Claude, Gemini, or custom agents) to seamlessly interface with your SQL and Ruby codebase through structured, semantic queries rather than fragile regular expressions.
The new MCP server provides three incredibly powerful SQL tools:
search_sql_ast: Search SQL files using Fast AST patterns.rewrite_sql: Preview structural SQL transformations safely.rewrite_sql_file: Apply AST pattern replacements to SQL files in-place.
By coupling MCP with fast, AI agents can understand the deep semantic structure of your SQL queries, not just the text. They can find specific nodes (like create_hypertable in TimescaleDB) or locate poorly optimized queries, and even rewrite them autonomously.
The Problem: Massive CTE Duplication
Let’s look at a concrete optimization problem where AST analysis shines. In massive analytical queries, it’s common to use Common Table Expressions (CTEs) to organize logic. However, developers or ORMs often accidentally duplicate the inner logic of a CTE later in the query.
Consider this example:
WITH sales_2023 AS (
SELECT region, SUM(amount) as total_sales
FROM sales
WHERE extract(year from date) = 2023
GROUP BY region
),
top_regions AS (
SELECT region
FROM (
-- This inner query is identical to the sales_2023 CTE!
-- The database might re-compute this unnecessarily.
SELECT region, SUM(amount) as total_sales
FROM sales
WHERE extract(year from date) = 2023
GROUP BY region
) sub
WHERE total_sales > 100000
)
SELECT s.region, s.total_sales
FROM sales_2023 s
JOIN top_regions t ON s.region = t.region;
This query re-compiles the exact same aggregation twice. In a large database, this is an expensive mistake.
Automating CTE Optimization with Fast
Using fast, we can write a script to automatically map all defined CTEs and then scan the rest of the AST to see if any subqueries are structurally identical to the CTEs we just declared.
Here is the Fastfile shortcut we built to prove the concept:
Fast.shortcut :find_duplicate_ctes do
require 'fast/sql'
file = ARGV.last
ast = Fast.parse_sql_file(file)
# Map all CTEs
ctes = {}
Fast.search('(common_table_expr ...)', ast).each do |node|
name_node = node.search('(ctename $_)').first
query_wrapper = node.search('(ctequery $_)').first
if name_node && query_wrapper
name = name_node.children.first # extract string name
ctes[name] = query_wrapper.children.first # the actual select_stmt
end
end
# Helper to recursively walk the AST for all SELECT statements
def self.walk(node, results = [])
results << node if node.respond_to?(:type) && node.type == :select_stmt
if node.respond_to?(:children)
node.children.each do |child|
if child.is_a?(Array)
child.each { |c| walk(c, results) }
else
walk(child, results)
end
end
end
results
end
# Compare inner subqueries against defined CTEs
walk(ast).each do |node|
ctes.each do |name, query|
# Deep AST structural comparison ignoring whitespace formatting
if node.to_s.gsub(/\s+/, ' ') == query.to_s.gsub(/\s+/, ' ') && node.object_id != query.object_id
puts "Found duplication of CTE '#{name}'!"
puts "Lines: #{node.loc.expression.first_line}..#{node.loc.expression.last_line}"
end
end
end
end
Running this via fast .find_duplicate_ctes cte_example.sql instantaneously finds the duplicated logic without getting confused by varying indentation or line breaks.
The Future with MCP
With the fast MCP server, you no longer have to write these Ruby scripts manually every time. An AI assistant can perform the AST structural analysis for you on the fly.
We can instruct the AI to:
“Analyze all
.sqlfiles in the repository. Identify any subqueries that are identical to existing CTEs, and rewrite the file to use the CTE reference instead.”
The agent will leverage search_sql_ast and rewrite_sql_file to perform surgical, AST-aware refactoring that is magnitudes safer than any Regex approach could ever be.
Conclusion
The integration of MCP into the fast gem marks a significant milestone in how we interact with SQL and Ruby codebases. By providing AI agents with the ability to understand and manipulate ASTs directly, we unlock new levels of automation and optimization that were previously too complex or risky.
Whether you’re hunting for duplicate CTEs or refactoring legacy queries, the combination of structured analysis and AI intelligence makes the process faster, safer, and much more intuitive.
Happy hacking!
