When I started building ExoQuery, I had one goal: take codebases with hundreds of duplicate SQL queries and give developers the tools to abstract out the common parts. Not through views. Not through table-returning UDFs. Through actual, composable query fragments that work at compile time.
The epitome of this concept is automatic SQL rewriting. You feed an AI a bunch of similar queries, it figures out the common patterns, and it refactors them into maintainable ExoQuery code. When I built the ExoQuery MCP server, I didn't actually expect this to work. But it does.
Try out these prompts as you go through the blog post. Use the following URL to access the ExoQuery MCP server:
https://backend.exoquery.com/mcpThe Setup
Here was my prompt:
Write me 3 medium size SQL queries in a random domain that are very close but have minor differences e.g. an extra join and an extra column. Make sure they are runnable by Sqlite.
Claude faithfully gave me three e-commerce queries. Same domain, same base tables, minor differences: an extra join here, an extra column there, a filter added. The kind of thing you see in real codebases where someone copy-pasted a query and tweaked it.
Query 1: Basic order summary
SELECT
o.id AS order_id,
o.order_date,
c.name AS customer_name,
SUM(oi.quantity * oi.unit_price) AS total_amount
FROM "Order" o
INNER JOIN Customer c ON c.id = o.customer_id
INNER JOIN OrderItem oi ON oi.order_id = o.id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.id, o.order_date, c.name
ORDER BY o.order_date DESC;Query 2: Adds product category (extra join + column)
SELECT
o.id AS order_id,
o.order_date,
c.name AS customer_name,
cat.name AS category_name,
SUM(oi.quantity * oi.unit_price) AS total_amount
FROM "Order" o
INNER JOIN Customer c ON c.id = o.customer_id
INNER JOIN OrderItem oi ON oi.order_id = o.id
INNER JOIN Product p ON p.id = oi.product_id
INNER JOIN Category cat ON cat.id = p.category_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.id, o.order_date, c.name, cat.name
ORDER BY o.order_date DESC;Query 3: Adds shipping info + status filter (extra join + column + WHERE clause)
SELECT
o.id AS order_id,
o.order_date,
c.name AS customer_name,
cat.name AS category_name,
s.carrier AS shipping_carrier,
SUM(oi.quantity * oi.unit_price) AS total_amount
FROM "Order" o
INNER JOIN Customer c ON c.id = o.customer_id
INNER JOIN OrderItem oi ON oi.order_id = o.id
INNER JOIN Product p ON p.id = oi.product_id
INNER JOIN Category cat ON cat.id = p.category_id
LEFT JOIN Shipment s ON s.order_id = o.id
WHERE o.order_date >= '2024-01-01'
AND o.status = 'shipped'
GROUP BY o.id, o.order_date, c.name, cat.name, s.carrier
ORDER BY o.order_date DESC;Three queries. Nine repeated join lines. The same pattern I've seen in every codebase I've worked on.
What Claude Produced
I told Claude to use the ExoQuery MCP server to abstract the common parts.Here was my second prompt:
Now use ExoQuery to extract out the common parts of these queries and compose together each of the three from the abstracted pieces. You can make minor alterations to the queries so long as they still do the same thing. Use the ExoQuery MCP server documentation and the validation & execution capability to check that what you produce actually works.
Here's what it came up with:
import io.exoquery.*
import kotlinx.serialization.Serializable
import kotlinx.serialization.SerialName
// === ENTITIES ===
@Serializable data class Customer(val id: Int, val name: String)
@Serializable data class Category(val id: Int, val name: String)
@Serializable data class Product(val id: Int, val name: String, @SerialName("category_id") val categoryId: Int)
@Serializable data class Order(val id: Int, @SerialName("customer_id") val customerId: Int, @SerialName("order_date") val orderDate: String, val status: String)
@Serializable data class OrderItem(val id: Int, @SerialName("order_id") val orderId: Int, @SerialName("product_id") val productId: Int, val quantity: Int, @SerialName("unit_price") val unitPrice: Double)
@Serializable data class Shipment(val id: Int, @SerialName("order_id") val orderId: Int, val carrier: String)
// === COMPOSITE TYPE FOR COMMON JOINS ===
@Serializable
data class OrderBase(val o: Order, val c: Customer, val oi: OrderItem)
// === ABSTRACTED BASE FRAGMENT (shared by all 3 queries) ===
@SqlFragment
fun ordersWithCustomerAndItems(since: String): SqlQuery<OrderBase> = sql.select {
val o = from(Table<Order>())
val c = join(Table<Customer>()) { cust -> cust.id == o.customerId }
val oi = join(Table<OrderItem>()) { item -> item.orderId == o.id }
where { o.orderDate >= since }
OrderBase(o, c, oi)
}
// === EXTENSION FRAGMENTS FOR OPTIONAL JOINS ===
@SqlFragment
fun OrderItem.product() = sql {
composeFrom.join(Table<Product>()) { prod -> prod.id == this@product.productId }
}
@SqlFragment
fun Product.category() = sql {
composeFrom.join(Table<Category>()) { cat -> cat.id == this@category.categoryId }
}
@SqlFragment
fun Order.shipment() = sql {
composeFrom.joinLeft(Table<Shipment>()) { ship -> ship.orderId == this@shipment.id }
}And the three queries become:
// === QUERY 1: Base only ===
val query1 = sql.select {
val (o, c, oi) = from(ordersWithCustomerAndItems("2024-01-01"))
groupBy(o.id, o.orderDate, c.name)
sortBy(o.orderDate to Ord.Desc)
Result1(o.id, o.orderDate, c.name, sum(oi.quantity.toDouble() * oi.unitPrice))
}
// === QUERY 2: Base + Product/Category ===
val query2 = sql.select {
val (o, c, oi) = from(ordersWithCustomerAndItems("2024-01-01"))
val p = from(oi.product()) // +1 line
val cat = from(p.category()) // +1 line
groupBy(o.id, o.orderDate, c.name, cat.name)
sortBy(o.orderDate to Ord.Desc)
Result2(o.id, o.orderDate, c.name, cat.name, sum(oi.quantity.toDouble() * oi.unitPrice))
}
// === QUERY 3: Base + Product/Category + Shipment + filter ===
val query3 = sql.select {
val (o, c, oi) = from(ordersWithCustomerAndItems("2024-01-01"))
val p = from(oi.product())
val cat = from(p.category())
val s = from(o.shipment()) // +1 line (s is nullable)
where { o.status == "shipped" } // +1 line
groupBy(o.id, o.orderDate, c.name, cat.name, s?.carrier)
sortBy(o.orderDate to Ord.Desc)
Result3(o.id, o.orderDate, c.name, cat.name, s?.carrier, sum(oi.quantity.toDouble() * oi.unitPrice))
}40 lines of SQL with repeated joins compressed to 21 lines of Kotlin plus 13 lines of reusable fragments. Each query variant differs by 1-2 lines that add the extra joins or filters. The join logic is defined once and composed, not copy-pasted.
The MCP server validated and executed all three. Here's the actual output:
Query 1:
Result1(orderId=2, orderDate=2024-04-01, customerName=Globex Inc, totalAmount=29.95)
Result1(orderId=1, orderDate=2024-03-15, customerName=Acme Corp, totalAmount=2059.88)Query 2:
Result2(orderId=2, orderDate=2024-04-01, customerName=Globex Inc, categoryName=Office, totalAmount=29.95)
Result2(orderId=1, orderDate=2024-03-15, customerName=Acme Corp, categoryName=Electronics, totalAmount=1999.98)
Result2(orderId=1, orderDate=2024-03-15, customerName=Acme Corp, categoryName=Office, totalAmount=59.90)Query 3:
Result3(orderId=1, orderDate=2024-03-15, customerName=Acme Corp, categoryName=Electronics, shippingCarrier=FedEx, totalAmount=1999.98)
Result3(orderId=1, orderDate=2024-03-15, customerName=Acme Corp, categoryName=Office, shippingCarrier=FedEx, totalAmount=59.90)It works. The outputs match exactly what the raw SQL queries produce.
The Journey to Get Here
When I first built the ExoQuery MCP server and tried this, it worked. But it took Claude and ChatGPT many iterations to get the query right. They kept trying. They kept refining the query and running the validateAndRunExoQuery endpoint until they found working variants. I honestly didn't expect this to work, but they just kept at it.
The initial attempts required about 20 validateAndRunExoQuery calls before landing on correct code. That's a lot of back-and-forth.
Through a process of refining my MCP server documentation endpoints, I managed to reduce that to about 3 calls. (Side note: my documentation is still exposed as tool calls rather than MCP resources because MCP clients are still not particularly good at handling resources. Hopefully that changes.)
Two things made the biggest difference:
-
Asking Claude directly: "What documentation would have helped you get to this conclusion sooner?" The answers were illuminating.
-
Including decision trees in the documentation. Not just "here's what you can do" but "here's how to decide which pattern to use."
I'll elaborate on both of these in an upcoming blog post about "training" MCP documentation. There's a methodology here that I think generalizes beyond ExoQuery.
Performance Without Fine-Tuning
After the documentation refinements, Claude and ChatGPT performed quite well. I did not expect them to do this well without having to fine-tune a model myself.
The context windows that current AIs have are well within the needed limits to understand ExoQuery's documentation and patterns without a vector database or any other enhancements. The entire ExoQuery documentation fits comfortably in context, and the AI can reason about patterns, compose fragments, and validate results in a tight feedback loop.
I do hope to explore vector databases and other enhancements in the future, but they're not necessary for this to work today.
Bug Discovery: The Unexpected Benefit
When the ExoQuery MCP server first started working, it uncovered six ExoQuery bugs:
The AI wasn't particularly good at figuring out why these bugs were happening. But it was lightning fast at giving me bug reproducers for each one. Its ability to create minimal reproduction cases saved a massive amount of time.
I deeply believe this is a game-changer for root-cause analysis, at least in isolated code traces. You give the AI a library, it explores the edges, and it hands you reproducers for the cases that break. The debugging part is still on you, but the exploration part is dramatically accelerated.
A Personal Note
This entire process left me thrilled but also highly personally jarred.
I've always been a healthy skeptic of AI. That skepticism has helped me not get caught up by a lot of LinkedIn-fueled AI hype. But based on the power of what I'm seeing, in what has been developed in just a couple of days, I need to rethink things.
I'm not saying AI is going to replace us. I am saying that the feedback loop between "I have an idea" and "I have reasonably working code" just got utterly, insanely, and ridiculously shorter for certain classes of problems. SQL abstraction and query composition happen to be an extreme case of one of these classes.
Bottom Line
The ExoQuery MCP Server is Language-Integrated SQL authoring for a new era.
If you can get a couple of your most nasty, repetitive queries into Claude and refine them with the ExoQuery MCP server, you can likely chop them down to maintainable components in a vastly shorter time frame than doing it manually.
This is what I built ExoQuery for. Not just to write queries in Kotlin instead of SQL strings, but to finally, actually abstract the common parts of queries that have been copy-pasted across codebases for decades.
The tools exist now. They work. Give it a try. Setup ExoQuery MCP.
https://backend.exoquery.com/mcpYou can report any bugs you find at ExoQuery/issues.
Comments