connect to cockroachdb

[araneta]

is it possible to connect to cockroachdb? the connection string is like this :
postgresql://aldo:@free-tier6.gcp-asia-southeast1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=$HOME/.postgresql/root.crt&options=--cluster%3Dsmoggy-deer-1769

i tried this in the props file but failed

dsl=./dsl
postgres=free-tier6.gcp-asia-southeast1.cockroachlabs.cloud/smoggy-deer-1769.defaultdb?user=aldo&password=KM89dd7]za#Kt3V&sslmode=verify-full&sslrootcert=$HOME/.postgresql/root.crt&options=--cluster%3Dsmoggy-deer-1769
migration
apply
force

[zapov]

I never tried it so I don't know. Without the error/logs its hard to say anything

[araneta]

here is the error
Error connecting to the database.
The connection attempt failed.
Retry database connection with different credentials (y/N):

i dont know how to add this line of code
sslmode=verify-full&sslrootcert=$HOME/.postgresql/root.crt&options=--cluster%3Dsmoggy-deer-1769
in the props file

[zapov]

CLC does not understand variables, so try it without the $HOME (just to test if it can connect at all)

[araneta]

i tried this but does not work
postgres=free-tier6.gcp-asia-southeast1.cockroachlabs.cloud/smoggy-deer-1769.defaultdb?user=aldo&password=KM89dd7zaKt3V&sslmode=verify-full&sslrootcert=/home/aldo/.postgresql/root.crt&options=--cluster%3Dsmoggy-deer-1769

how to include the ssl certificate file ?

[zapov]

What does it say exactly? Does this connection string work via pgjdbc?

[araneta]

here is the documentation using jdbc https://www.cockroachlabs.com/docs/v21.2/build-a-java-app-with-cockroachdb
we need to set the ssl file.
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerNames(new String[]{"{globalhost}"});
ds.setPortNumbers(new int[]{26257});
ds.setDatabaseName("{cluster_name}.bank");
ds.setSsl(true);
ds.setUser("{username}");
ds.setPassword("{password}");
ds.setSslMode("verify-full");
ds.setSslRootCert(System.getenv("$HOME/.postgresql/root.crt"));
ds.setReWriteBatchedInserts(true); // add rewriteBatchedInserts=true to pg connection string
ds.setApplicationName("BasicExample");