Compile ExoQuery Kotlin code and EXECUTE it against an Sqlite database with provided schema.
ExoQuery is a compile-time SQL query builder that translates Kotlin DSL expressions into SQL.
WHEN TO USE: When you need to verify ExoQuery produces correct results against actual data.
INPUT REQUIREMENTS:
- Complete Kotlin code (same requirements as validateExoquery)
- SQL schema with CREATE TABLE and INSERT statements for test data
- Data classes MUST exactly match the schema table structure
- Column names in data classes must match schema (use @SerialName for snake_case columns)
- Must include or or more .runSample() calls in main() to trigger SQL generation and execution
(note that .runSample() is NOT or real production use, use .runOn(database) instead)
OUTPUT FORMAT:
Returns one or more JSON objects, each on its own line. Each object can be:
1. SQL with output (query executed successfully):
{"sql": "SELECT u.name FROM \"User\" u", "output": "[(name=Alice), (name=Bob)]"}
2. Output only (e.g., print statements, intermediate results):
{"output": "Before: [(id=1, title=Ion Blend Beans)]"}
3. Error output (runtime errors, exceptions):
{"outputErr": "java.sql.SQLException: Table \"USERS\" not found"}
Multiple results appear when code has multiple queries or print statements:
{"sql": "SELECT * FROM \"InventoryItem\"", "output": "[(id=1, title=Ion Blend Beans, unit_price=32.00, in_stock=25)]"}
{"output": "Before:"}
{"sql": "INSERT INTO \"InventoryItem\" (title, unit_price, in_stock) VALUES (?, ?, ?)", "output": "Rows affected: 1"}
{"output": "After:"}
{"sql": "SELECT * FROM \"InventoryItem\"", "output": "[(id=1, title=Ion Blend Beans, unit_price=32.00, in_stock=25), (id=2, title=Luna Fuel Flask, unit_price=89.50, in_stock=6)]"}
Compilation errors return the same format as validateExoquery:
{
"errors": {
"File.kt": [
{
"interval": {"start": {"line": 12, "ch": 10}, "end": {"line": 12, "ch": 15}},
"message": "Type mismatch: inferred type is String but Int was expected",
"severity": "ERROR",
"className": "ERROR"
}
]
}
}
Runtime Errors can have the following format:
{
"errors" : {
"File.kt" : [ ]
},
"exception" : {
"message" : "[SQLITE_ERROR] SQL error or missing database (no such table: User)",
"fullName" : "org.sqlite.SQLiteException",
"stackTrace" : [ {
"className" : "org.sqlite.core.DB",
"methodName" : "newSQLException",
"fileName" : "DB.java",
"lineNumber" : 1179
}, ...]
},
"text" : "<outStream><outputObject>\n{\"sql\": \"SELECT x.id, x.name, x.age FROM User x\"}\n</outputObject>\n</outStream>"
}
If there was a SQL query generated before the error, it will appear in the "text" field output stream.
EXAMPLE INPUT CODE:
```kotlin
import io.exoquery.*
import kotlinx.serialization.Serializable
import kotlinx.serialization.SerialName
@Serializable
data class User(val id: Int, val name: String, val age: Int)
@Serializable
data class Order(val id: Int, @SerialName("user_id") val userId: Int, val total: Int)
val userOrders = sql.select {
val u = from(Table<User>())
val o = join(Table<Order>()) { o -> o.userId == u.id }
Triple(u.name, o.total, u.age)
}
fun main() = userOrders.buildPrettyFor.Sqlite().runSample()
```
EXAMPLE INPUT SCHEMA:
```sql
CREATE TABLE "User" (id INT, name VARCHAR(100), age INT);
CREATE TABLE "Order" (id INT, user_id INT, total INT);
INSERT INTO "User" (id, name, age) VALUES
(1, 'Alice', 30),
(2, 'Bob', 25);
INSERT INTO "Order" (id, user_id, total) VALUES
(1, 1, 100),
(2, 1, 200),
(3, 2, 150);
```
EXAMPLE SUCCESS OUTPUT:
{"sql": "SELECT u.name AS first, o.total AS second, u.age AS third FROM \"User\" u INNER JOIN \"Order\" o ON o.user_id = u.id", "output": "[(first=Alice, second=100, third=30), (first=Alice, second=200, third=30), (first=Bob, second=150, third=25)]"}
EXAMPLE WITH MULTIPLE OPERATIONS (insert with before/after check):
{"output": "Before:"}
{"sql": "SELECT * FROM \"InventoryItem\"", "output": "[(id=1, title=Ion Blend Beans)]"}
{"sql": "INSERT INTO \"InventoryItem\" (title, unit_price, in_stock) VALUES (?, ?, ?)", "output": ""}
{"output": "After:"}
{"sql": "SELECT * FROM \"InventoryItem\"", "output": "[(id=1, title=Ion Blend Beans), (id=2, title=Luna Fuel Flask)]"}
EXAMPLE RUNTIME ERROR (if a user divided by zero):
{"outputErr": "Exception in thread "main" java.lang.ArithmeticException: / by zero"}
KEY PATTERNS:
(See validateExoquery for complete pattern reference)
Summary of most common patterns:
- Filter: sql { Table<T>().filter { x -> x.field == value } }
- Select: sql.select { val x = from(Table<T>()); where { ... }; x }
- Join: sql.select { val a = from(Table<A>()); val b = join(Table<B>()) { b -> b.aId == a.id }; Pair(a, b) }
- Left join: joinLeft(Table<T>()) { ... } returns nullable
- Insert: sql { insert<T> { setParams(obj).excluding(id) } }
- Update: sql { update<T>().set { it.field to value }.where { it.id == x } }
- Delete: sql { delete<T>().where { it.id == x } }
SCHEMA RULES:
- Table names should match data class names (case-sensitive, use quotes for exact match)
- Column names must match @SerialName values or property names
- Include realistic test data to verify query logic
- Sqlite database syntax (mostly compatible with standard SQL)
COMMON PATTERNS:
- JSON columns: Use VARCHAR for storage, @SqlJsonValue on the nested data class
- Auto-increment IDs: Use INTEGER PRIMARY KEY
- Nullable columns: Use Type? in Kotlin, allow NULL in schema