PostgreSQL 18 is the first PostgreSQL release with native UUID v7 support — gen_random_uuid_v7() and uuid_extract_timestamp() are built-in without any extensions. Combined with Prisma ORM, this is the cleanest UUID v7 setup available: database-generated IDs, no application-side generation required. See PostgreSQL 18 Native UUID v7 for the PostgreSQL context.
Prisma schema with PostgreSQL 18 UUID v7
// prisma/schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Order {
id String @id @default(dbgenerated("gen_random_uuid_v7()")) @db.Uuid
customerId String @db.Uuid
total Decimal @db.Decimal(10, 2)
status String @default("pending")
// No createdAt — uuid_extract_timestamp(id) provides creation time
customer Customer @relation(fields: [customerId], references: [id])
@@index([customerId])
}
model Customer {
id String @id @default(dbgenerated("gen_random_uuid_v7()")) @db.Uuid
email String @unique
orders Order[]
}
The generated migration SQL:
-- Migration: CreateOrders
CREATE TABLE "orders" (
"id" UUID NOT NULL DEFAULT gen_random_uuid_v7(),
"customer_id" UUID NOT NULL,
"total" DECIMAL(10,2) NOT NULL,
"status" TEXT NOT NULL DEFAULT 'pending',
CONSTRAINT "orders_pkey" PRIMARY KEY ("id")
);
-- Expression index on extracted timestamp (replaces created_at index)
CREATE INDEX "orders_created_at_idx" ON "orders" (uuid_extract_timestamp("id"));
Prisma client queries
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
// Create — UUID v7 generated by PostgreSQL
const order = await prisma.order.create({
data: { customerId: 'customer-uuid', total: 49.99 },
// id is returned with UUID v7 value from gen_random_uuid_v7()
});
console.log(order.id); // 018fbe3a-4c5d-7b12-8f3e-1a2b3c4d5e6f
// Cursor pagination — UUID v7 lexicographic = chronological
async function listOrders(cursor?: string, limit = 20) {
const orders = await prisma.order.findMany({
orderBy: { id: 'asc' },
take: limit,
...(cursor ? { skip: 1, cursor: { id: cursor } } : {}),
});
const nextCursor = orders.length === limit ? orders.at(-1)!.id : null;
return { orders, nextCursor };
}
Time-range queries with uuid_extract_timestamp()
// Query orders created in the last 24 hours using PostgreSQL 18 native function
const oneDayAgo = new Date(Date.now() - 86_400_000);
const recentOrders = await prisma.$queryRaw<{ id: string; total: number; created_at: Date }[]>`
SELECT
id,
total::float AS total,
uuid_extract_timestamp(id) AS created_at
FROM orders
WHERE uuid_extract_timestamp(id) > ${oneDayAgo}
ORDER BY id
LIMIT 100
`;
// Extract timestamp from UUID v7 in TypeScript (client-side)
function extractCreatedAt(id: string): Date {
const hex = id.replace(/-/g, '').slice(0, 12);
return new Date(parseInt(hex, 16));
}
Zero-downtime migration: UUID v4 → UUID v7
// Step 1: Add nullable UUID v7 column
// prisma/migrations/add_id_v7/migration.sql
// ALTER TABLE orders ADD COLUMN id_v7 UUID DEFAULT gen_random_uuid_v7();
// Step 2: Backfill script
async function backfillV7(batchSize = 1000) {
let processed = 0;
while (true) {
const result = await prisma.$executeRaw`
UPDATE orders
SET id_v7 = gen_random_uuid_v7()
WHERE id_v7 IS NULL
LIMIT ${batchSize}
`;
processed += result;
if (result < batchSize) break;
console.log(`Backfilled ${processed} rows`);
await new Promise(r => setTimeout(r, 10)); // yield
}
}
See UUID Zero-Downtime Migration for the full pattern.
Prisma $extends: computed createdAt from UUID v7
const prismaWithTimestamp = new PrismaClient().$extends({
result: {
order: {
createdAt: {
needs: { id: true },
compute(order) {
const hex = order.id.replace(/-/g, '').slice(0, 12);
return new Date(parseInt(hex, 16));
},
},
},
},
});
const order = await prismaWithTimestamp.order.findFirst();
console.log(order?.createdAt); // Date extracted from UUID v7
Further reading
- PostgreSQL 18 Native UUID v7
- UUID in Prisma — v7 Setup, Migration, Typed IDs
- UUID v7 in Prisma Accelerate
- UUID Zero-Downtime Migration
- UUID v7 for Cursor Pagination
External references
Frequently asked questions
How do I use gen_random_uuid_v7() as a Prisma column default in PostgreSQL 18?
In your Prisma schema, annotate the id field with @default(dbgenerated("gen_random_uuid_v7()")) @db.Uuid. Prisma will emit this as a PostgreSQL column default in the migration SQL. The UUID v7 value is generated by PostgreSQL at insert time — no application-side ID generation needed. Run npx prisma migrate dev to apply the migration. See UUID in Prisma.
How do I query records by creation time using uuid_extract_timestamp() in PostgreSQL 18?
Use uuid_extract_timestamp(id) directly in WHERE clauses: WHERE uuid_extract_timestamp(id) > NOW() - INTERVAL '1 day'. For best performance, create an expression index: CREATE INDEX ON orders (uuid_extract_timestamp(id)). This eliminates the need for a separate created_at column for time-range queries. See PostgreSQL 18 Native UUID v7.
Can I use Prisma's $queryRaw to call uuid_extract_timestamp()?
Yes. Use prisma.$queryRaw for queries that use PostgreSQL-specific functions: prisma.$queryRaw\`SELECT id, uuid_extract_timestamp(id) AS created_at FROM orders WHERE uuid_extract_timestamp(id) > ${date}\`. Prisma's typed raw query returns correctly typed results. For ORM-level access to the timestamp, add a virtual createdAt computed via a Prisma $extends result transformer.