PlanetScale is a serverless MySQL platform built on Vitess — the database clustering system that powers YouTube. UUID v7 as BINARY(16) primary keys is the optimal strategy for PlanetScale: the 74 random bits distribute writes evenly across Vitess shards while the timestamp prefix enables time-ordered queries. See UUID v7 in MySQL 8 for the underlying MySQL storage patterns.
Schema setup
CREATE TABLE orders (
id BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
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 (customer_id)
);
Generate UUID v7 in the application and pass it in:
-- Insert with UUID v7
INSERT INTO orders (id, customer_id, total)
VALUES (UUID_TO_BIN('018fbe3a-4c5d-7b12-8f3e-1a2b3c4d5e6f', 1), UUID_TO_BIN('...', 1), 99.99);
-- Read back
SELECT BIN_TO_UUID(id, 1) AS id, total, status FROM orders WHERE id = UUID_TO_BIN('018fbe3a-4c5d-7b12-8f3e-1a2b3c4d5e6f', 1);
The swap=1 flag reorders bytes so the timestamp occupies the most significant bits — UUID v7 sort order is preserved in BINARY(16) storage. See MySQL UUID Storage Best Practices.
Drizzle ORM with PlanetScale
// schema.ts
import { mysqlTable, customType, varchar, decimal } from 'drizzle-orm/mysql-core';
import { sql } from 'drizzle-orm';
import { v7 as uuidv7 } from 'uuid';
// Custom BINARY(16) type for UUID v7
const uuidBinary = customType<{ data: string; driverData: Buffer }>({
dataType() { return 'binary(16)'; },
toDriver(val: string) {
// UUID string → BINARY(16) via MySQL UUID_TO_BIN(val, 1)
return Buffer.from(val.replace(/-/g, ''), 'hex');
},
fromDriver(val: Buffer) {
const hex = val.toString('hex');
return [
hex.slice(0, 8), hex.slice(8, 12), hex.slice(12, 16),
hex.slice(16, 20), hex.slice(20),
].join('-');
},
});
export const orders = mysqlTable('orders', {
id: uuidBinary('id').primaryKey().$defaultFn(() => uuidv7()),
customerId: uuidBinary('customer_id').notNull(),
total: decimal('total', { precision: 10, scale: 2 }).notNull(),
status: varchar('status', { length: 20 }).default('pending').notNull(),
});
// db.ts
import { drizzle } from 'drizzle-orm/planetscale-serverless';
import { connect } from '@planetscale/database';
import * as schema from './schema';
const connection = connect({ url: process.env.DATABASE_URL! });
export const db = drizzle(connection, { schema });
// usage
import { db } from './db';
import { orders } from './schema';
import { gt, eq } from 'drizzle-orm';
// Create order — UUID v7 from $defaultFn
const [order] = await db.insert(orders)
.values({ customerId: 'user-uuid-here', total: '49.99' })
.returning();
// Cursor pagination
async function listOrders(cursor?: string, limit = 20) {
const rows = await db.select().from(orders)
.where(cursor ? gt(orders.id, cursor) : undefined)
.orderBy(orders.id)
.limit(limit);
const nextCursor = rows.length === limit ? rows[rows.length - 1].id : null;
return { rows, nextCursor };
}
See UUID in Drizzle ORM for the full Drizzle setup guide.
Node.js with @planetscale/database
import { connect } from '@planetscale/database';
import { v7 as uuidv7 } from 'uuid';
const conn = connect({ url: process.env.DATABASE_URL! });
// Helper: UUID string → hex for BINARY(16) with swap
function uuidToHex(uuid: string): string {
// Swap time fields for sort-preserving storage (same as UUID_TO_BIN(?, 1))
const clean = uuid.replace(/-/g, '');
// Move time_low (chars 0-7) and time_mid (8-11) after version (12-15)
return clean.slice(12, 16) + clean.slice(8, 12) + clean.slice(0, 8) + clean.slice(16);
}
async function createOrder(customerId: string, total: number) {
const id = uuidv7();
await conn.execute(
'INSERT INTO orders (id, customer_id, total) VALUES (UNHEX(?), UNHEX(?), ?)',
[uuidToHex(id), uuidToHex(customerId), total]
);
return id;
}
Cursor pagination with UUID v7
async function getOrdersPage(cursor?: string, limit = 20) {
const query = cursor
? 'SELECT BIN_TO_UUID(id, 1) id, total, status FROM orders WHERE id > UUID_TO_BIN(?, 1) ORDER BY id LIMIT ?'
: 'SELECT BIN_TO_UUID(id, 1) id, total, status FROM orders ORDER BY id LIMIT ?';
const args = cursor ? [cursor, limit] : [limit];
const result = await conn.execute(query, args);
const rows = result.rows as { id: string; total: string; status: string }[];
const nextCursor = rows.length === limit ? rows[rows.length - 1].id : null;
return { rows, nextCursor };
}
UUID v7’s BINARY(16) swap storage preserves chronological sort order — WHERE id > cursor ORDER BY id works correctly. See UUID v7 for Cursor Pagination.
Further reading
- UUID v7 in MySQL 8 — BINARY(16) Storage
- MySQL UUID Storage Best Practices
- UUID in Drizzle ORM — v7 Setup and Migration
- UUID v7 for Cursor Pagination
- UUID Zero-Downtime Migration
External references
Frequently asked questions
Should I store UUID v7 as BINARY(16) or VARCHAR(36) in PlanetScale?
BINARY(16) is the recommended storage format for PlanetScale. It is 2.25× more compact than VARCHAR(36), avoids character set conversion overhead, and produces tighter B-tree index pages. Use UUID_TO_BIN(uuid, 1) (swap=1) to store UUID v7 and BIN_TO_UUID(id, 1) to retrieve it. The swap flag rearranges bytes so the timestamp occupies the most significant bits, preserving UUID v7's chronological sort order in MySQL's binary comparison. See MySQL UUID Storage Best Practices.
How does UUID v7 work with Vitess sharding in PlanetScale?
Vitess uses the primary key's hash for shard routing by default. UUID v7's 74 random bits ensure writes are distributed evenly across shards, avoiding the hot spot problem that sequential auto-increment keys create. The 48-bit timestamp prefix does not affect shard distribution — Vitess hashes the full 128-bit value. UUID v7 gives you both even distribution and chronological ordering.
How do I use UUID v7 with PlanetScale's branching and schema migrations?
PlanetScale's non-blocking schema changes (pscale deploy requests) work with UUID v7 primary keys. To migrate from VARCHAR(36) to BINARY(16): create a new column, backfill with UUID_TO_BIN(old_id, 1), and swap the primary key in a deploy request. UUID v7's sort-preserving storage means indexes remain valid after the migration. See UUID Zero-Downtime Migration.