### Running Backend Services

Source: https://github.com/electric-sql/d2ts/blob/main/examples/electric/README.md

Starts the PostgreSQL and ElectricSQL services using Docker Compose. This is a prerequisite for running the data pipeline.

```bash
pnpm backend:up
```

--------------------------------

### Running D2QL Examples

Source: https://github.com/electric-sql/d2ts/blob/main/examples/d2ql/README.md

Demonstrates the command-line usage for executing D2QL examples using tsx, including a specific example for the kitchen sink.

```bash
npx tsx examples/d2ql/EXAMPLE_FILENAME.ts
```

```bash
npx tsx examples/d2ql/05_kitchen_sink.ts
```

--------------------------------

### Starting the D2TS Pipeline

Source: https://github.com/electric-sql/d2ts/blob/main/examples/electric/README.md

Initiates the D2TS data processing pipeline, which consumes data from ElectricSQL and processes it incrementally. The processed data is output to the console.

```bash
pnpm start
```

--------------------------------

### Loading Sample Data

Source: https://github.com/electric-sql/d2ts/blob/main/examples/electric/README.md

Loads sample data for users, issues, and comments into the PostgreSQL database. This populates the database with initial content for testing.

```bash
pnpm db:load-data
```

--------------------------------

### D2QL Query Structure Example

Source: https://github.com/electric-sql/d2ts/blob/main/examples/d2ql/README.md

Illustrates the basic structure of a D2QL query object in TypeScript, including select, from, where, and join clauses.

```typescript
const query = {
  select: ['@column1', { alias: '@column2' }],
  from: 'table_name',
  where: ['@column', '=', 'value'],
  join: [{
    type: 'inner',
    from: 'other_table',
    on: ['@table.column', '=', '@other_table.column']
  }]
};
```

--------------------------------

### D2QL Basic ORDER BY, LIMIT, and OFFSET Example

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Shows how to use ORDER BY, LIMIT, and OFFSET clauses in D2QL for sorting and pagination. This example selects users older than 21, orders them by age, and limits the results to the first 10, skipping the first 5.

```typescript
const query: Query = {
  select: [
    '@id',
    '@name',
    { age_in_years: '@age' }
  ],
  from: 'users',
  where: [
    '@age', '>', 21
  ],
  orderBy: '@age',  // Order by age in ascending order
  limit: 10,        // Return only the first 10 results
  offset: 5         // Skip the first 5 results
};
```

--------------------------------

### Resetting Services and Data

Source: https://github.com/electric-sql/d2ts/blob/main/examples/electric/README.md

Tears down existing services, recreates them, applies migrations, and loads fresh data. This is a comprehensive reset command for the project.

```bash
pnpm reset
```

--------------------------------

### Install @electric-sql/d2ts

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Installs the D2TS library using npm. This is the first step to using the library for building data pipelines.

```bash
npm install @electric-sql/d2ts
```

--------------------------------

### Database Migration

Source: https://github.com/electric-sql/d2ts/blob/main/examples/electric/README.md

Applies database migrations to set up the necessary tables for the issue tracking system. This command ensures the database schema is correctly configured.

```bash
pnpm db:migrate
```

--------------------------------

### ElectricSQL Integration with D2TS

Source: https://github.com/electric-sql/d2ts/blob/main/examples/electric/README.md

Demonstrates how to integrate ElectricSQL ShapeStreams with D2TS inputs using `MultiShapeStream` and `electricStreamToD2Input`. This is the core of the real-time data processing.

```typescript
// Example of creating a D2TS graph and setting up inputs
// const graph = new D2TSGraph();
// const electricStream = ... // Get stream from ElectricSQL
// const d2Input = electricStreamToD2Input(electricStream);
// graph.addInput(d2Input);
```

--------------------------------

### Supported Aggregate Functions

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Lists and provides examples for supported aggregate functions in D2QL, including SUM, COUNT, AVG, MIN, MAX, MEDIAN, and MODE.

```typescript
// SUM: Calculates the sum of values in a group
{ total_amount: { SUM: '@amount' } }

// COUNT: Counts the number of rows in a group
{ order_count: { COUNT: '@order_id' } }

// AVG: Calculates the average of values in a group
{ avg_amount: { AVG: '@amount' } }

// MIN: Finds the minimum value in a group
{ min_amount: { MIN: '@amount' } }

// MAX: Finds the maximum value in a group
{ max_amount: { MAX: '@amount' } }

// MEDIAN: Calculates the median value in a group
{ median_amount: { MEDIAN: '@amount' } }

// MODE: Finds the most common value in a group
{ most_common_status: { MODE: '@status' } }
```

--------------------------------

### D2QL Wildcard Select Example (TypeScript)

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Illustrates using a wildcard '*' to select all columns from a table, combined with specific selections from a joined table and a WHERE clause.

```typescript
const wildcardQuery: Query = {
  select: ['@e.*', '@d.name', { budget: '@d.budget' }],
  from: 'employees',
  as: 'e',
  join: [{ type: 'inner', from: 'departments', as: 'd', on: ['@e.department_id', '=', '@d.id'] }],
  where: ['@e.active', '=', true]
};
```

--------------------------------

### D2Mini Basic Usage Example

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2mini/README.md

Demonstrates the core concepts of D2Mini, including creating a D2 graph, defining an input stream, building a data processing pipeline with map, filter, and debug operators, finalizing the graph, sending data as a MultiSet, and running the graph.

```typescript
import { D2, map, filter, debug, MultiSet, v } from '@electric-sql/d2ts'

// Create a new D2 graph
const graph = new D2()

// Create an input stream
// We can specify the type of the input stream, here we are using number.
const input = graph.newInput<number>()

// Build a simple pipeline that:
// 1. Takes numbers as input
// 2. Adds 5 to each number
// 3. Filters to keep only even numbers
// Pipelines can have multiple inputs and outputs.
const output = input.pipe(
  map((x) => x + 5),
  filter((x) => x % 2 === 0),
  debug('output'),
)

// Finalize the pipeline, after this point we can no longer add operators or
// inputs
graph.finalize()

// Send some data
// Data is sent as a MultiSet, which is a map of values to their multiplicity
// Here we are sending 3 numbers (1-3), each with a multiplicity of 1
// The key thing to understand is that the MultiSet represents a *change* to
// the data, not the data itself. "Inserts" and "Deletes" are represented as
// an element with a multiplicity of 1 or -1 respectively.
input.sendData(
  new MultiSet([
    [1, 1],
    [2, 1],
    [3, 1],
  ]),
)

// Process the data
graph.run()

// Output will show:
// 6 (from 1 + 5)
// 8 (from 3 + 5)
```

--------------------------------

### LIMIT and OFFSET Requirements with ORDER BY

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Illustrates the requirement of an ORDER BY clause when using LIMIT and OFFSET for deterministic results in D2QL queries. Includes valid and invalid examples.

```typescript
// This is valid - has both ORDER BY and LIMIT/OFFSET
const query1: Query = {
  select: ['@id', '@name', '@age'],
  from: 'users',
  orderBy: '@id',
  limit: 10,
  offset: 5
};

// This would throw an error - LIMIT without ORDER BY
const query2: Query = {
  select: ['@id', '@name', '@age'],
  from: 'users',
  limit: 10  // Error: LIMIT requires an ORDER BY clause
};
```

--------------------------------

### Basic GROUP BY Example

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates a basic GROUP BY clause to group data by a single column and perform aggregate calculations like SUM and COUNT.

```typescript
const query: Query = {
  select: [
    '@customer_id',
    { total_amount: { SUM: '@amount' } },
    { order_count: { COUNT: '@order_id' } }
  ],
  from: 'orders',
  groupBy: ['@customer_id']
};
```

--------------------------------

### Install D2TS Package

Source: https://github.com/electric-sql/d2ts/blob/main/README.md

Installs the D2TS package using npm. This command is used to add the differential dataflow library for TypeScript to your project dependencies.

```bash
npm install @electric-sql/d2ts
```

--------------------------------

### D2QL GROUP BY and Aggregations Example (TypeScript)

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates how to use GROUP BY with aggregation functions (AVG, SUM, COUNT, MAX, MIN) to analyze data, including a HAVING clause to filter grouped results.

```typescript
const groupByQuery: Query = {
  select: [
    '@d.name',
    {
      avg_salary: { AVG: '@e.salary' },
      total_salary: { SUM: '@e.salary' },
      employee_count: { COUNT: '@e.id' },
      max_salary: { MAX: '@e.salary' },
      min_salary: { MIN: '@e.salary' }
    }
  ],
  from: 'employees',
  as: 'e',
  join: [
    {
      type: 'inner',
      from: 'departments',
      as: 'd',
      on: ['@e.department_id', '=', '@d.id']
    }
  ],
  where: ['@e.active', '=', true],
  groupBy: ['@d.name'],
  having: [{ col: 'employee_count' }, '>=', 2]
};
```

--------------------------------

### Keyed MultiSet Creation

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Illustrates creating a keyed MultiSet where the value is a tuple of `[key, value]`. This example keys `Comment` objects by `userId`.

```typescript
const multiSet = new MultiSet<[string, Comment]>([
  [['321', { id: '1', text: 'Hello, world!', userId: '321' }], 1],
  [['123', { id: '2', text: 'Hello, world!', userId: '123' }], 1],
])
```

--------------------------------

### Basic D2TS Pipeline Example

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Demonstrates creating a D2 graph, defining an input stream, building a pipeline with map and filter operators, finalizing the graph, sending data with versioning, and running the graph. It shows how to transform and filter numerical data incrementally.

```typescript
import { D2, map, filter, debug, MultiSet, v } from '@electric-sql/d2ts'

// Create a new D2 graph with initial frontier
// The initial frontier is the lower bound of the version of the data that may
// come in future.
const graph = new D2({ initialFrontier: 0 })

// Create an input stream
// We can specify the type of the input stream, here we are using number.
const input = graph.newInput<number>()

// Build a simple pipeline that:
// 1. Takes numbers as input
// 2. Adds 5 to each number
// 3. Filters to keep only even numbers
// Pipelines can have multiple inputs and outputs.
const output = input.pipe(
  map((x) => x + 5),
  filter((x) => x % 2 === 0),
  debug('output'),
)

// Finalize the pipeline, after this point we can no longer add operators or
// inputs
graph.finalize()

// Send some data
// Data is sent as a MultiSet, which is a map of values to their multiplicity
// Here we are sending 3 numbers (1-3), each with a multiplicity of 1
// When you send data, you set the version number, here we are using 0
// The key thing to understand is that the MultiSet represents a *change* to
// the data, not the data itself. "Inserts" and "Deletes" are represented as
// an element with a multiplicity of 1 or -1 respectively.
input.sendData(
  0, // The version of the data
  new MultiSet([
    [1, 1],
    [2, 1],
    [3, 1],
  ]),
)

// Set the frontier to version 1
// The "frontier" is the lower bound of the version of the data that may come in future.
// By sending a frontier, you are indicating that you are done sending data for any version less than the frontier and therefor D2TS operators that require them can process that data and output the results.
input.sendFrontier(1)

// Process the data
graph.run()

// Output will show:
// 6 (from 1 + 5)
// 8 (from 3 + 5)

```

--------------------------------

### D2QL Multiple Columns and Direction in ORDER BY

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates ordering results by multiple columns with specified directions (ascending or descending) in D2QL. This example orders users first by name (ascending) and then by age (descending).

```typescript
const query: Query = {
  select: ['@id', '@name', '@age'],
  from: 'users',
  orderBy: [
    '@name',                // Order by name in ascending order
    { '@age': 'desc' }      // Then by age in descending order
  ]
};
```

--------------------------------

### Join Operator Example

Source: https://github.com/electric-sql/d2ts/blob/main/README.md

Demonstrates using the `rekey` and `join` operators to combine comment data with issue data based on issue IDs.

```typescript
// Transform comments into [issue_id, comment] pairs for joining
const commentsByIssue = inputComments.pipe(
  rekey(comment => comment.issue_id)
)

// Join comments with issues
const issuesWithComments = issuesForProject.pipe(
  join(commentsByIssue)
)
```

--------------------------------

### D2QL Query Builder - GROUP BY and HAVING

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Illustrates how to create aggregation queries using `groupBy` and `having` clauses with the D2QL query builder. This example groups employees by department name and filters for departments with more than 5 employees.

```typescript
const query = queryBuilder<MySchema>()
  .from('employees', 'e')
  .join({
    type: 'inner',
    from: 'departments',
    as: 'd',
    on: ['@e.department_id', '=', '@d.id']
  })
  .select(
    '@d.name',
    { avg_salary: { AVG: '@e.salary' } },
    { employee_count: { COUNT: '@e.id' } }
  )
  .groupBy('@d.name')
  .having({ COUNT: '@e.id' }, '>', 5)
  .buildQuery();
```

--------------------------------

### ElectricSQL D2TS Integration Example

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Demonstrates how to integrate D2TS with ElectricSQL by creating a D2 graph, configuring a pipeline with map, filter, and output operators, and connecting an ElectricSQL ShapeStream to the D2 input.

```typescript
import { D2, map, filter, output } from '@electric-sql/d2ts'
import { electricStreamToD2Input } from '@electric-sql/d2ts/electric'
import { ShapeStream } from '@electric-sql/client'

// Create D2 graph
const graph = new D2({ initialFrontier: 0 })

// Create D2 input
const input = graph.newInput<any>()

// Configure the pipeline
input.pipe(
  map(([key, data]) => data.value),
  filter((value) => value > 10),
  // ... any other processing / joining
  output((msg) => doSomething(msg)),
)

// Finalize graph
graph.finalize()

// Create Electric stream (example)
const electricStream = new ShapeStream({
  url: 'http://localhost:3000/v1/shape',
  params: {
    table: 'items',
    replica: 'full', // <-- IMPORTANT!
  },
})

// Connect Electric stream to D2 input
electricStreamToD2Input(electricStream, input)
```

--------------------------------

### D2QL Multiple CTEs Example

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates how to define and use multiple CTEs in D2QL. The first CTE 'adult_users' filters users by age, and the second CTE 'user_order_counts' joins 'adult_users' with 'orders' to count orders per user.

```typescript
const query: Query = {
  with: [
    {
      select: ['@id', '@name', '@age'],
      from: 'users',
      where: ['@age', '>', 21],
      as: 'adult_users'
    },
    {
      select: ['@id', '@name', { order_count: 'COUNT(@order_id)' }],
      from: 'adult_users',
      join: [
        {
          type: 'left',
          from: 'orders',
          on: ['@adult_users.id', '=', '@orders.user_id']
        }
      ],
      groupBy: ['@id', '@name'],
      as: 'user_order_counts'
    }
  ],
  select: ['@id', '@name', '@order_count'],
  from: 'user_order_counts',
  where: ['@order_count', '>', 0]
};
```

--------------------------------

### D2QL Basic CTE Example

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Illustrates the use of a basic Common Table Expression (CTE) in D2QL. A CTE named 'adult_users' is defined to filter users older than 21, and this CTE is then used in the main query.

```typescript
const query: Query = {
  with: [
    {
      select: ['@id', '@name', '@age'],
      from: 'users',
      where: ['@age', '>', 21],
      as: 'adult_users'
    }
  ],
  select: ['@id', '@name'],
  from: 'adult_users',
  where: ['@name', 'like', 'A%']
};
```

--------------------------------

### MultiSet for Inserts and Deletes

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Demonstrates how inserts and deletes are represented in a MultiSet using multiplicities of 1 and -1 respectively. This example shows inserting one comment and deleting another.

```typescript
const multiSet = new MultiSet<[string, Comment]>([
  [['321', { id: '1', text: 'Hello, world!', userId: '321' }], 1],
  [['123', { id: '2', text: 'Hello, world!', userId: '123' }], -1],
])
```

--------------------------------

### D2TS Operators

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Provides examples of common operators used in D2TS for transforming and manipulating data within the dataflow graph. These operators define the computation logic.

```typescript
import { map, filter, join } from "@electric-sql/d2ts";

// Example using map
const mappedStream = myStream.pipe(map(x => x * 2));

// Example using filter
const filteredStream = myStream.pipe(filter(x => x > 10));

// Example using join (conceptual)
// const joinedStream = streamA.pipe(join(streamB, (a, b) => a.id === b.id));
```

--------------------------------

### Basic D2QL Query Compilation and Execution

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates the basic usage of D2QL by defining a query, compiling it into a D2TS pipeline, and executing it with sample data. It shows how to import necessary components, create a D2 graph, send data, and run the pipeline.

```typescript
import { D2, MultiSet, output, v, Antichain } from '@electric-sql/d2ts';
import { Query, compileQuery } from '@electric-sql/d2ql';

// Define a D2QL query
const query: Query = {
  select: [
    '@id',
    '@name',
    { age_in_years: '@age' }
  ],
  from: 'users',
  where: [
    '@age', '>', 21
  ]
};

// Create a D2 graph
const graph = new D2({ initialFrontier: v([0, 0]) });
const input = graph.newInput<User>();
const pipeline = compileQuery(query, { [query.from]: input });

// Add an output handler
pipeline.pipe(
  output((message) => {
    console.log(message);
  })
);

// Finalize the graph
graph.finalize();

// Send data to the input
input.sendData(
  v([1, 0]),
  new MultiSet([
    [{ id: 1, name: 'Alice', age: 25, email: 'alice@example.com' }, 1],
    [{ id: 2, name: 'Bob', age: 19, email: 'bob@example.com' }, 1],
    [{ id: 3, name: 'Charlie', age: 30, email: 'charlie@example.com' }, 1]
  ])
);

// Send frontier
input.sendFrontier(new Antichain([v([1, 0])]));

// Run the graph
graph.run();
```

--------------------------------

### count Operator

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Counts the number of elements in a stream, typically by key. This example keys data by `somethingToKeyOn`.

```typescript
const output = input.pipe(
  map((data) => [data.somethingToKeyOn, data]),
  count(),
)
```

--------------------------------

### Update Versions and Publish

Source: https://github.com/electric-sql/d2ts/blob/main/RELEASING.md

Commands for manually updating package versions and changelogs, building the project, and publishing packages to npm using Changesets.

```bash
pnpm changeset version
pnpm build
pnpm changeset publish
```

--------------------------------

### D2QL Query Builder - Basic Usage

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates the basic usage of the D2QL query builder to construct a type-safe query. It shows how to define schema types, create a query builder instance, specify the 'from' table, select columns, apply a 'where' clause, and build the final query object.

```typescript
import { queryBuilder } from '@electric-sql/d2ql/query-builder';
import { Schema, Input } from '@electric-sql/d2ql/types';

// Define your schema types
interface Employee extends Input {
  id: number
  name: string
  department_id: number
  salary: number
}

interface Department extends Input {
  id: number
  name: string
  budget: number
}

// Define your schema
interface MySchema extends Schema {
  employees: Employee
  departments: Department
}

// Create a query using the builder
const query = queryBuilder<MySchema>()
  .from('employees')
  .select('@id', '@name', { salary_amount: '@salary' })
  .where('@salary', '>', 50000)
  .buildQuery();

// Use the query with compileQuery as normal
// const pipeline = compileQuery(query, { [query.from]: employeesInput });
```

--------------------------------

### Keyed Streams with Multiple Keys

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Illustrates how to use the `keyBy` method with an array of columns to define multiple keys for an output stream. This is useful for partitioning data based on a combination of fields.

```typescript
// Use multiple keys
const multiKeyQuery = queryBuilder<MySchema>()
  .from('employees')
  .select('@id', '@name', '@department_id')
  .keyBy(['@department_id', '@id'])
  .buildQuery();
```

--------------------------------

### Keyed Streams with Single Key

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates how to use the `keyBy` method with a single column to define the key for an output stream. This ensures that the stream is partitioned or identified by the specified key.

```typescript
const query = queryBuilder<MySchema>()
  .from('employees')
  .select('@id', '@name', '@department_id')
  .keyBy('@id')  // Use id as the key
  .buildQuery();
```

--------------------------------

### Create Changeset

Source: https://github.com/electric-sql/d2ts/blob/main/RELEASING.md

Command to create a new changeset file for managing package versions and changelogs. It prompts the user to select packages, specify the version change type (major, minor, patch), and provide a summary of the changes.

```bash
pnpm changeset
```

--------------------------------

### D2QL Query Builder - Joins

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Shows how to construct JOIN operations using the D2QL query builder. The `join` method takes an object specifying the join type, the table to join from, an optional alias, and the join condition.

```typescript
const query = queryBuilder<MySchema>()
  .from('employees', 'e')
  .join({
    type: 'inner',
    from: 'departments',
    as: 'd',
    on: ['@e.department_id', '=', '@d.id']
  })
  .select('@e.id', '@e.name', '@d.name')
  .where('@d.budget', '>', 1000000)
  .buildQuery();
```

--------------------------------

### D2QL Query Builder - ORDER BY, LIMIT, and OFFSET

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Shows how to sort and paginate query results using the `orderBy`, `limit`, and `offset` methods in the D2QL query builder. Results can be ordered by multiple columns with specified directions.

```typescript
const query = queryBuilder<MySchema>()
  .from('employees')
  .select('@id', '@name', '@salary')
  .orderBy(['@salary', { '@name': 'asc' }])  // Order by salary, then by name ascending
  .limit(10)  // Return only 10 records
  .offset(20) // Skip the first 20 records
  .buildQuery();
```

--------------------------------

### D2QL Keyed Streams with keyBy

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Illustrates how to create keyed streams in D2QL using the keyBy parameter, allowing data to be indexed or looked up by specified column(s).

```typescript
const query1: Query = {
  select: ['@id', '@name', '@email'],
  from: 'users',
  keyBy: '@id'
};

const query2: Query = {
  select: ['@id', '@name', '@department', '@role'],
  from: 'employees',
  keyBy: ['@department', '@role']
};
```

--------------------------------

### Function Calls in ORDER BY

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates using function calls like UPPER for case-insensitive sorting and specifying sort order in the orderBy clause.

```typescript
const query: Query = {
  select: ['@id', '@name', '@email'],
  from: 'users',
  orderBy: [
    { 'UPPER': '@name' },   // Order by uppercase name (case-insensitive sort)
    { '@email': 'desc' }    // Then by email in descending order
  ]
};
```

--------------------------------

### D2QL Wildcard Selects

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates how to use wildcard selects in D2QL to retrieve all columns from tables, including selecting all columns from all tables, a specific table, or specific tables within joins.

```typescript
const query1: Query = {
  select: ['@*'],
  from: 'users'
};

const query2: Query = {
  select: ['@users.*'],
  from: 'users',
  as: 'users'
};

const query3: Query = {
  select: [
    '@u.*',                   // All columns from users
    { 'order_id': '@o.id' }   // Just the id from orders
  ],
  from: 'users',
  as: 'u',
  join: [
    {
      type: 'inner',
      from: 'orders',
      as: 'o',
      on: ['@u.id', '=', '@o.userId']
    }
  ]
};
```

--------------------------------

### Basic D2TS Pipeline

Source: https://github.com/electric-sql/d2ts/blob/main/README.md

Demonstrates creating a D2 graph, defining an input stream, applying map and filter operations, and sending data. It shows how to process numbers by adding 5 and filtering for even results.

```typescript
import { D2, map, filter, debug, MultiSet, v } from '@electric-sql/d2ts'

// Create a new D2 graph with initial frontier
// The initial frontier is the lower bound of the version of the data that may
// come in future.
const graph = new D2({ initialFrontier: 0 })

// Create an input stream
// We can specify the type of the input stream, here we are using number.
const input = graph.newInput<number>()

// Build a simple pipeline that:
// 1. Takes numbers as input
// 2. Adds 5 to each number
// 3. Filters to keep only even numbers
// Pipelines can have multiple inputs and outputs.
const output = input.pipe(
  map((x) => x + 5),
  filter((x) => x % 2 === 0),
  debug('output'),
)

// Finalize the pipeline, after this point we can no longer add operators or
// inputs
graph.finalize()

// Send some data
// Data is sent as a MultiSet, which is a map of values to their multiplicity
// Here we are sending 3 numbers (1-3), each with a multiplicity of 1
// When you send data, you set the version number, here we are using 0
// The key thing to understand is that the MultiSet represents a *change* to
// the data, not the data itself. "Inserts" and "Deletes" are represented as
// an element with a multiplicity of 1 or -1 respectively.
input.sendData(
  0, // The version of the data
  new MultiSet([
    [1, 1],
    [2, 1],
    [3, 1],
  ]),
)

// Set the frontier to version 1
// The "frontier" is the lower bound of the version of the data that may come in future.
// By sending a frontier, you are indicating that you are done sending data for any version less than the frontier and therefor D2TS operators that require them can process that data and output the results.
input.sendFrontier(1)

// Process the data
graph.run()

// Output will show:
// 6 (from 1 + 5)
// 8 (from 3 + 5)

```

--------------------------------

### Using SQLite Backend

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Demonstrates how to configure and use a SQLite backend for D2TS, enabling persistent storage and execution of dataflow computations.

```typescript
import { sqlite } from "@electric-sql/d2ts/sqlite";

const db = await sqlite("my_database.db");
const d2tsGraph = graph([
  // ... nodes and edges using db
]);
```

--------------------------------

### D2QL Query Builder - Complex Where Conditions

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates building complex WHERE conditions by chaining multiple `where` calls. These conditions are automatically combined using the AND operator.

```typescript
const query = queryBuilder<MySchema>()
  .from('employees', 'e')
  .where('@e.salary', '>', 50000)
  .where('@e.department_id', '=', 1)
  .buildQuery();

// Results in where: [['@e.salary', '>', 50000], 'and', ['@e.department_id', '=', 1]]
```

--------------------------------

### D2QL Query with Joins and Functions (TypeScript)

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates a D2QL query with direct and aliased column selection, table aliases, function calls (UPPER, JSON_EXTRACT, DATE, CONCAT), LEFT JOIN, and complex WHERE clauses with AND/OR logic.

```typescript
import { D2, MultiSet, output, v, Antichain } from '@electric-sql/d2ts';
import { Query, compileQuery } from '@electric-sql/d2ql';
import { Message, MessageType } from '@electric-sql/d2ts';

// Define sample types
type Employee = {
  id: number
  name: string
  department_id: number | null
  salary: number
  hire_date: string
  active: boolean
  preferences: string  // JSON string
};

type Department = {
  id: number
  name: string
  location: string
  budget: number
};

// Create a D2QL query with multiple features
const query: Query = {
  select: [
    // Non-aliased columns (direct references)
    '@e.id',
    '@e.active',
    {
      // Aliased columns
      emp_name: '@e.name',
      dept_name: '@d.name',
      location: '@d.location',
      // Function calls
      upper_name: {
        UPPER: '@e.name',
      },
      annual_salary: '@e.salary',
      theme: {
        JSON_EXTRACT: ['@e.preferences', 'theme'],
      },
      hire_date: {
        DATE: '@e.hire_date',
      },
      employee_info: {
        CONCAT: ['Employee: ', '@e.name'],
      },
    },
  ],
  from: 'employees',
  as: 'e',  // Short alias for employees table
  join: [
    {
      type: 'left',  // Could be 'inner', 'right', or 'full'
      from: 'departments',
      as: 'd',  // Short alias for departments table
      on: ['@e.department_id', '=', '@d.id']
    }
  ],
  where: [
    ['@e.salary', '>', 50000, 'and', '@e.active', '=', true],
    'or',
    [{
      UPPER: '@d.name'
    }, '=', 'ENGINEERING']
  ]
};

// Create a D2 graph
const graph = new D2({ initialFrontier: v([0, 0]) });

// Create inputs for both tables
const employeesInput = graph.newInput<Employee>();
const departmentsInput = graph.newInput<Department>();

// Compile the query
const pipeline = compileQuery(query, {
  'employees': employeesInput,
  'departments': departmentsInput
});

// Add an output handler
pipeline.pipe(
  output((message: Message<any>) => {
    if (message.type === MessageType.DATA) {
      const results = message.data.collection.getInner().map(([data]) => data);
      console.log("Results:", results);
    }
  })
);

// Finalize the graph
graph.finalize();

// Send employee data
employeesInput.sendData(
  v([1, 0]),
  new MultiSet([
    [{ 
      id: 1, 
      name: 'Alice Smith', 
      department_id: 1, 
      salary: 85000, 
      hire_date: '2021-05-15', 
      active: true, 
      preferences: '{"theme":"dark","notifications":true}' 
    }, 1],
    [{ 
      id: 2, 
      name: 'Bob Johnson', 
      department_id: 2, 
      salary: 65000, 
      hire_date: '2022-02-10', 
      active: true, 
      preferences: '{"theme":"light","notifications":false}' 
    }, 1],
    [{ 
      id: 3, 
      name: 'Charlie Brown', 
      department_id: 1, 
      salary: 45000, 
      hire_date: '2023-01-20', 
      active: false, 
      preferences: '{"theme":"system","notifications":true}' 
    }, 1]
  ])
);
employeesInput.sendFrontier(new Antichain([v([1, 0])]));

// Send department data
departmentsInput.sendData(
  v([1, 0]),
  new MultiSet([
    [{ id: 1, name: 'Engineering', location: 'Building A', budget: 1000000 }, 1],
    [{ id: 2, name: 'Marketing', location: 'Building B', budget: 500000 }, 1],
    [{ id: 3, name: 'Finance', location: 'Building C', budget: 750000 }, 1]
  ])
);
departmentsInput.sendFrontier(new Antichain([v([1, 0])]));

// Run the graph
graph.run();
```

--------------------------------

### D2 Graph Construction API

Source: https://github.com/electric-sql/d2ts/blob/main/README.md

Details the constructor for creating a D2 graph instance and its primary methods for building dataflow pipelines. Includes options for initialization and core functions like adding inputs and running the graph.

```APIDOC
D2 graph construction:

```typescript
const graph = new D2({ initialFrontier: 0 })
```

The `D2` constructor takes an optional `options` object with the following properties:

- `initialFrontier`: The initial frontier of the graph, defaults to `0`

An instance of a D2 graph is used to build a dataflow graph, and has the following main methods:

- `newInput<T>(): IStreamBuilder<T>`: Create a new input stream
- `finalize(): void`: Finalize the graph, after this point no more operators or inputs can be added
- `run(): void`: Process all pending versions of the dataflow graph

```

--------------------------------

### Version and Antichain API

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

API reference for managing versions and frontiers in D2TS, including helper functions for creating versions and the Antichain constructor.

```APIDOC
Version Helper Function:
  v(version: number | number[]): Version
    - Creates a Version object. Reuses existing objects for efficiency.
    - Example:
      const version = v(1)
      const multiDimVersion = v([1, 2])

  Sending Single Integer Versions:
    - Input streams accept single integer versions directly for data and frontier.
    - Example:
      input.sendData(1, new MultiSet([[1, 1]]))
      input.sendFrontier(1)

Antichain Constructor:
  new Antichain(versions: Version[]): Antichain
    - Creates an Antichain representing a set of disjoint versions (frontier).
    - Example:
      const frontier = new Antichain([v(1), v([2])])
```

--------------------------------

### GROUP BY with Multiple Columns

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Shows how to group data by multiple columns to create more granular groupings and perform aggregate operations on these combined groups.

```typescript
const query: Query = {
  select: [
    '@customer_id',
    '@status',
    { total_amount: { SUM: '@amount' } },
    { order_count: { COUNT: '@order_id' } }
  ],
  from: 'orders',
  groupBy: ['@customer_id', '@status']
};
```

--------------------------------

### Create Version (TypeScript)

Source: https://github.com/electric-sql/d2ts/blob/main/README.md

Demonstrates how to create a version object using the `v` helper function. Supports both single integer and multidimensional versions.

```typescript
const version = v(1)
```

```typescript
const version = v([1, 2])
```

--------------------------------

### D2QL Query Builder - Common Table Expressions (WITH)

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates the use of Common Table Expressions (CTEs) with the `with` method in the D2QL query builder. CTEs allow you to define temporary, named result sets that can be referenced within a single query.

```typescript
const query = queryBuilder<MySchema>()
  .with('high_salary_employees', q => 
    q.from('employees')
     .where('@salary', '>', 80000)
     .select('@id', '@name', '@salary')
  )
  .with('dept_with_high_salary', q =>
    q.from('high_salary_employees', 'e')
     .join({
       type: 'inner',
       from: 'departments',
       as: 'd',
       on: ['@e.department_id', '=', '@d.id']
     })
     .select('@d.id', '@d.name', { emp_count: { COUNT: '@e.id' } })
     .groupBy('@d.id', '@d.name')
  )
  .from('dept_with_high_salary')
  .select('@id', '@name', '@emp_count')
  .where('@emp_count', '>', 3)
  .buildQuery();
```

--------------------------------

### Top K with Stable Fractional Indexing

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

An advanced version of `topKWithIndex` that uses fractional indexing for stable, lexicographically sortable indices. Minimizes updates for moved elements.

```typescript
// Get top 10 leaderboard entries with stable fractional indices
const leaderboard = scores.pipe(
  map((score) => ['leaderboard', score]),
  topKWithFractionalIndex((a, b) => b.points - a.points, { limit: 10 }),
  map(([_, [score, fractionalIndex]]) => ({
    ...score,
    position: fractionalIndex, // Lexicographically sortable string index
  }))
)
```

--------------------------------

### Create Antichain (TypeScript)

Source: https://github.com/electric-sql/d2ts/blob/main/README.md

Illustrates creating an `Antichain` object, which represents a set of disjoint versions, using the `Antichain` constructor.

```typescript
const frontier = new Antichain([v(1), v([2])])
```

--------------------------------

### Basic SQLite Operator Usage

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Demonstrates how to use a SQLite operator like `consolidate` with an explicitly provided `better-sqlite3` database instance.

```typescript
import { Database } from 'better-sqlite3';
import { BetterSQLite3Wrapper } from '@electric-sql/sqlite-adapter';
import { consolidate } from '@electric-sql/operators';

// Assuming 'input' is an observable stream of data
const sqlite = new Database('./my_database.db');
const db = new BetterSQLite3Wrapper(sqlite);

const output = input.pipe(consolidate(db));
```

--------------------------------

### Combining WHERE and GROUP BY

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Demonstrates how to use WHERE to filter rows before grouping and HAVING to filter the resulting groups based on aggregate conditions.

```typescript
const query: Query = {
  select: [
    '@customer_id',
    { total_amount: { SUM: '@amount' } },
    { order_count: { COUNT: '@order_id' } }
  ],
  from: 'orders',
  where: ['@status', '=', 'completed'],
  groupBy: ['@customer_id'],
  having: [{ col: 'total_amount' }, '>', 500]
};
```

--------------------------------

### D2 Graph Construction API

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

API reference for constructing a D2 graph using the `D2` constructor, including available options and core methods for managing the dataflow graph.

```APIDOC
D2 constructor
  new D2(options?: { initialFrontier?: number })
    - Creates a new D2 graph instance.
    - Parameters:
      - options: An optional object for configuration.
        - initialFrontier: The initial frontier of the graph. Defaults to 0.

  Methods:
    - newInput<T>(): IStreamBuilder<T>
      - Creates a new input stream for the graph.
    - finalize(): void
      - Finalizes the graph, preventing further additions of operators or inputs.
    - run(): void
      - Processes all pending versions of the dataflow graph.
```

--------------------------------

### Versions and Frontiers

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ts/README.md

Explains the concepts of versions and frontiers in D2TS, which are crucial for managing the state and progress of incremental computations.

```typescript
import { version, frontier } from "@electric-sql/d2ts";

const myVersion = version(1);
const myFrontier = frontier([myVersion]);
```

--------------------------------

### Including Row Index with ORDER_INDEX

Source: https://github.com/electric-sql/d2ts/blob/main/packages/d2ql/README.md

Shows how to include the row index in query results using the ORDER_INDEX function in the SELECT clause, supporting numeric and fractional indexes.

```typescript
const query: Query = {
  select: [
    '@id',
    '@name',
    { age_in_years: '@age' },
    { index: { 'ORDER_INDEX': 'numeric' } }  // Include the numeric index
  ],
  from: 'users',
  orderBy: '@age'
};
```