Adding Persistence using CloudSQL
From this post I did 2 weeks ago, we're left with a pipeline that automatically builds on feature branches and deploys on the master branch. I think it's a pretty good developer flow to start contributing code. The article is about building out an MVP with persistence and interacting with that persistence.
Here's the work in progress: http://notes.bwang.io/
Using flask on the backend to generate dynamic urls, I'm using postgresql as the backend, hosted on Cloud SQL; it's a managed database service that does scaling, security, and backups for you. It doesn't really makes sense for me to use some NoSQL/Cloud Spanner since all my queries are transactional. app.config has a bunch of session configs so if I wanted to, I could extend the functionality of this app to also host user sessions and maybe even stripe payments.
In the future, it doesn't make sense for these projects to be in an interpreted, duck-typed language, but I'll be using python for my first one. For postgres tooling, everything I'm using is pretty standard according to articles online:
- Psycopg2 - threadsafe python access to postgres
- SQLAlchemy - python ORM, maps code to db
- Alembic - works with orm to generate migrations
Anyways, everything works great! At a high level, alembic detects changes to your models and autogenerates migration scripts. The migration script is just python that updates the database through the sqlalchamy orm. Currently, I am using cloud_sql_proxy to run everything local against a production database, but alembic makes it really easy to sync your data models up when I decouple my environments with environment level configs.
- alembic comes with upgrade/downgrade that works a lot like git reset, traversing a linked list; pretty nifty tool
- When I ran alembic to add a non-nullable boolean column, I had to manually edit the migration file; maybe migrating when nullable is set to False with existing rows is also a common issue with other orms
- By default, sqlalchemy expires on commit; this means that when the data is transferred from local memory to be committed onto the database, the object can't be used anymore--it's expired and no longer exists locally.
Cool it works! Here's a list of other things I need to do:
- Cosmetics, adding some markdown js
- Improving code maintanability/structure
- Make cloud sql db private
- Improve local tooling: testing, connect to local db instance
This is a work in progress; maybe finished in a future post.
Resources:
Other information:
- I'm having to append 2020 at the end of a lot of these google searches
- At this point, my top level directory is littered with a motley assortment of files: .ini, cloudbuild, Dockerfile, kube yamls, models, tests, scripts (for db/port-forward shortcuts). I'll soon reach an inflection point where my project is big enough to start worrying about structure