PlanetScale runs MySQL on Vitess with horizontal sharding and non-blocking DDL. UUID v7 is the natural primary key choice — shard-safe, time-ordered, and RFC-standard. See UUID in MySQL for full MySQL UUID patterns.
Schema with BINARY(16) primary key
CREATE TABLE orders (
id BINARY(16) NOT NULL,
customer_id BINARY(16) NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
PRIMARY KEY (id),
INDEX idx_customer_id (customer_id)
) ENGINE=InnoDB;
Insert with UUID v7 (Node.js)
import mysql2 from 'mysql2/promise';
import { v7 as uuidv7 } from 'uuid';
const pool = mysql2.createPool({
uri: process.env.DATABASE_URL, // PlanetScale connection string
ssl: { rejectUnauthorized: true },
});
async function createOrder(customerId: string, total: number) {
const id = uuidv7();
const bin = (u: string) => Buffer.from(u.replace(/-/g, ''), 'hex');
await pool.execute(
'INSERT INTO orders (id, customer_id, total) VALUES (?, ?, ?)',
[bin(id), bin(customerId), total]
);
return id;
}
async function getOrder(id: string) {
const bin = Buffer.from(id.replace(/-/g, ''), 'hex');
const [rows] = await pool.execute(
'SELECT LOWER(HEX(id)) AS id, total, status FROM orders WHERE id = ?',
[bin]
);
return (rows as any[])[0] ?? null;
}
Cursor pagination
async function listOrders(cursor?: string, limit = 20) {
const params: any[] = [limit];
let sql = 'SELECT LOWER(HEX(id)) AS id, total, status FROM orders';
if (cursor) {
sql += ' WHERE id > UNHEX(?)';
params.unshift(cursor.replace(/-/g, ''));
}
sql += ' ORDER BY id LIMIT ?';
const [rows] = await pool.execute(sql, params);
const items = rows as any[];
return {
items,
nextCursor: items.length === limit ? items.at(-1)?.id : null,
};
}
CHAR(36) alternative (simpler)
CREATE TABLE orders_simple (
id CHAR(36) NOT NULL,
customer_id CHAR(36) NOT NULL,
total DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
// Simpler insert — no UNHEX conversion
await pool.execute(
'INSERT INTO orders_simple (id, customer_id, total) VALUES (?, ?, ?)',
[uuidv7(), customerId, total]
);
Vitess sharding and UUID v7
UUID v7 works with Vitess scatter-gather queries because each shard can independently generate UUIDs with no conflict. For range-based sharding on id, the time-ordered prefix keeps recent records co-located on the same shard — improving locality for time-window queries.
Further reading
- UUID in MySQL — Full Guide
- UUID v7 in MySQL Galera Cluster
- UUID v4 vs UUID v7
- UUID v7 for Cursor Pagination
External references
Frequently asked questions
Why use UUID v7 instead of AUTO_INCREMENT in PlanetScale?
PlanetScale uses Vitess sharding — AUTO_INCREMENT IDs require a centralized sequence that does not scale across shards. UUID v7 is generated client-side with no coordination, making it naturally shard-safe. UUID v7's time-ordered prefix also reduces InnoDB B-tree page splits compared to UUID v4. See UUID v4 vs UUID v7.
Should I use CHAR(36) or BINARY(16) for UUID v7 in PlanetScale?
BINARY(16) stores UUID v7 in 16 bytes — 20 bytes smaller than CHAR(36). For high-volume tables, BINARY(16) reduces index size and improves scan performance. Convert with UNHEX(REPLACE(uuid, '-', '')) on insert and LOWER(HEX(id)) on read. For smaller tables or simpler code, CHAR(36) is acceptable. See UUID in MySQL.
Does UUID v7 work with PlanetScale schema branching?
Yes. PlanetScale schema branches are isolated MySQL databases. UUID v7 IDs are unique across all branches — the 74 random bits prevent collisions when merging branches. Unlike AUTO_INCREMENT, there is no sequence state to merge.