### Initializing Development Environment with Prisma and Yarn

Source: https://github.com/ardsh/slonik-trpc/blob/main/examples/datagrid-example/README.md

This snippet provides the essential commands to set up the database schema using Prisma and then start the local development server. These steps are crucial for getting the T3 App project running and accessible for development.

```bash
yarn prisma db push
yarn dev
```

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

### Installing Slonik-TRPC Library (Yarn)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/setup.md

This command installs the `slonik-trpc` library itself using Yarn. This package integrates `slonik` with `tRPC`, enabling type-safe API development with a database.

```bash
yarn add slonik-trpc
```

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

### Installing Slonik and Zod Dependencies (Yarn)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/setup.md

This command installs the `slonik` and `zod` packages using Yarn. These libraries are essential prerequisites for `slonik-trpc`, providing database client and schema validation functionalities respectively.

```bash
yarn add slonik zod
```

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

### Starting Local Development Server with Yarn

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/README.md

This command initiates a local development server, typically opening a browser window, and enables live reloading for immediate reflection of code changes during development.

```Shell
$ yarn start
```

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

### Installing Project Dependencies with Yarn

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/README.md

This command installs all necessary project dependencies listed in the `package.json` file using Yarn, preparing the project for development or build.

```Shell
$ yarn
```

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

### Installing Slonik Dependency - Bash

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/slonik.md

This command installs the Slonik library, a Node.js PostgreSQL client, as a project dependency using Yarn. It's the first step required to use Slonik in your application.

```bash
yarn add slonik
```

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

### Installing slonik-trpc and Dependencies with Yarn

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This command adds `slonik-trpc` and its peer dependencies, `slonik` (a PostgreSQL client) and `zod` (a schema validation library), to the project using Yarn. These packages are essential for building type-safe SQL-based APIs.

```sh
yarn add slonik-trpc slonik zod
```

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

### Building Custom Plugin with slonik-trpc

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/plugins.md

This snippet illustrates how to create a custom plugin using the `Plugin` type. The example shows a `cachePlugin` that checks a cache before loading data, demonstrating how to stop execution and return a cached result.

```typescript
import type { Plugin } from 'slonik-trpc';

const cachePlugin: Plugin = {
    onLoad(options) {
        const key = getKey(options.args);
        if (cache.hasKey(key)) {
            // You can return a promise
            return options.setResultAndStopExecution(cache.get(key));
        }
    }
}
```

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

### Testing All Query Filters with makeQueryAnalyzer (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/utility-extra/benchmark-utils.md

This example shows how to use `testAllFilters` from `makeQueryAnalyzer` to execute all defined filters for a given query loader. This is crucial for detecting SQL syntax errors or other issues within the filters, ensuring their correctness and preventing runtime failures.

```TypeScript
test("All filters work", async () => {
    const result = await makeQueryAnalyzer(db).testAllFilters(loader);
    expect(result).toBeDefined();
});
```

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

### Manual Cursor-Based Pagination with searchAfter (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

Demonstrates an alternative, Prisma-style manual cursor pagination using the `searchAfter` option. Instead of an encoded cursor, specific column values are provided to define the starting point for pagination, achieving the same result as encoded cursors.

```TypeScript
const usersAfterBob = await db.any(await sortableLoader.getQuery({
    orderBy: [["name", "DESC"], ["id", "ASC"]],
    searchAfter: {
        name: "Bob",
        id: 45
    },
    take: 5
}))
```

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

### Initializing PostgreSQL Database Schema and Seeding Data - TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/slonik.md

This TypeScript function `initializeDatabase` connects to the Slonik database instance and executes SQL commands to create and populate tables. It first optionally creates and sets a schema, then drops existing `users` and `posts` tables before recreating them with defined columns and constraints. Finally, it inserts sample data into both tables for tutorial purposes. This function is called immediately to set up the 'playground' schema.

```typescript
import { db } from './slonik.ts';

export async function initializeDatabase(schema?: string) {
    if (schema) {
        await db.query(sql.unsafe`
            CREATE SCHEMA IF NOT EXISTS ${sql.identifier([schema])};
            SET search_path TO ${sql.identifier([schema])};
        `);
    }
    await db.query(sql.unsafe`
        DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS posts;

        CREATE TABLE IF NOT EXISTS posts (
            id integer NOT NULL PRIMARY KEY,
            author_id text NOT NULL,
            title text NOT NULL,
            date date NOT NULL,
            content text NOT NULL DEFAULT '',
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS users (
            "id" text NOT NULL PRIMARY KEY,
            "first_name" text NOT NULL,
            "last_name" text NOT NULL,
            "email" text NOT NULL,
            "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );`);
    await db.query(sql.unsafe`
        INSERT INTO posts
            (id, author_id, title, content, date)
        VALUES
            (1, 'z', 'aaa', 'This is a post', '2022-01-01'),
            (2, 'y', 'aaa', 'This is a post', '2022-02-01'),
            (3, 'x', 'bbb', 'This is a post', '2022-03-01'),
            (4, 'w', 'bbb', 'This is a post', '2022-04-01'),
            (5, 'v', 'ccc', 'This is a post', '2022-05-01'),
            (6, 'u', 'ccc', 'This is a post', '2022-06-01'),
            (7, 't', 'ddd', 'This is a post', '2022-07-01'),
            (8, 's', 'ddd', 'This is a post', '2022-08-01'),
            (9, 'r', 'eee', 'This is a post', '2022-09-01');

        INSERT INTO users
            (id, "first_name", "last_name", email)
        VALUES
            ('z', 'Haskell', 'Nguyen', 'haskell04@gmail.com'),
            ('y', 'Padberg', 'Fletcher', 'padberg.shawna@hotmail.com'),
            ('x', 'Neal', 'Phillips', 'nvandervort@collier.com'),
            ('w', 'Nolan', 'Muller', 'qnolan@yahoo.com'),
            ('v', 'Bob', 'Dean', 'acummerata@gmail.com'),
            ('u', 'Rebecca', 'Mercer', 'moore.rebeca@yahoo.com'),
            ('t', 'Katheryn', 'Ritter', 'katheryn89@hotmail.com'),
            ('s', 'Dulce', 'Espinoza', 'dulce23@gmail.com'),
            ('r', 'Paucek', 'Clayton', 'paucek.deangelo@hotmail.com');
    `);
}

initializeDatabase('playground');
```

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

### Example SQL Query for Posts Data

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This SQL query retrieves post information, including the post ID, author's full name (concatenated from first and last names), post text, and the age of the post in days. It joins the `posts` table with the `users` table to link posts to their authors.

```sql
SELECT "posts".id
  , "users"."firstName" || ' ' || "users"."lastName" AS "author"
  , "posts"."text"
  , EXTRACT(DAYS FROM NOW() - posts."created_at") AS "age"
FROM users
LEFT JOIN posts
    ON posts.author = users.id
```

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

### Pagination Count Query Example (SQL)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This SQL snippet shows the structure of the additional query executed by loadPagination when takeCount: true is specified. It wraps the main query (represented by ...) in a SELECT COUNT(*) statement to efficiently retrieve the total number of records for pagination purposes.

```sql
SELECT COUNT(*) FROM (...) subQuery
```

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

### Using Distinct On with Slonik Query Loader

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/distinct.md

This snippet illustrates how to use the `distinctOn` option with `postsLoader` to retrieve distinct records. It shows two examples: one for getting posts by distinct authors, and another demonstrating how `distinctOn` automatically adjusts `orderBy` fields, even if explicitly specified, to ensure the leftmost order matches the distinct criteria.

```TypeScript
const distinctAuthors = await postsLoader.load({
    distinctOn: ["name"]
    // NOTE: Distinct On automatically adds orderBy fields
    // So you don't have to specify
    // orderBy: ["name", "ASC"],
}));

const sortedByNameAndDate = await postsLoader.load({
    distinctOn: ["name"]
    // NOTE: distinctOn rearranges the orderBy fields, if specified, so the leftmost order is the same
    orderBy: [["date", "ASC"], ["name", "DESC"]],
    // This example would sort by name DESC first, then date, despite the specified orderBy
    take: 5,
})
```

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

### Defining a Grouped Query Loader with Slonik-TRPC

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/grouping.md

This snippet defines a `postsLoader` using `makeQueryLoader` to aggregate posts by author. It uses `sql.type` for schema validation, `sql.fragment` for the `GROUP BY` clause on `users.id`, and joins `posts` with `users` to get author names and post counts. It also integrates a `postsFilter` for later use.

```TypeScript
const postsLoader = makeQueryLoader({
    db,
    query: {
        select: sql.type(z.object({
            author: z.string(),
            count: z.number()
        }))`SELECT
            users.first_name || ' ' || users.last_name AS author,
            COUNT(*) AS "postsCount"`,
        view: buildView`FROM posts
        LEFT JOIN users
            ON users.id = posts.author_id`,
        groupBy: sql.fragment`users.id`
    },
    filters: postsFilter
});
```

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

### Integrating Slonik-tRPC Loader with tRPC Procedures

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This example shows how to define a tRPC public procedure (`getUsers`) that utilizes `loader.getLoadArgs` to infer input types and `loader.loadPagination` to handle data retrieval with pagination. It also illustrates how to disable specific filters, such as 'OR' filters, for public API endpoints.

```TypeScript
getUsers: publicProcedure
    .input(loader.getLoadArgs({
        disabledFilters: {
            // OR filters can be disabled on a public API
            OR: true,
        }
    }))
    .query(({ input, ctx }) => {
        return loader.loadPagination({
            ...input,
            ctx,
        });
    }),
```

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

### Defining Column Groups with makeQueryLoader (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/overfetching.md

This code defines a `postsLoader` using `makeQueryLoader`, organizing fields into logical groups (`basic`, `author`, `extraPostFields`) via the `columnGroups` option. This setup allows for flexible data retrieval by selecting only necessary groups, improving query efficiency and data organization. It also specifies the base `query` and `view` for the loader.

```TypeScript
const postsLoader = makeQueryLoader({
    columnGroups: {
        basic: ["id", "name"],
        author: ["first_name", "last_name"],
        extraPostFields: ["created_at", "content"]
    },
    query: {
        select: sql.type(zodType)`SELECT posts.*, users.first_name, users.last_name`,
        view: buildView`FROM posts LEFT JOIN users ON users.id = posts.author_id`
    }
});
```

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

### Adding Various Filters to a Slonik-tRPC View

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This example illustrates how to declaratively add different types of filters to a `slonik-trpc` view using methods like `addInArrayFilter`, `addStringFilter`, `addComparisonFilter`, `addDateFilter`, `addJsonContainsFilter`, and `addBooleanFilter`. This enables flexible and powerful data filtering capabilities.

```TypeScript
buildView`FROM users`
    .addInArrayFilter('id')
    .addStringFilter(['users.name', 'users.profession'])
    .addComparisonFilter('postsCount', () => sql.fragment`users."authoredPosts"`)
    .addDateFilter('createdDate', () => sql.fragment`users.created_at`)
    .addJsonContainsFilter('users.settings') // jsonb column
    .addBooleanFilter('isGmail', () => sql.fragment`users.email ILIKE '%gmail.com'`)
```

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

### Using a General Comparison Filter in Slonik-trpc Queries (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/utility-extra/sql-utils.md

This example shows how to apply the `postTitle` comparison filter in a `where` clause. It demonstrates combining `_in` to match specific values and `_is_null` to exclude null values, providing a powerful way to filter data based on multiple conditions.

```TypeScript
where: {
    postTitle: {
        _in: ["A", "B", "C"],
        _is_null: false
    }
}
```

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

### Querying a Slonik-tRPC Loader with Select and Where Clauses

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This TypeScript example demonstrates how to interact with the `loader` created previously. It shows how to specify which fields to `select` (e.g., 'id', 'text', 'age') for type-safe overfetching prevention, apply complex `where` conditions using `OR` for filtering, and limit the number of results using `take`. The returned `posts` array will be type-safe, containing only the selected fields.

```ts
// This array is type-safe, only having "id", "text", and "age" fields (no author)
const posts = await loader.load({
    select: ["id", "text", "age"],
    where: {
        OR: [{
            "posts.title": "Lorem Ipsum"
        }, {
            "posts.id": [3, 4]
        }]
    },
    take: 200
});
```

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

### Filtering Grouped Data by Category (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

Illustrates how to apply a `where` clause to a grouped loader to filter aggregated results. This example shows filtering authors by their posts' categories while still obtaining the posts count for each user.

```TypeScript
const bigPostsCount = await sortableLoader.load({
    where: {
        'posts.category': ["typescript"]
    }
});
```

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

### Applying Sorting with postsLoader.load (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/sorting.md

This snippet illustrates how to apply sorting to data using the `postsLoader.load` method. It shows examples of sorting by a single column ('name') and by multiple columns ('name' and 'date') with different sort orders (ASC/DESC), leveraging the aliases defined in `sortableColumns`.

```TypeScript
const sortedByName = await postsLoader.load({
    orderBy: ["name", "ASC"]
});

const sortedByNameAndDate = await postsLoader.load({
    orderBy: [["name", "DESC"], ["date", "ASC"]],
    take: 5
})
```

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

### Using a Simple Filter with `where` Object (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/classic-filtering.md

This example shows how to apply the previously defined simple filter by passing a `where` object to the query. It illustrates how a single `id` value is used to construct a basic equality condition in the SQL query.

```TypeScript
where: {
    id: 5
}
```

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

### Loading Detailed Post Data with Multiple Column Groups (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/overfetching.md

This example demonstrates loading detailed post data using `postsLoader.load` by selecting multiple column groups. The `selectGroups` option includes "basic", "author", and "extraPostFields", ensuring all relevant information (`id`, `name`, `first_name`, `last_name`, `created_at`, `content`) for a specific post is retrieved, suitable for a post-specific view.

```TypeScript
const detailedPostData = await postsLoader.load({
    take: 1,
    where: {
        postIds: 3
    },
    selectGroups: ["basic", "author", "extraPostFields"]
});
```

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

### Selecting Virtual Fields with `load` (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/virtual-columns.md

This example shows how to select a previously defined virtual field, `fullName`, alongside a real field, `id`, when loading data using `virtualFieldsLoader.load()`. The return type of the virtual field is automatically inferred.

```TypeScript
const data = await virtualFieldsLoader.load({
    select: ["id", "fullName"]
});
```

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

### Using Table Loader Context Provider in React

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/client-type-safety/table-selection.md

This example demonstrates how to use the `employeeLoader.ContextProvider` component to wrap a table component (`EmployeesTable`). This ensures that all child components within the `EmployeesTable` can access the shared dependency state and dispatch functions provided by the table data loader.

```tsx
<employeeLoader.ContextProvider>
  <EmployeesTable />
</employeeLoader.ContextProvider>
```

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

### Using String Comparison Filter in Where Clause (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/filtering.md

This example demonstrates how to apply string comparison filters in the `where` clause. It uses `_ilike` for case-insensitive 'like' matching and `_iregex` for case-insensitive regular expression matching on specified columns.

```typescript
where: {
    "users.name": {
        _ilike: 'John',
    },
    "users.profession": {
        _iregex: 'programmer',
    },
}
```

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

### Applying Merged Filters in `load()` Function (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/classic-filtering.md

This example demonstrates how to apply the merged filters when calling the `load()` function of a query loader. It shows how complex `OR` conditions can be constructed using filters from different merged sets, such as `userIds` and `longPost`.

```TypeScript
const posts = await postsLoader.load({
  where: {
    OR: [{
      userIds: [1, 2, 3],
    }, {
      longPost: true,
    }]
  }
});
```

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

### Defining a Boolean Filter for Slonik-trpc Queries (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/utility-extra/sql-utils.md

This code defines a boolean filter using `createFilter` and `booleanFilter`. It allows filtering based on a boolean value, applying a condition when `true` and its inverse when `false` (by default). The filter is not applied for `null` values and is demonstrated with a `largePosts` example.

```TypeScript
createFilter<Context>()({
    largsPosts: z.boolean().nullish(),
}, {
    largePosts: (value) => booleanFilter(value, sql.fragment`LEN(posts.text) >= 500`)
});
```

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

### Using a Boolean Filter in Slonik-trpc Queries (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/utility-extra/sql-utils.md

This example shows how to apply the previously defined `largePosts` boolean filter in a `where` clause. Setting `largePosts: false` will return only posts where the text length is less than 500 characters, demonstrating the inverse application of the condition.

```TypeScript
where: {
    largePosts: false
}
```

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

### Avoiding Inefficient Async Loading in Virtual Fields (Anti-pattern)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This example illustrates an anti-pattern for loading remote data within virtual fields. Directly calling an async function like `fetchPostsForAuthor` for each row in the `resolve` method can lead to significant performance issues due to N+1 queries.

```TypeScript
virtualFields: {
    posts: {
        dependencies: ["id"],
        // 📉 This can lead to performance issues.
        resolve: async (row) => {
            const posts = await fetchPostsForAuthor(row.id);
            return posts;
        }
    }
},
```

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

### Configuring Slonik PostgreSQL Connection with Type Parsers - TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/slonik.md

This TypeScript code sets up a Slonik connection pool, configuring it to connect to a PostgreSQL database using `POSTGRES_DSN` or `DATABASE_URL` environment variables. It includes custom type parsers to convert `date`, `timestamp`, and `timestamptz` columns into ISO 8601 string format, overriding Slonik's default integer timestamp behavior. A proxy `db` object is also created to ensure queries are executed against the resolved pool.

```typescript
import { CommonQueryMethods, createPool, createTypeParserPreset, sql } from 'slonik';
import { createResultParserInterceptor } from "slonik-trpc/utils";

export const slonik = createPool(process.env.POSTGRES_DSN || process.env.DATABASE_URL, {
    interceptors: [createResultParserInterceptor()],
    typeParsers: [
    ...createTypeParserPreset().filter(
        (a) => a.name !== "timestamp" && a.name !== "timestamptz" && a.name !== "date"
    ), {
        name: "date",
        parse: (a) => !a || !Date.parse(a) ? a :
            new Date(a).toISOString().slice(0, 10),
    }, {
        name: "timestamptz",
        parse: (a) => !a || !Date.parse(a) ? a : new Date(a).toISOString(),
    }, {
        name: "timestamp",
        parse: (a) => !a || !Date.parse(a) ? a : new Date(a + "Z").toISOString(),
    }],
})

// If you're using ES modules with node 14+ you can use top-level await here
// export const db = await slonik;
export const db: CommonQueryMethods = new Proxy({} as never, {
    get(target, prop: keyof CommonQueryMethods) {
        return (...args: any[]) => {
            return pool.then((db) => {
                return Function.prototype.apply.apply(db[prop], [db, args]);
            });
        };
    },
});
```

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

### Inferring Slonik-tRPC Query Payload with Hooks & Pagination (Client)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This advanced client-side example shows how to achieve type safety when using tRPC hooks with pagination. It introduces a `ReplaceNodes` utility type that correctly re-types the `nodes` array within paginated results, ensuring that the `nodes` property contains an array of `InferPayload` objects, even when using `useQuery`.

```TypeScript
import type { InferArgs, InferPayload, Post } from '../../server';

type ReplaceNodes<TResult, TPayload> = TResult extends { nodes?: ArrayLike<any>, hasNextPage?: boolean } ? Omit<TResult, "nodes"> & {
    nodes: TPayload[]
} : TResult extends object ? {
    [K in keyof TResult]: ReplaceNodes<TResult[K], TPayload>
} : TResult;

const getPosts = <TArgs extends InferArgs<PostLoader>>(args: TArgs) => {
    const result = trpc.loadPosts.useQuery(args);
    return result as ReplaceNodes<typeof result, InferPayload<PostLoader, TArgs>>;
}
```

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

### Building Static Website Content with Yarn

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/README.md

This command compiles the Docusaurus website into static content, generating files in the `build` directory, which can then be served by any static hosting service.

```Shell
$ yarn build
```

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

### Using Boolean Filter in Where Clause (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/filtering.md

This example shows how to use the `isGmail` boolean filter in the `where` clause. Setting `isGmail: false` will filter for users whose email does not end with 'gmail.com'.

```typescript
where: {
    isGmail: false,
}
```

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

### Running Slonik-tRPC Tests

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This snippet provides the necessary shell commands to set up the environment and execute tests for the `slonik-trpc` project. It requires exporting the PostgreSQL database connection URL before running the test command.

```Bash
export DATABASE_URL=postgresql://postgres:password@localhost:5432/database
yarn test
```

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

### Using Comparison Filter in Where Clause (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/filtering.md

This example shows how to use the `postsCount` comparison filter in the `where` clause. It applies a `_gte` (greater than or equal to) operator to filter users based on their post count.

```typescript
where: {
    postsCount: {
        _gte: 5,
    },
}
```

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

### Using Contained-in-Array Filter in Where Clause (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/filtering.md

This example shows how to use the 'in-array' filter within the `where` API. It supports `OR` and `NOT` conditions, allowing filtering by multiple IDs or excluding specific IDs from the results.

```typescript
where: {
    OR: [{
        id: [3, 4, 5]
    }, {
        NOT: {
            id: 6
        }
    }]
}
```

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

### Deploying Website Using SSH with Yarn

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/README.md

This command deploys the website to a hosting service using SSH for authentication, often pushing the built content to a `gh-pages` branch for GitHub Pages hosting.

```Shell
$ USE_SSH=true yarn deploy
```

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

### Creating a Post Query Loader with slonik-trpc (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/usage.md

This snippet demonstrates how to create a `slonik-trpc` query loader for posts. It defines a SQL query to select post details including author, title, and date, and specifies the view for joining posts with users. The `makeQueryLoader` function then initializes the loader with the database connection and the defined query.

```TypeScript
import { makeQueryLoader, buildView, sql } from 'slonik-trpc';

const postsQuery = {
    select: sql.type(z.object({
        id: z.number(),
        author: z.string(),
        title: z.string(),
        date: z.string(),
    }))`SELECT
        posts.id,
        users.first_name || ' ' || users.last_name AS author,
        posts.title,
        posts.date`,
    view: buildView`FROM posts
    LEFT JOIN users
        ON users.id = posts.author_id`
};

export const postsLoader = makeQueryLoader({
    db,
    query: postsQuery,
});
```

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

### Using JSON Contains Filter in Where Clause (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/filtering.md

This example shows how to use the `settings` JSON contains filter in the `where` clause. It filters for records where the 'settings' JSONB column contains both `notifications: true` and `theme: 'dark'`.

```typescript
where: {
    settings: {
        notifications: true,
        theme: 'dark'
    }
}
```

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

### Using a Boolean Filter with `where` Object (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/classic-filtering.md

This example shows how to apply the `isGmail` boolean filter by setting its value to `false` in the `where` object. This will filter results to include only users whose email does not end with 'gmail.com'.

```TypeScript
const nonGmailUsers = await filtersLoader.load({
    where: {
        isGmail: false,
    }
});
```

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

### Deploying Website Without SSH with Yarn

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/README.md

This command deploys the website without relying on SSH, requiring the GitHub username to be specified, and is commonly used for pushing built content to a `gh-pages` branch for GitHub Pages.

```Shell
$ GIT_USER=<Your GitHub username> yarn deploy
```

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

### Integrating Prisma Client with Slonik-TRPC Loader (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This snippet demonstrates how to configure slonik-trpc's makeQueryLoader to use an existing Prisma client instead of the default Slonik client. It shows how to map the db.any function to prisma.$queryRawUnsafe, allowing slonik-trpc to execute SQL queries through Prisma. This approach is not officially supported and may lead to issues, but it leverages Prisma's raw query execution for compatibility.

```ts
export const loader = makeQueryLoader({
    query,
    db: {
        any: (sql) => {
            return prisma.$queryRawUnsafe(sql.sql, ...sql.values);
        }
    },
    // ...
```

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

### Disabling Specific Filters in Slonik-tRPC Load Arguments

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This example shows how to explicitly disable certain filters, such as the `OR` filter, when configuring `loader.getLoadArgs`. Disabling computationally expensive filters like `OR` is recommended for public APIs to prevent performance bottlenecks.

```TypeScript
loader.getLoadArgs({
    disabledFilters: {
        // OR filters should be disabled on a public API
        OR: true,
    }
})
```

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

### Loading Next Page Manually with searchAfter - TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/cursor-pagination.md

This snippet demonstrates using the `searchAfter` option for manual pagination to retrieve the next 25 items. It specifies the `orderBy` columns and the exact values of the last item on the current page (`name: 'Bob'`, `id: 65`) to fetch the subsequent data. This method is generally not recommended over opaque cursors.

```TypeScript
const nextPage = await postsLoader.loadPagination({
    orderBy: [["name", "ASC"], ["id", "ASC"]],
    searchAfter: {
        name: "Bob",
        id: 65,
    },
    take: 25,
});
```

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

### Using an Array Filter with `where` Object (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/classic-filtering.md

This example demonstrates how to apply an array filter by passing an array of values to the `ids` property within the `where` object. This allows querying for multiple specific IDs in a single request.

```TypeScript
where: {
    ids: [3, 4, 5]
}
```

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

### Implementing Authorization Constraints in TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/authorization.md

This TypeScript snippet demonstrates how to use the `constraints` option within a `makeQueryLoader` to implement authorization logic. It allows 'ADMIN' users to query all data by returning `null`, while other users are restricted to data within their organization by adding a SQL fragment condition based on `ctx.orgId`.

```TypeScript
const userLoader = makeQueryLoader({
    // ...
    constraints(ctx) {
        if (ctx.role === 'ADMIN') {
            // Allow admins to query anyone by returning no extra permission rules.
            return null;
        } else {
            // Only allow querying the users in the same org as the logged in user
            return sql.fragment`users.org_id=${ctx.orgId}`;
        }
    }
});
```

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

### Applying Authorization Constraints with makeQueryLoader (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

Demonstrates how to use the `constraints` option within `makeQueryLoader` to enforce authorization rules. It allows dynamic SQL fragment generation based on the context (e.g., user role) to filter data, ensuring only authorized data is queried.

```TypeScript
const userLoader = makeQueryLoader({
    db,
    query,
    filters,
    constraints(ctx) {
        if (ctx.role === 'ADMIN') {
            // Allow admins to query anyone by returning no extra permission rules.
            return null;
        } else {
            // Only allow querying the users in the same org as the logged in user
            return sql.fragment`users.org_id=${ctx.orgId}`;
        }
    }
});
```

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

### Implementing Offset-based Pagination with Slonik-TRPC

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/pagination.md

This snippet demonstrates how to use the `loadPagination` method to fetch a specific range of items. It specifies `take: 50` to retrieve 50 items and `skip: 75` to bypass the first 75 items, effectively fetching items 76 through 125.

```TypeScript
const posts = await postsLoader.loadPagination({
    take: 50,
    skip: 75,
});
```

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

### Fetching Previous Page with Cursor Pagination - TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/cursor-pagination.md

This snippet illustrates how to fetch the previous page using cursor-based pagination. Setting `cursor: startCursor` and a negative `take` value instructs the system to retrieve items preceding the current page.

```TypeScript
cursor: startCursor,
take: -25
```

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

### Loading Previous Page Manually with searchAfter - TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/cursor-pagination.md

This snippet shows how to use the `searchAfter` option to retrieve the previous page. By setting a negative `take` value (`-25`) and specifying the `orderBy` columns (with `name` in `DESC` order for backward paging) along with the `searchAfter` values, it fetches the preceding set of items.

```TypeScript
const previousPage = await postsLoader.loadPagination({
    orderBy: [["name", "DESC"], ["id", "ASC"]],
    searchAfter: {
        name: "Bob",
        id: 65,
    },
    take: -25,
});
```

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

### Benchmarking Query Loader Fields with makeQueryAnalyzer (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/utility-extra/benchmark-utils.md

This snippet demonstrates how to use `makeQueryAnalyzer` to benchmark the performance of individual fields within a query loader. It executes the specified loader with given arguments for a set number of iterations, returning the time taken for each field to resolve, which helps identify performance inefficiencies.

```TypeScript
import { makeQueryAnalyzer } = 

const analyzer = makeQueryAnalyzer(db)
const result = await analyzer.benchmarkQueryLoaderFields(loader, {
    iterations: 10,
    args: {
        take: 200,
        where: {
            id: 42,
        }
    }
});

result['id']// number in milliseconds of the time it takes DB to resolve the id field 10 times.
result['posts'] // same number, for the posts field
```

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

### Calling a tRPC Query for Posts

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/trpc.md

This snippet demonstrates how to call the `getPosts` tRPC query from a client. It specifies `select` to retrieve only `id`, `title`, and `content` columns, and uses `take` and `skip` for pagination, showcasing how to consume the API defined with `postsLoader`.

```TypeScript
const posts = await client.getPosts.query({
    select: ["id", "title", "content"], // Will query only these 3 columns.
    take: 25,
    skip: 0,
});
```

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

### Calling Slonik-tRPC Pagination Procedure

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This snippet demonstrates how to call a tRPC procedure that uses `slonik-trpc`'s pagination loader. It shows how to specify which columns to select and how to control pagination using `take` and `skip` parameters, resulting in a relay-like pagination structure.

```TypeScript
const users = await client.getUsers.query({
    select: ["id", "name", "email"], // Will query only these 3 columns, ignoring the rest
    take: 25,
    skip: 0,
});
```

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

### Implementing Server-Side Sorting with Pagination Reset in TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/client-type-safety/cursor-pagination.md

This example shows how to combine server-side sorting with cursor-based pagination. When the sort order changes, the `onChange` callback of `useSort` triggers `paginationProps.onFirstPage()` to reset the cursor, ensuring data consistency. It also updates the `orderBy` state for the TRPC query.

```TypeScript
import { useSort } from '@table-library/react-table-library/sort';

// ...

const [orderBy, setOrderBy] = React.useState();

const paginationProps = employeeLoader.usePaginationProps();
const pagination = employeeLoader.useVariables();

const { data, isLoading } = trpc.employees.getEmployees.useQuery({
    ...pagination,
    orderBy,
});
employeeLoader.useUpdateQueryData(data);

const sort = useSort(data, {
  onChange: (action: any, state: any) => {
    // Reset to first page when sorting changes
    paginationProps.onFirstPage();
    setOrderBy([state.sortKey, state.reverse ? "DESC" : "ASC"]);
  },
}, {
  isServer: true,
  sortFns: {},
});
```

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

### Fetching Next Page with Cursor Pagination - TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/cursor-pagination.md

This snippet shows the parameters to use when fetching the next page in a cursor-based pagination system. By specifying `cursor: endCursor` and a positive `take` value, the system retrieves the subsequent set of items.

```TypeScript
cursor: endCursor,
take: 25
```

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

### Implementing Cursor-Based Pagination (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

Explains how to use the `cursor` option with `orderBy` in `getQuery` for efficient cursor-based pagination. This allows fetching items after a specific point, typically using a cursor retrieved from a previous result's `pageInfo.endCursor`.

```TypeScript
const usersAfterBob = await db.any(await sortableLoader.getQuery({
    orderBy: [["name", "DESC"], ["id", "ASC"]],
    cursor: "eyJuYW1lIjoiQm9iIiwiaWQiOjQ1fQ==",
    take: 5
}));
```

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

### Inefficient Remote Join in Virtual Field (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/virtual-columns.md

This example demonstrates an anti-pattern for fetching remote data within a virtual field. Using an `async resolve` function that performs a separate `fetchPostsForAuthor` call for each row can lead to N+1 query problems and significant performance degradation, especially with many rows.

```TypeScript
virtualFields: {
    posts: {
        dependencies: ["id"],
        // 📉 This can lead to performance issues.
        resolve: async (row) => {
            const posts = await fetchPostsForAuthor(row.id);
            return posts;
        }
    }
},
```

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

### Creating Table Loader with Multiple Context Providers (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/client-type-safety/cursor-pagination.md

This code defines `createTableLoader`, a factory function that sets up the initial state and creates multiple React Contexts for different parts of the table state (dependencies, pagination, and dispatch). The `ContextProvider` component uses `useReducer` to manage the combined state and provides these separate contexts, which helps prevent unnecessary re-renders for components that only depend on specific parts of the state.

```ts
export const createTableLoader = <TPayload extends Record<string, any>>() => {
  const initialState = {
    dependencies: [],
    pagination: initialCursorPagination,
  };
  const DependenciesContext = React.createContext([] as (keyof TPayload)[]);
  const PaginationContext = React.createContext(initialCursorPagination);
  const DispatchContext = React.createContext((() => {
    throw new Error("tableDataLoader Context provider not found!");
  }) as React.Dispatch<Action>);

  return {
    ContextProvider: ({ children }: { children: React.ReactNode }) => {
      const [state, dispatch] = React.useReducer(stateReducer, initialState);
      return (<DispatchContext.Provider value={dispatch}>
        <DependenciesContext.Provider value={state.dependencies}>
          <PaginationContext.Provider value={state.pagination}>
            {children}
          </PaginationContext.Provider>
        </DependenciesContext.Provider>
      </DispatchContext.Provider>)
    },
    // ...
```

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

### Defining a tRPC Query with postsLoader

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/trpc.md

This snippet defines a `getPosts` public procedure in tRPC. It uses `postsLoader.getLoadArgs()` to validate input and `postsLoader.loadPagination()` to fetch paginated data, passing the input and context. This allows for type-safe and structured data retrieval via the tRPC API.

```TypeScript
getPosts: publicProcedure
    .input(postsLoader.getLoadArgs())
    .query(({ input, ctx }) => {
        return postsLoader.loadPagination({
            ...input,
            ctx,
        });
    }),
```

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

### Configuring Loader for Cursor Pagination - TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/cursor-pagination.md

This snippet demonstrates how to configure a query loader for cursor-based pagination. It sets up `sortableColumns` for `id` and defines a default `orderBy` clause to ensure consistent sorting, which is crucial for cursor pagination.

```TypeScript
makeQueryLoader({
    ...options,
    sortableColumns: {
        id: sql.fragment`users.id`,
    },
    defaults: {
        orderBy: [['id', 'ASC']],
    },
})
```

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

### Adding Advanced Generic Filters for String and Count in Slonik-trpc

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/filtering.md

This example shows how to define more complex generic filters using `addGenericFilter`. It includes a `name_contains` filter for case-insensitive string matching (`ILIKE`) and a `postsCount_gt` filter for comparing a subquery's result (post count) against a numeric value. These filters provide powerful, customizable SQL generation.

```TypeScript
const userView = buildView`FROM users`
    .addGenericFilter('name_contains', (value: string) => sql.fragment`users.name ILIKE ${'%' + value + '%'}`)
    .addGenericFilter('postsCount_gt', (value: number) => sql.fragment`(
        SELECT COUNT(*) FROM posts
        WHERE posts.author_id = users.id
    ) > ${value}`)
```

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

### Building a Slonik-tRPC View with Filters in TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This TypeScript snippet demonstrates how to create a `slonik-trpc` view using `buildView` from a SQL `FROM` clause. It adds `addStringFilter` for `posts.title` to enable `_ilike` filtering and `addInArrayFilter` for `id` to allow filtering by an array of numeric IDs. This view forms the base for defining API endpoints.

```ts
import { buildView, sql } from 'slonik-trpc';

const postsView = buildView`
FROM users
LEFT JOIN posts
    ON posts.author = users.id`
// Allows filtering posts.title: { _ilike: '%hello%' }
.addStringFilter('posts.title')
// Allows filter id: [1, 2, 3]
.addInArrayFilter('id', () => sql.fragment`posts.id`, 'numeric')
```

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

### Creating a Type-Safe Query Loader with Slonik-tRPC and Zod

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This TypeScript code defines the return type of the SQL query using Zod for runtime validation and type inference. It then creates a `makeQueryLoader` instance, which acts as a type-safe API endpoint. This loader combines the defined `select` query and the `postsView` to enable structured data retrieval.

```ts
import { z } from 'zod';
import { sql, makeQueryLoader } from 'slonik-trpc';

const queryType = z.object({
    id: z.string(),
    author: z.string(),
    text: z.string(),
    age: z.number()
});
const selectQuery = sql.type(queryType)`
SELECT "posts".id
  , "users"."firstName" || ' ' || "users"."lastName" AS "author"
  , "posts"."text"
  , EXTRACT(DAYS FROM NOW() - posts."created_at") AS "age"
`

// This loader is the type-safe API
const loader = makeQueryLoader({
    db: slonikConnection, // Any connection pool will do
    query: {
        select: selectQuery,
        view: postsView
    }
});
```

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

### Efficient Batch Loading for Virtual Fields using load in TypeScript

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

This snippet demonstrates the recommended approach for loading remote data in virtual fields using the `load` function. It allows fetching data for multiple rows in a single batch, significantly improving performance by reducing database round trips. The `resolve` function then synchronously processes the pre-loaded data.

```TypeScript
virtualFields: {
    posts: {
        dependencies: ["id"],
        // 🚀 Use the load function to fetch data in batches
        async load(rows) {
            const allPosts = await fetchPostsForAuthors(rows.map(row => row.id));
            return allPosts;
        },
        // ✔️ Use the resolve function to handle synchronous operations.
        resolve: (row, args, posts) => {
            // Only return the posts of each user.
            return posts.filter(post => post.authorId = row.id)
        }
    }
},
```

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

### Loading Grouped Data with Filters in Slonik-TRPC

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/grouping.md

This snippet demonstrates how to load aggregated data using the previously defined `postsLoader`. It applies a `where` clause to filter for non-Gmail users, showcasing the reusability of existing filters with grouped queries. The result `groupedByName` will contain post counts for each qualifying user.

```TypeScript
// Get post counts of non-gmail users
const groupedByName = await postsLoader.load({
    where: {
        isGmail: false
    }
});
```

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

### Implementing a React Table Component with `createTableLoader` and tRPC

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/client-type-safety/table-selection.md

This snippet demonstrates how to use the `createTableLoader` to build an `EmployeeList` component. It initializes an `employeeTableLoader` instance for the `Employee` type, defines table columns using `createColumn` with specified data dependencies, and then uses `useVariables` to pass these dependencies to a tRPC query (`trpc.employees.getPaginated.useQuery`). Finally, it renders the data using the `CompactTable` component from `@table-library/react-table-library`.

```TypeScript
import React from 'react';
import { CompactTable } from '@table-library/react-table-library/compact';

import { trpc, type Employee } from '../../utils/trpc';

const employeeTableLoader = createTableLoader<Employee>();

export default function EmployeeList() {
  const employeeColumns = employeeTableLoader.useColumns([
    employeeTableLoader.createColumn({
        label: 'Name',
        dependencies: ["firstName", "lastName"],
        renderCell: (employee) => {
            return <div>{employee.firstName} {employee.lastName}</div>
        },
    }), employeeTableLoader.createColumn({
        label: 'Salary',
        dependencies: ["salary"],
        renderCell: (employee) => employee.salary,
    }), employeeTableLoader.createColumn({
        label: 'Start Date',
        dependencies: ["startDate"],
        renderCell: (employee) => employee.startDate,
    }), employeeTableLoader.createColumn({
        label: 'Company',
        dependencies: ["company"],
        renderCell: (employee) => employee.company,
    })
  ]);

  const pagination = employeeTableLoader.useVariables();

  const { data, isLoading } = trpc.employees.getPaginated.useQuery({
    take: 100,
    ...pagination,
  });

  if (!data) return null;

  return (
    <>
      <CompactTable columns={employeeColumns} data={data} />
    </>
  );
}
```

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

### Defining Sortable Columns for Query Loader (TypeScript)

Source: https://github.com/ardsh/slonik-trpc/blob/main/README.md

Explains how to define `sortableColumns` within `makeQueryLoader` using various formats (string, SQL fragment, or array) to enable sorting capabilities. It also demonstrates how to execute queries with single or multiple `orderBy` conditions.

```TypeScript
const sortableLoader = makeQueryLoader({
    query,
    sortableColumns: {
        id: sql.fragment`users.id`,
        name: "name",
        // All 3 methods are acceptable as long as the specified column is accessible from the FROM query and non-ambiguous
        createdAt: ["users", "created_at"]
    }
});

const sortedByName = await db.any(await sortableLoader.getQuery({
    orderBy: ["name", "ASC"]
}));

const sortedByNameAndDate = await db.any(await sortableLoader.getQuery({
    orderBy: [["name", "DESC"], ["id", "ASC"]],
    take: 5
}))
```

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

### Using Slow Query Plugin with slonik-trpc

Source: https://github.com/ardsh/slonik-trpc/blob/main/docs/docs/usage-main-features/plugins.md

This snippet demonstrates how to integrate the `useSlowQueryPlugin` with a `makeQueryLoader` to log queries that exceed a specified `slowQueryThreshold`. It logs the query's SQL, values, and execution duration to the console.

```typescript
import { useSlowQueryPlugin } from 'slonik-trpc/utils';

const postsLoader = makeQueryLoader({
    db,
    plugins: [
        useSlowQueryPlugin({
            slowQueryThreshold: 500,
            callback({ query, args, duration }) {
                console.log(`Slow query executed in ${duration}ms`);
                console.log(query.sql, query.values);
            }
        })
    ]
    filters: postsFilter,
});
```