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");