PlanetScale Has a Free API: Here's How to Use It for Serverless MySQL

#planetscale #serverless #mysql #database

PlanetScale offers a serverless MySQL-compatible database with branching, non-blocking schema changes, and a REST API. The free Hobby plan gives you 5GB storage and 1 billion row reads per month.

Why Use PlanetScale?

Serverless — no connection pooling headaches
Branching — test schema changes like Git branches
Non-blocking schema changes — no downtime migrations
MySQL compatible — use any MySQL client or ORM

Getting Started

PlanetScale provides a REST API and a MySQL-compatible connection string:

# Connect via MySQL CLI
mysql -h aws.connect.psdb.cloud -u your-username -p --ssl-mode=VERIFY_IDENTITY

# Or use the PlanetScale CLI
pscale auth login
pscale shell my-database main

Python Client (mysql-connector)

import mysql.connector

def get_connection():
    return mysql.connector.connect(
        host='aws.connect.psdb.cloud',
        user='your-username',
        password='your-password',
        database='my-database',
        ssl_ca='/etc/ssl/certs/ca-certificates.crt'
    )

# Query data
conn = get_connection()
cursor = conn.cursor(dictionary=True)
cursor.execute('SELECT * FROM products WHERE price < %s ORDER BY price LIMIT 10', (100,))
for row in cursor.fetchall():
    print(f"{row['name']:30s} ${row['price']:.2f}")
cursor.close()
conn.close()

Serverless with PlanetScale HTTP API

import requests

class PlanetScaleClient:
    def __init__(self, host, username, password, database):
        self.url = f"https://{host}/psdb.v1alpha1.Database/Execute"
        self.auth = (username, password)
        self.database = database

    def execute(self, query, params=None):
        payload = {'query': query}
        if params:
            payload['params'] = params
        resp = requests.post(self.url, json=payload, auth=self.auth)
        return resp.json()

db = PlanetScaleClient(
    host='aws.connect.psdb.cloud',
    username='your-username',
    password='your-password',
    database='my-database'
)

# Execute queries via HTTP - no connection management!
result = db.execute('SELECT COUNT(*) as total FROM users')
print(f"Total users: {result}")

Database Branching

# Create a branch (like Git for your database)
pscale branch create my-database feature-new-schema

# Connect to the branch
pscale shell my-database feature-new-schema

# Make schema changes safely
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(500);
ALTER TABLE products ADD INDEX idx_category (category);

# Create a deploy request (like a pull request for schema)
pscale deploy-request create my-database feature-new-schema

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

Schema Migration Without Downtime

def safe_migration(db, migration_sql):
    # PlanetScale handles non-blocking schema changes
    # No locks, no downtime, no maintenance windows

    migrations = [
        'ALTER TABLE orders ADD COLUMN shipping_method VARCHAR(50) DEFAULT "standard"',
        'ALTER TABLE orders ADD COLUMN estimated_delivery DATE',
        'ALTER TABLE products ADD FULLTEXT INDEX ft_search (name, description)',
    ]

    for sql in migrations:
        print(f"Applying: {sql[:60]}...")
        db.execute(sql)
        print("  Applied successfully (non-blocking!)")

safe_migration(db, None)

Connection Pooling for Serverless

# PlanetScale's serverless driver eliminates connection pooling issues
# Perfect for Vercel, Cloudflare Workers, AWS Lambda

from planetscale import connect

async def handler(request):
    conn = connect({
        'host': 'aws.connect.psdb.cloud',
        'username': 'your-username',
        'password': 'your-password'
    })

    results = await conn.execute('SELECT * FROM products WHERE category = ?', ['electronics'])
    return {'products': results.rows}

Analytics Queries

def revenue_dashboard(db):
    queries = {
        'Today Revenue': 'SELECT SUM(total) as rev FROM orders WHERE DATE(created_at) = CURDATE()',
        'This Month': 'SELECT SUM(total) as rev FROM orders WHERE MONTH(created_at) = MONTH(CURDATE())',
        'Top Products': 'SELECT p.name, COUNT(*) as sales, SUM(oi.quantity * oi.price) as revenue FROM order_items oi JOIN products p ON oi.product_id = p.id GROUP BY p.id ORDER BY revenue DESC LIMIT 5',
        'Conversion Rate': 'SELECT (SELECT COUNT(*) FROM orders WHERE DATE(created_at) = CURDATE()) / (SELECT COUNT(*) FROM sessions WHERE DATE(created_at) = CURDATE()) * 100 as rate'
    }

    for name, query in queries.items():
        result = db.execute(query)
        print(f"{name}: {result}")

revenue_dashboard(db)

Real-World Use Case

A startup needed to add a new column to a 50M-row table in production. With traditional MySQL, this would lock the table for 30+ minutes. With PlanetScale, they created a branch, tested the migration, then deployed it — zero downtime, zero locks. The schema change completed in the background while the app served requests normally.