https://www.prisma.io/ logo
Join Slack
Powered by
```> pii-api@1.0.0 start:nobuild /app > npm ...
# orm-help
r
Copy code
> pii-api@1.0.0 start:nobuild /app
> npm run prisma:migration && npm run prisma:seed && node ./dist/main
> pii-api@1.0.0 prisma:migration /app
> npm run prisma:cli -- migrate deploy --preview-feature
> pii-api@1.0.0 prisma:cli /app
> node ./node_modules/.bin/prisma "migrate" "deploy" "--preview-feature"
Environment variables loaded from .env
Environment variables loaded from prisma/.env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "oct_dev", schema "pii" at "<http://pgpool.pg:5432|pgpool.pg:5432>"
1 migration found in prisma/migrations
Error: P1002
The database server at `<http://pgpool.pg|pgpool.pg>`:`5432` was reached but timed out.
Please try again.
Please make sure your database server is running at `<http://pgpool.pg|pgpool.pg>`:`5432`.
npm ERR! code ELIFECYCLE
npm ERR! errno 1
r
@Ron Mizrahi 👋 Is your database instance accessible like this publicly? And the database URL along with the schema was added correctly?
r
right and right
I use the same env variables as local development which there it works fine.
I compose the connection string inside .env inside prisma folder:
DATABASE_URL=postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_DATABASE}?sslmode=required?&schema=pii&?pool_timeout=30
ofcourse doesn't work with or without the pool_timeout
r
Could you try without SSL and check? Also does your password have any special characters in them like 
@
?
prisma green 1
r
interesting
I think my password has it
what I should do in that case?
ha no it doesn't
I can't connect with or without ssl
tried both.
j
Random idea, is there some IP allowlist for that db?
prisma green 1
r
this is the same machine that used to run with typeorm, just fine 🙂
I have reverted the code to migrate with typeorm and it worked fine, same configs.
r
Could you DM me the URL so that I can try it on my end?
❤️ 1
r
it's accessible only inside the remote machine
it's production for my company, so I can't grant you access 😕
r
So does your development database connect properly?
r
nope and nope
all our databases are secure
even the development
I test it locally
😞
so I can't grant you access
don't you use the same pgclient as knex / typeorm?
r
No the client is not the same.
all our databases are secure
Hosted on AWS?
j
Note: 
.env
loading doesn’t override process.env (if both are set, process.env wins) I can recommend double checking the url in prod
❤️ 1
r
Will check
Thanks 💓
💚 1
I did override insert the connection string in the source .env
and removed the .env in prisma folder ,didn't help 😕
but, locally when I use my vpn I can't access the database with the same creds as well with prisma (I can with dbeaver)
so I can reproduce it somehow.. 😕
j
One step closer I guess 🤞
I can’t access the database with the same creds as well with prisma (I can with dbeaver)
Do you have the same error message or different one?
r
same one
P1002
I have tried to check - what happens If I enter wrong username or password - I get an error about it so I'm validated.
👍 1
j
And the url is exactly the same now than with TypeORM ?
r
yes
you use native pg client behind the scene?
j
Behind the scenes is here https://github.com/prisma/quaint
Is the database in the same region than the server? Maybe @Julius de Bruijn has an idea about this error
By the way try 
npx prisma -v
to check your current version
r
2.17.0
j
(for both cli and client I assume?)
r
yes I made sure those match
Hey
I can prove same config and connectio url works on typeorm but not on prisma
👀 1
😞
Sad, I wanted to deploy our new tool with prisma
same url for typeorm:
Copy code
ron@ron-ThinkPad-E14:~/git/pii-api$ npm run typeorm:seed

> pii-api@1.0.0 typeorm:seed /home/ron/git/pii-api
> npm run typeorm:cli -- migration:run -c seed


> pii-api@1.0.0 typeorm:cli /home/ron/git/pii-api
> npm run build && node ./node_modules/.bin/typeorm --config ./ormconfig.js "migration:run" "-c" "seed"


> pii-api@1.0.0 prebuild /home/ron/git/pii-api
> rimraf dist


> pii-api@1.0.0 build /home/ron/git/pii-api
> nest build

query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'pii_seed'
query: SELECT * FROM "pii_seed" "pii_seed"  ORDER BY "id" DESC
Copy code
query: SELECT * FROM "pii_seed" "pii_seed"  ORDER BY "id" DESC
No migrations are pending
j
I think opening an issue at this point with all the versions / environment specific (database / OS / prisma, where is the daatabase / vs server…) could be helpful for figuring that one out 🤔
r
and actually ran the migrations:
Copy code
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'pii_mig'
query: CREATE TABLE "pii_mig" ("id" SERIAL NOT NULL, "timestamp" bigint NOT NULL, "name" character varying NOT NULL, CONSTRAINT "PK_2b78045acb312153655d7526f4c" PRIMARY KEY ("id"))
query: SELECT * FROM "pii_mig" "pii_mig"  ORDER BY "id" DESC
0 migrations are already loaded in the database.
3 migrations were found in the source code.
3 migrations are new migrations that needs to be executed.
query: START TRANSACTION
query: CREATE SCHEMA IF NOT EXISTS "pii"
query: INSERT INTO "pii_mig"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1612902936671,"createPiiSchema1612902936671"]
Migration createPiiSchema1612902936671 has been executed successfully.
query: CREATE TABLE "pii"."activation" ("id" SERIAL NOT NULL, "activation_code" character varying NOT NULL, "inst_uuid" character varying NOT NULL, "sms_transmission_token" character varying NOT NULL, "auth_token" character varying NOT NULL, "inst_pub_key" character varying NOT NULL, "app_info" json NOT NULL, "system_info" json NOT NULL, "phone_number" character varying NOT NULL, "push_notification_token" character varying NOT NULL, "timezone" character varying NOT NULL, "user_settings" json NOT NULL, CONSTRAINT "PK_16191e9d1031fa81dfce606c878" PRIMARY KEY ("id"))
query: CREATE INDEX "IDX_16191e9d1031fa81dfce606c87" ON "pii"."activation" ("id") 
query: CREATE INDEX "IDX_f261bd10799e7018789667b7f4" ON "pii"."activation" ("activation_code") 
query: CREATE INDEX "IDX_b969c331239552efc87452768e" ON "pii"."activation" ("inst_uuid") 
query: CREATE INDEX "IDX_ae3352db269ea9e63003c8f2c9" ON "pii"."activation" ("sms_transmission_token") 
query: CREATE INDEX "IDX_bc7845ca749e46eeb705709415" ON "pii"."activation" ("auth_token") 
query: CREATE INDEX "IDX_cb82f0fd00475d61a277c78ba4" ON "pii"."activation" ("inst_pub_key") 
query: CREATE TABLE "pii"."session" ("id" SERIAL NOT NULL, "auth_token" character varying NOT NULL, "session_id" character varying NOT NULL, "ext_patient_id" character varying NOT NULL, CONSTRAINT "PK_11ca8633defb4cf013ebe19c26f" PRIMARY KEY ("id"))
query: CREATE INDEX "IDX_11ca8633defb4cf013ebe19c26" ON "pii"."session" ("id") 
query: CREATE INDEX "IDX_377484e9843f9048fddbc1d1f6" ON "pii"."session" ("auth_token") 
query: CREATE INDEX "IDX_a2d7fdd57b038109db637a3553" ON "pii"."session" ("session_id") 
query: CREATE INDEX "IDX_029c7770d274b5c80431fc5ce8" ON "pii"."session" ("ext_patient_id") 
query: INSERT INTO "pii_mig"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1612985211014,"actAndSess1612985211014"]
Migration actAndSess1612985211014 has been executed successfully.
query: CREATE TABLE "pii"."key_table" ("key_provision_token" character varying NOT NULL, "key_provision_base64" character varying DEFAULT null, CONSTRAINT "PK_e4e8c2a5f99ee45d871917dd9d6" PRIMARY KEY ("key_provision_token"))
query: CREATE INDEX "IDX_e4e8c2a5f99ee45d871917dd9d" ON "pii"."key_table" ("key_provision_token") 
query: ALTER TABLE "pii"."activation" ADD "key_provision_token" character varying
query: INSERT INTO "pii_mig"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1613998321364,"addProvisioningTable1613998321364"]
Migration addProvisioningTable1613998321364 has been executed successfully.
query: COMMIT
I can't provide more info than that
😕
I can share this problem in a zoom call but I can't share the info online
👍 1
j
what is behind 
<http://pgpool.pg|pgpool.pg>
, is it a pgbouncer or postgresql?
plus one +1 1
r
checking
j
another, did you try directly with the ipv4 address of the pool?
prisma rainbow 1
could this be some resolving issue?
👀 1
r
and checking this one as well
j
this kind of connection issues are either: firewall, using a wrong port or hostname, or we handle resolving the ip address differently in prisma compared to what nodejs does
we by default and by all standards always must try ipv6 first, so I'd try to connect directly with ipv4 address
because if you have dysfunctional ipv6 stack, and you still try connecting with ipv6 address, that leads to trouble like this
r
we use pgpool that works as load balancer for 2 postgres instances with replication
j
mm, and the idea is you should be able to use the hostname, but could you just get the 
<http://pgpool.pg|pgpool.pg>
ipaddress first with 
dig
or 
ping
, and try connecting directly with that address
if 
ping
gives you ipv6 address, try with 
ping -4
r
checking
returns ipv4
let me try with this ip address
j
also what would be interesting is to do 
dig <http://pgpool.pg|pgpool.pg> A
and 
dig <http://pgpool.pg|pgpool.pg> AAAA
r
ipv4 didn't work
Copy code
dig <http://pgpool.sqwrd.xyz|pgpool.sqwrd.xyz>

; <<>> DiG 9.16.1-Ubuntu <<>> <http://pgpool.sqwrd.xyz|pgpool.sqwrd.xyz>
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 47188
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 65494
;; QUESTION SECTION:
;pgpool.sqwrd.xyz.		IN	A

;; ANSWER SECTION:
<http://pgpool.sqwrd.xyz|pgpool.sqwrd.xyz>.	779	IN	A	52.149.138.113

;; Query time: 0 msec
;; SERVER: 127.0.0.53#53(127.0.0.53)
;; WHEN: Wed Feb 24 14:37:15 IST 2021
;; MSG SIZE  rcvd: 61
Copy code
dig <http://pgool.dev.sqwrd.xyz|pgool.dev.sqwrd.xyz> AAAA

; <<>> DiG 9.16.1-Ubuntu <<>> <http://pgool.dev.sqwrd.xyz|pgool.dev.sqwrd.xyz> AAAA
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 45641
;; flags: qr rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 65494
;; QUESTION SECTION:
;pgool.dev.sqwrd.xyz.		IN	AAAA

;; Query time: 112 msec
;; SERVER: 127.0.0.53#53(127.0.0.53)
;; WHEN: Wed Feb 24 14:37:29 IST 2021
;; MSG SIZE  rcvd: 48
j
so it only has ipv4 address
we don't really test with pgpool, could this be that we can't connect using it...
we test with pgbouncer, but these things should be transparent, we should always be able to use the database through the pools
so setting the connection string to 
<postgres://52.149.138.113:5432/>...
just blocks a while and then times out?
r
yes
j
you can connect to this database with 
psql
?
r
yes
and dbeaver
j
I'm still thinking you must somehow have a wrong connection string in use at some point of your app
somewhere it's overwritten
because we extensively test against pg9 to pg13 all versions, including pgbouncer
r
I did copy and paste
removed .env
defined it just in the .env inside prisma folder
I did double check 🙂
j
@Joël do you happen to know a way to output the actual connection string what the engines get?
(I'm not a js dev so I can't really help from that side of the stack)
this really looks like the connection string is not correct, I can't imagine anything else
j
I think the easy way would be to copy paste the full url directly in the schema (not using env())
j
I'm installing pgpool meanwhile and see if I can connect to it
party postgres 1
r
@Joël trying
💯 1
Didn't work..
I got nuts 😛
plus one +1 1
🤯 1
j
oh man me too...
😄 1
r
just checking I did write everything good there
j
you see postgres is our most used database, it's been used by tens of thousands of prisma devs and it's also our main focus in databases
that's why I want to understand what's really going on here
r
Yes I'm not here to blame
I'm here to help
💚 1
j
no me neither 😄
r
let me recheck everything once again
j
I'm trying to get the pgpool running
r
If you intend to use mine
know it's vpn blocked 😛
@Joël didn't work
👍 1
same url exactly on typeorm inside ormconfig.json works..
wtf
j
how deeply you'd like to debug this, I can go a bit lower level with you if you have time
r
We can go deep about it but know that I'm not a devops and I can't grant you access inside
but
we can have a session about it
We can try a few commands to investigate it
j
I'll write a small test project for you, you need rust though first, but it should be easy with https://rustup.rs/
I'm writing a project using our tools that you can run, and gives more debug output
r
installing
installed
what is next
j
about 15 mins, I'll contact you in private
wait, I need to write some code first
r
I have a meeting in 15 minutes
maybe later? 🙂
I know you want to help
j
yeah, I'll ping you
r
Thanks Juilus
Great community guys ❤️
💚 1
I did all the steps again
same connection string
same everything
insert it directly to prisma client
doesn't work
there is a problem for sure
I'll debug with @Julius de Bruijn
💯 1
s
hayho, is there an issue writedown of what caused the issue in the end? Facing more or less the same problem 😅
r
I didn't solve it
I gave up Prisma :(
s
for me the replacing url with ip seems to make this only error in ~60% of cases. I currently always run it twice or trice and it works... not a great solution though.
🔥 1
👀 1
3 Views
Open in Slack
PreviousNext
Powered by