getExoQueryDocs
Access comprehensive ExoQuery documentation organized by topic and category.
ExoQuery is a Language Integrated Query library for Kotlin Multiplatform that translates Kotlin DSL expressions into SQL at compile time. This resource provides access to the complete documentation covering all aspects of the library.
AVAILABLE DOCUMENTATION CATEGORIES:
1. **Getting Started**
- Introduction: What ExoQuery is and why it exists
- Installation: Project setup and dependencies
- Quick Start: First query in minutes
2. **Core Concepts**
- SQL Blocks: The sql { } construct and query building
- Parameters: Safe runtime data handling
- Composing Queries: Functional query composition
3. **Query Operations**
- Basic Operations: Map, filter, and transformations
- Joins: Inner, left, and implicit joins
- Grouping: GROUP BY and HAVING clauses
- Sorting: ORDER BY operations
- Subqueries: Correlated and nested queries
- Window Functions: Advanced analytics
4. **Actions**
- Insert: INSERT with returning and conflict handling
- Update: UPDATE operations with setParams
- Delete: DELETE with returning
- Batch Operations: Bulk inserts and updates
5. **Advanced Features**
- SQL Fragment Functions: Reusable SQL components with @SqlFragment
- Dynamic Queries: Runtime query generation with @SqlDynamic
- Free Blocks: Custom SQL and user-defined functions
- Transactions: Transaction support patterns
- Polymorphic Queries: Interfaces, sealed classes, higher-order functions
- Local Variables: Variables within SQL blocks
6. **Data Handling**
- Serialization: kotlinx.serialization integration
- Custom Type Encoding: Custom encoders and decoders
- JSON Columns: JSON and JSONB support (PostgreSQL)
- Column Naming: @SerialName and @ExoEntity annotations
- Nested Datatypes: Complex data structures
- Kotlinx Integration: JSON and other serialization formats
7. **Schema-First Development**
- Entity Generation: Compile-time code generation from database schema
- AI-Enhanced Entities: Using LLMs to generate cleaner entity code
8. **Reference**
- SQL Functions: Available string, math, and date functions
- API Reference: Core types and function signatures
HOW TO USE THIS RESOURCE:
The resource URI follows the pattern:
exoquery://docs/{file-path}
Where {file-path} is the relative path from the docs root, e.g.:
- exoquery://docs/01-getting-started/01-introduction.md
- exoquery://docs/03-query-operations/02-joins.md
- exoquery://docs/05-advanced-features/01-sql-fragments.md
To discover available documents, use the MCP resources/list endpoint which will return all available documentation files with their titles, descriptions, and categories.
Each document includes:
- Title and description
- Category classification
- Complete markdown content with code examples
- Cross-references to related topics
WHEN TO USE:
- User asks about ExoQuery syntax, features, or capabilities
- User needs examples of specific query patterns
- User encounters errors and needs to verify correct usage
- User wants to understand advanced features or best practices
getExoQueryDocsMulti
Access multiple ExoQuery documentation sections simultaneously.
This tool is similar to the single-document retrieval tool but allows fetching multiple documentation files in a single request. This is particularly useful when you need to gather information from several related topics at once.
ExoQuery is a Language Integrated Query library for Kotlin Multiplatform that translates Kotlin DSL expressions into SQL at compile time. This resource provides access to the complete documentation covering all aspects of the library.
HOW TO USE THIS RESOURCE:
Provide a list of file paths, where each path is the relative path from the docs root, e.g.:
- 01-getting-started/01-introduction.md
- 03-query-operations/02-joins.md
- 05-advanced-features/01-sql-fragments.md
To discover available documents, use the MCP resources/list endpoint which will return all available documentation files with their titles, descriptions, and categories.
Each returned document includes:
- Title and description
- Category classification
- Complete markdown content with code examples
- Cross-references to related topics
WHEN TO USE:
- User asks about multiple ExoQuery topics that require information from different sections
- User needs to compare or understand relationships between different features
- User wants to get comprehensive information across multiple categories
- More efficient than making multiple single-document requests
listExoQueryDocs
Lists all available ExoQuery documentation resources with their metadata
runRawSql
Execute raw, client-provided SQL queries against an ephemeral database initialized with the provided schema.
Returns query results in a simple JSON format with column headers and row data as a 2D array.
The database type (SQLite or Postgres) is specified via the databaseType parameter:
- SQLITE: In-memory, lightweight, uses standard SQLite syntax
- POSTGRES: Temporary isolated schema with dedicated user, uses PostgreSQL syntax and features
WHEN TO USE: When you need to run your own hand-written SQL queries to test database behavior or
compare the output with ExoQuery results from validateAndRunExoquery. This lets you verify that
ExoQuery-generated SQL produces the same results as your expected SQL.
INPUT REQUIREMENTS:
- query: A valid SQL query (SELECT, INSERT, UPDATE, DELETE, etc.)
- schema: SQL schema with CREATE TABLE and INSERT statements to initialize the test database
- databaseType: Either "SQLITE" or "POSTGRES" (defaults to SQLITE if not specified)
OUTPUT FORMAT:
On success, returns JSON with the SQL query and a 2D array of results:
{"sql":"SELECT * FROM users ORDER BY id","output":[["id","name","age"],["1","Alice","30"],["2","Bob","25"],["3","Charlie","35"]]}
Output format details:
- First array element contains column headers
- Subsequent array elements contain row data
- All values are returned as strings
On error, returns JSON with error message and the attempted query (if available):
{"error":"Query execution failed: no such table: USERS","sql":"SELECT * FROM USERS"}
Or if schema initialization fails:
{"error":"Database initialization failed due to: near \"CREAT\": syntax error\\nWhen executing the following statement:\\n--------\\nCREAT TABLE users ...\\n--------","sql":"CREAT TABLE users ..."}
EXAMPLE INPUT:
Query:
SELECT * FROM users ORDER BY id
Schema:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER
);
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30);
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 25);
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 35);
EXAMPLE SUCCESS OUTPUT:
{"sql":"SELECT * FROM users ORDER BY id","output":[["id","name","age"],["1","Alice","30"],["2","Bob","25"],["3","Charlie","35"]]}
EXAMPLE ERROR OUTPUT (bad table name):
{"error":"Query execution failed: no such table: invalid_table","sql":"SELECT * FROM invalid_table"}
EXAMPLE ERROR OUTPUT (bad schema):
{"error":"Database initialization failed due to: near \"CREAT\": syntax error\\nWhen executing the following statement:\\n--------\\nCREAT TABLE users (id INTEGER)\\n--------\\nCheck that the initialization SQL is valid and compatible with SQLite.","sql":"CREAT TABLE users (id INTEGER)"}
COMMON QUERY EXAMPLES:
Select all rows:
SELECT * FROM users
Select specific columns with filtering:
SELECT name, age FROM users WHERE age > 25
Aggregate functions:
SELECT COUNT(*) as total FROM users
Join queries:
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id
Insert data:
INSERT INTO users (name, age) VALUES ('David', 40)
Update data:
UPDATE users SET age = 31 WHERE name = 'Alice'
Delete data:
DELETE FROM users WHERE age < 25
Count with grouping:
SELECT age, COUNT(*) as count FROM users GROUP BY age
SCHEMA RULES:
- Use standard SQLite syntax
- Table names are case-sensitive (use lowercase for simplicity or quote names)
- Include INSERT statements to populate test data for meaningful results
- Supported data types: INTEGER, TEXT, REAL, BLOB, NULL
- Use INTEGER PRIMARY KEY for auto-increment columns
- Schema SQL is split on semicolons (;), so each statement after a ';' is executed separately
- Avoid semicolons in comments as they will cause statement parsing issues
COMPARISON WITH EXOQUERY:
This tool is designed to work alongside validateAndRunExoquery for comparison purposes:
1. Use validateAndRunExoquery to run ExoQuery Kotlin code and see the generated SQL + results
2. Use runRawSql with your own hand-written SQL to verify you get the same output
3. Compare the outputs to ensure ExoQuery generates the SQL you expect
4. Test edge cases with plain SQL before writing equivalent ExoQuery code
validateAndRunExoquery
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
validateExoquery
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