SQL with TypeScript

The SQL Interpolator

Datoria combines SQL and TypeScript through a special sql interpolator—a JavaScript template literal that preserves the full structure of your queries. This approach goes beyond simple string concatenation to maintain source positions, interpolated values, and other metadata. This approach combines the readability of SQL with the organizational benefits of a modern programming language.

Here's a basic example:

import { sql } from "@datoria/sdk";

export const query = sql`
    SELECT 1 as simple_value
`;

Unlike a regular string template, this expression returns an SQL Fragment object that maintains the full structure of the query.

Value Interpolation

You can seamlessly incorporate values into your SQL queries:

import { userTable } from "../tables-and-partitioning.md";

const region = "East";
const limit = 10;

export const regionQuery = sql`
    SELECT * FROM ${userTable}
    WHERE region = ${region}
    LIMIT ${limit}
`;

Datoria automatically handles proper quoting and parameter binding:

• String values like "East" are properly quoted
• Numeric values like 10 are inserted as literals
• Other values are appropriately converted based on their types

This approach inherently protects against SQL injection attacks by properly escaping and quoting values, eliminating a common security vulnerability found in string concatenation approaches.

Working with Identifiers

For column names or other SQL identifiers, use the StringIdent function:

import { StringIdent } from "@datoria/sdk";

const columnName = StringIdent("id");

export const dynamicQuery = sql`
    SELECT ${columnName} FROM ${userTable}
`;
// Generates: SELECT id FROM my_project.my_dataset.users

Fragment Composition

The real power of Datoria's SQL approach is fragment composition. You can build complex queries from reusable components:

// Let's assume userTable has is_active and plan_type fields
// Create reusable components
const activeFilter = sql`is_active = TRUE`;
const premiumFilter = sql`plan_type = 'premium'`;

// Combine them into a larger query
export const activePremiumUsersQuery = sql`
    SELECT id, name, signup_date
    FROM ${userTable}
    WHERE ${activeFilter} AND ${premiumFilter}
`;

You can also create functions that accept parameters and return SQL fragments:

// Function that returns a SQL fragment based on input
function minAgeFilter(minAge: number) {
  return sql`age >= ${minAge}`;
}

// Using the function with specific values
export const adultUsersQuery = sql`
    SELECT * FROM ${userTable} 
    WHERE ${minAgeFilter(18)}
`;

// Function that controls query flow based on parameter
function userStatusFilter(includeInactive: boolean) {
  if (includeInactive) {
    return sql``; // Empty fragment, no filtering
  } else {
    return sql`WHERE is_active = TRUE`;
  }
}

// Query changes based on parameter value
export const usersQuery = sql`
    SELECT id, name, signup_date
    FROM ${userTable}
    ${userStatusFilter(false)} -- Will add WHERE clause
`;

This allows you to build a library of SQL fragments that can be reused across your application.

SQL Combinators

Datoria provides utilities in the fragments module to handle common SQL patterns:

import { fragments } from "@datoria/sdk";

// Conditional WHERE clauses
const conditions = [];
const isActive = true;
const minAge = 21;

if (isActive) {
  conditions.push(sql`is_active = TRUE`);
}
if (minAge) {
  conditions.push(sql`age >= ${minAge}`);
}

export const userDynamicQuery = sql`
    SELECT id, name
    FROM ${userTable}
    ${fragments.whereAndOpt(conditions)}
`;
// With conditions: SELECT id, name FROM my_project.my_dataset.users WHERE is_active = TRUE AND age >= 21
// Without conditions: SELECT id, name FROM my_project.my_dataset.users

Other useful combinators include:

Joining Fragments

const fields = [sql`id`, sql`name`, sql`signup_date`];

export const selectQuery = sql`
    SELECT ${fragments.joinWith(fields, sql`, `)}
    FROM ${userTable}
`;
// SELECT id, name, signup_date FROM my_project.my_dataset.users

Optional Clauses

import { userSegmentsTable } from "../tables-and-partitioning.md";

const hasMinValue = true;
const minValue = 100;

const havingClause = hasMinValue
  ? sql`HAVING SUM(user_count) > ${minValue}`
  : sql``;

export const aggregateQuery = sql`
    SELECT segment_name, SUM(user_count) as total_users
    FROM ${userSegmentsTable.allPartitions()}
    GROUP BY segment_name
    ${havingClause}
`;

Table References

When referencing tables in queries, you need to specify how to access the table's partitions:

import { dailyEventsTable } from "../tables-and-partitioning.md";

// For unpartitioned tables or to query all partitions
export const allEventsQuery = sql`
    SELECT * FROM ${dailyEventsTable.allPartitions()}
`;

// For a specific partition
export const todayEventsQuery = sql`
    SELECT * FROM ${dailyEventsTable.atExecutionDate()}
`;

This explicit partition specification ensures efficient querying and proper dependency tracking.

Common SQL Patterns

Subqueries

const subquery = sql`
    SELECT user_id, COUNT(*) as event_count
    FROM ${dailyEventsTable.allPartitions()}
    WHERE event_type = 'login'
    GROUP BY user_id
`;

export const mainQuery = sql`
    SELECT u.id, u.name, s.event_count
    FROM ${userTable} u
    JOIN (${subquery}) s ON u.id = s.user_id
`;

Window Functions

export const rankQuery = sql`
    SELECT
        segment_id,
        user_count,
        RANK() OVER (PARTITION BY segment_name ORDER BY user_count DESC) as rank
    FROM ${userSegmentsTable.allPartitions()}
`;

Dynamic Pivots

function createPivotQuery(events, dimensions) {
  // Create a SQL fragment for each event type in the pivot
  const pivotClauses = events.map(
    (event) =>
      sql`COUNT(CASE WHEN event_type = ${event} THEN 1 ELSE NULL END) as ${StringIdent(event)}`,
  );

  return sql`
    SELECT 
        ${fragments.joinWith(
          dimensions.map((d) => StringIdent(d)),
          sql`, `,
        )},
        ${fragments.joinWith(pivotClauses, sql`,\n        `)}
    FROM ${dailyEventsTable.allPartitions()}
    GROUP BY ${fragments.joinWith(
      dimensions.map((_, i) => sql`${i + 1}`),
      sql`, `,
    )}
  `;
}

export const pivotQuery = createPivotQuery(
  ["login", "signup", "purchase"],
  ["user_id", "event_date"],
);

// Generates a query like:
// SELECT
//     user_id, event_date,
//     COUNT(CASE WHEN event_type = "login" THEN 1 ELSE NULL END) as login,
//     COUNT(CASE WHEN event_type = "signup" THEN 1 ELSE NULL END) as signup,
//     COUNT(CASE WHEN event_type = "purchase" THEN 1 ELSE NULL END) as purchase
// FROM my_project.my_dataset.daily_events
// GROUP BY 1, 2