# Lakebase Client

Execute SQL queries and statements against Lakebase 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, lakebase } from "@superblocksteam/sdk-api";

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

const UserSchema = z.object({
  id: z.string(),
  name: z.string(),
  email: z.string(),
  created_at: z.string(),
});

export default api({
  name: "GetUsers",
  integrations: {
    db: lakebase(LAKEBASE_DB),
  },
  input: z.object({
    status: z.string(),
  }),
  output: z.object({
    users: z.array(UserSchema),
  }),
  async run(ctx, { status }) {
    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, lakebase } from "@superblocksteam/sdk-api";

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

export default api({
  name: "ManageUsers",
  integrations: {
    db: lakebase(LAKEBASE_DB),
  },
  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 = NOW() WHERE email = $1",
      [email],
    );
    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 };
  },
});
```

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

### Uses `$1` Placeholders (PostgreSQL-Style)

Lakebase uses `$1, $2, ...` for parameter placeholders, following PostgreSQL conventions:

```typescript
// WRONG - Question-mark placeholders
const users = await ctx.integrations.db.query(
  "SELECT * FROM users WHERE name = ? AND status = ?",
  UserSchema,
  [name, status],
);

// CORRECT - Use $1, $2, ... placeholders
const users = await ctx.integrations.db.query(
  "SELECT * FROM users WHERE name = $1 AND status = $2",
  UserSchema,
  [name, status],
);
```

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

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