Back to home

Generating DBML Files From A Prisma Schema main image

Generating DBML Files From A Prisma Schema

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

Prerequisites

  1. Basic familiarity with the Database Markup Language.
  2. Basic familiarity with Prisma.
  3. 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.

DBDiagram output

DBDiagram output

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

Personal image

Dennis O'Keeffe

@dennisokeeffe92
  • Melbourne, Australia

Hi, I am a professional Software Engineer. Formerly of Culture Amp, UsabilityHub, Present Company and NightGuru.
I am currently working on workingoutloud.dev, Den Dribbles and LandPad .

1,200+ PEOPLE ALREADY JOINED ❤️️

Get fresh posts + news direct to your inbox.

No spam. We only send you relevant content.