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
