Connect to Second Database Dynamically After User Login - python Flask

Question

I have a flask application which connects to an initial database which simply holds login data (user credentials, passwords etc...) - this is working fine.

But, Each user gets their own database when they create an account (necessary for security purposes and individual access)

Once the user logs in, the app queries the users login credentials for their unique database url.

How do I take this database url and connect the second database "on the fly" so to say. Is it even possible?

Using python, flask and sqlalchemy.

Answer 1

Just manage each sqlalchemy object within each request. You won't be able to make use of flask-sqlalchemy for this, you'll have to use regular sqlalchemy. Functionize the following, taking in your engine args, returning a session. Performance will also be worse (though probably not enough to really care), but that's unavoidable.

engine = create_engine(config.engine_str, **config.engine_args)
Session = sessionmaker(bind=engine)
session = Session()

You should also rethink having a database for each user. It's very likely that it's unnecessary.