Raw SQL

While the query builder aims to cover the vast majority of use cases, there will always be times when you need the power and flexibility of raw SQL—for accessing database-specific functions, performing complex calculations, or writing highly optimized queries.

The ORM provides a safe and convenient way to do this using the sql template literal.

The sql Template Literal

The sql helper is not simple string interpolation. It's a tagged template that automatically parameterizes your values, providing a robust defense against SQL injection attacks.

import { sql } from '@type32/tauri-sqlite-orm';

// This is SAFE. The value of `userId` will be passed as a bound parameter.
const condition = sql`id = ${userId}`; 

// This is DANGEROUS and should NEVER be done.
const unsafeCondition = { sql: `id = ${userId}`, params: [] };

You can embed dynamic values, column objects, and even table objects directly into the template.

Usage in .where()

The most common use case for sql is in a .where() clause when you need to use a function that isn't provided by the built-in operators. SQLite's JSON functions are a perfect example.

Let's say our users table has a TEXT column named metadata with { "mode": "json" }, where we store a JSON object. We want to find all users who have is_verified: true in their metadata.

import { db } from '$lib/db';
import { users } from '$lib/db/schema';
import { sql } from '@type32/tauri-sqlite-orm';

// Assume users.metadata is defined as: text('metadata', { mode: 'json' })
const verifiedUsers = await db.select(users)
  .where(sql`json_extract(${users._.columns.metadata}, '$.is_verified') = true`)
  .all();

The ORM correctly substitutes users.metadata with the actual column name (metadata), resulting in a safe and valid query.

Usage in .set() for Atomic Updates

Another powerful use is performing atomic updates directly in the database, avoiding race conditions that can occur with a "read-then-write" approach.

// Atomically increment a user's login count
// Note: You can now use the .increment() helper instead!
await db.update(users)
  .increment('loginCount')
  .where(eq(users._.columns.id, 1))
  .execute();

// Or use raw SQL for more complex updates
await db.update(users)
  .set({ 
    loginCount: sql`${users._.columns.loginCount} + 1` 
  })
  .where(eq(users._.columns.id, 1))
  .execute();

This sends a single UPDATE users SET login_count = login_count + 1 WHERE id = ? query, ensuring the operation is safe even with concurrent requests.

While sql is a powerful escape hatch, you should always prefer the built-in query builders and operators when possible. They provide stronger type safety and are often more readable for common operations.