Migrating from MongoDB to PostgreSQL: How to handle embedded types/objects?

I'm an intermediate developer working with Next.js, Node.js, and React. I'm currently using Prisma with MongoDB for my project, but I'm considering migrating to PostgreSQL.

One of my biggest challenges is figuring out how to handle embedded types/objects that I use extensively in my MongoDB schema. For example, I have structures like:

// In my MongoDB Prisma schema
type ColorPalette {
  font       String @default("#000000")
  background String @default("#ffffff")
  primary    String @default("#ff0000")
  accent     String @default("#ff0000")
}

type FontPalette {
  primary     String @default("Roboto")
  secondary   String @default("Open Sans")
  handWriting String @default("Dancing Script")
}

model Brand {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  // other fields...
  colorPalette ColorPalette
  fontPalette  FontPalette
}
]]

// In my MongoDB Prisma schema
type ColorPalette {
  font       String @default("#000000")
  background String @default("#ffffff")
  primary    String @default("#ff0000")
  accent     String @default("#ff0000")
}

type FontPalette {
  primary     String @default("Roboto")
  secondary   String @default("Open Sans")
  handWriting String @default("Dancing Script")
}

model Brand {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  // other fields...
  colorPalette ColorPalette
  fontPalette  FontPalette
}
]]

`

I also have more complex nested structures like:

type Slide {
  title           DisplayableText?
  paragraphs      DisplayableText[]
  image           Image?
  settings        SlideOverrides?
  // more fields...
}

type DisplayableText {
  content String  @default("")
  isShown Boolean @default(true)
}

type Image {
  url      String
  alt      String
  caption  String?
  opacity  Float    @default(1)
  // more fields...
}

model Deck {
  id      String  @id @default(auto()) @map("_id") @db.ObjectId
  slides  Slide[]
  // other fields...
}

type Slide {
  title           DisplayableText?
  paragraphs      DisplayableText[]
  image           Image?
  settings        SlideOverrides?
  // more fields...
}

type DisplayableText {
  content String  @default("")
  isShown Boolean @default(true)
}

type Image {
  url      String
  alt      String
  caption  String?
  opacity  Float    @default(1)
  // more fields...
}

model Deck {
  id      String  @id @default(auto()) @map("_id") @db.ObjectId
  slides  Slide[]
  // other fields...
}

I know PostgreSQL doesn't support embedded types like MongoDB does. I'm considering using JSON/JSONB fields, but I'm concerned about:

0. Should normalize everything into separate tables, or use JSON fields?

Any advice on maintaining type safety with TypeScript when working with JSON fields in Prisma?

I have tried prisma generators before, and it's a mess (at least it was for me!). I prefer a "manual" approach, and I don't...clearly see how the workflow would be.

Thanks in advance for any insights!

Prisma•11mo ago•

2 replies

Risatoga

Migrating from MongoDB to PostgreSQL: How to handle embedded types/objects?

// In my MongoDB Prisma schema
type ColorPalette {
  font       String @default("#000000")
  background String @default("#ffffff")
  primary    String @default("#ff0000")
  accent     String @default("#ff0000")
}

type FontPalette {
  primary     String @default("Roboto")
  secondary   String @default("Open Sans")
  handWriting String @default("Dancing Script")
}

model Brand {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  // other fields...
  colorPalette ColorPalette
  fontPalette  FontPalette
}
]]

// In my MongoDB Prisma schema
type ColorPalette {
  font       String @default("#000000")
  background String @default("#ffffff")
  primary    String @default("#ff0000")
  accent     String @default("#ff0000")
}

type FontPalette {
  primary     String @default("Roboto")
  secondary   String @default("Open Sans")
  handWriting String @default("Dancing Script")
}

model Brand {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  // other fields...
  colorPalette ColorPalette
  fontPalette  FontPalette
}
]]

`

I also have more complex nested structures like:

type Slide {
  title           DisplayableText?
  paragraphs      DisplayableText[]
  image           Image?
  settings        SlideOverrides?
  // more fields...
}

type DisplayableText {
  content String  @default("")
  isShown Boolean @default(true)
}

type Image {
  url      String
  alt      String
  caption  String?
  opacity  Float    @default(1)
  // more fields...
}

model Deck {
  id      String  @id @default(auto()) @map("_id") @db.ObjectId
  slides  Slide[]
  // other fields...
}

type Slide {
  title           DisplayableText?
  paragraphs      DisplayableText[]
  image           Image?
  settings        SlideOverrides?
  // more fields...
}

type DisplayableText {
  content String  @default("")
  isShown Boolean @default(true)
}

type Image {
  url      String
  alt      String
  caption  String?
  opacity  Float    @default(1)
  // more fields...
}

model Deck {
  id      String  @id @default(auto()) @map("_id") @db.ObjectId
  slides  Slide[]
  // other fields...
}

Migrating from MongoDB to PostgreSQL: How to handle embedded types/objects?

Migrating from MongoDB to PostgreSQL: How to handle embedded types/objects?

Similar Threads

Similar Threads

Similar Threads