Cannot seed or pg_restore prisma-postgres db
I have set up a prisma postgres project, and have successfully deployed my schema to it. I can not run
prisma seed against it as I get "Permission denied for Table xyz" error, and when trying to import a backup from my previous local postgres DB via tunnel I get errors "Table/Function" etc already exists".
What am I missing? I followed all steps on the dashboard, just wanna see if the paid solution is any good with deep nested includes, wher the ORM for a locally hosted DB fails. I also get all sorts of errors in the UI for console.prisma.io15 Replies
You decided to hold for human wisdom. We'll chime in soon! Meanwhile,
#ask-ai is there if you need a quick second opinion.Hi max!
Could you please share the email id or GitHub handle with which you signed up?
I would like to take a look at the logs to further investigate this
Do you mind sharing the errors you are seeing in the UI?
do the id's from this linke help? https://console.prisma.io/cmdocays4031b0k0wvnze0qle/cmdoeg1kp02i9vr0vb72ds5a5/cmdoeg1kp02i5vr0vq607z8vn/dashboard
Yes, that's helpful.
Thanks, I am checking.
I'm asuming I misunderstand the "Import existing data" option. Does it only work for new databases that don't have a schema deployed onto it yet?
Yes, you can try the instructions of "Importing existing data" on an empty database and that should work as expected.
Just to confirm, when you run the
prisma db seed command, are you running it on the connection string starting with prisma+postgres:// or are you using the direct connection string starting with postgres://yes in my env i just have
prisma+postgres://
im getting different errors now that are on my side. I executed. npx prisma db seed -- --env dev
If i remove the env option it works. I've previoulsy needed this for the old setup.
I'll keep playing around, thanks for your help. Will try with a new database if it's not possible to load a backup with pg_restore into an existing oneI see these errors in our logs:
I am glad to hear that removing the env option fixes the issue.
Do you mind using the Direct TCP connection string and confirm if that works?
You can get the Direct TCP connection string if you create a new API Key
I've created a new Database and imported via pg_restore, the Size of the db is now larger than an empty one so i think it has data, however in prisma studio its all still empty. I'm asuming this is due to to our migration scrips enabling RLS for all tables. Rows can only be retrieved when the transaction has a valid transaction variable set (
tenant_id). However I would've assumed that in Prisma Studio I can see data from the perspective of a superuser?
Connection to it directly via TCP works as you suggested
Thanks for pointing that out!I think the RLS policy is preventing the Prisma Studio from viewing the data. Could you temporarily disable it for one of the table and check if that table is visible?
Disabled it for table "AbsenceType", but still nothing
@Nurul (Prisma) but don't worry. I'll worry about this later. The direct TCP link does work nicely and gives me more insight into the db! Thanks a lot for the info, wasn't sure it was possible to connect to the DB with external tools, this is cool!
I've made a very interesting finding tho. If I seed both the prisma postgres hosted db and a local postgres instance running inside docker with the exact same data, and run the exact same SQL-Query aganist it (a very large query that was build by prisma-orm, that I exctracted from prisma/optimize and added the variables back in) its about 40x faster on your hosted instance than locally.
This surprises me a lot! What things do you do between me sending a sql query and the database executing it? Since im not running it through PrismaClient, I'm wondering why its that much faster? Do you have any idea? Its 1:1 the exact same data and sql query. Its 3 seconds (!!!) locally and just 80ms on prisma/postgres
Disabled it for table "AbsenceType", but still nothingI'll need to check with team internally on how RLS will affect Prisma Studio.
Regarding the latencyPrisma Postgres runs on a unique bare metal architecture, leveraging technologies like unikernels and high-bandwidth networking. This setup is designed for extremely low-latency and high-throughput, which is not typical for local Dockerized Postgres setups that often run on virtualized or resource-constrained environments.
cool! impressive speeds... I wish i could have the same on a local machine for testing purposes and better debugging...
Just to understand the Architecture better... Is there more layers between the prisma-client processing a query and returning the result vs executing sql directly on the db? Like caching for example. Will I benefit from caching also if I execute sql directly, or only when doing so from the PrismaClient? Cause it does surpsie me that some of my queries run 1000-3000ms locally. So maybe the query itself has never been the problem but our postgres setup has. I always thought our queries were super inefficient cause we do mand deep nested includes in the client that run a lot concurrently. But if it can be that much faster on your hosting, maybe it's not the query thats the problem
@Nurul (Prisma)
Just to follow up on this: the 40x speed improvement was just from an output query from Prisma, and i managed to get it as fast on my local machine. I got Prisma postgres to run fully without rls but its now actually much slower than our productive database. It was interesting to check out what you guys have been working on but we'll better stick to our setup for now.
Thanks for the update, Do you mind sharing your current setup?
Nothing fancy, just locally the default postgres docker-image and remote on a kubernetes cluster also a default instance of postgres. I was a little surprised once i got it to work, any idea why we're having such slow responses? it was probably like 3x slower than what we already have. And we are aware that our queries currently are pretty inefficient with even 300ms per query on a local machine for deeply nested includes over views & tables. But this can be solved with writing better views / de-normalizing some data etc...