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