Advanced Topics

Common Table Expressions

CTEs in Tauri SQLite ORM.

Common Table Expressions (WITH)

A Common Table Expression (CTE), or WITH clause, allows you to create a temporary, named result set that you can reference within a subsequent query. CTEs are an excellent way to break down complex queries into logical, readable steps, improving maintainability.

The ORM supports CTEs through the db.$with() builder.

How It Works

You define a CTE using db.$with('cte_name').as(query).
The query you pass to .as() must be a query object: { sql: string; params: any[] }. You can generate this using the sql template literal or by using another query builder's .build() method.
You then chain your main query (.select(), .update(), etc.) onto the with builder.
The CTE is now available by name within your main query, typically for use inside a join or subquery written with the sql template literal.

Example: Selecting Users with Many Posts

Let's find all users who have written more than 10 posts. We can solve this cleanly by first creating a CTE that finds the author_id of prolific authors, and then selecting users whose IDs are in that set.

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

async function getProlificAuthors() {
  // 1. Define the query for the CTE.
  // This query finds all author IDs that appear more than 10 times in the posts table.
  const prolificAuthorsQuery = sql`
    SELECT ${posts.authorId}
    FROM ${posts}
    GROUP BY ${posts.authorId}
    HAVING COUNT(*) > 10
  `;
  // Note: We are using the `sql` helper to safely embed column/table objects.
  // This will be translated to: SELECT author_id FROM posts ...

  // 2. Build and execute the full query.
  const results = await db
    // Define the CTE named 'prolific_authors'
    .$with('prolific_authors').as(prolificAuthorsQuery)
    
    // Chain the main SELECT query
    .select(users)
    
    // Use the CTE in the WHERE clause as a subquery
    .where(sql`${users.id} IN (SELECT author_id FROM prolific_authors)`)
    
    .all();

  console.log('Users with more than 10 posts:', results);
  return results;
}

In this example, the final SQL executed by the database would look something like this:

WITH prolific_authors AS (
  SELECT author_id
  FROM posts
  GROUP BY author_id
  HAVING COUNT(*) > 10
)
SELECT
  users.id,
  users.full_name,
  -- ... other user columns
FROM users
WHERE users.id IN (SELECT author_id FROM prolific_authors)

Edit this pageorReport an issue

Transactions

Transactions in Tauri SQLite ORM.

Raw SQL

Raw SQL in Tauri SQLite ORM.