SQL Templates

import { sql, query, identifier, raw } from "@electric-sql/pglite/template";

/**
 * Template tag for nested SQL queries with parameter interpolation
 * @param strings - Template string parts
 * @param values - Template parameter values
 * @returns TemplateContainer for composition
 */
function sql(strings: TemplateStringsArray, ...values: any[]): TemplateContainer;

interface TemplateContainer {
  _templateType: 'container';
  strings: TemplateStringsArray;
  values: any[];
}

/**
 * Template tag for parameterized queries
 * @param strings - Template string parts  
 * @param values - Template parameter values
 * @returns TemplatedQuery ready for execution
 */
function query(strings: TemplateStringsArray, ...values: any[]): TemplatedQuery;

interface TemplatedQuery {
  /** SQL query string with parameter placeholders */
  query: string;
  /** Array of parameter values */
  params: any[];
}

/**
 * Template tag for safe identifier interpolation
 * Automatically escapes identifiers to prevent injection
 * @param strings - Template string parts
 * @param values - Identifier values
 * @returns TemplatePart for composition
 */
function identifier(strings: TemplateStringsArray, ...values: any[]): TemplatePart;

/**
 * Template tag for raw string interpolation
 * Values are inserted directly without parameterization
 * @param strings - Template string parts
 * @param values - Raw string values
 * @returns TemplatePart for composition
 */
function raw(strings: TemplateStringsArray, ...values: any[]): TemplatePart;

interface TemplatePart {
  _templateType: 'part';
  str: string;
}

interface TemplatedQuery {
  query: string;
  params: any[];
}

import { PGlite } from "@electric-sql/pglite";
import { sql, query, identifier, raw } from "@electric-sql/pglite/template";

const db = new PGlite();

// Basic parameterized query
const userQuery = query`SELECT * FROM users WHERE age > ${18}`;
const results = await db.query(userQuery.query, userQuery.params);

// Nested SQL composition
const condition = sql`age > ${21}`;
const orderBy = sql`ORDER BY ${identifier`name`}`;
const fullQuery = sql`
  SELECT * FROM users 
  WHERE ${condition}
  ${orderBy}
`;

// Execute composed query
const composedResults = await db.query(...fullQuery.toQuery());

// Safe identifier interpolation
const tableName = "user_profiles";
const columnName = "updated_at";
const tableQuery = sql`
  SELECT ${identifier`${columnName}`} 
  FROM ${identifier`${tableName}`}
  WHERE active = ${true}
`;

// Raw SQL for complex expressions
const complexFilter = raw`(age BETWEEN 18 AND 65) OR vip_status = true`;
const complexQuery = sql`
  SELECT * FROM users 
  WHERE ${complexFilter}
  AND country = ${'US'}
`;

// Building dynamic queries
function buildUserQuery(filters: { age?: number; country?: string; active?: boolean }) {
  let query = sql`SELECT * FROM users WHERE 1=1`;
  
  if (filters.age !== undefined) {
    query = query.append(sql` AND age > ${filters.age}`);
  }
  
  if (filters.country) {
    query = query.append(sql` AND country = ${filters.country}`);
  }
  
  if (filters.active !== undefined) {
    query = query.append(sql` AND active = ${filters.active}`);
  }
  
  return query.toQuery();
}

// Use dynamic query
const dynamicQuery = buildUserQuery({ age: 25, country: 'US' });
const dynamicResults = await db.query(dynamicQuery.query, dynamicQuery.params);

// Template composition with subqueries
const subquery = sql`SELECT id FROM organizations WHERE type = ${'nonprofit'}`;
const mainQuery = sql`
  SELECT u.name, u.email 
  FROM users u 
  WHERE u.org_id IN (${subquery})
`;

const subqueryResults = await db.query(...mainQuery.toQuery());