PlanetScale Database Setup for Web Application

Setting Up PlanetScale for Web Application

PlanetScale is managed MySQL based on Vitess (the same technology that scales YouTube and Slack). The key feature is branching for database schema: like git branches, but for table structures. Deploy requests allow schema changes without downtime and without fear of blocking production.

Creating a project

# Install CLI
curl -fsSL https://raw.githubusercontent.com/planetscale/cli/main/install.sh | bash

# Authentication
pscale auth login

# Create database
pscale database create myapp --region eu-central

# Default branch — main (this is production)
pscale branch list myapp

Connecting to production

# Proxy for local work
pscale connect myapp main --port 3309

# Connection string
DATABASE_URL="mysql://[email protected]:3309/myapp"

For production application — get credentials in dashboard: Settings → Passwords → New password. PlanetScale doesn't support direct connections without TLS.

DATABASE_URL="mysql://username:[email protected]/myapp?sslaccept=strict"

Branching for migrations

# Create branch for new feature
pscale branch create myapp add-user-profiles

# Connect to branch
pscale connect myapp add-user-profiles --port 3309

# Apply migration to branch
mysql -u root -h 127.0.0.1 -P 3309 myapp < migrations/add_profiles.sql

# Create deploy request (like pull request for schema)
pscale deploy-request create myapp add-user-profiles

# View diff
pscale deploy-request diff myapp 1

# Deploy
pscale deploy-request deploy myapp 1

Prisma + PlanetScale

PlanetScale doesn't support foreign keys at database level (Vitess limitation) — only application-level constraints.

// schema.prisma
datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"  // emulate FK at Prisma level
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?  @db.Text
  authorId  String
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())

  @@index([authorId])  // required with relationMode = "prisma"
}

# Create migration (don't apply to main directly)
prisma migrate diff \
  --from-empty \
  --to-schema-datamodel prisma/schema.prisma \
  --script > migrations/0001_init.sql

# Apply to branch
pscale connect myapp dev --port 3309
prisma db push  # or manually via mysql-client

Working with data

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_URL }
  },
  log: process.env.NODE_ENV === 'development' ? ['query'] : ['error']
})

// Query with pagination
async function getPosts(page: number, limit = 20) {
  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      skip: (page - 1) * limit,
      take: limit,
      include: { author: { select: { id: true, name: true } } },
      orderBy: { createdAt: 'desc' }
    }),
    prisma.post.count()
  ])
  return { posts, total, pages: Math.ceil(total / limit) }
}

Insights — query analytics

PlanetScale Dashboard → Insights shows top queries by load. No need to configure slow query log manually. Queries without indexes are highlighted separately.

PlanetScale limitations

• No stored procedures and triggers support
• No foreign key constraints at DB level
• No SELECT ... FOR UPDATE in some configurations
• Maximum row size — 65535 bytes
• Free plan: 5 GB storage, 1 billion row reads/month

Backups

PlanetScale automatically creates daily backups on paid plans. Manual export:

pscale database dump myapp main --output ./backup_$(date +%Y%m%d)

Timelines

PlanetScale project setup, Prisma connection and branching workflow configuration: 1 day. CI/CD integration (automatic deploy requests from branch): 1 more day. Full setup for new project: 1–2 days.