Roblox PostgrestREST SDK

A TypeScript client library for PostgREST designed for Roblox games using roblox-ts.

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

$3

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

$3

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

$3

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): Match multiple columns
- imatch(values: Record): 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(options?: ExecuteOptions): Execute the query and return all matching rows
- single(): Execute the query and return the first row
- maybeSingle(): Execute the query and return the first row, with error if multiple rows found

#### Write Operations

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

Examples

$3

#### 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();
`

$3

`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();
`

$3

`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
}
`

$3

`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 {
// 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

Contributing

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