What is a database migration?

I know the answer to the question in other contexts: a database migration is the change of the database schema, adding, removing or changing columns or tables, and taking care that all the software and the tools will keep working.

But I don’t know the answer in an Anvil app.

I keep getting the warning that the database schema does not match the app, often after I change a table.
This is weird, because I have never told the app about the database schema, the app got it from the database. But for some reason, sometimes the app instead of getting it from the database wants to ask me whether to get it from the database. Why?

I usually ignore the warning, because it seems to have no effect.
When I get tired of it I click on “database is correct” and the warning goes away. But that warning seems to be the only thing that changes.

What does the warning mean?
Why does it keep getting out of sync?
Why do I need to fix it?
Am I actually fixing something?

In a single branch, there’s the version of the database as it actually exists, and the version as represented in the .yaml file (the app).

Normally Anvil does a pretty good job of automatically updating the app when you make changes to the database, but sometimes that doesn’t happen. If what you just did was to make a change to the database, the bottom button is the one to sync up the app.

When you’re migrating changes between branches, it’s generally the other way, you want to update the other branch’s database from the app (assuming the app was up-to-date!)

1 Like

Thanks for the answer Jay, but I still don’t understand.

Yes, I understand there are two descriptions of the database, one useful, the database itself, and one in the yaml which all it does is being out of sync and asking me to update it. What is that for?

I don’t know when it fails to update it. I gave up long time ago keeping track with it. All my apps say it is out of sync and they work just fine. So… why do I need to fix it if it ain’t broke?

I don’t understand what that means.

Good question. I know what I use it for (pulling the db schema out of the anvil.yaml file to run database backups through uplink), and I know that the open source server uses it to generate the database when you first run the app, but I don’t know what it was originally for.

You don’t, unless you need the app itself to be up-to-date for another reason (or if you want to get rid of the message about it being out of sync).

Let’s say you have two branches, one for dev and one for production. You have an environment setup for each, and each runs its own copy of the database.

You make changes to your development database, and eventually want to migrate those changes to your production database.

  1. Make sure your app is up-to-date with your development database (e.g. you aren’t being notified that the app is out of sync)
  2. Migrate your code changes (which will include the app’s view of the database) to production
  3. Mark your production database as the one used for development
  4. When prompted to resolve the database migration, choose the button that says the app is correct. The changes you made in the development database will be made in the production database (columns added/removed, tables added/removed)
  5. Mark your development database as the one used for development

I’m sure there are other workflows, but that’s the one I’ve come to use. It beats having to keep track of the database changes made in dev and manually replicating those in production.

What do steps 3 and 5 mean?

How do you convert the existing data to the new format?

When I need to do a big change that includes modifying some columns of a table:

  1. I create a new table with the new structure
  2. I create a script that copies and converts the data from the old format on the old table to the new format on the new table
  3. I work on the app and test it. While I test it I keep running the conversion script until the development app works with the new tables. When it works…
  4. (In the night when no one is using the app) I run the conversion script for the last time
  5. I merge to the production branch
  6. I delete the old table

How do you do something similar while working with two databases?

Is it possible for an app to copy and convert from a table in a database to a table in another database?

image

The three dots next to the database allows you to select which database will be used for development (e.g. when running the app in the IDE). My production branch uses the Default Database, and I have a second copy of the database for development so I can make breaking changes without affecting production. (And a third copy for the testing environment)

As part of my migration flow, I temporarily make the production database the one used for development, so Anvil will then resolve the database changes for me.

The flow I listed will work for anything where data migration isn’t an issue, e.g. adding new columns, deleting columns, adding new tables, etc.

No, the environment says which database will be used.

When existing data has to be migrated to a new format, I do the database migration from dev to prod, and then run some code in prod to migrate the data (that code’s been tested in dev, first, of course).

If there are then some database elements that need removed, I’d do that in dev, make sure everything still worked with them gone, and then migrate those changes to the production database.

Having two separate databases allows me to fill the dev database with junk data as needed for testing new features, without affecting anything the real users would see. There are certainly other ways of partitioning the data in a single database to get the same effect, but it makes me jumpy to develop on the production database.

1 Like

What does this mean?


I have never used multiple databases (I think that’s clear by now!) because the beta editor doesn’t allow to add an existing table created in another app to a database, and switching to the classic editor only allows to add existing tables to the default database.

Has this limitation been removed or is there a workaround?

The steps 1 through 5 I listed above.

I’m not sure. I moved away from having multiple apps sharing tables before the beta editor came around. At that time properly separating development and production environments involved cloning apps, and keeping the dev and prod versions of multiple apps in sync was too cumbersome.

My multiple “apps” now are a single app partitioned by URLs using hash routing. example.com/#admin, example.com/#account, etc. So I don’t have any tables shared between apps.