# Snowflake Postgres Client

Execute SQL queries and statements against Snowflake databases using the PostgreSQL wire protocol, with full type safety and runtime validation.

Snowflake Postgres uses the same API contract as the PostgreSQL integration. If you are familiar with the Postgres client, this works identically.

## 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, snowflakePostgres } from "@superblocksteam/sdk-api";

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

const SalesSchema = z.object({
  region: z.string(),
  total_revenue: z.string(), // NUMERIC returned as string
  order_count: z.coerce.number(),
});

export default api({
  name: "GetSalesByRegion",
  integrations: {
    db: snowflakePostgres(SNOWFLAKE_PG),
  },
  input: z.object({
    region: z.string(),
  }),
  output: z.object({
    sales: z.array(SalesSchema),
  }),
  async run(ctx, { region }) {
    const sales = await ctx.integrations.db.query(
      "SELECT region, total_revenue, order_count FROM sales_summary WHERE region = $1",
      SalesSchema,
      [region],
    );

    return { sales };
  },
});
```

### Executing INSERT, UPDATE, DELETE Statements

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

const SNOWFLAKE_PG = "a1b2c3d4-5678-90ab-cdef-111111111111";

export default api({
  name: "ManageRecords",
  integrations: {
    db: snowflakePostgres(SNOWFLAKE_PG),
  },
  input: z.object({
    name: z.string(),
    email: z.string(),
  }),
  output: z.object({ success: z.boolean() }),

  async run(ctx, { 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 = CURRENT_TIMESTAMP() WHERE email = $1",
      [email],
    );
    console.log(`Updated ${updateResult.rowCount} rows`);

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

### Query with JOIN

```typescript
const OrderSchema = z.object({
  order_id: z.string(),
  customer_name: z.string(),
  total: z.string(), // NUMERIC as string
});

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

## 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" },
);

await ctx.integrations.db.execute(
  "DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP()",
  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

### Schema Parameter is Required

The `query()` method requires a Zod schema for runtime validation:

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

### NUMERIC/DECIMAL Values Returned as Strings

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

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

// CORRECT - Keep as string or transform
const schema = z.object({
  price: z.string().transform((val) => parseFloat(val)),
});
```

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

Use `z.coerce.number()` to convert:

```typescript
const StatsSchema = z.object({
  total_orders: z.coerce.number(),
  total_revenue: z.coerce.number(),
});
```

### Handling NULL Values

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

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

## Error Handling

### QueryValidationError

Thrown when query results fail schema validation:

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

- [Snowflake SQL Reference](https://docs.snowflake.com/en/sql-reference)
- [Snowflake Data Types](https://docs.snowflake.com/en/sql-reference/data-types)