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:

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

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.

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.

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

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

// Insert a single record
const { success, data, error } = client
  .from("users")
  .insert({
    username: "newuser",
    email: "[email protected]",
    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

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

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

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

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

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

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

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

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

// 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:

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:

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.