npm package: @rbxts/roblox-postgrest
Description: A TypeScript client library for PostgREST designed for Roblox games using roblox-ts.
Latest version: 1.1.3

# Roblox PostgrestREST SDK

A TypeScript client library for [PostgREST](https://postgrest.org/) designed for Roblox games using [roblox-ts](https://roblox-ts.com/).

This SDK allows you to interact with your PostgreSQL database through a PostgREST API within Roblox games, providing a simple and intuitive interface for CRUD operations.

## Installation

Install the package using npm:

```bash
npm install @rbxts/roblox-postgrest
```

## Overview

The Roblox PostgrestREST SDK provides a type-safe way to interact with your PostgreSQL database from within Roblox games. It offers a fluent interface for building queries and handling responses, with features including:

- Full CRUD operations (Create, Read, Update, Delete)
- Comprehensive filtering capabilities
- Pagination options
- Strongly typed responses
- Support for both standard PostgREST and Supabase backends

## Basic Usage

```typescript
import { PostgrestClient } from "@rbxts/roblox-postgrest";

// Initialize the client with your PostgREST URL and API token
const client = new PostgrestClient("https://your-postgrest-server.com", "your-api-token");

// Query a table from the public schema
const { success, data, error } = client
  .from("users")
  .select("id, username, email")
  .execute();

if (!success) {
  warn(`Error fetching users: ${error}`);
} else {
  print(`Found ${data?.size() || 0} users`);
  // Process the data
}
```

## Features

- **Fully typed**: Written in TypeScript for type safety
- **Query builder**: Intuitive query building with method chaining
- **Filtering**: Comprehensive filter operations (eq, gt, lt, like, etc.)
- **Pagination**: Support for limit/offset and range-based pagination
- **Multiple backend support**: Works with both standard PostgREST and Supabase backends
- **Full CRUD operations**: Insert, update, delete, and upsert capabilities
- **Array operations**: Specialized operators for PostgreSQL array columns
- **Full-text search**: Support for PostgreSQL's full-text search functionality
- **Error handling**: Consistent error reporting and response structure

## API Reference

### PostgrestClient

The main client to interact with your PostgREST API.

```typescript
const client = new PostgrestClient(baseUrl: string, apiKey: string);
```

#### Methods

- `auth(apiKey: string)`: Update the authentication token
- `from(tableName: string)`: Query the specified table
- `rpc(functionName: string)`: Call a PostgreSQL stored procedure

### SupabaseClient

A specialized client for Supabase backend.

```typescript
const client = new SupabaseClient(baseUrl: string, anonKey: string, userToken?: string);
```

#### Methods

- `auth(anonKey: string, userToken?: string)`: Update the authentication tokens
- `from(tableName: string)`: Query the specified table
- `rpc(functionName: string)`: Call a PostgreSQL stored procedure

### QueryBuilder

The builder for constructing database queries with various filters.

#### Selection

- `select(columns: string)`: Select specific columns (default: "*")

#### Filtering

- `eq(column: string, value: unknown)`: Equal to
- `gt(column: string, value: unknown)`: Greater than
- `gte(column: string, value: unknown)`: Greater than or equal to
- `lt(column: string, value: unknown)`: Less than
- `lte(column: string, value: unknown)`: Less than or equal to
- `neq(column: string, value: unknown)`: Not equal to
- `like(column: string, value: unknown)`: LIKE operator (case-sensitive)
- `ilike(column: string, value: unknown)`: ILIKE operator (case-insensitive)
- `in(column: string, values: unknown[])`: IN operator
- `is(column: string, value: undefined | boolean)`: IS operator for null/boolean checks
- `match(values: Record<string, unknown>)`: Match multiple columns
- `imatch(values: Record<string, unknown>)`: Case-insensitive match on multiple columns
- `not(fn: (f: FilterBuilder) => void)`: Negate a filter
- `or(...fns: ((f: FilterBuilder) => void)[])`: OR multiple filters
- `inAll(column: string, value: unknown[])`: Column must contain all values
- `inAny(column: string, value: unknown[])`: Column must contain any of the values

#### Full-text Search

- `fts(column: string, query: string, options?: { language?: string })`: Full-text search
- `plfts(column: string, query: string, options?: { language?: string })`: Plain full-text search
- `phfts(column: string, query: string, options?: { language?: string })`: Phrase full-text search
- `wfts(column: string, query: string, options?: { language?: string })`: WebSearch full-text search

#### Array Operations

- `cs(column: string, value: unknown[])`: Contains array
- `cd(column: string, value: unknown[])`: Contained in array
- `ov(column: string, value: unknown[])`: Overlaps with array
- `sl(column: string, value: unknown[])`: Array starts with
- `sr(column: string, value: unknown[])`: Array ends with
- `nxl(column: string, value: unknown[])`: Array does not start with
- `nxr(column: string, value: unknown[])`: Array does not end with
- `adj(column: string, value: unknown[])`: Array is adjacent to

#### Ordering

- `order(column: string, options?: { ascending?: boolean, nullsFirst?: boolean })`: Order results

#### Pagination

- `limit(count: number)`: Limit the number of rows returned
- `offset(count: number)`: Offset results by a number of rows
- `range(from: number, to: number)`: Range-based pagination

#### Read Operations

- `execute<T = unknown>(options?: ExecuteOptions)`: Execute the query and return all matching rows
- `single<T = unknown>()`: Execute the query and return the first row
- `maybeSingle<T = unknown>()`: Execute the query and return the first row, with error if multiple rows found

#### Write Operations

- `insert<T = unknown>(values: Record<string, unknown> | Record<string, unknown>[], options?: ExecuteOptions)`: Insert one or more records
- `update<T = unknown>(values: Record<string, unknown>, options?: ExecuteOptions)`: Update records that match the query filters
- `delete<T = unknown>(options?: ExecuteOptions)`: Delete records that match the query filters
- `upsert<T = unknown>(values: Record<string, unknown> | Record<string, unknown>[], options?: ExecuteOptions)`: Insert records if they don't exist, update them if they do

## Examples

### Basic CRUD Operations

#### Fetching Data

```typescript
// Get all users
const { success, data: users } = client.from("users").select().execute();

// Get specific columns
const { success, data } = client.from("users").select("id, username, email").execute();

// Get a single user
const { success, data: user } = client.from("users").eq("id", 123).single();
```

#### Inserting Data

```typescript
// Insert a single record
const { success, data, error } = client
  .from("users")
  .insert({
    username: "newuser",
    email: "user@example.com",
    created_at: os.time()
  });

// Insert multiple records
const { success, data, error } = client
  .from("products")
  .insert([
    { name: "Product 1", price: 29.99 },
    { name: "Product 2", price: 39.99 }
  ]);
```

#### Updating Data

```typescript
// Update a record
const { success, data, error } = client
  .from("users")
  .eq("id", 123)
  .update({ status: "active", last_login: os.time() });

// Update with returning minimal data
const { success, data, error } = client
  .from("products")
  .eq("id", 456)
  .update(
    { stock: 100 },
    { returning: "minimal" }
  );
```

#### Deleting Data

```typescript
// Delete records
const { success, data, error } = client
  .from("temp_logs")
  .lt("created_at", os.time() - 86400) // Older than 1 day
  .delete();

// Delete with count
const { success, data, error, count } = client
  .from("archived_items")
  .eq("status", "expired")
  .delete({ count: "exact" });
```

#### Upsert Operations

```typescript
// Upsert a record (insert if not exists, update if exists)
const { success, data, error } = client
  .from("profiles")
  .upsert(
    { user_id: 123, username: "updated_user", bio: "New bio" },
    { onConflict: "user_id" }
  );
```

#### Filtering Data

```typescript
// Basic filters
const { success, data } = client
  .from("products")
  .gte("price", 100)
  .lt("price", 200)
  .execute();

// AND conditions are applied by default
const { success, data: activeExpensiveProducts } = client
  .from("products")
  .eq("active", true)
  .gt("price", 100)
  .execute();

// OR conditions
const { success, data } = client
  .from("products")
  .or((f) => f.eq("category", "electronics"), (f) => f.eq("category", "computers"))
  .execute();

// Complex filtering with NOT
const { success, data } = client
  .from("products")
  .not(f => f.eq("status", "discontinued"))
  .execute();

// Full text search
const { success, data } = client
  .from("articles")
  .fts("content", "search terms", { language: "english" })
  .execute();
```

#### Pagination

```typescript
// Limit and offset
const { success, data: page1 } = client.from("products").limit(20).execute();
const { success, data: page2 } = client.from("products").limit(20).offset(20).execute();

// Range based pagination
const { success, data } = client.from("products").range(0, 9).execute(); // First 10 items
```

#### Ordering

```typescript
// Order by a column (ascending by default)
const { success, data } = client.from("products").order("price").execute();

// Descending order
const { success, data } = client
  .from("products")
  .order("price", { ascending: false })
  .execute();

// Order with null handling
const { success, data } = client
  .from("products")
  .order("price", { ascending: true, nullsFirst: false })
  .execute();

// Multiple ordering criteria
const { success, data } = client
  .from("products")
  .order("category")
  .order("price", { ascending: false })
  .execute();
```

### Working with Arrays

```typescript
// Find records where the tags array contains specific values
const { success, data } = client
  .from("posts")
  .cs("tags", ["important", "announcement"])
  .execute();

// Find records where the post tags overlap with a set of tags
const { success, data } = client
  .from("posts")
  .ov("tags", ["tutorial", "guide"])
  .execute();
```

### Error Handling

```typescript
// Complete error handling
const { success, data, error, status, statusMessage } = client
  .from("users")
  .select("id, username")
  .execute();

if (!success) {
  warn(`Error ${status} ${statusMessage}: ${error}`);
} else {
  print(`Successfully fetched ${data?.size() || 0} users`);
  // Process the data
}
```

### Using Supabase Client

```typescript
// Create a new Supabase client
const client = new SupabaseClient('https://your-supabase-instance.supabase.co', 'your-anon-key');

// You can update tokens as needed
client.auth('updated-anon-key', 'user-token');

// Query data just like with PostgrestClient
const { success, data, error } = client
  .from('users')
  .select('id, username, email')
  .execute();
```

## Response Structure

All query methods return a response with the following structure:

```typescript
interface ExecuteResponse<T> {
  // Whether the request was successful
  success: boolean;
  // Response data if successful
  data: T | undefined;
  // Error message if failed
  error: string | undefined;
  // Count of affected/returned rows when requested
  count: number | undefined;
  // HTTP status code
  status: number;
  // HTTP status message
  statusMessage: string;
}
```

## Execute Options

Many query methods accept an options object with the following properties:

```typescript
interface ExecuteOptions {
  // Type of count to return: exact, planned, or estimated
  count?: "exact" | "planned" | "estimated";
  // Whether to execute a HEAD request instead of GET
  head?: boolean;
  // What to return after mutations: minimal or representation
  returning?: "minimal" | "representation";
  // Column to handle conflict on when upserting
  onConflict?: string;
}
```

## License

[MIT](https://gitlab.waffly.xyz/rbxts/roblox-postgrest/-/blob/main/LICENSE)

## Contributing

Contributions are welcome! Please feel free to submit a Pull Request.