db pull from an existing database errors with SASL authentication failed

[simondpalmer]

I have an existing prisma database at supabase using prisma-client-js.
Everything is working except I now have a python server that I wish to connect to the same database.
For the python backend, when I use prisma db pull I get:

Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "db.xdyhoezrrqavppxekqqc.supabase.co:6543"

× Introspecting based on datasource defined in schema.prisma

Error: db error: FATAL: SASL authentication failed

I have created an exact replica of the js schema for the python client with the exception I changed provider to provider = "prisma-client-py"
See below schema.prisma that I have in my python .venv folder

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

datasource db {
  provider = "postgresql"
  url      = "postgres://postgres:PASSWORD@db.xdyhoezrrqavppxekqqc.supabase.co:6543/postgres"
}

// schema.prisma
model Account {
  id                 String  @id @default(cuid())
  userId             String
  type               String
  provider           String
  providerAccountId  String
  refresh_token      String?  @db.Text
  access_token       String?  @db.Text
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String?  @db.Text
  session_state      String?

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

  @@unique([provider, providerAccountId], name: "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())
  name          String?
  email         String?   @unique
  emailVerified DateTime? @map("email_verified")
  stripeId      String?    @unique
  isSubscribed  Boolean?   @default(false)
  image         String?
  createdAt     DateTime?
  updatedAt     DateTime?
  social        Json?
  accounts      Account[]
  sessions      Session[]
  lots          Lot[]
  lotAnalysis LotAnalysis[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

model Lot {
  id            Int     @default(autoincrement()) @id
  address       String  @unique
  latitude      Float   
  longitude     Float   
  apn           String
  acreage       Float
  zoneType      String
  zoneDescription     String
  geometry      Json?
  zone          Json?

  // Relation fields
  enquiry User[]
  analysis LotAnalysis[]
}

model LotAnalysis {
  role      UserRole

  // Relation Fields
  userId    String
  lotId     Int     @unique @default(autoincrement())
  price     Float?
  results   LotResult?
  user      User   @relation(fields: [userId], references: [id])
  lot       Lot @relation(fields: [lotId], references: [id])

  @@id([userId, lotId])
}

model LotResult {
  id     Int     @id @default(autoincrement())
  createdAt  DateTime @default(now())
  sides      Json?
  codeReview Json?
  setbacks   Json?

  analysisId    Int? @unique// relation scalar field
  lot      LotAnalysis? @relation(fields: [analysisId], references: [lotId]) // relation field
}

enum UserRole {
  CLIENT
  PROFESSIONAL
}

enum TokenType {
  EMAIL // used as a short lived token sent to the user's email
  API
}

Is there something I am missing?

[RobertCraigie]

Worth noting that you should be able to use the same schema for both languages & use the --generator flag to specify which one to use.

It could be a bug that was fixed in a recent version of Prisma, the last Prisma Client Python release is 3 versions behind Prisma. You could try installing from this repo to use the latest version

[simondpalmer]

So would that be something like prisma generate --generator js-client --generator py-client if so, what would be js-client and py-client?
Would it be the folder locations where these schemas are located and add in each of these schemas generator client { provider = "prisma-client-js" } for js-client and generator client { provider = "prisma-client-py" for the py-client?
Note the python API is in a seperate folder location entirely is that an issue for the CLI?

[RobertCraigie]

Ah sorry, you would define one schema, e.g.

generator python {
  provider = "prisma-client-py"
}

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

datasource db {
  provider = "postgresql"
  url      = "postgres://postgres:PASSWORD@db.xdyhoezrrqavppxekqqc.supabase.co:6543/postgres"
}

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

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

  @@unique([provider, providerAccountId], name: "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())
  name          String?
  email         String?   @unique
  emailVerified DateTime? @map("email_verified")
  stripeId      String?    @unique
  isSubscribed  Boolean?   @default(false)
  image         String?
  createdAt     DateTime?
  updatedAt     DateTime?
  social        Json?
  accounts      Account[]
  sessions      Session[]
  lots          Lot[]
  lotAnalysis LotAnalysis[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

model Lot {
  id            Int     @default(autoincrement()) @id
  address       String  @unique
  latitude      Float   
  longitude     Float   
  apn           String
  acreage       Float
  zoneType      String
  zoneDescription     String
  geometry      Json?
  zone          Json?

  // Relation fields
  enquiry User[]
  analysis LotAnalysis[]
}

model LotAnalysis {
  role      UserRole

  // Relation Fields
  userId    String
  lotId     Int     @unique @default(autoincrement())
  price     Float?
  results   LotResult?
  user      User   @relation(fields: [userId], references: [id])
  lot       Lot @relation(fields: [lotId], references: [id])

  @@id([userId, lotId])
}

model LotResult {
  id     Int     @id @default(autoincrement())
  createdAt  DateTime @default(now())
  sides      Json?
  codeReview Json?
  setbacks   Json?

  analysisId    Int? @unique// relation scalar field
  lot      LotAnalysis? @relation(fields: [analysisId], references: [lotId]) // relation field
}

enum UserRole {
  CLIENT
  PROFESSIONAL
}

enum TokenType {
  EMAIL // used as a short lived token sent to the user's email
  API
}

And in your JS service you would run a command like:

prisma generate --generator=js

In your Python service:

prisma generate --generator=python --schema=./path/to/schema

Note that the schema I showed defines both generators.

[simondpalmer]

Thanks so much!

[RobertCraigie]

Happy to help :)

[simondpalmer]

Thanks for the help, I now have a basic query in python like below

async def get_lot(user_id, lot_id):
    db = Prisma(auto_register=True)
    await db.connect()

    # find lot by lotid and userid
    lot = await db.LotAnalysis.find_unique(
        where = {
            'userId_lotId': {
                'userId': user_id,
                'lotId': lot_id,
                    } 
                },
        include = {
            'lot': True
        }
    )
    await db.disconnect()
    return lot

and

lot = asyncio.run(get_lot(user_id, lot_id))

but I get the error

  File "C:\Users\Simon Palmer\AppData\Local\Programs\Python\Python310\lib\asyncio\runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "C:\Users\Simon Palmer\AppData\Local\Programs\Python\Python310\lib\asyncio\base_events.py", line 641, in run_until_complete
    return future.result()
  File "C:\Users\Simon Palmer\Documents\Programming\ideally_app\api\main.py", line 206, in get_lot
    db = Prisma(auto_register=True)
  File "C:\Users\Simon Palmer\AppData\Local\Programs\Python\Python310\lib\site-packages\prisma\client.py", line 206, in __init__
    register(self)
  File "C:\Users\Simon Palmer\AppData\Local\Programs\Python\Python310\lib\site-packages\prisma\client.py", line 122, in register
    raise errors.ClientAlreadyRegisteredError()
prisma.errors.ClientAlreadyRegisteredError: A client has already been registered.

Is it getting confused because of the generator flag?

[RobertCraigie]

@simondpalmer no this error is because you're creating a new client instance for this particular function. You should either:

1. Create a single client instance & use it everywhere you access the database
2. Not pass in the auto_register argument

I would highly recommend option 1 as option 2 means you'll be creating a new connection pool every time you run that function which will be incredibly slow!

For some more background on what is happening here, the auto_register=True argument is used to register the Prisma client instance so that you can write queries directly using model classes, e.g.

from prisma.models import User

user = await User.prisma().create({'name': 'Robert'})

If you have not registered a Prisma instance then the above snippet will raise an error.