Problem with model and existing db

Get Help and Help Others
1

Hi,
I have a postgresql db that already have tables populated with tons of data.
I could not use the “prisma pull” cli as prisma does not handle cross schema references (yet, #1175), so I crated the model to one of the tables manually in the schema.prisma file:

model Source {
  id                   Int       @id @default(autoincrement())
  name                 String?
  name_acronym         String?
  description          String?
  url                  String?
  contract             String?
  date_start           DateTime?
  date_end             DateTime?
  original_id          String?
  original_create_date DateTime?
  original_update_date DateTime?
  active               Int?      @default(1)
  create_by            String?
  create_date          DateTime?
  update_by            String?
  update_date          DateTime?
  @@map(name: "sources")

}

I created cell, page, and service using yarn rw scaffold source cli.

When I try to get to: localhost:8910\sources i get the following error:

Invalid prisma.source.findMany()  invocation:

The table `gvg.Source` does not exist in the current database.

The table is accessible using prisma studio.

I tried changing the service from:

export const sources = () => {
  return db.source.findMany()
}

to

export const sources = () => {
  return db.sources.findMany()
}

but then I get the error: Cannot read property 'findMany' of undefined

Any thoughts on how to fix it, as I have about 20 more tables with relationship between then in the db with around 80M records, that I would like to access?

Thank you,
Wiezman

2

Whoops :sweat_smile: Just saw something I didn’t before which means my input was redundant.

It’s pretty strange how it says :thinking: :

The table `gvg.Source` does not exist in the current database.

when it is accessible via prisma studio.

3

Did you try introspecting you database instead?

You will want to be careful if you point to production data when you migrate and use deploy Deploying database changes with Prisma Migrate | Prisma Docs such that you aren’t resetting or recreating tables in prod.

I’d recommend you develop of a local db with the schema or a snapshot of production until you get your app’s schema in sync.

You’ll also need to be sure the prisma client is generated.

4

I didn’t try that on the prod db. I have a copy that I use for dev. Thank you for the warning though.

@dthyresson, I cannot use introspecting as Prisma introspect does not support cross schema connection, references, and keys.
I had to manually create the model in schema.prisma

If I change the table name in the db to Source, the issue goes away. Unfortunately, I cannot change the table name permanently, as it is used in the production application.

Any other thoughts?

5

This is known issue with how redwood handles model names internally. When you pass model name to generate command it is translated to PascalCase automatically by redwood js, which obviosly does not exist in database.
For more details: Support snake_case in `generate` command · Issue #2401 · redwoodjs/redwood · GitHub

6

I know this will sound weird, but I was able to resolve the issue with a workaround:

  1. created all models in the schema.prisma based on the tables structure in the existing postgres db.
  2. setup the api for working with sqlite db
  3. run: yarn rw prisma generate
  4. run: yarn rw prisma migrate dev
  5. scaffold all the tables
  6. stop the dev server
  7. changed the setting for postgres db
  8. run: yarn rw prisma generate
  9. start the server
  10. use the routes to make sure that all the queries are working.

weird, but working.
Not happy as don’t understand what is causing the issue. Need to follow these steps if need to add any additional table.

1 Like
7

Correct. You can modify the generated schema to consider snake case models, fields, and plural table names using Prisma @map and @@map.

See: Mapping model names to tables: Models

Prisma model naming conventions (singular form, PascalCase) do not always match table names in the database. A common approach for naming tables in databases is to use plural form and snake_case notation - for example: comments. When you introspect a database with a table named comments, the result Prisma model will look like this:

model comments {   // Fields}

However, you can still adhere to the naming convention without renaming the underlying comments table in the database by using the @@map attribute:

model Comment {   // Fields  @@map(name: "comments")}

Note: You can also @map a column name or enum value, and @@map an enum.

@map and @@map allow you to tune the shape of your Prisma Client API by decoupling model and field names from table and column names in the underlying database.

This Custom model and field names shows some examples.

I think you would be able to use Prisma introspection to introspect the db, map the table names and fields, then run the yarn rw g sdl <Model> in the expected pascal case.

8

@dthyresson , already tried that. Prisma does not support introspecting cross schema reference (#1175).
What I do not understand is why I have to “update” the migrations in sqlite first and then transfer to app to connect to the prostgres option.

9

Hi @wiezmankimchi perhaps I am misunderstanding, but if you intend to use PostgreSQL you can start off using that – you don’t need to being with SQLite. That’s just a quick-start option for those who do not of PG installed locally or access to a hosted database.

I switch mine immediately when I start a new app and work in PG throughout.

10

This is definitely a non-technical problem, but it is a major and common issue.

11

Hey @SerpaJohn1, if you have any more details about your specific use case and blockers you are hitting let us know and we’d be happy to help out!

12

I recommend you a wonderful site reports that will help you write any written work.