docs: document how to connect with unix socket connection

[donchev7]

I am trying to connect hasura to a local postgres instance via Unix socket connection. Is this possible? Couldn't find anything on the website

[0x777]

@donchev7 This is currently not possible but hopefully soon.

[coffenbacher]

This would be great for Google Cloud Run, which offers socket connections to Cloud SQL instances

[pronevich]

@0x777 as was mentioned by @coffenbacher this required by Cloud Run to connect Cloud SQL without adding SQL Proxy to container like in solution described in #2326

[schettino]

As per v1.0.0-beta.8, which has better support for Postgres connection string parameters, I've managed to make the unix connection to work, from Cloud Run to Cloud SQL, without embedding the proxy into the container.

The connection should look something like this:

postgres://<user>:<password>@/<database>?host=/cloudsql/<instance_name>

Notice that the client will add the suffix /.s.PGSQL.5432 for you.

Make sure you added also the Cloud SQL client permission.

[wiesson]

@schettino, could you share your config / files? I was not able to get a connection from clound run to sql.

[coco98]

@schettino Would be great if you could submit a small PR to the docs as well. https://docs.hasura.io/1.0/graphql/manual/guides/deployment/google-kubernetes-engine-cloud-sql.html

You can use the github browser editor feature to add your notes, so you don't need to build.

@rakeshkky did you test this with google cloud sql as well?

[schettino]

Absolutely. Will do that, first thing in the morning. "Google Cloud Platform with Cloud Run and Cloud SQL", sound good?

@wiesson not sure what it can be. I've done all over from scratch here, here's how my revision looks like:

apiVersion: serving.knative.dev/v1
kind: Service
metadata:
  name: hasura
  namespace: '485552058505'
  selfLink: /apis/serving.knative.dev/v1/namespaces/[NAMESPACE_NUMBER]/services/hasura
  uid: [UID]
  resourceVersion: AAWVniUSRRg
  generation: 4
  creationTimestamp: '2019-10-24T00:15:54.649676Z'
  labels:
    cloud.googleapis.com/location: us-central1
  annotations:
    run.googleapis.com/client-name: gcloud
    serving.knative.dev/creator: [ACCOUNT]
    serving.knative.dev/lastModifier: [ACCOUNT]
    client.knative.dev/user-image: gcr.io/[PROJECT_ID]/hasura:v1.0.0-beta.8
    run.googleapis.com/client-version: 268.0.0
spec:
  traffic:
  - percent: 100
    latestRevision: true
  template:
    metadata:
      labels:
        client.knative.dev/nonce: olqbeswjqo
      annotations:
        run.googleapis.com/cloudsql-instances: [PROJECT_ID]:[REGION]:[CLOUDSQL_DB_ID]
    spec:
      containers:
      - image: gcr.io/[PROJECT_ID]/hasura:v1.0.0-beta.8
        env:
        - name: HASURA_GRAPHQL_DATABASE_URL
          value: postgres://[USER]:[PASSWORD]@/c?host=/cloudsql/[PROJECT_ID]:[REGION]:[CLOUDSQL_DB_ID]
        - name: HASURA_GRAPHQL_ENABLE_CONSOLE
          value: 'true'

Perhaps I'm missing somethin. Will create a repo to reproduce it alongside the docs page, so we can move from there.

[plmercereau]

@schettino, could you share your config / files? I was not able to get a connection from clound run to sql.

Hello @wiesson, I had the same problem, I only forgot to add a set-cloudsql-instances flag when I deploy, as it was not required when I was using the proxy. More on google's documentation. I hope it will solve your problem.

ps many thanks for the tip, @schettino

[wiesson]

@plmercereau wow - that is super hard to miss!!

gcloud beta run deploy \
    --image gcr.io/<PROJECT_ID>/graphql-server:latest \
    --region <CLOUD_RUN_REGION> \
    --platform managed \
    --set-env-vars HASURA_GRAPHQL_DATABASE_URL="postgres://<DB_USER>:<DB_PASS>@/<DB_NAME>?host=/cloudsql/<PROJECT_ID>:<CLOUD_SQL_REGION>:<INSTANCE_ID>" \
    --timeout 900 \
    --set-cloudsql-instances <PROJECT_ID>:<CLOUD_SQL_REGION>:<INSTANCE_ID>

Here is the command that I've used for cloud run! Works perfectly! 👌

// edit: Also updated my sw question -> https://stackoverflow.com/questions/58361874/use-hasura-with-google-clound-run-and-google-cloud-sql

[ighormartins]

@plmercereau wow - that is super hard to miss!!

gcloud beta run deploy \
    --image gcr.io/<PROJECT_ID>/graphql-server:latest \
    --region <CLOUD_RUN_REGION> \
    --platform managed \
    --set-env-vars HASURA_GRAPHQL_DATABASE_URL="postgres://<DB_USER>:<DB_PASS>@/<DB_NAME>?host=/cloudsql/<PROJECT_ID>:<CLOUD_SQL_REGION>:<INSTANCE_ID>" \
    --timeout 900 \
    --set-cloudsql-instances <PROJECT_ID>:<CLOUD_SQL_REGION>:<INSTANCE_ID>

Here is the command that I've used for cloud run! Works perfectly! 👌

// edit: Also updated my sw question -> https://stackoverflow.com/questions/58361874/use-hasura-with-google-clound-run-and-google-cloud-sql

Do you happen to know if subscriptions (Outbound sockets) are working?

[wiesson]

Good point, I haven't tested it yet, but I will probably have a look later that day.

[ighormartins]

Good point, I haven't tested it yet, but I will probably have a look later that day.

Actually, I can't make it work with cli-migrations.

I'm building this simple image:

FROM hasura/graphql-engine:v1.0.0-beta.8.cli-migrations

WORKDIR /hasura

COPY ./migrations /hasura-migrations

ENV HASURA_GRAPHQL_ENABLE_CONSOLE="false"
ENV HASURA_GRAPHQL_UNAUTHORIZED_ROLE="anonymous"
ENV HASURA_GRAPHQL_JWT_SECRET='{"type":"RS256", "jwk_url": "https://www.googleapis.com/service_accounts/v1/jwk/securetoken@system.gserviceaccount.com", "audience": "adasdas", "issuer": "https://securetoken.google.com/asdsa"}'
ENV HASURA_GRAPHQL_ADMIN_SECRET="eg4"
ENV HASURA_GRAPHQL_DATABASE_URL="postgres://postgres:eg1@/eg2?host=/cloudsql/eg3"

ENV HASURA_GRAPHQL_MIGRATIONS_SERVER_TIMEOUT=60
ENV HASURA_GRAPHQL_SERVER_PORT=8080

And I'm deploying with this command:

gcloud --quiet beta run deploy hasura --image gcr.io/eg1/eg2:latest --region europe-west1 --platform managed --allow-unauthenticated --set-cloudsql-instances eg3

• Deployment works fine. The GCR creates the new revision.
• When opening hasura console for the first time I get this error: FATA[0000] version check: failed to get version from server: GET URL/v1/version failed - [503] (probably applying migrations)
• The second time it opens up fine. All the tables are there with data. But it looks like hasura migrations weren't applied because there is nothing on the GraphiQL explorer tab.
• Navigating in the console works, but it looks like randomly some requests fails.
• When checking the GCRun logs, it looks like some random POST requests fails. Servers exist with -1. After that, it tries to apply migrations all over again, which goes fine.

(Because of that, couldn't try websockets yet)

[pronevich]

@ighormartins subscriptions not working

[ighormartins]

@ighormartins subscriptions not working

Yep. I figured that out. Thanks.

What about migrations? are they working for you? I mean, without rewriting the entrypoint and migrations script?

[pronevich]

@ighormartins At the moment I do not use a container with migrations. I suppose this is not the best option to launch at the GCR, every cold start and instance number scale will take more time. Sure it could be creared as separated service.

p.s. try to add --timeout 900 to your gcloud deploy command

[wiesson]

Regarding migrations, have a look at this blog post -> https://medium.com/tactable-blog/building-a-serverless-graphql-app-with-next-js-hasura-and-cloudrun-fb8ca7c5e757 or go directly to the source -> https://gitlab.com/tactable.io/graphql-next-app/tree/master/hasura

A bunch of shell scripts have been added in this example so that you could run migrations as well. The author of the post uses the cloud_sql_proxy within the container, but with the information written above, you don't need this.

[ighormartins]

@ighormartins At the moment I do not use a container with migrations. I suppose this is not the best option to launch at the GCR, every cold start and instance number scale will take more time. Sure it could be creared as separated service.

p.s. try to add --timeout 900 to your gcloud deploy command

@pronevich how do you use it then? Is there a way of doing it without applying migrations? I can handle database migrations on my own, but how about hasura metadata? How can I run hasura with custom metadata without applying it as a migration?

Yeah, I noticed it takes longer on cold starts. But this is my infrastructure for a bunch of projects in development and staging envs. Not a big deal.

Edit: Seems like the problem is on the cli auto-update.

[ighormartins]

@wiesson Yeah. Have been using it this way. But sockets (subscription) doesn't work. Theoretically, it should work since GCR supports outbound connections, but it doesn't. So I'm trying to use the newest version 0.8 of Hasura to try to connect without the proxy.