Generating DBML Files From A Prisma Schema

This post will cover how to generate Database Markup Language (DBML) files from a Prisma schema.

Prerequisites

Basic familiarity with the Database Markup Language.
Basic familiarity with Prisma.
A current Prisma project (I will not be detailing the setup for this part).

Getting started

Assuming that you have a npm project ready, the next step will be to install the plugin for our generator:

$ yarn add -D prisma-dbml-generator

Once this is ready, we can update the Prisma schema to have a dbml generator.

The schema code that I am using for the demo is the following:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

generator dbml {
  provider = "prisma-dbml-generator"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Post {
  id         String              @id @default(cuid())
  createdAt  DateTime            @default(now())
  updatedAt  DateTime            @updatedAt
  title      String              @db.VarChar(255)
  content    String?
  published  Boolean             @default(false)
  categories CategoriesOnPosts[]
  author     User                @relation(fields: [authorId], references: [id])
  authorId   String
}

model Category {
  id    String              @id @default(cuid())
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     String // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId String // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

model Profile {
  id     String  @id @default(cuid())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId String  @unique
}

model Account {
  id                 String  @id @default(cuid())
  userId             String
  type               String
  provider           String
  providerAccountId  String
  refresh_token      String?
  access_token       String?
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String?
  session_state      String?
  oauth_token_secret String?
  oauth_token        String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model User {
  id            String    @id @default(cuid())
  email         String?   @unique
  name          String?
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
  posts         Post[]
  profile       Profile?
  role          UserRole  @default(USER)
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

enum UserRole {
  USER
  SUPERUSER
}

Running the generator

Once the generator has been setup in the schema file, we can simply run the Prisma generate command:

$ npx prisma generate

The output for our prisma schema will be under prisma/dbml/schema.dbml.

//// ------------------------------------------------------
//// THIS FILE WAS AUTOMATICALLY GENERATED (DO NOT MODIFY)
//// ------------------------------------------------------

Table Post {
  id String [pk]
  createdAt DateTime [default: `now()`, not null]
  updatedAt DateTime [not null]
  title String [not null]
  content String
  published Boolean [not null, default: false]
  categories CategoriesOnPosts [not null]
  author User [not null]
  authorId String [not null]
}

Table Category {
  id String [pk]
  name String [not null]
  posts CategoriesOnPosts [not null]
}

Table CategoriesOnPosts {
  post Post [not null]
  postId String [not null]
  category Category [not null]
  categoryId String [not null]
  assignedAt DateTime [default: `now()`, not null]
  assignedBy String [not null]

  indexes {
    (postId, categoryId) [pk]
  }
}

Table Profile {
  id String [pk]
  bio String
  user User [not null]
  userId String [unique, not null]
}

Table Account {
  id String [pk]
  userId String [not null]
  type String [not null]
  provider String [not null]
  providerAccountId String [not null]
  refresh_token String
  access_token String
  expires_at Int
  token_type String
  scope String
  id_token String
  session_state String
  oauth_token_secret String
  oauth_token String
  user User [not null]

  indexes {
    (provider, providerAccountId) [unique]
  }
}

Table Session {
  id String [pk]
  sessionToken String [unique, not null]
  userId String [not null]
  expires DateTime [not null]
  user User [not null]
}

Table User {
  id String [pk]
  email String [unique]
  name String
  emailVerified DateTime
  image String
  accounts Account [not null]
  sessions Session [not null]
  posts Post [not null]
  profile Profile
  role UserRole [not null, default: 'USER']
}

Table VerificationToken {
  identifier String [not null]
  token String [unique, not null]
  expires DateTime [not null]

  indexes {
    (identifier, token) [unique]
  }
}

Enum UserRole {
  USER
  SUPERUSER
}

Ref: Post.authorId > User.id

Ref: CategoriesOnPosts.postId > Post.id

Ref: CategoriesOnPosts.categoryId > Category.id

Ref: Profile.userId - User.id

Ref: Account.userId > User.id [delete: Cascade]

Ref: Session.userId > User.id [delete: Cascade]

Viewing the output

To view the output, head to the DBDiagram website and paste in the generated code.

Hovering over the diagram and interacting will give you insight into the schema and relations.

Summary

Today's post demonstrated how to output a DBML file from a Prisma Schema and then view it on the DBDiagram website.

Resources and further reading

Photo credit: maxon