Ecto migrations on Google Cloud

Phoenix Forum Questions / Help
1

Hi, I’m relatively new to Elixir and Phoenix but I wanted to deploy a very simple app on Google Cloud App Engine Flexible (works on localhost). The problem is that the deployment pipeline of elixir-runtime does not include any ecto.migrate routine, and once the app is deployed there is no access to any mix utility.

I tried to include this task in the pipeline but then it doesn’t have access to the database, here is my app.yaml:

env: flex

runtime: gs://elixir-runtime/elixir.yaml
runtime_config:
  release_app: my_project
  build:
      - mix phx.digest
      - mix ecto.migrate

env_variables:
  DB_USER: postgres
  DB_PASS: my_password
  DB_NAME: my_project
  DB_HOST: /cloudsql/my_project:europe-west1:my_project-sql

beta_settings:
  cloud_sql_instances: my_project:europe-west1:my_project-sql

As I was not able to find any clue on the Internet, you are my last hope! :sunny:

EDIT: Allowing any IP address to connect to the database (as bad as it is) doesn’t work either as the builder container does not seem to have access to that network.

1 Like
2

As i see there is no elixir in machines. You can try install to machines to elixir. But i think wont persist.

@kokolegorille told production machines dont need elixir, erlang, phoenix. I dont know why but he can explain better than me.

3

Because when You produce a release everything is included inside. You don’t need to install anything but the binary :slight_smile: … and the database, if hosted on the server, … and maybe a reverse proxy, like nginx.

1 Like
4

For the question, I may hardly answer because I never used Google Cloud, but to me the release Repo should be configured with the config/prod_secret.exs file.

This should grant access to the database…

There should be an equivalent inside this config/prod_secret.exs of

env_variables:
DB_USER: postgres
DB_PASS: my_password
DB_NAME: my_project
DB_HOST: /cloudsql/my_project:europe-west1:my_project-sql

Then the migration is a separate task. As I mentionned in the repo I did the migration by hand using pg_dump and psql to restore database on the production server.

I have no idea about how to configure Google Cloud, but I would look if I can initialize it with a simple sql dump file.

This part, once You get it right, can be automate by edeliver.

But their are great change coming in the deployment system…

1 Like
5

Thank you @dokuzbir and @kokolegorille for your replies.

As you mentioned if everything is packed into deployment binaries, installing elixir on the final container and migrating wouldn’t help. I wish there was a way to migrate during the deployment pipeline.

Here is my prod.secret.exs:

 use Mix.Config

config :my_project, MyProject.Endpoint,
       secret_key_base: "my_secret_key_base"

# Configure your database
config :my_project, MyProject.Repo,
       adapter: Ecto.Adapters.Postgres,
       username: "${DB_USER}",
       password: "${DB_PASS}",
       database: "${DB_NAME}",
       socket: "/cloudsql/my_project:europe-west1:my_project-sql/.s.PGSQL.5432",
       pool_size: 10

This configuration is working as the build has access to the Cloud SQL database, but no migrations are made along the way.

For this project I made a simple API endpoint to retrieve JSON data but the table associated isn’t initialized at deployment.

EDIT: Would it work if I make a dump of the dev database and manage to restore it on the production server?

6

I had a look at

https://cloud.google.com/sql/docs/postgres/quickstart

I don’t know if it is the service used… but it seems You can send sql to psql client in the cloud shell. I would dump the database from dev, and use this to restore db on production.

Unless there is something I am missing.

Did You look at edeliver?

1 Like
7

The Distillery docs have a page on migrations in case that’s any help. It’s possible to have a migration task run on every (re)start of the app when using Distillery releases, which is what I’ve been doing, but I’m not using Google. @bitwalker may be able to advise.

https://hexdocs.pm/distillery/running-migrations.html

There are other approaches that may make more sense for your use case, for example, automatically running migrations by defining a pre-start hook which does basically the same thing as above, just in a hook instead of a command. You can even define the command, and execute the command as part of the hook, giving you the flexibility of both approaches

1 Like
8

Just made a dump of the table and imported it on my Google Cloud SQL instance and it totally works! :grin:

@kokolegorille, @BrightEyesDavid Thank you for your support, I’ll definitely check out the documentation of edeliver and distillery as my understanding of Elixir/Phoenix get stronger.

I will update this post in the future if I happen to find a viable solution.

9

@kokolegorille What should we do when we need add a new migration to existing database. And existing database has records.

10

Migrations are just facilities to help us manage database, but everything can be done with sql. Including dump, restore, selected dump, schema dump, data only dump etc.

1 Like
11

I’ve updated my reply above with a link to the Distillery migrations documentation page.

1 Like