Migrate Anvil app_tables to external Postgresql DB

[stu]

Does anyone have any experiences of migrating from app_tables to a external postgresql database?

Are there any mappings or python packages to make the migration/transition more simplified across the existing Anvil Data Tables? Or, a replacement of the Anvil app_tables calls where SQL is not needed?

I am coming across multiple scenarios where SQL would be highly beneficial but have already created many Anvil Data Tables so am going to have to make the transition. Just trying to work out the least painful way of doing it!

[jon1]

We’re about to embark on migrating to our own Postgresql database too. Does anyone have any tools, libraries, or lessons learned that they can share with us on this process?

@stu I would really like to hear about what you ended up doing.

Thank you!

[p.colbert]

See

Backing up data tables

I’ve seen some other topics on the question of backing up data tables, but I haven’t seen anything that looks like a final solution to the problem. As I’m (distantly) viewing the prospect of deploying my large app and filling its data tables with production data, I’m thinking about a backup strategy. The basic options seem to be: Rely on Anvil’s automatic backups (and restores if needed) Use an in-app backup and restore mechanism Anvil’s backups How often are these done? How granula…

and

Backup to SQLite via Uplink

I’ve written an Uplink module that backs up an App’s tables to a SQLite database file. It started out as a hard-coded script, but I’ve taken some steps to generalize it. Some caveats: I haven’t tried it with multi-link columns. (My tables don’t have any.) To avoid trouble, the (incomplete) code to handle that is commented out. If you have such columns, you’ll want to uncomment those lines (and test thoroughly!). If you do, please let me know how it went! It reads one of the App’s .yaml f…

With some editing, it may be able to “back up” to PostgreSQL instead of SQLite.

[james.patrick]

It’s easy enough really. Multiple routes from drop your data out as CSV and replumb via uplink, or code straight in your server modules. The external databases tutorial is essential reading. You can also migrate and backup via endpoint, if you want to be really cute. Only limit is what you can think of in Python.

[stu]

Hi Jon,

I abandoned the external postgresql concept (probably due to my inexperience at the time when I was attempting to do this).

I did however manage to get a NoSQL firestore db connection (installed firebase and firebase_admin packages directly to my project which worked nicely - thanks Anvil for that update) but haven’t had the time to explore moving data across and using this as a primary database.

The speed of app_tables when looping over 200+ results (sometimes 8000+ results - extracting a simple csv from 3 linked tables takes nearly an hour) is my main issue with app_tables. Firestore/Firebase definitely overcomes this and I’ll be exploring this when time allows.