How to parse `DATABASE_URL` secret?

Hello Fly.io community,
I’m trying to deploy Nextcloud on the platform through Docker Hub. However, I’m stuck at parsing the DATABASE_URL variable after attaching a PostgreSQL database with it. The app (Nextcloud) specifically requires to be provided with the host, username, password and database name separately as opposed to the connection URL. What should I do in this case? It’d be nice if we were given the option to choose between connection URL and individual variables when we run flyctl postgres attach. Thanks in advance.

The same goes for Redis as well. There is 1 additional problem here: unlike PostgreSQL, it doesn’t allow us to “attach” it to an app and thus make the REDIS_URL available as an environment variable in it.

You could set the required environment vars manually for your nextcloud app from the output when you created your postgresql db:

Creating postgres cluster c-pg-test in organization personal
Postgres cluster c-pg-test created
  Username:    postgres
  Password:    8a93cbc09798f3805056333072bd2b35be7eb634b13a05c3
  Hostname:    c-pg-test.internal
  Proxy Port:  5432
  PG Port: 5433
Save your credentials in a secure place, you won't be able to see them again!

Monitoring Deployment

1 desired, 1 placed, 1 healthy, 0 unhealthy [health checks: 3 total, 3 passing]
--> v0 deployed successfully

Connect to postgres
Any app within the personal organization can connect to postgres using the above credentials and the hostname "c-pg-test.internal."
For example: postgres://postgres:8a93cbc09798f3805056333072bd2b35be7eb634b13a05c3@c-pg-test.internal:5432

See the postgres docs for more information on next steps, managing postgres, connecting from outside fly:  https://fly.io/docs/reference/postgres/

In this case, add the following environment variables or secrets to your fly.io app:

  • POSTGRES_USER = postgres
  • POSTGRES_PASSWORD = 8a93cbc09798f3805056333072bd2b35be7eb634b13a05c3
  • POSTGRES_HOST = c-pg-test.internal:5432

not sure if the port should be included in the hostname. Please remove the port when you encounter issues

POSTGRES_DB can be set to the name you like your database to have

Hope it helps!

Thanks. I was actually developing a shell script which I wished would do that automatically. Well, I guess that’s the way I have to go for now – no automation can be done. I’d do that anyway, even if someone from Fly.io couldn’t tell me another way.

I really wished the platform would allow us to deploy apps from the web UI. :cry:

To automate it fully you would need to create your own nextcloud docker image with an entrypoint script that reads DATABASE_URL, parses it and exports the various POSTGRES_ variables.
But that involves keeping an additional docker image up to date… (which can also be automated, but still)

The whole platform is as messy as it can be. Some operations are only available for config file (i.e. health checks), while some are only available for CLI (i.e. setting region). Heroku the MF stormed most of the small developers. :triumph:

I believe the format is [database type]://[username]:[password]@[host]:[port]/[database name]. I’m not sure what language your app is, but some languages, like Java (Kotlin), are actually able to parse this format using standard URI facilities.

I use this piece of code to parse it into a JDBC-compatible Datasource:

val databaseUrl = URI(get<ApplicationConfig>().property("database.url").getString()) // effectively equals to DATABASE_URL

return PGSimpleDataSource().apply {
    setUrl("jdbc:postgresql://${databaseUrl.host}:${databaseUrl.port}${databaseUrl.path}")
    user = databaseUrl.userInfo.split(":")[0]
    password = databaseUrl.userInfo.split(":")[1]
}

The app itself (Nextcloud) wants the following information in the defined format:

POSTGRES_HOST=<host>:<port>
POSTGRES_USER=<username>
POSTGRES_PASSWORD=<password>
POSTGRES_DB=<dbname>

The app, I believe, is written in PHP. Since it’s not written by me, I don’t have control over the environment variables, I gotta pass whatever it asks.