Translate a Graph Database Schema to a Prisma Schema

When working on software projects, we have many options for a database structure. Although relational databases remain the standard choice, the graph database structure has been gaining steam. This is partly due to its flexible schema definition, which gives it an advantage in representing deep relationships with its node/edge structure. Graph databases also provide the querying power to produce real-time recommendations based on the connections between users and products or other content.

Our Project

On a recent project, we chose to migrate data from an Amazon Neptune graph database to an Amazon RDS PostgreSQL relational database. We valued the flexibility of the graph database and the ability to build our own recommendation engine on top of it. However, we no longer wanted this database as the source of truth for the project. Migrating to Postgres was a natural choice. This is because relational databases strongly support the enforcement of data consistency, integrity, and transaction management, making them suitable for applications where data accuracy and reliability are crucial.

In this project, we used Prisma ORM to work with our Postgres database. To migrate data from Amazon Neptune to PostgreSQL, my job was to translate our schema from a graph database to a relational database. Though I found many resources online about translation from a relational database to a graph structure, a discussion of the opposite translation was practically nonexistent. So here it is! I will outline an example of translating from a node/edge schema to a Prisma schema representing a relational database.

A Hypothetical Example

For our example, let’s consider a model for a streaming service where users buy and view episodes of television shows. They can also follow shows to receive notifications when new episodes are released. Users, episodes, and shows are represented as nodes in the graph. Meanwhile, purchases, views, follow events, and episode/show relationships are represented as edges.

Imagine the nodes and edges have the following properties:

User
– ID
– username
– email

Show
– ID
– name
– description
– genre

Episode
– ID
– title
– description
– premiere date
– price

Views
– status
– start date
– completed date

Owns
– purchase date
– transaction ID

Follows (no properties)

Belongs to (no properties)

We’ll map this graph schema to a Prisma schema. But, let’s first discuss how Prisma models data. Prisma typically describes the database structure with models defined in a Prisma schema file, using Prisma’s DSL (Domain Specific Language). These models describe tables in the database with their respective fields and relationships. My initial considerations when translating a similar graph schema to Prisma were the following.

1) What tables should exist in our relational database?
2) What relationships must exist between these tables?

Step 1

To start, we will need a table for each node type in the graph, and for edges, we will need to create relations. Relations in Prisma are one-to-one, one-to-many, and many-to-many. Taking the nodes and edges defined above, we can begin to map to Prisma models. For Users, it will be important to preserve the user properties as well as the “likes”, “owns” and “views” edges. Here is what I came up with.

model User {
    id                String @id @default(uuid())
    username          String?
    email             String
    purchases         Purchase[]
    views             View[]
    followedEpisodes  Follow[]
}

The ID, username, and email properties are represented as fields on the User model. This is quite straightforward. But, it’s a bit more complicated to define the edge relationships. For “owns,” “views,” and “follows” edges, we need to create one-to-many relationships. This is because a user can interact with multiple episodes, and episodes can have interactions with multiple users. To represent “owns,” “views,” and “follows” edges, we want to create many-to-many relationships, using relation tables we’ll define below.

Step 2

Next, we create similar models of the other node types.

model Show {
    id            String @id @default(uuid())
    name          String
    description   String
    genre         Genre
    episodes      Episodes[]
    follows       Follows[]
}

model Episode {
    id            String @id @default(uuid())
    title         String
    description   String
    premiereDate  DateTime
    price         Int
    show          Show @relation(fields: [showId], references: [id])
    showId        String
    purchases     Purchase[]
    views         View[]
}

For the Show and Episode model, we maintain the “belongsTo” relationship with a one-to-many relation where one show can have multiple episodes. We represent this in the Prisma schema with a relation attribute (noted as @relation on the Show model). In turn, this attribute references a scalar field called showId (the foreign key that connects Episode and Show in the database), along with a corresponding relation field on the Show model called Episodes.

Step 3

Finally, we create the relation tables (a.k.a. JOIN, link, or pivot tables) to maintain the many-to-many relations. These tables each have two relation fields (noted with the @relation attribute) and scalar fields for the corresponding IDs referenced by the relation fields.

model View {
    startDate.     DateTime
    completedDate  DateTime
    episode        Episode @relation(fields: [episodeId], references: [id])
    episodeId      String
    user           User @relation(fields: [userId], references: [id])
    userId         String

    @@id([episodeId, userId])
}

model Purchase {
    transactionID  String
    purchaseDate   DateTime
    episode        Episode @relation(fields: [episodeId], references: [id])
    episodeId      String
    user           User @relation(fields: [userId], references: [id])
    userId         String

    @@id([episodeId, userId])
}

model Follow {
    show      Show @relation(fields: [showId], references: [id])
    showId    String
    user      User @relation(fields: [userId], references: [id])
    userId    String

    @@id([showId, userId])
}

Now we have our complete Prisma schema! With this schema, we’ll preserve all data from the graph database when we migrate to the new relational database.

Graph Database to Relational Database

I hope this exercise helped put you in the right head space if you’re trying to translate a graph structure into a relational database structure. If so, I’d love to hear more about your experience in the comments!