Sorry for taking so long to reply. I'm afraid I don't check this mailing list as often as I should. :)
On Tue, 2021-12-07 at 08:52 +0100, Aurelien Bompard wrote:
Thanks for your input!1. We're using a clustered database (CockroachDB, for those who care)
that uses optimistic concurrency, so automatic transaction retries are
a must, and we need control over how those retries are done.Interesting, we don't use that, but then again we've recently started using more funky stuff on the database side (TimescaleDB) so maybe one day...
Unfortunately CockroachDB has gone the route of MongoDB in its licensing, so it's not really open. YugabyteDB looks like it has most of the same features and is Apache 2.0 licensed, so would probably be a better fit for Fedora (and, if it wasn't for the fact that it's missing GIN indexes, we would probably be using it too).
2. We are using the same models for a couple of different projects (the
API itself and a script that is synchronizing between the old database
and the new), and not all the projects are built on Flask. Initially,
I was able to get the sync script working with Flask-SQLAlchemy, but
things got ugly quickly when I started doing multithreading, so I
abandoned it and am now using Flask and SQLAlchemy separately.When I thought about that use case, I supposed it would be OK to instantiate the app and start the app context from within the script, as it would also give you access to Flask's config file. But I did not think about multithreading. Would you recommend against creating the app instance and the app context in a command-line script?
Well, that was what I tried to do first, but, as I said, everything broke down when I tried to do multithreading (and got worse when I tried to setup multiprocessing). The problem is that Flask-SQLAlchemy tries to manage the DB session for you, and, since SQLAlchemy sessions aren't thread-safe, my command-line script kept crashing, and a few hours of poking around couldn't fix it. If I'd been willing to poke around more in Flask-SQLAlchemy's, I might have figured something out, but it just didn't seem to be worth the effort, when manually managing my sessions fixed the problem completely.
Is the code you wrote to integrate Flask and SQLAlchemy opensource, and available somewhere?
Unfortunately not, but there was actually very little integration code written.
Our code follows the following pattern (we're using Flask-RESTX, and I've omitted serializers to keep it simple):
endpoint:
import business
from util import run_transaction
@ns.route("/user/<string:id>")class UserLink:
def get(self, id):
return run_transaction(lambda s: business.get_user(s, id))
business:
from database.model import *
def get_user(session, id):
return session.query(User).filter(User.id == id).one()
util:
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker
import sqlalchemy_cockroachdb
engine = create_engine('postgresql://admin:swordfish@localhost/') SessionMaker = sessionmaker(engine)
def run_transaction(func):sqlalchemy_cockroachdb.run_transaction(SessionMaker, func)
The purpose of the run_transaction function is to repeat transactions if there's a conflict, rather than trying to lock the record, which is a CockroachDB paradigm.
I hope the above is at least somewhat helpful in explaining how we're working without Flask-SQLAlchemy
Jonathan
_______________________________________________ infrastructure mailing list -- infrastructure@xxxxxxxxxxxxxxxxxxxxxxx To unsubscribe send an email to infrastructure-leave@xxxxxxxxxxxxxxxxxxxxxxx Fedora Code of Conduct: https://docs.fedoraproject.org/en-US/project/code-of-conduct/ List Guidelines: https://fedoraproject.org/wiki/Mailing_list_guidelines List Archives: https://lists.fedoraproject.org/archives/list/infrastructure@xxxxxxxxxxxxxxxxxxxxxxx Do not reply to spam on the list, report it: https://pagure.io/fedora-infrastructure
