# PostgreSQL Client

Execute SQL queries and statements against PostgreSQL databases with full type safety and runtime validation.

## Methods

| Method                                      | Description                                                                |
| ------------------------------------------- | -------------------------------------------------------------------------- |
| `query<T>(sql, schema, params?, metadata?)` | Execute a SELECT query and return validated, typed results                 |
| `execute(sql, params?, metadata?)`          | Execute a statement (INSERT, UPDATE, DELETE) and return affected row count |

## Usage

### Basic Query with Schema Validation

```typescript
import { api, z, postgres } from "@superblocksteam/sdk-api";

// Integration ID from the integrations panel
const PROD_POSTGRES = "a1b2c3d4-5678-90ab-cdef-111111111111";

const UserSchema = z.object({
  id: z.string(),
  name: z.string(),
  email: z.string().email(),
  created_at: z.string(), // Timestamps returned as ISO strings
});

export default api({
  name: "GetUsersByStatus",

  // Declare integrations upfront
  integrations: {
    db: postgres(PROD_POSTGRES),
  },

  input: z.object({
    status: z.string(),
  }),
  output: z.object({
    users: z.array(UserSchema),
  }),
  async run(ctx, { status }) {
    // ctx.integrations.db is fully typed as PostgresClient
    const users = await ctx.integrations.db.query(
      "SELECT id, name, email, created_at FROM users WHERE status = $1",
      UserSchema,
      [status],
    );

    return { users };
  },
});
```

### Executing INSERT, UPDATE, DELETE Statements

```typescript
import { api, z, postgres } from "@superblocksteam/sdk-api";

// Integration ID from the integrations panel
const PROD_POSTGRES = "a1b2c3d4-5678-90ab-cdef-111111111111";

export default api({
  name: "ManageUsers",

  integrations: {
    db: postgres(PROD_POSTGRES),
  },

  input: z.object({
    userId: z.string(),
    name: z.string(),
    email: z.string(),
  }),
  output: z.object({ success: z.boolean() }),

  async run(ctx, { userId, name, email }) {
    // INSERT
    await ctx.integrations.db.execute(
      "INSERT INTO users (name, email) VALUES ($1, $2)",
      [name, email],
    );

    // UPDATE
    const updateResult = await ctx.integrations.db.execute(
      "UPDATE users SET last_login = NOW() WHERE id = $1",
      [userId],
    );
    console.log(`Updated ${updateResult.rowCount} rows`);

    // DELETE
    const deleteResult = await ctx.integrations.db.execute(
      "DELETE FROM sessions WHERE expires_at < NOW()",
    );
    console.log(`Deleted ${deleteResult.rowCount} expired sessions`);

    return { success: true };
  },
});
```

### Array Parameters

Use arrays with PostgreSQL's `ANY()` operator to filter by multiple values:

```typescript
// Filter by multiple IDs
const items = await ctx.integrations.db.query(
  "SELECT * FROM items WHERE id = ANY($1::int[])",
  ItemSchema,
  [[1, 2, 3]], // Array of integers
);

// Filter by multiple statuses
const users = await ctx.integrations.db.query(
  "SELECT * FROM users WHERE status = ANY($1::text[])",
  UserSchema,
  [["active", "pending"]], // Array of strings
);

// Multiple parameters including arrays
const orders = await ctx.integrations.db.query(
  "SELECT * FROM orders WHERE id = ANY($1::int[]) AND status = $2",
  OrderSchema,
  [[100, 101, 102], "shipped"], // Array + scalar
);
```

Arrays are automatically serialized to PostgreSQL's `ARRAY[...]` syntax:

- `[1, 2, 3]` → `ARRAY[1,2,3]`
- `['a', 'b']` → `ARRAY['a','b']`
- `[[1, 2], [3, 4]]` → `ARRAY[ARRAY[1,2],ARRAY[3,4]]` (nested arrays)

### Query with JOIN and Complex Types

```typescript
const OrderWithCustomerSchema = z.object({
  order_id: z.string(),
  order_total: z.string(), // NUMERIC/DECIMAL returned as strings
  customer_name: z.string(),
  customer_email: z.string().email(),
});

const orders = await ctx.integrations.db.query(
  `SELECT
    o.id as order_id,
    o.total as order_total,
    c.name as customer_name,
    c.email as customer_email
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  WHERE o.status = $1`,
  OrderWithCustomerSchema,
  ["pending"],
);
```

### Handling Nullable Columns

```typescript
const UserWithOptionalFieldsSchema = z.object({
  id: z.string(),
  name: z.string(),
  bio: z.string().nullable(), // Column can be NULL
  avatar_url: z.string().nullable(),
});

const users = await ctx.integrations.db.query(
  "SELECT id, name, bio, avatar_url FROM users",
  UserWithOptionalFieldsSchema,
);
```

## Trace Metadata

All methods accept an optional `metadata` parameter as the last argument for diagnostics labeling:

```typescript
const users = await ctx.integrations.db.query(
  "SELECT * FROM users WHERE status = $1",
  UserSchema,
  ["active"],
  {
    label: "Fetch active users",
    description: "Paginated user list for dashboard",
  },
);

await ctx.integrations.db.execute(
  "DELETE FROM sessions WHERE expires_at < NOW()",
  undefined, // no params
  { label: "Clean expired sessions" },
);
```

When `includeDiagnostics` is enabled, `label` and `description` appear in the trace view. See the [root SDK README](../../../README.md#trace-metadata) for details.

## Common Pitfalls

### Aggregate Functions (COUNT, SUM) Return Strings

PostgreSQL aggregate functions like `COUNT(*)` return `BIGINT`, which is serialized as a string. Use `z.coerce.number()` to convert to a number:

```typescript
// Query: SELECT COUNT(*) as total FROM orders WHERE status = 'pending'

// WRONG - COUNT returns a string, not a number
const schema = z.object({
  total: z.number(), // Fails validation
});

// CORRECT - Use z.coerce.number() to convert string to number
const schema = z.object({
  total: z.coerce.number(),
});

// Example usage
const StatsSchema = z.object({
  total_orders: z.coerce.number(),
  total_revenue: z.coerce.number(), // SUM also returns string for BIGINT/NUMERIC
});

const stats = await ctx.integrations.db.query(
  `SELECT
    COUNT(*) as total_orders,
    SUM(amount) as total_revenue
  FROM orders
  WHERE created_at > $1`,
  StatsSchema,
  [startDate],
);
```

### BIGINT Values Returned as Strings

PostgreSQL `BIGINT` columns (8-byte integers) are returned as strings in JavaScript because they can exceed `Number.MAX_SAFE_INTEGER` (2^53 - 1 = 9,007,199,254,740,991).

```typescript
// Schema for a table with: id BIGINT, count BIGINT

// WRONG - Will fail validation for large values
const schema = z.object({
  id: z.number(),
  count: z.number(),
});

// CORRECT - Handle as strings
const schema = z.object({
  id: z.string(),
  count: z.string(),
});

// CORRECT - Coerce to number (safe for values < MAX_SAFE_INTEGER)
const schema = z.object({
  id: z.coerce.number(),
  count: z.coerce.number(),
});

// CORRECT - Parse as BigInt for truly large values
const schema = z.object({
  id: z.string().transform((val) => BigInt(val)),
  count: z.string().transform((val) => BigInt(val)),
});
```

### NUMERIC/DECIMAL Values Returned as Strings

Large `NUMERIC` and `DECIMAL` columns are also returned as strings to preserve precision:

```typescript
// For a column: price NUMERIC(10,2)

// WRONG - May lose precision
const schema = z.object({ price: z.number() });

// CORRECT - Keep as string and parse when needed
const schema = z.object({ price: z.string() });

// CORRECT - Transform to number if precision loss is acceptable
const schema = z.object({
  price: z.string().transform((val) => parseFloat(val)),
});
```

### Schema Parameter is Required

The `query()` method requires a Zod schema for runtime validation. This is by design to ensure type safety.

```typescript
// WRONG - Missing schema parameter
const users = await ctx.integrations.db.query(
  "SELECT * FROM users",
  [
    /* params */
  ], // This is wrong - params are 3rd argument
);

// CORRECT - Schema is the second parameter
const users = await ctx.integrations.db.query(
  "SELECT * FROM users WHERE id = $1",
  UserSchema, // Schema is required
  [userId], // Params are optional, third argument
);
```

### Use Parameterized Queries to Prevent SQL Injection

Always use `$1, $2, ...` placeholders instead of string interpolation:

```typescript
// WRONG - SQL injection vulnerability
const users = await ctx.integrations.db.query(
  `SELECT * FROM users WHERE name = '${userName}'`, // DANGEROUS!
  UserSchema,
);

// CORRECT - Use parameterized queries
const users = await ctx.integrations.db.query(
  "SELECT * FROM users WHERE name = $1",
  UserSchema,
  [userName], // Safe - value is escaped
);
```

### Handling NULL Values

PostgreSQL NULL values need explicit handling in your schema:

```typescript
// WRONG - Will fail if column contains NULL
const schema = z.object({
  name: z.string(),
  bio: z.string(), // Fails if bio is NULL
});

// CORRECT - Mark nullable columns
const schema = z.object({
  name: z.string(),
  bio: z.string().nullable(), // Accepts NULL
});

// CORRECT - Provide default for NULL
const schema = z.object({
  name: z.string(),
  bio: z.string().nullable().default(""), // NULL becomes ""
});
```

### Timestamp and Date Handling

PostgreSQL timestamps are returned as ISO 8601 strings:

```typescript
const schema = z.object({
  created_at: z.string(), // "2024-01-15T10:30:00.000Z"

  // Or transform to Date object
  updated_at: z.string().transform((val) => new Date(val)),

  // Or use Zod's coerce
  expires_at: z.coerce.date(),
});
```

## Error Handling

### QueryValidationError

Thrown when query results fail schema validation. Contains details about which row failed and why:

```typescript
import { QueryValidationError } from "@superblocksteam/sdk-api";

try {
  const users = await ctx.integrations.db.query(
    "SELECT * FROM users",
    UserSchema,
  );
} catch (error) {
  if (error instanceof QueryValidationError) {
    console.error("Row index:", error.details.rowIndex);
    console.error("Validation errors:", error.details.errors);
    console.error("Actual row data:", error.details.row);
  }
}
```

## API Reference

- [PostgreSQL Documentation](https://www.postgresql.org/docs/)
- [PostgreSQL Data Types](https://www.postgresql.org/docs/current/datatype.html)