Migrate to a hosted database

[tobynott80]

To ensure persistence, scalability and backups, we should research into free tiers of database hosting.

These should have SQLite or similar dialect options, to ensure interoperability between the current development database and for cognisance between the developer team.

[tobynott80]

Free Hosting Options:

PlanetScale

• MySQL (mostly interoperable with SQLite)
• Free 5GB, 1bn Reads, 10ml writes
• Prod and dev branches
• Easy scaling
• Serverless
• Nice Admin UI

Azure Free DB Tier

• Azure SQL (similar enough to SQLite)
• 10 DTUs provided (probably enough for our use-case)
• Can use free edu credits for expansion

Cardiff Uni Services

• Host on openShift/openStack/whatever else CU offers
• Free whilst students
• Needs VPN connection- ballache for hosting
• Whatever dialect we need - SQLite

CleverCloud

• PostgreSQL or MySQL
• 256MB
• Free fovever

MongoDB

• Shared Cluster: 512mb free forever + ∞ read/writes
• Serverless - $0.10/1ml reads
• Not SQL based, instead a document oriented db - learning curve and trickier migration

IBM Db2

• Only 200mb free tier
• 90 day trial expiry
• Proprietary dialect

[tobynott80]

https://gist.github.com/bmaupin/0ce79806467804fdbbf8761970511b8c

[tobynott80]

In the migration to production we will need some kind of profile to switch between a local development db and a live production db. gonna look into this

[tobynott80]

Decided on planet-scale for now given cost and scalability

[tobynott80]

Going to need to add a dev/prod profile switch https://flask.palletsprojects.com/en/2.0.x/config/#development-production