Laravel, as many other PHP frameworks, has command line tools to migrate database schema up and down.

It is not clear how to use those schema migration command line tools with the DigitalOcean App Platform. For instance:

  • If horizontal scaling is used, it is not clear which container should run the migration command, and how to make it so that the migration is executed once only

  • If the service is rolled back, how to make it also automatically roll back the database schema? Or at least to restore the database to the previous state at the point in time before the update happened?

Any additional comments / examples on database schema migration and rollback with the App Platform?

Thanks!

edited by AHA

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
2 answers

@ReWild 👋

This is a great question. We’re working to make operations like database migrations much easier to do on the App Platform, and plan to rollout support for pre-deploy and post-deploy jobs very soon for exactly this use-case.

In the meantime, you have a couple options:

  • Include the migration command in your custom run command. This can be unsafe in the case where you’re scaled up to multiple instances, and if your migration process isn’t locking on the DB to ensure only 1 is running.
  • Manually execute the migration using Console access in the UI. This has the downside of not running automatically with every deploy.

We’ll follow-up on this question when we ship pre/post deploy jobs, very soon, so stay tuned.

  • The features that are coming are amazing, sounds like exactly what we need. Thanks for the clarification on upcoming features! Please keep us updated.

We recently introduced the jobs component type that can be used for this, where you can specify the type such that the job will run pre-deploy or post-deploy.

This feature is currently only available via API/CLI (doctl), but not yet the UI.

We have an example of this here: https://github.com/digitalocean/sample-golang-notes/blob/migrate-job/.do/app.yaml

name: sample-golang-notes
services:
- name: web
  github:
    repo: digitalocean/sample-golang-notes
    branch: migrate-job
  envs:
  - key: DATABASE_URL
    value: ${db.DATABASE_URL}

jobs:
- name: migrate
  kind: PRE_DEPLOY
  github:
    repo: digitalocean/sample-golang-notes
    branch: migrate-job
  build_command: mkdir -p bin && cd cmd/migrate && go build -o ../../bin/migrate
  run_command: bin/migrate
  envs:
  - key: DATABASE_URL
    value: ${db.DATABASE_URL}

databases:
- name: db
  engine: PG
  version: "12"

You can submit this using doctl (make sure you’re using the latest version):

doctl apps create --spec .do/app.yaml

We have another job type that will be landing in production soon as well — executed on failed deploys — so stay tuned for that.

  • Thanks @snormoredo! A few extra questions!

    Q1. Provided its kind is set to PRE_DEPLOY, will such a worker sit there after the run_command and consume our credits?

    Q2. Can it reuse the same container built by a service from the same app, or will it build from scratch? Our service containers takes more than 10 minutes to build, so with such a migration job it will total more than 20 minutes.

    Q3. What happens if the LIVE application is using the database during the migration, and the migration changes database schema in such a way that it becomes incompatible with the existing LIVE application.

    • @ReWild Great questions, thanks for asking!

      Q1. Jobs are charged for only the time that they are running. After the job is complete, it stops running.

      Q2. Currently if any components in your app share the same “build context” (git repo + commit, build command, and build-time envs), then the build will be reused across them (and for subsequent deployments). As mentioned in your question about app-level envs, we have plans to simplify this so that you don’t have to duplicate envs across components.

      Q3. This would be handled by the application code and the way you rollout changes. The best practice around shipping DB migrations in zero-downtime systems like this, is to not ship a migration that would be incompatible with your current/previous code. This does mean you may need to rollout changes that require a migration in multiple stages to avoid the incompatibility. We’ll look into putting together some documentation about strategies and best practices for DB migrations.

      • @snormoredo you said:

        Currently if any components in your app share the same “build context” (git repo + commit, build command, and build-time envs), then the build will be reused across them (and for subsequent deployments).

        However, in a private support ticket I was told that builds will always happen from scratch and never reused between jobs and services. This is the experience I’ve been having so far. So, could you clarify please.

Submit an Answer