Claude Skill

api-baas-planetscale

Serverless MySQL platform with branching, deploy requests, and edge-compatible driver

LLM Mart · 0 points · 0 views 0 listing impressions 0 install-command copies
Virus-scanned Reviewed automatically before listing.

Full trust report

Download agents-inc-skills-dist_plugins_api-baas-planetscale_skills_api-baas-planetscale-3a51ef5.zip · 18 KB
agents-inc/skills 24 8 forks MIT Updated 23d ago
Part of agents-inc/skills — 130 skills

Install

skills CLI npx skills add https://github.com/agents-inc/skills/tree/main/dist/plugins/api-baas-planetscale/skills/api-baas-planetscale
Claude Code claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install agents-inc-skills@llmmart
Git git clone https://github.com/agents-inc/skills.git

The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole agents-inc/skills collection as a plugin from our marketplace. Git is the plain clone.

Skill manifest

PlanetScale Serverless MySQL Patterns

Quick Guide: Use @planetscale/database for edge/serverless MySQL access via HTTP (Fetch API). Use Client to create per-request connections, conn.execute() for parameterized queries, and conn.transaction() for atomic operations. Never run DDL directly on production -- use deploy requests with safe migrations enabled. PlanetScale runs on Vitess: foreign keys are supported but opt-in, stored procedures are not supported, and all schema changes go through online DDL. The built-in cast handles regular integers and floats automatically, but provide a custom cast for BigInt, Date, and boolean columns. Branch your database like git branches for dev/preview environments.

<critical_requirements>

CRITICAL: Before Using This Skill

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(You MUST use conn.execute(sql, params) with parameterized queries -- never interpolate user input into SQL strings)

(You MUST use deploy requests for ALL schema changes on production branches with safe migrations enabled -- direct DDL is rejected)

(You MUST create a fresh Client.connection() per request in serverless environments -- do not reuse connections across invocations)

(You MUST handle the Vitess/MySQL compatibility differences: no stored procedures, no RENAME COLUMN via direct DDL, no := operator, no LOAD DATA INFILE)

(You MUST provide a custom cast function for BigInt (INT64/UINT64), Date (DATETIME/TIMESTAMP), and boolean (TINYINT(1)) columns -- the default cast handles regular integers and floats but leaves these as strings)

</critical_requirements>

Auto-detection: PlanetScale, @planetscale/database, planetscale serverless driver, pscale, deploy request, safe migrations, Vitess, database branching, planetscale branch, planetscale boost, mysql serverless, pscale CLI, planetscale connection

When to use:

  • Querying MySQL from edge/serverless functions via the PlanetScale serverless driver
  • Managing schema changes through deploy requests and safe migrations
  • Creating database branches for dev, preview, or CI environments
  • Setting up connections with @planetscale/database (host/username/password or URL)
  • Running transactions in serverless contexts
  • Handling Vitess-specific SQL compatibility constraints
  • Programmatic branch management via pscale CLI

Key patterns covered:

  • connect() / Client connection setup with host, username, password
  • conn.execute() with positional (?) and named (:param) parameters
  • conn.transaction() for atomic multi-statement operations
  • Custom cast functions for type-safe value conversion (BigInt, Date, boolean)
  • Deploy request workflow (branch, change schema, create DR, review, deploy)
  • Safe migrations and the no-direct-DDL enforcement model
  • Database branching for dev/preview/CI environments
  • Vitess SQL compatibility constraints and workarounds
  • pscale CLI for branch and deploy request management

When NOT to use:

  • Long-running server processes with persistent TCP MySQL connections (use mysql2 driver)
  • Complex ORM-specific patterns (use your ORM's own skill)
  • General MySQL query syntax (use a SQL/MySQL skill)
  • PostgreSQL workloads (use Neon or another Postgres provider)

Detailed Resources:

  • For decision frameworks, CLI reference, and quick lookup tables, see reference.md

Driver & Queries:

  • examples/core.md -- Connection setup, parameterized queries, transactions, type casting

Branching & Schema Changes:

<decision_framework>

Decision Framework

Connection Method

What is the runtime environment?
+-- Edge/serverless (Cloudflare Workers, Vercel Edge, etc.)
|   +-- Use @planetscale/database (HTTP-based, no TCP needed)
+-- Traditional Node.js server (always-on)
|   +-- Need PlanetScale branching/deploy workflow?
|   |   +-- YES --> @planetscale/database works fine (HTTP)
|   |   +-- NO --> mysql2 driver with TCP may be simpler
+-- ORM integration?
    +-- Check your ORM's docs for its PlanetScale/serverless adapter

connect() vs Client

How many connections per process?
+-- Single connection (scripts, simple handlers) --> connect()
+-- Multiple connections (serverless, per-request) --> Client + client.connection()

Schema Change Strategy

Is the target branch a production branch with safe migrations?
+-- YES --> Deploy requests ONLY (direct DDL is rejected)
|   +-- Simple change (add column, add index) --> Standard deploy request
|   +-- Needs controlled cutover timing --> Gated deployment (--disable-auto-apply)
|   +-- Instant-eligible change --> Deploy with --instant flag
+-- NO (development branch) --> Direct DDL is allowed
    +-- Experimenting --> pscale shell <db> <branch>
    +-- Scripted migration --> Connect to branch, run DDL

Foreign Keys

Do you need foreign key constraints?
+-- YES --> Enable in database settings (opt-in)
|   +-- Aware of limitations?
|   |   +-- Deploy requests don't validate existing referential integrity
|   |   +-- Reverts can create orphaned rows
|   |   +-- Performance impact in high-concurrency workloads
|   +-- Sharded database? --> FK only supported on unsharded databases
+-- NO --> Use application-level referential integrity
    +-- ORM-level relationship definitions
    +-- Application validation before INSERT/DELETE

</decision_framework>

<red_flags>

RED FLAGS

High Priority Issues:

  • String interpolation in SQL -- conn.execute(\SELECT * FROM users WHERE id = '$'`)bypasses parameterization. Always use?or:param` placeholders with the params argument.
  • Direct DDL on production with safe migrations -- ALTER TABLE statements are silently rejected on production branches with safe migrations enabled. All schema changes must go through deploy requests.
  • No custom cast for BigInt/Date columns -- The default cast handles regular integers and floats, but INT64/UINT64 remain as strings and DATETIME/TIMESTAMP are not converted to Date objects. Provide a custom cast for these types.

Medium Priority Issues:

  • Reusing connections across serverless invocations -- Each serverless invocation gets a fresh execution context. Do not store connection state in global variables expecting it to persist.
  • Using RENAME COLUMN in deploy requests -- Column renames can be destructive through Vitess online DDL. Use the three-step pattern: add new column, migrate data, drop old column.
  • Missing revert window awareness -- Deploy requests can be reverted within 30 minutes. After that window closes, you must create a new deploy request to undo changes. Plan accordingly.
  • Foreign keys enabled without understanding implications -- FK constraints on PlanetScale don't validate existing referential integrity during ALTER TABLE ADD FOREIGN KEY. Orphaned rows will silently remain.

Common Mistakes:

  • Wrong package name -- The package is @planetscale/database, not planetscale, mysql-planetscale, or @planetscale/serverless.
  • Expecting connection pooling in the driver -- @planetscale/database does not do client-side connection pooling. PlanetScale handles pooling at the infrastructure level (Vitess VTTablet + Global Routing). Do not wrap it in a pool library.
  • Using positional and named params together -- A single execute() call uses either ? with an array OR :param with an object. Never mix them.
  • Expecting Node.js mysql2 compatibility -- @planetscale/database has a different API from mysql2. There is no pool.query(), no connection.query(). The API is conn.execute(sql, params).
  • Running CREATE DATABASE or DROP DATABASE -- Database creation/deletion is managed via the PlanetScale dashboard, API, or pscale CLI, not SQL.

Gotchas & Edge Cases:

  • INT64/UINT64 and dates remain as strings with the default cast -- SELECT count(*) as total returns { total: 42 } (INT64 is an exception -- it stays as "42" string). DATETIME returns "2024-01-15 10:30:00". Regular INT32 and FLOAT types are auto-converted.
  • rowsAffected is 0 for SELECT -- Only DML statements (INSERT, UPDATE, DELETE) populate rowsAffected. For SELECT, check rows.length or size.
  • insertId is a string -- Even though MySQL auto-increment IDs are integers, insertId in the result is always a string. Cast if needed: BigInt(result.insertId).
  • Transactions over HTTP are not interactive -- Unlike traditional MySQL transactions, PlanetScale's HTTP transactions send all statements in a single request. You CAN use conditional logic within the transaction() callback (it runs client-side), but each tx.execute() is an HTTP round trip.
  • DATETIME values lack timezone -- MySQL DATETIME is stored without timezone info. The driver returns it as a string like "2024-01-15 10:30:00". Append "Z" when parsing as UTC, or handle timezone explicitly.
  • 64KB query limit per execute -- Individual SQL statements have a size limit. For bulk inserts, batch into multiple execute() calls.
  • SQL mode is session-only -- SET sql_mode = '...' only lasts for the current connection. On PlanetScale's HTTP driver, that means a single request. Global SQL mode changes are not allowed.
  • PlanetScale Boost requires explicit opt-in -- Boost query caching is available on Scaler Pro plans and above. Enable per-query via @@boost_cached_queries = true in a session SET before the boosted query. Not all queries are eligible.
  • Empty schemas are invalid -- Production branches require at least one table. You cannot have an empty database on a production branch.
  • Instant deployments cannot be reverted -- Using --instant on a deploy request uses MySQL's ALGORITHM=INSTANT and skips the revert window entirely.

</red_flags>

<critical_reminders>

CRITICAL REMINDERS

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(You MUST use conn.execute(sql, params) with parameterized queries -- never interpolate user input into SQL strings)

(You MUST use deploy requests for ALL schema changes on production branches with safe migrations enabled -- direct DDL is rejected)

(You MUST create a fresh Client.connection() per request in serverless environments -- do not reuse connections across invocations)

(You MUST handle the Vitess/MySQL compatibility differences: no stored procedures, no RENAME COLUMN via direct DDL, no := operator, no LOAD DATA INFILE)

(You MUST provide a custom cast function for BigInt (INT64/UINT64), Date (DATETIME/TIMESTAMP), and boolean (TINYINT(1)) columns -- the default cast handles regular integers and floats but leaves these as strings)

Failure to follow these rules will cause SQL injection vulnerabilities, failed deploy requests, or silent type coercion bugs.

</critical_reminders>

Files (skills)
  • examples
    • branching.md 11.8 KB 
      # PlanetScale -- Branching & Schema Changes

> Database branching, deploy requests, safe migrations, and CI/CD workflows. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites:** Understand connection setup and query patterns from [core.md](core.md) first.

---

## Pattern 1: Dev Branch Workflow

### Good Example -- Feature Development Branch

```bash
# Create a branch for a feature
pscale branch create my-database feat-user-profiles

# Open interactive MySQL shell on the branch
pscale shell my-database feat-user-profiles

# In the shell: make schema changes directly (DDL allowed on dev branches)
# mysql> CREATE TABLE user_profiles (
# mysql>   id BIGINT AUTO_INCREMENT PRIMARY KEY,
# mysql>   user_id BIGINT NOT NULL,
# mysql>   bio TEXT,
# mysql>   avatar_url VARCHAR(512),
# mysql>   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
# mysql>   INDEX idx_user_id (user_id)
# mysql> );

# When ready: create a deploy request to merge into main
pscale deploy-request create my-database feat-user-profiles --into main

# Review the diff
pscale deploy-request diff my-database 1

# Deploy
pscale deploy-request deploy my-database 1

# Clean up the branch
pscale branch delete my-database feat-user-profiles
```

**Why good:** Schema changes are isolated on the development branch, deploy request provides a reviewable diff before touching production, `pscale shell` gives a MySQL-compatible prompt for interactive DDL, clean deletion after merge

---

## Pattern 2: Safe Column Rename (Three-Step Pattern)

PlanetScale's online DDL via Vitess does not safely support `ALTER TABLE ... RENAME COLUMN`. The safe approach uses three deploy requests.

### Good Example -- Renaming `name` to `full_name`

```bash
# Step 1: Add the new column
pscale branch create my-database rename-step-1
pscale shell my-database rename-step-1
# mysql> ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
pscale deploy-request create my-database rename-step-1 --into main
pscale deploy-request deploy my-database 1

# Step 2: Migrate data and update application to write to both columns
# In your application code:
#   INSERT INTO users (name, full_name, ...) VALUES (?, ?, ...)
#   UPDATE users SET full_name = name WHERE full_name IS NULL;
# Deploy application changes, then backfill:
pscale branch create my-database rename-step-2
pscale shell my-database rename-step-2
# mysql> UPDATE users SET full_name = name WHERE full_name IS NULL;
pscale deploy-request create my-database rename-step-2 --into main
pscale deploy-request deploy my-database 2

# Step 3: Drop the old column (after application no longer reads from it)
pscale branch create my-database rename-step-3
pscale shell my-database rename-step-3
# mysql> ALTER TABLE users DROP COLUMN name;
pscale deploy-request create my-database rename-step-3 --into main
pscale deploy-request deploy my-database 3
```

**Why good:** Each step is independently deployable and revertable, no data loss at any point, application can be updated between steps, backward-compatible at every stage

**When to use:** Any column rename on a production branch with safe migrations enabled. This is the Vitess-safe pattern.

---

## Pattern 3: PR Preview Branches with GitHub Actions

### Good Example -- Create Branch on PR Open, Delete on Close

```yaml
# .github/workflows/preview-db.yml
name: Preview Database Branch

on:
  pull_request:
    types: [opened, reopened, closed]

env:
  PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
  DATABASE_NAME: my-database

jobs:
  create-branch:
    if: github.event.action != 'closed'
    runs-on: ubuntu-latest
    steps:
      - name: Install pscale CLI
        run: |
          curl -sL https://github.com/planetscale/cli/releases/latest/download/pscale_linux_amd64.tar.gz | tar xz
          sudo mv pscale /usr/local/bin/

      - name: Create preview branch
        run: |
          pscale branch create $DATABASE_NAME preview-pr-${{ github.event.number }} \
            --org ${{ secrets.PLANETSCALE_ORG }} \
            || echo "Branch may already exist"

      - name: Get connection credentials
        id: creds
        run: |
          CREDS=$(pscale password create $DATABASE_NAME preview-pr-${{ github.event.number }} \
            ci-password-${{ github.run_id }} \
            --org ${{ secrets.PLANETSCALE_ORG }} \
            --format json)
          echo "host=$(echo $CREDS | jq -r '.access_host_url')" >> $GITHUB_OUTPUT
          echo "username=$(echo $CREDS | jq -r '.username')" >> $GITHUB_OUTPUT
          echo "password=$(echo $CREDS | jq -r '.plain_text')" >> $GITHUB_OUTPUT

      - name: Run migrations
        env:
          DATABASE_HOST: ${{ steps.creds.outputs.host }}
          DATABASE_USERNAME: ${{ steps.creds.outputs.username }}
          DATABASE_PASSWORD: ${{ steps.creds.outputs.password }}
        run: npx your-migration-tool migrate

  delete-branch:
    if: github.event.action == 'closed'
    runs-on: ubuntu-latest
    steps:
      - name: Install pscale CLI
        run: |
          curl -sL https://github.com/planetscale/cli/releases/latest/download/pscale_linux_amd64.tar.gz | tar xz
          sudo mv pscale /usr/local/bin/

      - name: Delete preview branch
        run: |
          pscale branch delete $DATABASE_NAME preview-pr-${{ github.event.number }} \
            --org ${{ secrets.PLANETSCALE_ORG }} \
            --force \
            || echo "Branch may already be deleted"
```

**Why good:** Branch lifecycle tied to PR lifecycle, `reopened` event handles re-opened PRs, `--force` on delete avoids confirmation prompts in CI, `|| echo` prevents failures if branch already exists/deleted, service token auth for non-interactive CI, unique password name per run prevents collisions

---

## Pattern 4: Gated Deployment Workflow

### Good Example -- Coordinated Schema + Application Deploy

```bash
# 1. Create and deploy schema change with manual cutover
pscale deploy-request create my-database add-user-roles --into main --disable-auto-apply
pscale deploy-request deploy my-database 1
# Schema migration runs in background (online DDL) but table swap is held

# 2. Deploy application code that handles both old and new schema
# ... deploy your application ...

# 3. When ready, apply the cutover (table swap happens)
pscale deploy-request apply my-database 1

# 4. If issues found within 30 minutes, revert
pscale deploy-request revert my-database 1

# 5. If all good, skip the revert period to finalize
pscale deploy-request skip-revert my-database 1
```

**Why good:** Schema migration completes in background without blocking reads/writes, manual cutover lets you coordinate with application deployment, 30-minute revert window provides a safety net, skip-revert releases resources early when confident

**When to use:** Large schema changes (adding indexes, altering column types) that need coordination with application code changes. Also useful for high-traffic databases where you want control over cutover timing.

---

## Pattern 5: Safe Migrations Setup

### Good Example -- Enabling Safe Migrations on Production

```bash
# Enable safe migrations on your production branch
pscale branch safe-migrations enable my-database main

# Now, direct DDL on main is rejected:
pscale shell my-database main
# mysql> ALTER TABLE users ADD COLUMN age INT;
# ERROR: DDL statements are not allowed on branches with safe migrations enabled.
# Use deploy requests to make schema changes.

# Verify safe migrations status
pscale branch show my-database main
# Look for: safe_migrations: true
```

**Why good:** Prevents accidental DDL on production, forces all schema changes through the deploy request review workflow

#### What Safe Migrations Block

- `CREATE TABLE`
- `ALTER TABLE`
- `DROP TABLE`
- `CREATE INDEX` / `DROP INDEX`
- `TRUNCATE TABLE`

#### What Still Works

- All DML: `SELECT`, `INSERT`, `UPDATE`, `DELETE`
- `SET` (session variables)
- `SHOW`, `DESCRIBE`, `EXPLAIN`

---

## Pattern 6: Foreign Key Constraints Setup

### Good Example -- Enabling and Using Foreign Keys

```bash
# Enable FK support in database settings (via dashboard or API)
# Dashboard: Database Settings > Enable foreign key constraints

# Then create tables with FKs on a development branch
pscale branch create my-database add-fk-constraints
pscale shell my-database add-fk-constraints
```

```sql
-- Create parent table
CREATE TABLE authors (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

-- Create child table with foreign key
CREATE TABLE posts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  CONSTRAINT fk_posts_author FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);
```

```bash
# Deploy via deploy request
pscale deploy-request create my-database add-fk-constraints --into main
pscale deploy-request deploy my-database 1
```

**Why good:** FK constraint named explicitly (`fk_posts_author`), `ON DELETE CASCADE` handles cleanup, deployed through proper deploy request workflow

#### Foreign Key Limitations on PlanetScale

- **Opt-in only**: Must be enabled in database settings before use
- **Unsharded databases only**: Sharded databases do not support FK constraints
- **No referential integrity validation on ALTER TABLE ADD FK**: Adding a FK to an existing table does not check if orphaned rows already exist
- **Revert complications**: Reverting a deploy request that dropped a FK may fail if new non-conforming data was inserted
- **Performance trade-off**: FK enforcement adds overhead in high-concurrency workloads

---

## Pattern 7: Branch Cleanup Script

### Good Example -- Delete Stale Preview Branches via pscale CLI

```bash
#!/bin/bash
# cleanup-stale-branches.sh
# Run as a weekly cron job in CI

DATABASE_NAME="my-database"
STALE_DAYS=14
PREVIEW_PREFIX="preview-pr-"

# List branches and filter stale preview branches
pscale branch list "$DATABASE_NAME" --org "$PLANETSCALE_ORG" --format json \
  | jq -r --arg prefix "$PREVIEW_PREFIX" --argjson days "$STALE_DAYS" '
    .[] | select(.name | startswith($prefix))
    | select(
        (now - (.created_at | fromdateiso8601)) > ($days * 86400)
      )
    | .name
  ' \
  | while read -r branch_name; do
      echo "Deleting stale branch: $branch_name"
      pscale branch delete "$DATABASE_NAME" "$branch_name" --org "$PLANETSCALE_ORG" --force
    done
```

**Why good:** Only targets preview branches (never dev or main), configurable stale threshold, `--format json` enables programmatic filtering, `--force` skips confirmation in CI, safe to run repeatedly

**When to use:** As a scheduled CI job to clean up preview branches from closed PRs that were not properly deleted.

---

## Pattern 8: Instant Deployments

### Good Example -- Fast Schema Changes with ALGORITHM=INSTANT

```bash
# Instant deployments use MySQL's ALGORITHM=INSTANT for near-zero-time changes
# Supported operations:
# - Adding columns (at the end of the table)
# - Dropping columns
# - Modifying column defaults
# - Changing ENUM/SET definitions

pscale deploy-request create my-database add-settings-col --into main
pscale deploy-request deploy my-database 1 --instant

# WARNING: Instant deployments CANNOT be reverted
# The 30-minute revert window does not apply
```

**Why good:** Near-instantaneous schema changes for supported operations, no table copy or rebuild

```bash
# BAD: Using --instant for unsupported operations
pscale deploy-request deploy my-database 1 --instant
# Adding an index is NOT instant-eligible -- this will fail
# Changing column types is NOT instant-eligible -- this will fail
```

**Why bad:** Only a subset of DDL operations support `ALGORITHM=INSTANT`. The deploy will fail if the change requires a table rebuild.

**When to use:** Adding nullable columns, dropping columns, or changing column defaults where you don't need the revert safety net.

---

_For driver setup and query patterns, see [core.md](core.md)._
    • core.md 13.4 KB 
      # PlanetScale -- Core Examples

> Driver setup, parameterized queries, transactions, and type casting patterns. See [SKILL.md](../SKILL.md) for core concepts.

**Branching & schema change patterns:** See [branching.md](branching.md).

---

## Pattern 1: Basic Connection and Query

### Good Example -- Typed Query with Named Constants

```typescript
// lib/db.ts
import { connect, cast } from "@planetscale/database";
import type { Config, Field } from "@planetscale/database";

const DATABASE_HOST = process.env.DATABASE_HOST!;
const DATABASE_USERNAME = process.env.DATABASE_USERNAME!;
const DATABASE_PASSWORD = process.env.DATABASE_PASSWORD!;

function customCast(field: Field, value: any): any {
  if (value == null) return null;
  // Default cast already handles INT8-32, FLOAT32/64 -- only extend for BigInt, Date, boolean
  if (field.type === "INT64" || field.type === "UINT64") return BigInt(value);
  if (field.type === "DATETIME" || field.type === "TIMESTAMP")
    return new Date(value + "Z");
  if (field.type === "INT8" && field.columnLength === 1) return value === "1";
  return cast(field, value);
}

const config: Config = {
  host: DATABASE_HOST,
  username: DATABASE_USERNAME,
  password: DATABASE_PASSWORD,
  cast: customCast,
};

export const conn = connect(config);

// Usage
const ACTIVE_STATUS = "active";

export async function getActiveUsers() {
  const { rows } = await conn.execute(
    "SELECT id, name, email FROM users WHERE status = ?",
    [ACTIVE_STATUS],
  );
  return rows;
}
```

**Why good:** Named constants for credentials and status, custom cast extends default behavior for BigInt/Date/boolean types, typed Config import, connection exported for reuse, parameterized query prevents injection

### Bad Example -- Hardcoded Values, No Custom Cast

```typescript
import { connect } from "@planetscale/database";

const conn = connect({ url: "mysql://root:pass@host/db" }); // Hardcoded credentials

async function getUsers() {
  const { rows } = await conn.execute(
    "SELECT * FROM users WHERE status = 'active'",
  );
  // Default cast handles INT32 (id would be 42, not "42")
  // But: created_at is "2024-01-15 10:30:00" (string!) -- Date comparisons fail
  // And: BIGINT ids remain as strings without custom cast
  return rows;
}
```

**Why bad:** Credentials in code, `SELECT *` fetches unnecessary data, no custom cast means BIGINT/DATETIME columns are strings, hardcoded status string

---

## Pattern 2: Client Factory for Serverless

### Good Example -- Per-Request Connection

```typescript
import { Client, cast } from "@planetscale/database";
import type { Field } from "@planetscale/database";

function customCast(field: Field, value: any): any {
  if (value == null) return null;
  // Default cast already handles INT8-32 and FLOAT32/64 -- extend for BigInt and Date
  if (field.type === "INT64" || field.type === "UINT64") return BigInt(value);
  if (field.type === "DATETIME" || field.type === "TIMESTAMP")
    return new Date(value + "Z");
  if (field.type === "INT8" && field.columnLength === 1) return value === "1";
  return cast(field, value);
}

// Client is safe to create once at module level
const client = new Client({
  host: process.env.DATABASE_HOST!,
  username: process.env.DATABASE_USERNAME!,
  password: process.env.DATABASE_PASSWORD!,
  cast: customCast,
});

// Each request gets a fresh connection
export async function handleRequest(request: Request): Promise<Response> {
  const conn = client.connection();

  try {
    const PAGE_SIZE = 20;
    const { rows } = await conn.execute(
      "SELECT id, title, published_at FROM posts WHERE published = ? ORDER BY published_at DESC LIMIT ?",
      [true, PAGE_SIZE],
    );

    return new Response(JSON.stringify(rows), {
      headers: { "Content-Type": "application/json" },
    });
  } catch (error) {
    const message =
      error instanceof Error ? error.message : "Unknown database error";
    const INTERNAL_ERROR = 500;
    return new Response(JSON.stringify({ error: message }), {
      status: INTERNAL_ERROR,
      headers: { "Content-Type": "application/json" },
    });
  }
}
```

**Why good:** `Client` created once at module level (safe -- it's just a config holder), `client.connection()` creates a fresh connection per request, custom cast extends defaults for BigInt/Date/boolean, named constant for page size, proper error handling

---

## Pattern 3: Named Parameters

### Good Example -- Complex Query with Named Params

```typescript
import { connect } from "@planetscale/database";

const conn = connect({ url: process.env.DATABASE_URL });

interface SearchOptions {
  query: string;
  role?: string;
  limit?: number;
  offset?: number;
}

const DEFAULT_PAGE_SIZE = 25;
const DEFAULT_OFFSET = 0;

async function searchUsers(options: SearchOptions) {
  const { rows, size } = await conn.execute(
    `SELECT id, name, email, role
     FROM users
     WHERE name LIKE :query AND (:role IS NULL OR role = :role)
     ORDER BY name ASC
     LIMIT :limit OFFSET :offset`,
    {
      query: `%${options.query}%`,
      role: options.role ?? null,
      limit: options.limit ?? DEFAULT_PAGE_SIZE,
      offset: options.offset ?? DEFAULT_OFFSET,
    },
  );

  return { users: rows, count: size };
}
```

**Why good:** Named parameters improve readability for complex queries, nullable role handled with `IS NULL OR` pattern, named constants for defaults, `LIKE` pattern safely parameterized (the `%` is part of the value, not interpolated SQL)

### Bad Example -- Mixing Parameter Styles

```typescript
// BAD: Cannot mix positional and named parameters
const results = await conn.execute(
  "SELECT * FROM users WHERE name = ? AND role = :role",
  ["alice", { role: "admin" }], // ERROR -- pick one style
);
```

**Why bad:** A single `execute()` call must use either positional (`?` + array) or named (`:param` + object), never both

---

## Pattern 4: Transaction with Error Handling

### Good Example -- Order Creation with Inventory Check

```typescript
import { connect, cast } from "@planetscale/database";
import type { Field } from "@planetscale/database";

function numericCast(field: Field, value: any): any {
  if (value == null) return null;
  // Default cast handles INT32, FLOAT64 -- extend for INT64 (BigInt) and DECIMAL
  if (field.type === "INT64" || field.type === "UINT64") return BigInt(value);
  if (field.type === "DECIMAL") return parseFloat(value);
  return cast(field, value);
}

const conn = connect({
  url: process.env.DATABASE_URL,
  cast: numericCast,
});

interface OrderResult {
  orderId: string;
  total: number;
}

async function createOrder(
  productId: string,
  quantity: number,
  userId: string,
): Promise<OrderResult> {
  const MIN_QUANTITY = 1;

  if (quantity < MIN_QUANTITY) {
    throw new Error("Quantity must be at least 1");
  }

  return conn.transaction(async (tx) => {
    // Check stock
    const { rows: products } = await tx.execute(
      "SELECT id, stock, price FROM products WHERE id = ? FOR UPDATE",
      [productId],
    );

    if (products.length === 0) {
      throw new Error("Product not found");
    }

    const product = products[0];

    if (product.stock < quantity) {
      throw new Error(
        `Insufficient stock: ${product.stock} available, ${quantity} requested`,
      );
    }

    // Deduct inventory
    await tx.execute("UPDATE products SET stock = stock - ? WHERE id = ?", [
      quantity,
      productId,
    ]);

    // Create order
    const total = product.price * quantity;
    const orderResult = await tx.execute(
      "INSERT INTO orders (user_id, product_id, quantity, total) VALUES (?, ?, ?, ?)",
      [userId, productId, quantity, total],
    );

    return {
      orderId: orderResult.insertId,
      total,
    };
  });
}
```

**Why good:** `FOR UPDATE` locks the product row preventing concurrent stock deductions, transaction auto-rolls back if any throw occurs, numeric cast ensures `stock` and `price` are numbers (not strings), named constant for minimum quantity, type-safe return

### Bad Example -- No Transaction for Related Operations

```typescript
// BAD: Two related operations without transaction
const {
  rows: [product],
} = await conn.execute("SELECT stock, price FROM products WHERE id = ?", [
  productId,
]);
// Another request could modify stock between these two queries!
await conn.execute("UPDATE products SET stock = stock - ? WHERE id = ?", [
  quantity,
  productId,
]);
await conn.execute(
  "INSERT INTO orders (user_id, product_id, quantity, total) VALUES (?, ?, ?, ?)",
  [userId, productId, quantity, product.price * quantity],
);
// If INSERT fails, stock is already deducted -- inconsistent state
```

**Why bad:** Without a transaction, stock can change between SELECT and UPDATE (race condition), and a failure in the INSERT leaves inventory deducted without a corresponding order

---

## Pattern 5: Full Results with Metadata

### Good Example -- Pagination with Row Count

```typescript
import { connect } from "@planetscale/database";

const conn = connect({ url: process.env.DATABASE_URL });

const PAGE_SIZE = 20;

interface PaginatedResult<T> {
  data: T[];
  page: number;
  pageSize: number;
  hasMore: boolean;
}

async function getPaginatedPosts(
  page: number,
): Promise<PaginatedResult<Record<string, unknown>>> {
  const offset = page * PAGE_SIZE;

  // Fetch one extra row to determine if there are more pages
  const FETCH_EXTRA = 1;
  const { rows } = await conn.execute(
    "SELECT id, title, excerpt, published_at FROM posts WHERE published = ? ORDER BY published_at DESC LIMIT ? OFFSET ?",
    [true, PAGE_SIZE + FETCH_EXTRA, offset],
  );

  const hasMore = rows.length > PAGE_SIZE;
  const data = hasMore ? rows.slice(0, PAGE_SIZE) : rows;

  return { data, page, pageSize: PAGE_SIZE, hasMore };
}
```

**Why good:** Fetches `N+1` rows to detect next page without a separate COUNT query, named constants for page size and extra fetch, typed return interface, avoids expensive `SELECT COUNT(*)` on large tables

---

## Pattern 6: Bulk Insert with Batching

### Good Example -- Batched Inserts for Large Datasets

```typescript
import { connect } from "@planetscale/database";

const conn = connect({ url: process.env.DATABASE_URL });

const BATCH_SIZE = 100;

interface UserRecord {
  name: string;
  email: string;
  role: string;
}

async function bulkInsertUsers(users: UserRecord[]): Promise<number> {
  let totalInserted = 0;

  for (let i = 0; i < users.length; i += BATCH_SIZE) {
    const batch = users.slice(i, i + BATCH_SIZE);

    // Build multi-row INSERT
    const placeholders = batch.map(() => "(?, ?, ?)").join(", ");
    const values = batch.flatMap((u) => [u.name, u.email, u.role]);

    const result = await conn.execute(
      `INSERT INTO users (name, email, role) VALUES ${placeholders}`,
      values,
    );

    totalInserted += result.rowsAffected;
  }

  return totalInserted;
}
```

**Why good:** Named constant for batch size, batching avoids hitting the 64KB query limit, multi-row INSERT is faster than individual inserts, `flatMap` builds parameter array cleanly

---

## Pattern 7: DatabaseError Handling

### Good Example -- Typed Error Handling

```typescript
import { connect, DatabaseError } from "@planetscale/database";

const conn = connect({ url: process.env.DATABASE_URL });

const ALREADY_EXISTS_CODE = "ALREADY_EXISTS";
const CONFLICT_STATUS = 409;
const INTERNAL_ERROR_STATUS = 500;

async function createUser(name: string, email: string): Promise<Response> {
  try {
    const result = await conn.execute(
      "INSERT INTO users (name, email) VALUES (?, ?)",
      [name, email],
    );

    return new Response(JSON.stringify({ id: result.insertId }), {
      status: 201,
    });
  } catch (error) {
    if (error instanceof DatabaseError) {
      // DatabaseError has .status (HTTP status) and .body (VitessError with .message and .code)
      // .body.code is a Vitess gRPC status string, not a MySQL errno number
      if (error.body?.code === ALREADY_EXISTS_CODE) {
        return new Response(
          JSON.stringify({ error: "A user with this email already exists" }),
          { status: CONFLICT_STATUS },
        );
      }
    }

    const message = error instanceof Error ? error.message : "Unknown error";
    return new Response(JSON.stringify({ error: message }), {
      status: INTERNAL_ERROR_STATUS,
    });
  }
}
```

**Why good:** `DatabaseError` is the specific error class from the driver, `.body.code` is a Vitess gRPC status string (`"ALREADY_EXISTS"`, `"NOT_FOUND"`, etc.), named constants for error codes and HTTP statuses, user-friendly error message for duplicate entries

---

## Pattern 8: Custom Fetch for HTTP/2

### Good Example -- Using fetch-h2 for Better Performance

```typescript
import { connect } from "@planetscale/database";
import { context } from "fetch-h2";

const { fetch: h2Fetch, disconnectAll } = context();

const conn = connect({
  host: process.env.DATABASE_HOST!,
  username: process.env.DATABASE_USERNAME!,
  password: process.env.DATABASE_PASSWORD!,
  fetch: h2Fetch,
});

// Use conn.execute() as normal -- HTTP/2 multiplexing reduces latency
const { rows } = await conn.execute("SELECT id, name FROM users LIMIT 10");

// Cleanup when shutting down
await disconnectAll();
```

**Why good:** HTTP/2 multiplexing allows concurrent queries over a single TCP connection, reducing connection overhead, `disconnectAll()` cleans up on shutdown

**When to use:** Long-running Node.js servers where HTTP/2 multiplexing provides latency benefits. Not needed for serverless (single request per invocation).

---

_For branching and deploy request patterns, see [branching.md](branching.md)._
  • reference.md 9.3 KB 
    # PlanetScale Reference

> Quick lookup tables, CLI commands, and configuration reference. See [SKILL.md](SKILL.md) for core concepts and [examples/](examples/) for code examples.

---

## Connection Configuration

### Config Object

| Option     | Type       | Description                                                                   |
| ---------- | ---------- | ----------------------------------------------------------------------------- |
| `host`     | `string`   | Database hostname (e.g., `aws.connect.psdb.cloud`)                            |
| `username` | `string`   | Authentication username                                                       |
| `password` | `string`   | Authentication password                                                       |
| `url`      | `string`   | MySQL connection URL (alternative to host/username/password)                  |
| `fetch`    | `Function` | Custom fetch implementation (Node.js < 18)                                    |
| `format`   | `Function` | Custom query parameter formatting function                                    |
| `cast`     | `Function` | Custom type casting override (default cast handles INT8-32, FLOAT32/64, JSON) |

### URL Format

```
mysql://[username]:[password]@[host]/[database]
```

---

## Execute Options

| Option | Values                | Default    | Description                      |
| ------ | --------------------- | ---------- | -------------------------------- |
| `as`   | `"object"`, `"array"` | `"object"` | Return rows as objects or arrays |
| `cast` | `Function`            | —          | Per-query type casting override  |

---

## ExecutedQuery Result Shape

| Property       | Type       | Description                                       |
| -------------- | ---------- | ------------------------------------------------- |
| `rows`         | `T[]`      | Array of row objects (or arrays if `as: "array"`) |
| `headers`      | `string[]` | Column names in order                             |
| `types`        | `object`   | Column type information                           |
| `fields`       | `Field[]`  | Detailed column metadata                          |
| `size`         | `number`   | Number of rows returned                           |
| `statement`    | `string`   | The executed SQL statement                        |
| `insertId`     | `string`   | Last insert ID (string, not number)               |
| `rowsAffected` | `number`   | Rows affected by DML operations                   |
| `time`         | `number`   | Execution time in milliseconds                    |

---

## Field Type Strings

Vitess returns these type identifiers in `field.type`:

| Vitess Type | MySQL Type          | Recommended Cast         |
| ----------- | ------------------- | ------------------------ |
| `INT8`      | `TINYINT`           | `parseInt()` or boolean  |
| `INT16`     | `SMALLINT`          | `parseInt()`             |
| `INT24`     | `MEDIUMINT`         | `parseInt()`             |
| `INT32`     | `INT`               | `parseInt()`             |
| `INT64`     | `BIGINT`            | `BigInt()`               |
| `UINT8`     | `TINYINT UNSIGNED`  | `parseInt()`             |
| `UINT16`    | `SMALLINT UNSIGNED` | `parseInt()`             |
| `UINT32`    | `INT UNSIGNED`      | `parseInt()`             |
| `UINT64`    | `BIGINT UNSIGNED`   | `BigInt()`               |
| `FLOAT32`   | `FLOAT`             | `parseFloat()`           |
| `FLOAT64`   | `DOUBLE`            | `parseFloat()`           |
| `DECIMAL`   | `DECIMAL`           | `parseFloat()` or string |
| `VARCHAR`   | `VARCHAR`           | string (default)         |
| `VARBINARY` | `VARBINARY`         | string                   |
| `BLOB`      | `BLOB`              | string                   |
| `TEXT`      | `TEXT`              | string (default)         |
| `JSON`      | `JSON`              | `JSON.parse()`           |
| `DATETIME`  | `DATETIME`          | `new Date(value + "Z")`  |
| `TIMESTAMP` | `TIMESTAMP`         | `new Date(value + "Z")`  |
| `DATE`      | `DATE`              | `new Date(value)`        |
| `TIME`      | `TIME`              | string                   |
| `ENUM`      | `ENUM`              | string (default)         |
| `SET`       | `SET`               | string (default)         |
| `BIT`       | `BIT`               | `parseInt(value, 2)`     |
| `NULL_TYPE` | `NULL`              | `null`                   |

---

## Vitess SQL Compatibility

### Supported

- Standard DML: `SELECT`, `INSERT`, `UPDATE`, `DELETE`
- DDL: `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `CREATE INDEX`
- JSON functions (except `JSON_TABLE`)
- Common Table Expressions (non-recursive)
- Window functions
- Subqueries, `UNION`, `INTERSECT`, `EXCEPT`
- `ON DUPLICATE KEY UPDATE`
- Foreign key constraints (opt-in, unsharded databases only)

### Not Supported

| Feature                  | Alternative                                      |
| ------------------------ | ------------------------------------------------ |
| Stored procedures        | Application logic                                |
| Stored functions         | Application logic                                |
| Triggers                 | Application logic or event-driven hooks          |
| Events (scheduled tasks) | External cron or job scheduler                   |
| `RENAME COLUMN`          | Add new column + migrate data + drop old (3 DRs) |
| `:=` assignment operator | `SET @var = 1` (use `=` instead)                 |
| `LOAD DATA INFILE`       | `INSERT` statements or bulk import via API       |
| `CREATE DATABASE`        | PlanetScale dashboard, API, or `pscale` CLI      |
| `DROP DATABASE`          | PlanetScale dashboard, API, or `pscale` CLI      |
| `JSON_TABLE`             | Application-side JSON processing                 |
| `KILL` (query killing)   | Not available from CLI connections               |
| Recursive CTEs           | Experimental SELECT-only support (Vitess 21+)    |

### SQL Mode Restrictions

- Global timezone is UTC (not configurable)
- `SET sql_mode` is session-only (single request on HTTP driver)
- Avoid `PIPES_AS_CONCAT` and `ANSI_QUOTES` (interfere with Vitess query parsing)

---

## pscale CLI Quick Reference

```bash
# Install
brew install planetscale/tap/pscale   # macOS
scoop install pscale                    # Windows

# Authenticate
pscale auth login

# Branch management
pscale branch list <DATABASE>
pscale branch create <DATABASE> <BRANCH>
pscale branch delete <DATABASE> <BRANCH>
pscale branch show <DATABASE> <BRANCH>
pscale shell <DATABASE> <BRANCH>                 # Interactive MySQL shell
pscale connect <DATABASE> <BRANCH> --port 3306   # Local tunnel

# Safe migrations
pscale branch safe-migrations enable <DATABASE> <BRANCH>
pscale branch safe-migrations disable <DATABASE> <BRANCH>

# Deploy requests
pscale deploy-request create <DATABASE> <BRANCH> --into main
pscale deploy-request create <DATABASE> <BRANCH> --disable-auto-apply  # Gated
pscale deploy-request list <DATABASE>
pscale deploy-request show <DATABASE> <DR_NUMBER>
pscale deploy-request diff <DATABASE> <DR_NUMBER>
pscale deploy-request deploy <DATABASE> <DR_NUMBER>
pscale deploy-request deploy <DATABASE> <DR_NUMBER> --instant
pscale deploy-request apply <DATABASE> <DR_NUMBER>       # For gated deployments
pscale deploy-request revert <DATABASE> <DR_NUMBER>      # Within 30-minute window
pscale deploy-request skip-revert <DATABASE> <DR_NUMBER> # Close revert window early
pscale deploy-request close <DATABASE> <DR_NUMBER>       # Cancel without deploying
pscale deploy-request review <DATABASE> <DR_NUMBER> --approve
pscale deploy-request review <DATABASE> <DR_NUMBER> --comment "LGTM"

# Passwords (connection credentials)
pscale password create <DATABASE> <BRANCH> <PASSWORD_NAME>
pscale password list <DATABASE> <BRANCH>
pscale password delete <DATABASE> <BRANCH> <PASSWORD_ID>
```

**Authentication:** Use `PLANETSCALE_SERVICE_TOKEN` and `PLANETSCALE_SERVICE_TOKEN_ID` environment variables for CI, or `pscale auth login` for interactive use.

---

## Deploy Request Revert Rules

| Scenario                                     | Can Revert? | Notes                                           |
| -------------------------------------------- | ----------- | ----------------------------------------------- |
| Standard deployment (within 30 min)          | Yes         | Original table preserved as shadow              |
| Standard deployment (after 30 min)           | No          | Create new deploy request to undo               |
| Instant deployment (`--instant`)             | No          | `ALGORITHM=INSTANT` skips revert infrastructure |
| New column added, data written to it         | Yes         | Data in new column is lost on revert            |
| Column dropped, data existed                 | Yes         | Dropped column data is preserved in shadow      |
| FK dropped, new non-conforming data inserted | Partial     | Revert may fail if new data violates constraint |
| Expanded field size, larger data inserted    | Partial     | Revert may fail if data exceeds original size   |

---

## Environment Variables

```bash
# Application (serverless driver)
DATABASE_HOST=aws.connect.psdb.cloud
DATABASE_USERNAME=your-username
DATABASE_PASSWORD=pscale_pw_...

# Or single URL
DATABASE_URL=mysql://user:pass@aws.connect.psdb.cloud/dbname

# CI / Automation
PLANETSCALE_SERVICE_TOKEN=pscale_tkn_...
PLANETSCALE_SERVICE_TOKEN_ID=your-token-id
PLANETSCALE_ORG=your-org-name
```
  • SKILL.md 18.4 KB 
    ---
name: api-baas-planetscale
description: Serverless MySQL platform with branching, deploy requests, and edge-compatible driver
---

# PlanetScale Serverless MySQL Patterns

> **Quick Guide:** Use `@planetscale/database` for edge/serverless MySQL access via HTTP (Fetch API). Use `Client` to create per-request connections, `conn.execute()` for parameterized queries, and `conn.transaction()` for atomic operations. Never run DDL directly on production -- use deploy requests with safe migrations enabled. PlanetScale runs on Vitess: foreign keys are supported but opt-in, stored procedures are not supported, and all schema changes go through online DDL. The built-in `cast` handles regular integers and floats automatically, but provide a custom `cast` for BigInt, Date, and boolean columns. Branch your database like git branches for dev/preview environments.

---

<critical_requirements>

## CRITICAL: Before Using This Skill

> **All code must follow project conventions in CLAUDE.md** (kebab-case, named exports, import ordering, `import type`, named constants)

**(You MUST use `conn.execute(sql, params)` with parameterized queries -- never interpolate user input into SQL strings)**

**(You MUST use deploy requests for ALL schema changes on production branches with safe migrations enabled -- direct DDL is rejected)**

**(You MUST create a fresh `Client.connection()` per request in serverless environments -- do not reuse connections across invocations)**

**(You MUST handle the Vitess/MySQL compatibility differences: no stored procedures, no `RENAME COLUMN` via direct DDL, no `:=` operator, no `LOAD DATA INFILE`)**

**(You MUST provide a custom `cast` function for BigInt (INT64/UINT64), Date (DATETIME/TIMESTAMP), and boolean (TINYINT(1)) columns -- the default cast handles regular integers and floats but leaves these as strings)**

</critical_requirements>

---

**Auto-detection:** PlanetScale, @planetscale/database, planetscale serverless driver, pscale, deploy request, safe migrations, Vitess, database branching, planetscale branch, planetscale boost, mysql serverless, pscale CLI, planetscale connection

**When to use:**

- Querying MySQL from edge/serverless functions via the PlanetScale serverless driver
- Managing schema changes through deploy requests and safe migrations
- Creating database branches for dev, preview, or CI environments
- Setting up connections with `@planetscale/database` (host/username/password or URL)
- Running transactions in serverless contexts
- Handling Vitess-specific SQL compatibility constraints
- Programmatic branch management via `pscale` CLI

**Key patterns covered:**

- `connect()` / `Client` connection setup with host, username, password
- `conn.execute()` with positional (`?`) and named (`:param`) parameters
- `conn.transaction()` for atomic multi-statement operations
- Custom `cast` functions for type-safe value conversion (BigInt, Date, boolean)
- Deploy request workflow (branch, change schema, create DR, review, deploy)
- Safe migrations and the no-direct-DDL enforcement model
- Database branching for dev/preview/CI environments
- Vitess SQL compatibility constraints and workarounds
- `pscale` CLI for branch and deploy request management

**When NOT to use:**

- Long-running server processes with persistent TCP MySQL connections (use `mysql2` driver)
- Complex ORM-specific patterns (use your ORM's own skill)
- General MySQL query syntax (use a SQL/MySQL skill)
- PostgreSQL workloads (use Neon or another Postgres provider)

**Detailed Resources:**

- For decision frameworks, CLI reference, and quick lookup tables, see [reference.md](reference.md)

**Driver & Queries:**

- [examples/core.md](examples/core.md) -- Connection setup, parameterized queries, transactions, type casting

**Branching & Schema Changes:**

- [examples/branching.md](examples/branching.md) -- Dev branches, deploy requests, safe migrations, pscale CLI, CI/CD workflows

---

<philosophy>

## Philosophy

PlanetScale is a serverless MySQL platform built on Vitess, the same technology that powers YouTube's database infrastructure. The `@planetscale/database` driver uses HTTP (Fetch API) instead of TCP, making MySQL accessible from edge runtimes that lack TCP support.

**Core principles:**

1. **HTTP-based, stateless connections** -- Every query is an HTTP request. There are no persistent connections to manage, no connection pools to configure. Create a connection, execute queries, done. PlanetScale handles connection pooling at the infrastructure level (Vitess VTTablet + Global Routing).
2. **Schema changes via deploy requests, never direct DDL** -- Production branches with safe migrations reject direct `CREATE`, `ALTER`, `DROP` statements. All schema changes go through deploy requests: branch, modify schema on the branch, create a deploy request, review the diff, deploy with zero downtime via online DDL.
3. **Branches are cheap** -- Database branches are isolated copies of your schema (and optionally data). Create them for feature development, PR previews, CI runs. Delete when done.
4. **Vitess under the hood** -- PlanetScale runs Vitess, which adds horizontal scaling but introduces SQL compatibility differences. No stored procedures, no `RENAME COLUMN` in DDL, no `:=` operator. Foreign keys are supported but opt-in and come with performance trade-offs.
5. **Default cast handles common types, customize for the rest** -- The driver's built-in `cast` function automatically converts INT8-32 and FLOAT32/64 to JavaScript numbers, and parses JSON. However, INT64/UINT64 (BigInt), DATETIME/TIMESTAMP (Date), DECIMAL, and TINYINT(1) (boolean) remain as strings -- provide a custom `cast` function for these.

**When to use PlanetScale serverless driver:**

- Edge/serverless functions that cannot open TCP connections
- Applications using PlanetScale's branching and deploy request workflow
- High-concurrency serverless apps benefiting from PlanetScale's infrastructure-level pooling
- Teams wanting git-like database workflows (branch, review, merge)

**When NOT to use:**

- Long-running server processes (use `mysql2` with TCP for persistent connections)
- Workloads requiring stored procedures, triggers, or events (Vitess does not support them)
- Applications requiring `LOAD DATA INFILE` (not supported)

</philosophy>

---

<patterns>

## Core Patterns

### Pattern 1: Connection Setup

The driver provides two connection methods: `connect()` for a single connection and `Client` for a connection factory. Use `Client` in serverless (fresh connection per request), `connect()` for single long-lived connection objects.

```typescript
import { connect } from "@planetscale/database";

const conn = connect({
  host: process.env.DATABASE_HOST!,
  username: process.env.DATABASE_USERNAME!,
  password: process.env.DATABASE_PASSWORD!,
});
const { rows } = await conn.execute(
  "SELECT id, name FROM users WHERE active = ?",
  [true],
);
```

See [examples/core.md](examples/core.md) for full connection patterns including `Client` factory, URL-based config, and custom fetch for HTTP/2.

---

### Pattern 2: Parameterized Queries

The driver supports positional (`?`) and named (`:param`) parameter styles. Both are auto-escaped preventing SQL injection. Never mix styles in a single `execute()` call.

```typescript
// Positional: array of values
await conn.execute("SELECT id, name FROM users WHERE id = ? AND active = ?", [
  userId,
  true,
]);

// Named: object of values
await conn.execute("SELECT id, name FROM users WHERE role = :role", {
  role: "admin",
});
```

See [examples/core.md](examples/core.md) for complex named parameter queries and bad examples to avoid.

---

### Pattern 3: Transactions

`conn.transaction()` executes multiple queries atomically with automatic rollback on error. Each `tx.execute()` is an HTTP round trip, but conditional logic runs client-side within the callback.

```typescript
const result = await conn.transaction(async (tx) => {
  const debit = await tx.execute(
    "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
    [amount, fromId, amount],
  );
  if (debit.rowsAffected === 0) throw new Error("Insufficient funds"); // triggers rollback
  await tx.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [
    amount,
    toId,
  ]);
  return debit;
});
```

See [examples/core.md](examples/core.md) for full transaction examples with inventory checks and `FOR UPDATE` locking.

---

### Pattern 4: Custom Type Casting

The built-in `cast` handles INT8-32 and FLOAT32/64 automatically. Provide a custom `cast` for INT64/UINT64 (BigInt), DATETIME/TIMESTAMP (Date), and TINYINT(1) (boolean) -- these remain as strings by default.

```typescript
import { connect, cast } from "@planetscale/database";
import type { Field } from "@planetscale/database";

function customCast(field: Field, value: any): any {
  if (value == null) return null;
  if (field.type === "INT64" || field.type === "UINT64") return BigInt(value);
  if (field.type === "DATETIME" || field.type === "TIMESTAMP")
    return new Date(value + "Z");
  if (field.type === "INT8" && field.columnLength === 1) return value === "1";
  return cast(field, value);
}

const conn = connect({ url: process.env.DATABASE_URL, cast: customCast });
```

See [examples/core.md](examples/core.md) for per-query cast overrides and type-specific cast variants.

---

### Pattern 5: Deploy Request Workflow

Schema changes on production branches with safe migrations must go through deploy requests. Direct DDL is rejected. The workflow is: branch, modify schema, create deploy request, review diff, deploy.

```bash
pscale branch create my-database add-user-roles          # 1. Create dev branch
pscale shell my-database add-user-roles                  # 2. Make schema changes (DDL)
pscale deploy-request create my-database add-user-roles --into main  # 3. Create DR
pscale deploy-request diff my-database 1                 # 4. Review schema diff
pscale deploy-request deploy my-database 1               # 5. Deploy (online DDL)
pscale deploy-request revert my-database 1               # 6. Revert within 30 min if needed
```

See [examples/branching.md](examples/branching.md) for gated deployments, instant deployments, and CI/CD workflows.

---

### Pattern 6: Database Branching

Branches are isolated copies of your database schema. Development branches allow direct DDL. Production branches require deploy requests when safe migrations is enabled.

```bash
pscale branch create my-database dev-alice               # Create dev branch
pscale shell my-database dev-alice                       # Interactive MySQL shell
pscale password create my-database dev-alice my-password # Generate app credentials
pscale branch delete my-database dev-alice               # Clean up when done
```

See [examples/branching.md](examples/branching.md) for PR preview branches, safe column renames, FK setup, and branch cleanup scripts.

---

### Pattern 7: Vitess SQL Compatibility

PlanetScale runs on Vitess, which introduces SQL differences from standard MySQL. Key constraints: no stored procedures/triggers/events, no `RENAME COLUMN` (use three-step add/migrate/drop pattern), no `:=` operator, no `LOAD DATA INFILE`, no `CREATE DATABASE`.

See [reference.md](reference.md) for the full supported/unsupported SQL compatibility table.

</patterns>

---

<decision_framework>

## Decision Framework

### Connection Method

```
What is the runtime environment?
+-- Edge/serverless (Cloudflare Workers, Vercel Edge, etc.)
|   +-- Use @planetscale/database (HTTP-based, no TCP needed)
+-- Traditional Node.js server (always-on)
|   +-- Need PlanetScale branching/deploy workflow?
|   |   +-- YES --> @planetscale/database works fine (HTTP)
|   |   +-- NO --> mysql2 driver with TCP may be simpler
+-- ORM integration?
    +-- Check your ORM's docs for its PlanetScale/serverless adapter
```

### connect() vs Client

```
How many connections per process?
+-- Single connection (scripts, simple handlers) --> connect()
+-- Multiple connections (serverless, per-request) --> Client + client.connection()
```

### Schema Change Strategy

```
Is the target branch a production branch with safe migrations?
+-- YES --> Deploy requests ONLY (direct DDL is rejected)
|   +-- Simple change (add column, add index) --> Standard deploy request
|   +-- Needs controlled cutover timing --> Gated deployment (--disable-auto-apply)
|   +-- Instant-eligible change --> Deploy with --instant flag
+-- NO (development branch) --> Direct DDL is allowed
    +-- Experimenting --> pscale shell <db> <branch>
    +-- Scripted migration --> Connect to branch, run DDL
```

### Foreign Keys

```
Do you need foreign key constraints?
+-- YES --> Enable in database settings (opt-in)
|   +-- Aware of limitations?
|   |   +-- Deploy requests don't validate existing referential integrity
|   |   +-- Reverts can create orphaned rows
|   |   +-- Performance impact in high-concurrency workloads
|   +-- Sharded database? --> FK only supported on unsharded databases
+-- NO --> Use application-level referential integrity
    +-- ORM-level relationship definitions
    +-- Application validation before INSERT/DELETE
```

</decision_framework>

---

<red_flags>

## RED FLAGS

**High Priority Issues:**

- **String interpolation in SQL** -- `conn.execute(\`SELECT \* FROM users WHERE id = '${id}'\`)`bypasses parameterization. Always use`?`or`:param` placeholders with the params argument.
- **Direct DDL on production with safe migrations** -- `ALTER TABLE` statements are silently rejected on production branches with safe migrations enabled. All schema changes must go through deploy requests.
- **No custom cast for BigInt/Date columns** -- The default cast handles regular integers and floats, but INT64/UINT64 remain as strings and DATETIME/TIMESTAMP are not converted to Date objects. Provide a custom `cast` for these types.

**Medium Priority Issues:**

- **Reusing connections across serverless invocations** -- Each serverless invocation gets a fresh execution context. Do not store connection state in global variables expecting it to persist.
- **Using `RENAME COLUMN` in deploy requests** -- Column renames can be destructive through Vitess online DDL. Use the three-step pattern: add new column, migrate data, drop old column.
- **Missing revert window awareness** -- Deploy requests can be reverted within 30 minutes. After that window closes, you must create a new deploy request to undo changes. Plan accordingly.
- **Foreign keys enabled without understanding implications** -- FK constraints on PlanetScale don't validate existing referential integrity during `ALTER TABLE ADD FOREIGN KEY`. Orphaned rows will silently remain.

**Common Mistakes:**

- **Wrong package name** -- The package is `@planetscale/database`, not `planetscale`, `mysql-planetscale`, or `@planetscale/serverless`.
- **Expecting connection pooling in the driver** -- `@planetscale/database` does not do client-side connection pooling. PlanetScale handles pooling at the infrastructure level (Vitess VTTablet + Global Routing). Do not wrap it in a pool library.
- **Using positional and named params together** -- A single `execute()` call uses either `?` with an array OR `:param` with an object. Never mix them.
- **Expecting Node.js `mysql2` compatibility** -- `@planetscale/database` has a different API from `mysql2`. There is no `pool.query()`, no `connection.query()`. The API is `conn.execute(sql, params)`.
- **Running `CREATE DATABASE` or `DROP DATABASE`** -- Database creation/deletion is managed via the PlanetScale dashboard, API, or `pscale` CLI, not SQL.

**Gotchas & Edge Cases:**

- **INT64/UINT64 and dates remain as strings with the default cast** -- `SELECT count(*) as total` returns `{ total: 42 }` (INT64 is an exception -- it stays as `"42"` string). DATETIME returns `"2024-01-15 10:30:00"`. Regular INT32 and FLOAT types are auto-converted.
- **`rowsAffected` is 0 for SELECT** -- Only DML statements (INSERT, UPDATE, DELETE) populate `rowsAffected`. For SELECT, check `rows.length` or `size`.
- **`insertId` is a string** -- Even though MySQL auto-increment IDs are integers, `insertId` in the result is always a string. Cast if needed: `BigInt(result.insertId)`.
- **Transactions over HTTP are not interactive** -- Unlike traditional MySQL transactions, PlanetScale's HTTP transactions send all statements in a single request. You CAN use conditional logic within the `transaction()` callback (it runs client-side), but each `tx.execute()` is an HTTP round trip.
- **`DATETIME` values lack timezone** -- MySQL `DATETIME` is stored without timezone info. The driver returns it as a string like `"2024-01-15 10:30:00"`. Append `"Z"` when parsing as UTC, or handle timezone explicitly.
- **64KB query limit per execute** -- Individual SQL statements have a size limit. For bulk inserts, batch into multiple `execute()` calls.
- **SQL mode is session-only** -- `SET sql_mode = '...'` only lasts for the current connection. On PlanetScale's HTTP driver, that means a single request. Global SQL mode changes are not allowed.
- **PlanetScale Boost requires explicit opt-in** -- Boost query caching is available on Scaler Pro plans and above. Enable per-query via `@@boost_cached_queries = true` in a session `SET` before the boosted query. Not all queries are eligible.
- **Empty schemas are invalid** -- Production branches require at least one table. You cannot have an empty database on a production branch.
- **Instant deployments cannot be reverted** -- Using `--instant` on a deploy request uses MySQL's `ALGORITHM=INSTANT` and skips the revert window entirely.

</red_flags>

---

<critical_reminders>

## CRITICAL REMINDERS

> **All code must follow project conventions in CLAUDE.md** (kebab-case, named exports, import ordering, `import type`, named constants)

**(You MUST use `conn.execute(sql, params)` with parameterized queries -- never interpolate user input into SQL strings)**

**(You MUST use deploy requests for ALL schema changes on production branches with safe migrations enabled -- direct DDL is rejected)**

**(You MUST create a fresh `Client.connection()` per request in serverless environments -- do not reuse connections across invocations)**

**(You MUST handle the Vitess/MySQL compatibility differences: no stored procedures, no `RENAME COLUMN` via direct DDL, no `:=` operator, no `LOAD DATA INFILE`)**

**(You MUST provide a custom `cast` function for BigInt (INT64/UINT64), Date (DATETIME/TIMESTAMP), and boolean (TINYINT(1)) columns -- the default cast handles regular integers and floats but leaves these as strings)**

**Failure to follow these rules will cause SQL injection vulnerabilities, failed deploy requests, or silent type coercion bugs.**

</critical_reminders>

Comments (0)

Sign in to join the conversation.

No comments yet.

Reviews (0)

No reviews yet.

Related

Claude Prompt
Tighten-my-prose editing prompt
Claude Skill
Adversarial code-review skill for Claude
Claude Workflow
Deep-research workflow (fan-out + verify)
Claude Skill
Conventional-commit writer for Claude