Testing PlanetScale and Vitess: Schema Changes, Branching, and Query Testing

# Install CLI
brew install planetscale/tap/pscale

<span class="hljs-comment"># Authenticate
pscale auth login

<span class="hljs-comment"># Create a test branch from main
pscale branch create mydb test-pr-123 --from main --<span class="hljs-built_in">wait

<span class="hljs-comment"># Connect to it
pscale connect mydb test-pr-123 --port 3309

# .github/workflows/test.yml
name: Test

on: [pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    env:
      PSCALE_SERVICE_TOKEN: ${{ secrets.PSCALE_SERVICE_TOKEN }}
      PSCALE_SERVICE_TOKEN_ID: ${{ secrets.PSCALE_SERVICE_TOKEN_ID }}
      PSCALE_ORG: myorg
      PSCALE_DB: mydb

    steps:
      - uses: actions/checkout@v4

      - name: Install pscale CLI
        run: |
          curl -fsSL https://github.com/planetscale/cli/releases/download/v0.179.0/pscale_0.179.0_linux_amd64.tar.gz | tar xz
          mv pscale /usr/local/bin/

      - name: Create test branch
        run: |
          pscale branch create $PSCALE_DB pr-${{ github.event.number }} --from main --wait
          pscale connect $PSCALE_DB pr-${{ github.event.number }} --port 3309 &
          sleep 3  # wait for proxy to be ready

      - name: Run migrations on branch
        run: |
          mysql -h 127.0.0.1 -P 3309 -u root < migrations/latest.sql

      - name: Run tests
        run: npm test
        env:
          DATABASE_URL: "mysql://root@127.0.0.1:3309/mydb"

      - name: Delete test branch
        if: always()
        run: pscale branch delete $PSCALE_DB pr-${{ github.event.number }} --force

// test/migrations/add-user-preferences.test.ts
import mysql from "mysql2/promise";

let connection: mysql.Connection;

beforeAll(async () => {
  connection = await mysql.createConnection({
    host: "127.0.0.1",
    port: 3309, // pscale connect proxy
    user: "root",
    database: "mydb",
  });
});

afterAll(() => connection.end());

test("migration adds user_preferences table with correct columns", async () => {
  // Apply the migration
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS user_preferences (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id BIGINT UNSIGNED NOT NULL,
      theme VARCHAR(50) NOT NULL DEFAULT 'light',
      notifications_enabled TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
      PRIMARY KEY (id),
      KEY idx_user_id (user_id)
    )
  `);

  // Verify the schema
  const [columns] = await connection.execute(`SHOW COLUMNS FROM user_preferences`);
  const colNames = (columns as any[]).map((c) => c.Field);

  expect(colNames).toContain("user_id");
  expect(colNames).toContain("theme");
  expect(colNames).toContain("notifications_enabled");
  expect(colNames).toContain("created_at");
  expect(colNames).toContain("updated_at");
});

test("migration is idempotent (IF NOT EXISTS works)", async () => {
  // Running the same migration twice should not throw
  await expect(
    connection.execute(`
      CREATE TABLE IF NOT EXISTS user_preferences (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (id)
      )
    `)
  ).resolves.not.toThrow();
});

test("orphaned records are prevented at application layer", async () => {
  // In Vitess, this INSERT succeeds even if parent doesn't exist
  // because FK enforcement is disabled — verify app code handles this
  const { rows: orgCount } = await pool.query(
    "SELECT COUNT(*) as count FROM organizations WHERE id = ?",
    [999999]
  );
  expect(orgCount[0].count).toBe(0);

  // Application should check before inserting
  await expect(
    userRepository.create({ orgId: 999999, email: "orphan@example.com" })
  ).rejects.toThrow("Organization not found");
});

test("user query includes shard key to avoid scatter", async () => {
  // Bad: SELECT without tenant_id causes scatter query in sharded Vitess
  // Good: always include the shard key
  const result = await userRepository.findByEmail("alice@example.com", {
    tenantId: "tenant-123", // shard key
  });

  expect(result).toBeDefined();
});

test("scatter query throws VT12001 in Vitess strict mode", async () => {
  // If your Vitess is configured with --allow_scatter=false
  await expect(
    connection.execute("SELECT * FROM users WHERE email = 'alice@example.com'")
  ).rejects.toThrow(/VT12001/);
});

test("application handles missing column gracefully during migration", async () => {
  // Simulate a migration that adds a nullable column
  // Before migration completes, the column doesn't exist
  // App code should use optional chaining or defaults
  const user = await userRepository.findById("user-123");

  // Should not throw even if 'preferences' column doesn't exist yet
  const theme = user.preferences?.theme ?? "light";
  expect(theme).toBe("light");
});

// test/connection-pool.test.ts
import { Pool } from "mysql2/promise";

test("concurrent queries respect connection pool size", async () => {
  const pool = mysql.createPool({
    host: "127.0.0.1",
    port: 3309,
    user: "root",
    database: "mydb",
    connectionLimit: 10, // PlanetScale Scaler plan allows 10,000 connections
    waitForConnections: true,
    queueLimit: 0,
  });

  // Simulate 50 concurrent queries
  const queries = Array.from({ length: 50 }, (_, i) =>
    pool.execute("SELECT ? AS n", [i])
  );

  const results = await Promise.all(queries);
  expect(results).toHaveLength(50);
  expect(results.every(([rows]) => (rows as any[])[0].n !== undefined)).toBe(true);

  await pool.end();
});

test("pool handles connection errors with retry", async () => {
  const pool = mysql.createPool({
    host: "127.0.0.1",
    port: 3309,
    user: "root",
    database: "mydb",
    connectionLimit: 5,
  });

  // Kill all connections and verify pool reconnects
  await pool.execute("KILL CONNECTION_ID()").catch(() => {});

  // Next query should succeed after reconnect
  const [rows] = await pool.execute("SELECT 1 AS alive");
  expect((rows as any[])[0].alive).toBe(1);

  await pool.end();
});

// test/prisma-integration.test.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_URL }, // points to pscale connect proxy
  },
});

beforeAll(() => prisma.$connect());
afterAll(() => prisma.$disconnect());

afterEach(async () => {
  // Clean up in reverse dependency order
  await prisma.userPreferences.deleteMany();
  await prisma.user.deleteMany();
});

test("creates user with preferences in a transaction", async () => {
  const user = await prisma.$transaction(async (tx) => {
    const u = await tx.user.create({
      data: { email: "alice@example.com", name: "Alice" },
    });
    await tx.userPreferences.create({
      data: { userId: u.id, theme: "dark" },
    });
    return u;
  });

  const prefs = await prisma.userPreferences.findFirst({
    where: { userId: user.id },
  });
  expect(prefs?.theme).toBe("dark");
});