Quick Fix: Drizzle ORM does not support foreign key constraints when using PlanetScale’s sharding feature. As a workaround, you can generate unique IDs using a Universally Unique Lexicographically Sortable Identifier (ULID) library or implement your own solution using a millisecond timestamp and a random number. Store these IDs as two 64-bit BigInt words and use them as primary keys instead of auto-incremented values.
The Solutions:
Solution 1: Use a Universally Unique Lexicographically Sortable Identifier (ULID) or LinearID (LID)
In PlanetScale’s sharded database system, auto-incremented primary keys lose their uniqueness, making them unsuitable as foreign keys. Instead, you can use a ULID or LID to generate unique IDs for your rows.
ULID
A ULID is a 128-bit identifier that includes a millisecond timestamp and random data, ensuring that values are sequentially sortable. Here’s how to use the ulid package:
import { monotonicFactory } from 'ulid';
const ulid = monotonicFactory();
const id = ulid();
LID
The linearid package provides a similar solution, but it stores the timestamp and random data in two 64-bit words, allowing for more efficient storage and retrieval.
import { LID } from 'linearid';
const id = LID();
These unique IDs can be used as primary keys and foreign keys in your Drizzle ORM tables.
Q&A
Why foreign keys are not allowed on PlanetScale? What are the possible solutions to this problem?
—
I ran into an error while using foreign keys in my PlanetScale database. I found that foreign keys are not allowed on PlanetScale to avoid data inconsistencies and ensure database performance. To address this, you can explore solutions such as using a unique ID generation system like ULID or LID (linear ID) to create unique identifiers for your rows, making them searchable through indexes.
Video Explanation:
The following video, titled "TypeORM Relations Tutorial - FULL details! - YouTube", provides additional insights and in-depth exploration related to the topics discussed in this post.
... with foreign keys, etc. Then we'll discuss the different ways to ... Drizzle ORM #6- One To Many Relation ⭐. Sakura Dev•4.1K views · 16:48 · Go ...
