PlanetScale

BaaSServerlessMySQLDatabaseGit-like workflowBranching

BaaS Platform

PlanetScale

Overview

PlanetScale is a serverless MySQL platform that adopts Git-like workflows for database management. Its innovative database branching feature enables safe and efficient schema change management. By combining the power of traditional MySQL with cloud-native convenience, it helps development teams improve productivity.

Details

PlanetScale is a next-generation MySQL platform built on Vitess cluster technology. Its standout feature is "database branching," which allows you to manage database schema changes just like code branches. This makes schema changes in development, testing, and production environments safe and traceable.

It provides a Fetch API-based JavaScript client, eliminating the need for traditional connection pool management and optimizing for serverless environments. It also includes automatic scaling, encryption, and backup features as standard, significantly reducing operational overhead.

Pros and Cons

Pros

Git-like workflow: Database branching and merging capabilities for revolutionary schema change management
Serverless ready: No connection management required, direct connection from Edge environments and serverless functions
Zero downtime: Schema changes and scaling don't affect production environment
Automatic backups: Safe data management with point-in-time recovery and branching features
High performance: Distributed architecture with Vitess for handling large-scale data

Cons

MySQL only: Cannot choose other database engines like PostgreSQL
Learning curve: Need to adapt to workflows different from traditional MySQL
Cost: May be expensive for small-scale use cases
Limitations: Some MySQL features (like foreign key constraints) have restrictions

Reference Links

Code Examples

1. Basic Connection and Query Execution

import { connect } from '@planetscale/database'

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

const conn = connect(config)
const results = await conn.execute('SELECT * FROM users WHERE active = ?', [true])
console.log(results)

2. Connection Setup with Environment Variables

import { connect } from '@planetscale/database'

const config = {
  url: process.env.DATABASE_URL
}

const conn = connect(config)
const result = await conn.execute('SELECT COUNT(*) as total FROM products')
console.log(result.rows[0].total)

3. Transaction Processing

import { connect } from '@planetscale/database'

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

const results = await conn.transaction(async (tx) => {
  const user = await tx.execute('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', '[email protected]'])
  const profile = await tx.execute('INSERT INTO profiles (user_id, bio) VALUES (?, ?)', [user.insertId, 'Software Engineer'])
  return { user, profile }
})
console.log(results)

4. Client Factory Pattern

import { Client } from '@planetscale/database'

const client = new Client({
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD
})

const conn = client.connection()
const results = await conn.execute('SELECT * FROM orders WHERE status = ?', ['pending'])
console.log(results)

5. Custom Type Casting

import { connect, cast } from '@planetscale/database'

function customCast(field, value) {
  if (field.type === 'INT64' || field.type === 'UINT64') {
    return BigInt(value)
  }
  return cast(field, value)
}

const conn = connect({
  cast: customCast,
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD
})

const result = await conn.execute('SELECT id, balance FROM accounts WHERE id = ?', [1])
console.log(typeof result.rows[0].balance) // 'bigint'

6. Next.js App Router Integration Example

// app/api/users/route.ts
import { connect } from '@planetscale/database'

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

export async function GET() {
  try {
    const results = await conn.execute('SELECT id, name, email FROM users ORDER BY created_at DESC LIMIT 10')
    return Response.json({ users: results.rows })
  } catch (error) {
    return Response.json({ error: 'Database query failed' }, { status: 500 })
  }
}

export async function POST(request: Request) {
  const { name, email } = await request.json()
  
  try {
    const result = await conn.execute('INSERT INTO users (name, email) VALUES (?, ?)', [name, email])
    return Response.json({ id: result.insertId, name, email }, { status: 201 })
  } catch (error) {
    return Response.json({ error: 'Failed to create user' }, { status: 500 })
  }
}