Standard Tier Database Connectivity

How to connect your application, and you, to a Standard tier database: the app-side configuration, sizing your connection pool, SSL, private network access through the Cloud SQL Auth Proxy, and gigalixir pg:psql.

Database Configuration

In elixir, you can use the DATABASE_URL environment variable to connect to your database.

Hostname: *.db.gigalixir.net

If your database has a hostname that ends with .db.gigalixir.net, you must use SSL to connect to your database. In this case, you can use the following configuration:

# config/runtime.exs

  database_url =
    System.get_env("DATABASE_URL") ||
      raise """
      environment variable DATABASE_URL is missing.
      For example: ecto://USER:PASS@HOST/DATABASE
      """

  config :my_app, MyApp.Repo,
    pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
    socket_options: maybe_ipv6,
    ssl: true,
    url: database_url

Hostname: IP Address

On older databases, the hostname will be an IP address. In this case, you should set the ssl_opts to disable SSL verification:

# config/runtime.exs

  database_url =
    System.get_env("DATABASE_URL") ||
      raise """
      environment variable DATABASE_URL is missing.
      For example: ecto://USER:PASS@HOST/DATABASE
      """

  config :my_app, MyApp.Repo,
    allowed_tls_versions: [:"tlsv1.2"],
    pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
    socket_options: maybe_ipv6,
    ssl_opts: [verify: :verify_none],
    ssl: true,
    url: database_url

Pool Size Determination

You may also want to adjust your pool_size. We recommend setting the pool size to (M-6)/(n+1) where M is the max connections and n is the num app replicas.

We subtract 6 because Cloud SQL will sometimes - but rarely - use 6 for maintenance purposes.

We use n+1 because rolling deploys will temporarily have an extra replica during the transition.

For example, if you are running a size 0.6 database with 1 app replica, the pool size should be (25-6)/(1+1)=9.

SSL Connections

All standard tier databases support SSL connections. By default, both SSL and non-SSL connections are accepted.

If you need to enforce SSL-only connections for compliance or security requirements, contact support and we can enable SSL enforcement on your database.

Private Network Access (Cloud SQL Auth Proxy)

By default, databases are accessible over a public IP with SSL. For enhanced security, you can use the Cloud SQL Auth Proxy to connect through a private network, eliminating the need for a public IP.

  • The proxy runs alongside your application replicas.
  • Consumes approximately 200 MB of additional memory per replica.
  • Adds a $10/month charge per replica for the additional resources.

To set up Cloud SQL Auth Proxy, contact support.

Connecting from your own machine

Once the proxy is set up, you can connect to the database from your own machine through the Cloud SQL connector with gigalixir pg:psql --use-proxy. It uses your app’s CSQL_PROXY_INSTANCE_CONNECTION_NAME (or indexed CSQL_PROXY_INSTANCE_CONNECTION_NAME_0, CSQL_PROXY_INSTANCE_CONNECTION_NAME_1, …) and CSQL_PROXY_SERVICE_ACCOUNT configs, so you don’t need to install the Cloud SQL Auth Proxy yourself. See Connecting with psql for details.

You need the owner or collaborator role on the app. With any other role, the configs are hidden from you and the command fails. Because the connection goes through your app’s configs, only databases running on Google Cloud (GCP) are supported.

For more details, see the Google Cloud SQL Auth Proxy documentation.

Connecting with psql

gigalixir pg:psql opens a psql console connected to your database. You need psql installed on your machine.

gigalixir pg:psql
Option Description
--use-proxy Connect through the Cloud SQL connector, using the app’s CSQL_PROXY_* configs, instead of the database’s public address. macOS and Linux only. Requires the owner or collaborator role on the app.
--user=USER Connect as USER instead of the database’s default user.
--password=VALUE Connect with VALUE as the password instead of the database’s default password.
--password With no value, prompt for the password. The input isn’t shown.

Give the value with =: --password=VALUE. --password VALUE doesn’t work, because VALUE is read as a separate argument.

Passing a password on the command line is less secure, since it can be saved in your shell history and shown in process listings. The CLI prints Warning: using a password on the command line interface can be insecure. when you do. Use a bare --password to be prompted instead.

With --use-proxy

gigalixir pg:psql --use-proxy
  • If the app has more than one Cloud SQL instance configured, you’re asked to pick one from a numbered list. This needs a terminal.
  • The database’s default user and password are used when your role can read them. Otherwise, pass --user and a password (--password=VALUE, or a bare --password to be prompted).
  • Use -a APP_NAME to pick the app, as with other commands.

If the app has no CSQL_PROXY_* configs, the command tells you which to set. To set up the proxy for your app, see Private Network Access.

What’s Next?