Advanced Topics
Query Debugging
Debug your queries using .toSQL()
The .toSQL() method allows you to inspect the SQL and parameters that will be executed without actually running the query. This is essential for debugging, logging, and understanding what SQL your ORM is generating.
Basic Usage
Every query builder has a .toSQL() method that returns the SQL string and parameter array:
import { db } from '$lib/db';
import { users } from '$lib/db/schema';
import { eq, and, gt } from '@type32/tauri-sqlite-orm';
// Build a query
const query = db.select(users)
.where(eq(users._.columns.email, 'john@example.com'));
// Inspect without executing
const { sql, params } = query.toSQL();
console.log('SQL:', sql);
// SELECT users.id AS "users.id", users.name AS "users.name", users.email AS "users.email"
// FROM users users WHERE users.email = ?
console.log('Params:', params);
// ['john@example.com']
// Execute when ready
const results = await query.all();
Works with All Query Types
SELECT Queries
const selectQuery = db.select(users)
.where(and(
eq(users._.columns.isActive, true),
gt(users._.columns.loginCount, 10)
))
.orderBy(users._.columns.createdAt, 'DESC')
.limit(10);
const { sql, params } = selectQuery.toSQL();
console.log(sql, params);
INSERT Queries
const insertQuery = db.insert(users)
.values({ name: 'Alice', email: 'alice@example.com' });
const { sql, params } = insertQuery.toSQL();
console.log(sql, params);
// INSERT INTO users (name, email) VALUES (?, ?)
// ['Alice', 'alice@example.com']
UPDATE Queries
const updateQuery = db.update(users)
.set({ name: 'Bob' })
.increment('loginCount')
.where(eq(users._.columns.id, 1));
const { sql, params } = updateQuery.toSQL();
console.log(sql, params);
// UPDATE users SET name = ?, login_count = login_count + ? WHERE id = ?
// ['Bob', 1, 1]
DELETE Queries
const deleteQuery = db.delete(users)
.where(eq(users._.columns.id, 1));
const { sql, params } = deleteQuery.toSQL();
console.log(sql, params);
// DELETE FROM users WHERE id = ?
// [1]
Use Cases
1. Query Logging
Log all queries for debugging or audit purposes:
async function executeWithLogging<T>(query: any): Promise<T> {
const { sql, params } = query.toSQL();
const timestamp = new Date().toISOString();
console.log(`[${timestamp}] SQL:`, sql);
console.log(`[${timestamp}] Params:`, params);
const startTime = Date.now();
const result = await query.execute();
const duration = Date.now() - startTime;
console.log(`[${timestamp}] Completed in ${duration}ms`);
return result;
}
// Usage
const users = await executeWithLogging(
db.select(users).where(eq(users._.columns.id, 1))
);
2. Testing
Verify that your ORM generates the expected SQL:
import { describe, test, expect } from 'vitest';
describe('User queries', () => {
test('should generate correct SELECT query', () => {
const query = db.select(users)
.where(eq(users._.columns.email, 'test@example.com'));
const { sql, params } = query.toSQL();
expect(sql).toContain('SELECT');
expect(sql).toContain('WHERE');
expect(sql).toContain('email = ?');
expect(params).toEqual(['test@example.com']);
});
});
3. Performance Analysis
Copy the SQL to analyze with EXPLAIN:
const query = db.select(posts)
.where(gt(posts._.columns.views, 1000))
.orderBy(posts._.columns.createdAt, 'DESC');
const { sql, params } = query.toSQL();
// Copy this SQL to analyze in a SQL tool
console.log('Run this to analyze performance:');
console.log(`EXPLAIN QUERY PLAN ${sql}`);
console.log('With params:', params);
4. Learning
Understand what SQL is being generated:
// Complex query with relations
const query = db.select(users)
.include({
posts: {
with: {
comments: true
}
}
})
.where(eq(users._.columns.isActive, true));
const { sql } = query.toSQL();
console.log('Generated SQL:', sql);
// See how the ORM handles JOINs and nested relations
5. Debugging Production Issues
When a query fails in production, log the SQL to understand what went wrong:
try {
await db.update(users)
.set({ status: newStatus })
.where(eq(users._.columns.id, userId))
.execute();
} catch (error) {
const { sql, params } = db.update(users)
.set({ status: newStatus })
.where(eq(users._.columns.id, userId))
.toSQL();
console.error('Query failed:', { sql, params, error });
// Send to error tracking service
}
Important Notes
toSQL() Doesn't Execute
Calling .toSQL() only builds the SQL - it doesn't execute anything:
const query = db.select(users);
// This doesn't query the database
const { sql } = query.toSQL();
// This actually executes
const results = await query.all();
toSQL() Doesn't Validate WHERE Clauses
For safety, UPDATE and DELETE queries require WHERE clauses when executing, but .toSQL() skips this validation (it's for debugging):
// This works - shows the SQL
const { sql } = db.update(users)
.set({ name: 'Bob' })
.toSQL();
// But executing without WHERE will throw an error
// await db.update(users).set({ name: 'Bob' }).execute(); // ❌ Error!
Can Be Called Anytime
You can call .toSQL() at any point during query building:
const query = db.select(users);
console.log('1:', query.toSQL());
query.where(eq(users._.columns.id, 1));
console.log('2:', query.toSQL());
query.orderBy(users._.columns.createdAt, 'DESC');
console.log('3:', query.toSQL());
Best Practices
- Use in development - Log queries to understand ORM behavior
- Use in tests - Verify SQL generation
- Remove in production - Or use conditional logging based on environment
- Copy for EXPLAIN - Paste into SQL tools for performance analysis
- Log before errors - Help debug production issues
Example: Query Logger Utility
// src/lib/db/logger.ts
export function createQueryLogger(enabled: boolean = true) {
return {
async execute<T>(query: any, label?: string): Promise<T> {
if (!enabled) {
return await query.execute();
}
const { sql, params } = query.toSQL();
const start = Date.now();
console.group(label || 'Query');
console.log('SQL:', sql);
console.log('Params:', params);
try {
const result = await query.execute();
const duration = Date.now() - start;
console.log(`✓ Success (${duration}ms)`);
console.groupEnd();
return result;
} catch (error) {
const duration = Date.now() - start;
console.error(`✗ Failed (${duration}ms)`, error);
console.groupEnd();
throw error;
}
}
};
}
// Usage
const logger = createQueryLogger(import.meta.env.DEV);
const users = await logger.execute(
db.select(users).where(eq(users._.columns.id, 1)),
'Fetch user by ID'
);
The .toSQL() method is your best friend for understanding and debugging your queries!