Chrysolites Tech Academy

Short-Video App with React Native and Supabase · Course book

Chapter 6: The Schema and Prisma

Pairs with Lesson 6: Designing the Database with Prisma

The database schema is the skeleton of a social app. Every feature in the coming chapters, uploading, the feed, likes, comments, follows and chat, reads and writes the tables defined here, so mistakes in the schema ripple through everything. This chapter explains how to design the tables for a short-video app and manage them with Prisma, a tool that describes the database in a readable schema file and applies changes to it. It covers why a schema tool is useful, how Prisma connects to the Supabase PostgreSQL database, how each table and relationship is modelled, how migrations and db push differ, and why UUIDs make good identifiers, with the odds of a collision worked out by hand.

Why manage the schema with a tool

Tables can be created by clicking in the Supabase dashboard, and for a quick experiment that is fine. For a real project, a schema written in a file has big advantages. It lives in version control alongside the code, so every change is recorded and reviewable. It can be applied to a fresh database in seconds, so a new developer, a test environment or a production project gets exactly the same structure. And it documents the data model in one readable place, which is invaluable when you return to a project months later.

Prisma provides this with a schema.prisma file. A datasource block says which database to use, read from an environment variable; model blocks describe tables, their columns and relationships; and Prisma's command-line tool compares the models with the real database and applies the difference. In this project Prisma is used on the developer's computer to build and evolve the tables, while the app itself reads and writes data through the Supabase client, which respects row level security. That split plays to the strengths of each tool.

Connecting Prisma to Supabase uses the database's connection string, found in the project's settings. It contains the database password, so it belongs in a local .env file that is never committed and never included in the app. Supabase offers both a direct connection and a pooled one; schema changes are best made through the direct connection.

Designing the tables

A short-video app needs six tables. Each has an id as its primary key and a created_at timestamp, so that lists can be sorted by time.

TableMain columnsRelationships
Userid, username, email, profile photo pathHas many videos, likes, comments; follows and is followed by users
Videoid, title, file path in storage, user_idBelongs to a user; has many likes and comments
Likeid, user_id, video_id, video_user_idLinks a user to a video; also records whose video it was
Commentid, text, user_id, video_id, video_user_idBelongs to a user and a video
Followerid, user_id, follower_user_idLinks two users: the followed and the follower
Chatid, text, user_id, chat_idMessages in a conversation identified by chat_id

Two design choices deserve explanation. The Like and Comment tables store video_user_id, the owner of the video, even though it could be found by looking up the video. This small, deliberate denormalisation makes the activity feed, "likes and comments on my videos", a simple filter on one table rather than a join, at the cost of one extra column to set when a like or comment is created. The Follower table describes a directed relationship: a row with user A and follower B means B follows A. A friendship, in this app, is two such rows, one in each direction.

Columns that link tables are foreign keys. Declaring them as relations in Prisma creates the database constraints that keep data consistent, for example rejecting a like for a video that does not exist. Deciding what happens when a parent row is deleted is part of the design: deleting a video should probably delete its likes and comments too, known as a cascading delete, rather than leaving orphaned rows.

Writing the models

In Prisma's schema language, each model lists its fields with a type and optional attributes. Relations are written from both sides: the side holding the foreign key names the field and references, and the other side lists the related records.

model User {
  id         String    @id @default(uuid())
  username   String    @unique
  created_at DateTime  @default(now())
  videos     Video[]
  likes      Like[]
}

model Video {
  id         String    @id @default(uuid())
  title      String?
  uri        String
  user_id    String
  user       User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  likes      Like[]
  created_at DateTime  @default(now())
}

model Like {
  id            String   @id @default(uuid())
  user_id       String
  video_id      String
  video_user_id String
  user          User     @relation(fields: [user_id], references: [id])
  video         Video    @relation(fields: [video_id], references: [id], onDelete: Cascade)
  created_at    DateTime @default(now())
  @@unique([user_id, video_id])
}

The @unique attribute on the username stops two accounts from sharing a name, and the compound @@unique([user_id, video_id]) on likes guarantees a user can like a video only once. A question mark after a type, as in String?, makes the column optional. The User model's id will later be set to the id that Supabase Authentication assigns, so that each account and its profile row share one identifier. Models for comments, followers and chats follow the same pattern.

Naming conventions are worth settling now, because changing them later touches every query. This schema uses lower-case column names with underscores, such as user_id, which match PostgreSQL's own style and need no quoting in SQL. Prisma model names are usually singular and capitalised, and Prisma can map a model to a differently named table with @@map if the database uses plural or lower-case table names. Whatever you choose, apply it everywhere: a mixture of userId and user_id across tables is a reliable source of confusing bugs when the app starts querying them through the Supabase client.

Migrations, db push and UUIDs

Prisma offers two ways to apply the schema. prisma migrate dev generates a migration, a SQL file describing the change, stores it in the project and applies it; the history of migrations can be replayed on any database, which is the safest approach for production. prisma db push applies the schema directly without creating migration files, which is quick for prototyping, at the cost of no change history and the possibility of data loss if a change cannot be applied safely, in which case Prisma warns first. Many projects use db push while the design is settling and switch to migrations before launch.

The models use UUIDs, universally unique identifiers, for their ids rather than counting numbers. A version 4 UUID is a 128-bit value of which 122 bits are random, written as 36 characters such as 3f2b8c1e-7d4a-4c6b-9e2f-1a5b6c7d8e9f. UUIDs can be generated anywhere, on the phone, on a server or in the database, without coordination; they do not reveal how many rows exist; and they cannot be guessed by adding one to a known id.

Worked example. How likely is it that two random UUIDs ever collide? With 122 random bits there are 2122 ≈ 5.3 × 1036 possible values. For n ids, the birthday approximation gives a collision probability of about n2 / (2 × 2122). For a billion ids, n = 109, that is 1018 / (1.06 × 1037) ≈ 9.4 × 10−20. Even for 1015 ids, a million billion, it is about 9.4 × 10−8. A 50 percent chance of any collision needs roughly 2.7 × 1018 ids.
10−1910−70.09 10−2010−101 106109101210151018 number of UUIDs generatedprobability of any collision
Figure 6.1 — Collision risk for random UUIDs. Each thousandfold increase in ids multiplies the risk by a million, yet it stays negligible far beyond any app's needs.

The practical conclusion is that collisions are not a concern for any realistic app, provided ids come from a proper random source, such as the database's built-in UUID function or a well-tested library. Sorting by UUID is meaningless, so lists are sorted by the created_at timestamp instead, ideally stored with its time zone so that times display correctly for every user.

Checking the result

After pushing the schema, the Supabase table editor shows the new tables, their columns and their foreign keys. Two follow-up tasks complete the job. First, enable row level security on every table and add policies, for example allowing signed-in users to read videos and to insert likes only with their own user_id; without policies, the app's queries will return nothing, which is the secure default. Second, add indexes on the foreign key columns that queries filter on, such as video_id in likes and comments and user_id in followers, so that the feed and activity screens stay fast as the tables grow. Prisma can declare these with @@index in the models, keeping them in the schema file with everything else.

Practice tasks

  1. Write a Prisma model for the Follower table, with a unique constraint preventing duplicate follows.
  2. Using the birthday approximation, estimate the collision probability for 1012 UUIDs.
  3. Explain why the Like table stores video_user_id, and what extra work this design requires.
  4. When would you use prisma db push, and when prisma migrate?
  5. What should happen to a video's comments when the video is deleted, and how is that expressed in the schema?
  6. List the indexes you would add to support the home feed, the comments screen and the activity feed.

Install Chrysolites Tech Academy

Android / Chrome / Edge: open the browser menu and choose Install app or Add to Home screen.

iPhone / iPad (Safari): tap the Share button, then Add to Home Screen.