object to schema?

[gaunt pulsar]

You're trying to convert an sql lite database (from Pocketbase) to a Postgres database (Supabase), right?

[bright fox]

That's right

[bright fox]

But I thought that it would probably be better to start from scratch

The top level was pretty easy to write a schema for. The roadblock came at attributes as I was told that I would need to create a model for each of those json fields instead of just pasting them in as Json fields as i did in PB

and obviously the same would be said for the ingredients

[gaunt pulsar]

Try the following

sqlite3 some.db .schema > schema.sql
sqlite3 some.db .dump > dump.sql
grep -vx -f schema.sql dump.sql > data.sql

Then, use postgres to push the sql file in the db

change some.db with the sqlite file

[bright fox]

pocketbase doesn't give me any sqlite files

[bright fox]

is sqlite3 some.db .schema > schema.sql a command?

[gaunt pulsar]

data.db is your database file

[bright fox]

after running all 3 commands - the final command returned grep: brackets ([ ]) not balanced

[gaunt pulsar]

Okay sorry for the delay! Now, push the data.sql content to your postgres database and it should work

psql connection_uri < data.sql

This should be the the right command for that

[bright fox]

right right, so this will just recreate the db structure. What about the actual prisma schema though? should I make each of those fields Json or should I make each one a model for type safery?

[bright fox]

^

[gaunt pulsar]

The schema file will be here for the rest

[gaunt pulsar]

I don't really know what this file is, never worked with this kind of file

[bright fox]

it's just a copy of the one of the attributes json object. The attributes table will contan all those properties. One row is one instance of attributes that will be related to its matching recipe

[gaunt pulsar]

Still i don't know what to do with that

[bright fox]

chatgpt told me to make each property its own model and then relate to the recipe attributes model and then relate that to the recipe model but it would mean that each model will have an extra id which seems so extra. It's opposite of clean and efficient. There must be a better way

[gaunt pulsar]

Okay, but without chat gpt?

Your goal is to migrate your database, right?

[bright fox]

No no, I'm not too bothered about migrating. I can easily create these tables in supabase in 10-15 minutes. The issue at hand is structuring the data properly.

• menus
• collections (many collections relate to one menu)
• recipes (many recipes relate to one collection)
• attributes (one attributes to one recipe)
• ingredients (many ingredients to one attributes)

supabase allows you to define JSON fields and Arrays of JSON objects. However, for me to get real type safety and to be able to generate correct types to use on client, I need to create a model for each of those JSON fields since Prisma would not parse the JSON to generate types by itself right?

[gaunt pulsar]

Except if i'm wrong, Supabase generates json data anyway. If it's not the case, yes i would recommend to use table instead of jsons

[bright fox]

Here's my prisma schema. So as far as the attributes go, each column in the attributes table would be defined as Jsonb, but I already have the types for each of those properties so can I not mark it as jsonb but also pass it the actual type of the jsonb to give both supabase and the client using the data the correct types?

[gaunt pulsar]

I think you can

Need to give it a try ¯_(ツ)_/¯

[bright fox]

I did - that's why I'm asking. I can't import the types nor can I define types in the prisma file. And if my prisma scheme has them defined as Json without the actual type, how would I then get type safety on the actual data?

[gaunt pulsar]

With Supabase, you don't get the type safety from Prisma's schema 🤔

[bright fox]

Are you available for a minute?

my prisma extension is going a little crazy. It keeps adding all those empty relations and multiple lines of RecipeAttributes in my models

look at the RecipeAttributes - country property. I defined a model for country and those are the only two properties that should be present in that model. Why does it keep adding the recipeattributes array and a relation when i'm just using that model to define the type?

[smoky briar]

So the formatter does more than just formatting and tries to fix things here. But it's making things worse 🙈

If you could create an issue and share how to reproduce this that would be helpful to fix

[bright fox]

I didn't do anything you wouldn't normally do. Just defining my schema - have not yet migrated anything

[smoky briar]

but to debug this on your side without waiting for a fix you can try to save without formatting

and then try npx prisma validate

[bright fox]

is that something i need to disable in the prisma extension settings?

[smoky briar]

in vscode normally

[smoky briar]

If you do command P then you can search for "save without formatting"

with your schema.prisma file open

I meant command shift P (shortcut for the command palette)

but changing the default formatter to None could work too I think

and I'm definitely curious to get your schema as text to be able to reproduce this

[bright fox]

Could you let me know if this is even valid?

[smoky briar]

Not it's not valid

Error: Prisma schema validation - (validate wasm)
Error code: P1012
error: Error validating model "RecipeAttributes": Ambiguous relation detected. The fields `basics`, `box_type`, `cuisine`, `diet_type`, `dietary_claims`, `food_brand`, `health_attributes`, `partnership` and `roundel` in model `RecipeAttributes` both refer to `Basic`. Please provide different relation names for them by adding `@relation(<name>).
  -->  schema.prisma:43
   | 
42 |   allergens                Allergen[]
43 |   basics                   Basic[]
44 |   box_type                 Basic                  
   | 

[bright fox]

but those properties are of the same type so i just used the same model. What am I not understanding?

[smoky briar]

Adding names helps here

like this

model RecipeAttributes {
  allergens                Allergen[]
  basics                   Basic[]
  box_type                 Basic  @relation(name: "BoxType")
  core_recipe_id           String
  country                  Country
  country_secondary        Country?               
  cuisine                  Basic           @relation(name: "Cuisine")                 
  description              String
  difficulty_level         DifficultyLevel?       
  diet_type                Basic          @relation(name: "DietType")         
  dietary_claims           Basic[]        @relation(name: "DietaryClaims")         
  dish_types               RecipeDeveloper[]
  equipment                RecipeDeveloper[]
  five_a_day               Int
  food_brand               Basic           @relation(name: "FoodBrand")                 
  gousto_reference         Int
  health_attributes        Basic[]  @relation(name: "HealthAttributes")         
  images                   Image[]
  chef_prepared            Boolean
  name                     String
  nutritional_information  NutritionalInformation
  prep_times               PrepTimes              
  partnership              Basic?      @relation(name: "Partnership")                
  rating                   Rating?                
  recipe_developer         RecipeDeveloper        
  roundel                  Basic?  @relation(name: "Roundel")                    
  spice_level              RecipeDeveloper?       
  shelf_life               ShelfLife              
  surcharges               Surcharges             
  ingredient_preparation   IngredientPreparation  
  // opposite relation to recipe
  recipe                   Recipe?
  recipeId                 String                 @id @unique // FK to Recipe model
  // relation to inredients
  nutritionalInformationId String
  surchargesId             String
}

after that it's country that has an issue
Error validating model "RecipeAttributes": Ambiguous relation detected. The fields country and country_secondary in model RecipeAttributes both refer to Country. Please provide different relation names for them by adding `@relation(<name>).

I just put random names here, feel free to adapt

[bright fox]

so country also needs to have a relation on the RecipeAttributes level?

model RecipeAttributes {
  allergens                Allergen[]
  basics                   Basic[]
  box_type                 Basic  @relation(name: "BoxType")
  core_recipe_id           String
  country                  Country @relation(name: "Country") 
  country_secondary        Country?               
  cuisine                  Basic           @relation(name: "Cuisine")                 
  description              String
  difficulty_level         DifficultyLevel?       
  diet_type                Basic          @relation(name: "DietType")         
  dietary_claims           Basic[]        @relation(name: "DietaryClaims")         
  dish_types               RecipeDeveloper[] @relation(name: "DishTypes") 
  equipment                RecipeDeveloper[] @relation(name: "Equipment") 
  five_a_day               Int
  food_brand               Basic           @relation(name: "FoodBrand")                 
  gousto_reference         Int
  health_attributes        Basic[]  @relation(name: "HealthAttributes")         
  images                   Image[]
  chef_prepared            Boolean
  name                     String
  nutritional_information  NutritionalInformation
  prep_times               PrepTimes              
  partnership              Basic?      @relation(name: "Partnership")                
  rating                   Rating?                
  recipe_developer         RecipeDeveloper   @relation(name: "RecipeDeveloper")      
  roundel                  Basic?  @relation(name: "Roundel")                    
  spice_level              RecipeDeveloper?       
  shelf_life               ShelfLife              
  surcharges               Surcharges             
  ingredient_preparation   IngredientPreparation  
  // opposite relation to recipe
  recipe                   Recipe?
  recipeId                 String                 @id @unique // FK to Recipe model
  // relation to inredients
  nutritionalInformationId String
  surchargesId             String
}

[smoky briar]

just a @relation(name: "something")

[bright fox]

now all my models are broken, it's asking me to add IDs

[smoky briar]

like

  country                  Country    @relation(name: "country1")   
  country_secondary        Country?      @relation(name: "country2") 

Yes you need a primary key

[bright fox]

and then add fields and references to the main one? box_type Basic @relation(name: "BoxType", fields: [], references: [])

Since i'm adding all those properties that should not really be present on the data, can I somehow make it so that the client only gets the data without all those extra fields?

[smoky briar]

I'm not sure, I don't have time to look more into this at the moment 😓

I can recommend trimming the schema and starting with less models

[bright fox]

Probably the best thing to do so that I can at least understand how this all works without constantly fixing my schema and not making any progress tryign to move my stuff over to supabase from pocketbase

[smoky briar]

Here is a simple example https://stackoverflow.com/a/67525780/1345244

[bright fox]

thanks