← Back to Frameworks

PlanetScale

databaseMySQLserverlessbranchingschema managementdistributed databasedeveloper tools

Database Platform

PlanetScale

Overview

PlanetScale is a MySQL-compatible serverless database platform that revolutionizes database management through innovative branching capabilities. It enables developers to manage database schemas like Git repositories, allowing safe schema changes and seamless collaboration. Built on Vitess technology that powers YouTube's backend, PlanetScale provides global distribution and unlimited scalability for modern applications.

Details

Database Branching

PlanetScale's flagship feature is database branching, allowing developers to create database branches similar to Git branches. This enables safe schema testing, rollbacks, and collaborative development workflows without affecting production data.

MySQL Compatibility

Offers full MySQL compatibility, enabling seamless migration of existing MySQL applications. All ORMs, drivers, and toolchains work without modification, minimizing migration overhead and learning curves.

Serverless Architecture

Features automatic scaling based on traffic demands with pay-per-usage pricing. The database scales down to zero during idle periods, eliminating unnecessary costs while maintaining instant availability.

Advanced Security

Provides industry-standard encryption, network security, and access controls. SOC 2 Type II compliant with enterprise-grade security features for mission-critical applications.

Pros and Cons

Pros

  • Innovative Branching: Git-like database schema management with safe deployments and rollbacks
  • Full MySQL Compatibility: Seamless integration with existing MySQL applications and tools
  • Auto-scaling: Dynamic scaling based on traffic with high availability guarantees
  • Schema Analysis: Built-in tools to detect potential issues before deployment
  • Zero-downtime Deployments: Apply schema changes without production impact
  • Global Distribution: Low-latency data access for users worldwide

Cons

  • Foreign Key Limitations: Some foreign key constraints limited due to Vitess architecture
  • Stored Procedure Restrictions: Limited support for MySQL-specific features like stored procedures
  • Relatively New Platform: Limited long-term track record compared to established solutions
  • Vendor Lock-in: Dependency on PlanetScale-specific features may complicate migration
  • High-traffic Costs: Can be expensive for large-scale workloads compared to self-hosted solutions

Reference Links

Implementation Examples

Setup

# Install PlanetScale CLI
npm install -g @planetscale/cli

# Login to PlanetScale
pscale auth login

# Create a new database
pscale database create my-database --region us-east

# Create development branch
pscale branch create my-database development

Schema Design

-- Create schema on main branch
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_published (published)
);

-- Test schema changes on development branch
CREATE TABLE comments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  post_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_post_id (post_id),
  INDEX idx_user_id (user_id)
);

Data Operations

import { connect } from '@planetscale/database-js'

// Database connection configuration
const config = {
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD
}

const conn = connect(config)

// Basic query operations
async function createUser(email, name) {
  const result = await conn.execute(
    'INSERT INTO users (email, name) VALUES (?, ?)',
    [email, name]
  )
  return result.insertId
}

async function getUserById(id) {
  const results = await conn.execute(
    'SELECT * FROM users WHERE id = ?',
    [id]
  )
  return results.rows[0]
}

async function updateUser(id, updates) {
  const setClause = Object.keys(updates)
    .map(key => `${key} = ?`)
    .join(', ')
  const values = [...Object.values(updates), id]
  
  await conn.execute(
    `UPDATE users SET ${setClause} WHERE id = ?`,
    values
  )
}

// Transaction handling
async function createPostWithUser(userEmail, userName, postTitle, postContent) {
  const results = await conn.transaction(async (tx) => {
    // Create user
    const userResult = await tx.execute(
      'INSERT INTO users (email, name) VALUES (?, ?)',
      [userEmail, userName]
    )
    
    // Create post
    const postResult = await tx.execute(
      'INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)',
      [userResult.insertId, postTitle, postContent]
    )
    
    return {
      userId: userResult.insertId,
      postId: postResult.insertId
    }
  })
  
  return results
}

Scaling

# Branch management
pscale branch list my-database
pscale branch create my-database feature/new-schema
pscale branch diff my-database feature/new-schema main

# Create deploy request (propose schema changes)
pscale deploy-request create my-database feature/new-schema

# Review and merge deploy request
pscale deploy-request list my-database
pscale deploy-request deploy my-database 1

# Create read replicas
pscale branch create my-database read-replica --restore-from main --read-only

# Database monitoring
pscale database insights my-database
pscale branch insights my-database main

Backup and Recovery

// Automatic backups are managed by PlanetScale,
// utilize point-in-time recovery and branching features

async function createBackupBranch() {
  // Use CLI command to create point-in-time branch
  // pscale branch create my-database backup-2024-01-01 --restore-from main@2024-01-01T00:00:00Z
  
  // Or export data using JavaScript SDK
  const exportData = await conn.execute(`
    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    ORDER BY TABLE_NAME, ORDINAL_POSITION
  `)
  
  return exportData.rows
}

// Get database statistics
async function getDatabaseStats() {
  const stats = await conn.execute(`
    SELECT 
      TABLE_NAME,
      TABLE_ROWS,
      DATA_LENGTH,
      INDEX_LENGTH,
      DATA_FREE
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
  `)
  
  return stats.rows
}

Integration

// Express.js integration example
import express from 'express'
import { connect } from '@planetscale/database-js'

const app = express()
app.use(express.json())

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

// REST API endpoints
app.get('/api/users/:id', async (req, res) => {
  try {
    const result = await db.execute(
      'SELECT id, email, name, created_at FROM users WHERE id = ?',
      [req.params.id]
    )
    
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'User not found' })
    }
    
    res.json(result.rows[0])
  } catch (error) {
    console.error('Database error:', error)
    res.status(500).json({ error: 'Internal server error' })
  }
})

app.post('/api/users', async (req, res) => {
  try {
    const { email, name } = req.body
    
    const result = await db.execute(
      'INSERT INTO users (email, name) VALUES (?, ?)',
      [email, name]
    )
    
    res.status(201).json({
      id: result.insertId,
      email,
      name
    })
  } catch (error) {
    if (error.message.includes('Duplicate entry')) {
      return res.status(409).json({ error: 'Email already exists' })
    }
    
    console.error('Database error:', error)
    res.status(500).json({ error: 'Internal server error' })
  }
})

// Next.js API Routes usage example
export default async function handler(req, res) {
  const { method } = req
  
  switch (method) {
    case 'GET':
      try {
        const posts = await db.execute(`
          SELECT p.*, u.name as author_name
          FROM posts p
          JOIN users u ON p.user_id = u.id
          WHERE p.published = true
          ORDER BY p.created_at DESC
          LIMIT 10
        `)
        
        res.status(200).json(posts.rows)
      } catch (error) {
        res.status(500).json({ error: 'Failed to fetch posts' })
      }
      break
      
    default:
      res.setHeader('Allow', ['GET'])
      res.status(405).end(`Method ${method} Not Allowed`)
  }
}