Mysql Planetscale

No Foreign Keys

PlanetScale uses Vitess under the hood, which doesn't support FK constraints. Enforce referential integrity in application code:

// DON'T rely on CASCADE DELETE — do it manually
async function deleteOrganization(orgId: string) {
  await db.transaction(async (tx) => {
    // Delete children first
    await tx.delete(polls).where(eq(polls.organizationId, orgId));
    await tx.delete(campaigns).where(eq(campaigns.organizationId, orgId));
    await tx.delete(orgMembers).where(eq(orgMembers.organizationId, orgId));
    // Then parent
    await tx.delete(organizations).where(eq(organizations.id, orgId));
  });
}

// Schema: define columns but skip .references()
organizationId: varchar('organization_id', { length: 128 }).notNull(),
// NOT: .references(() => organizations.id, { onDelete: 'cascade' })
// Drizzle relations still work for type-safe joins — they're not FK constraints

Branching Workflow

# Create branch for new feature
pscale branch create sided-db add-ask-tables

# Connect to branch for development
pscale connect sided-db add-ask-tables --port 3309
# DATABASE_URL=mysql://[email protected]:3309/sided-db

# Create deploy request (like a PR for schema changes)
pscale deploy-request create sided-db add-ask-tables

# Deploy (non-blocking — no table locks)
pscale deploy-request deploy sided-db 42

Migration Safety

PlanetScale migrations are non-blocking (online DDL). Safe operations:

-- ✅ SAFE (non-blocking)
ALTER TABLE polls ADD COLUMN sentiment_score FLOAT;
ALTER TABLE polls ADD INDEX idx_polls_sentiment (sentiment_score);
CREATE TABLE ask_quotes (...);

-- ⚠️ REQUIRES CARE
ALTER TABLE polls MODIFY question TEXT NOT NULL;  -- Type change
ALTER TABLE polls DROP COLUMN old_field;          -- Column removal

-- ❌ NOT SUPPORTED
ALTER TABLE polls ADD FOREIGN KEY (org_id) REFERENCES organizations(id);

Migration Checklist

1. Create a branch
2. Apply schema changes to branch
3. Test with application code against branch
4. Create deploy request
5. Review diff in PlanetScale dashboard
6. Deploy (zero-downtime)

Query Optimization

Indexing Strategy

// Composite indexes for common query patterns
mysqlTable('polls', {
  // ... columns
}, (table) => [
  // Most common query: list polls by org, ordered by creation
  index('idx_polls_org_created').on(table.organizationId, table.createdAt),
  // Filter by status within org
  index('idx_polls_org_status').on(table.organizationId, table.status),
  // Campaign targeting queries
  index('idx_polls_format_status').on(table.format, table.status),
]);

EXPLAIN Your Queries

// Development helper: check query plans
const result = await db.execute(sql`
  EXPLAIN SELECT * FROM polls
  WHERE organization_id = ${orgId}
  AND status = 'active'
  ORDER BY created_at DESC
  LIMIT 20
`);
console.table(result);
// Look for: type=ref or range (good), type=ALL (bad — full table scan)

Read Replicas

// PlanetScale provides read-only connection strings
const readPool = mysql.createPool({
  uri: process.env.DATABASE_READ_URL, // Read replica URL
  ssl: { rejectUnauthorized: true },
  connectionLimit: 20, // Higher limit for reads
});

const readDb = drizzle(readPool, { schema, mode: 'default' });

// Use readDb for analytics, dashboards, search
// Use db (primary) for writes

Connection Limits

Common Mistakes

1. Don't use FK constraints — PlanetScale will reject them
2. Always use SSL — ssl: { rejectUnauthorized: true } is required
3. Don't over-connect — each Heroku dyno should use 5-20 connections, not 100
4. Use deploy requests for schema changes — never run DDL directly on production
5. Index before you query — missing indexes on a 10M row table = multi-second queries
6. Batch large deletes — DELETE FROM table WHERE ... LIMIT 10000 in a loop, not one massive DELETE