librelist archives

« back to archive

Switching SQLAlchemy binds on the fly?

Switching SQLAlchemy binds on the fly?

From:
Matthew Hoopes
Date:
2013-08-21 @ 14:20
Hi - I have an app that we've built for a single customer, and would like
to expand and use the same app for others. Each client will have their own
(set of) databases - for now assume that it's a single DB with the account
table. So, if the browser is at client1.com, I'd like it to use db1, and if
the browser is at client2.com, I'd like it to use db2, and so on.

Our current strategy is to run a gunicorn instance per customer, and proxy
nginx via hostname to the proper app server/port. This will keep us in good
shape for a little bit, but I can see the future where we have 100 gunicorn
instances running. I could spread them out over servers, and just keep the
configuration books meticulously (and it could be argued that that's a good
problem to have anyways), but that seems a little unwieldy.

The other option would be a single gunicorn instance, and switching the
config on the fly (maybe in a before request function?). We're using
Flask-SQLAlchemy. However, we can't come up with a strategy for this, and
aren't even sure if it's possible.

(Yet another idea would be to put the client info in a single db, and
figure this all out with app code, but that is a pretty serious refactor,
and if I can get away with something easier, I'd really like to...)

Can anyone help me out, even if it's to tell me this is a dumb idea?

Thanks very much!

Re: [flask] Switching SQLAlchemy binds on the fly?

From:
Raphael Slinckx
Date:
2013-08-21 @ 14:27
We have a similar use case. What we ended up doing is to have one flask
instance per customer, and a wsgi dispatcher based on hostname which passes
the wsgi requests to the correct flask instance.

Each flask instance sets up its own extensions depending on the config,
here is the pseudo code:

class Dispatcher(object):
def __call__(self, environ, start_response):
 return get_application(environ)(environ, start_response)
app = Dispatcher()

where get_application(environ) extract the hostname, and find the right
config then creates the Flask object representing our real application.

You can then point gunicorn to the dispatcher app instead of the "real"
app, and it works...

Raf



On Wed, Aug 21, 2013 at 4:20 PM, Matthew Hoopes <matthew.hoopes@gmail.com>wrote:

> Hi - I have an app that we've built for a single customer, and would like
> to expand and use the same app for others. Each client will have their own
> (set of) databases - for now assume that it's a single DB with the account
> table. So, if the browser is at client1.com, I'd like it to use db1, and
> if the browser is at client2.com, I'd like it to use db2, and so on.
>
> Our current strategy is to run a gunicorn instance per customer, and proxy
> nginx via hostname to the proper app server/port. This will keep us in good
> shape for a little bit, but I can see the future where we have 100 gunicorn
> instances running. I could spread them out over servers, and just keep the
> configuration books meticulously (and it could be argued that that's a good
> problem to have anyways), but that seems a little unwieldy.
>
> The other option would be a single gunicorn instance, and switching the
> config on the fly (maybe in a before request function?). We're using
> Flask-SQLAlchemy. However, we can't come up with a strategy for this, and
> aren't even sure if it's possible.
>
> (Yet another idea would be to put the client info in a single db, and
> figure this all out with app code, but that is a pretty serious refactor,
> and if I can get away with something easier, I'd really like to...)
>
> Can anyone help me out, even if it's to tell me this is a dumb idea?
>
> Thanks very much!
>



-- 
Raphaël Slinckx
Whatever sa/nv
Lead Developer
Rue Fond Cattelain 5
1435 Mont-Saint-Guibert, Belgium
+32 485 133 248
http://www.whatever-company.com

Re: [flask] Switching SQLAlchemy binds on the fly?

From:
Matthew Hoopes
Date:
2013-08-21 @ 14:52
Thanks for the reply! After you mentioned that, did a little more googling,
and came up with http://flask.pocoo.org/docs/patterns/appdispatch/

Apparently I didn't read the docs as thoroughly as I should have. I'll give
this a shot. Thanks again!


On Wed, Aug 21, 2013 at 10:27 AM, Raphael Slinckx <
r.slinckx@whatever-company.com> wrote:

> We have a similar use case. What we ended up doing is to have one flask
> instance per customer, and a wsgi dispatcher based on hostname which passes
> the wsgi requests to the correct flask instance.
>
> Each flask instance sets up its own extensions depending on the config,
> here is the pseudo code:
>
> class Dispatcher(object):
> def __call__(self, environ, start_response):
>  return get_application(environ)(environ, start_response)
> app = Dispatcher()
>
> where get_application(environ) extract the hostname, and find the right
> config then creates the Flask object representing our real application.
>
> You can then point gunicorn to the dispatcher app instead of the "real"
> app, and it works...
>
> Raf
>
>
>
> On Wed, Aug 21, 2013 at 4:20 PM, Matthew Hoopes <matthew.hoopes@gmail.com>wrote:
>
>> Hi - I have an app that we've built for a single customer, and would like
>> to expand and use the same app for others. Each client will have their own
>> (set of) databases - for now assume that it's a single DB with the account
>> table. So, if the browser is at client1.com, I'd like it to use db1, and
>> if the browser is at client2.com, I'd like it to use db2, and so on.
>>
>> Our current strategy is to run a gunicorn instance per customer, and
>> proxy nginx via hostname to the proper app server/port. This will keep us
>> in good shape for a little bit, but I can see the future where we have 100
>> gunicorn instances running. I could spread them out over servers, and just
>> keep the configuration books meticulously (and it could be argued that
>> that's a good problem to have anyways), but that seems a little unwieldy.
>>
>> The other option would be a single gunicorn instance, and switching the
>> config on the fly (maybe in a before request function?). We're using
>> Flask-SQLAlchemy. However, we can't come up with a strategy for this, and
>> aren't even sure if it's possible.
>>
>> (Yet another idea would be to put the client info in a single db, and
>> figure this all out with app code, but that is a pretty serious refactor,
>> and if I can get away with something easier, I'd really like to...)
>>
>> Can anyone help me out, even if it's to tell me this is a dumb idea?
>>
>> Thanks very much!
>>
>
>
>
> --
> Raphaël Slinckx
> Whatever sa/nv
> Lead Developer
> Rue Fond Cattelain 5
> 1435 Mont-Saint-Guibert, Belgium
> +32 485 133 248
> http://www.whatever-company.com
>

Re: [flask] Switching SQLAlchemy binds on the fly?

From:
Jaime Wyant
Date:
2013-08-21 @ 14:52
I've done something very similar in a pylons app.  The same idea should
apply.  Below, I've tried to adapt my code for pylons.  This code was typed
in my email editor and not tested, caveat emptor!  But it should give you a
good direction.



from flask import request
from sqlalchemy.orm import scoped_session, sessionmaker, Session
from sqlalchemy import create_engine

# Maps host names to connection strings.  This *should* be in a config file.
host_to_conns = {
    'host1.com' : 'some connection string',
    'host2.com' : 'some connection string'
}


# Here setup the sqlalchemy engines.  These will be used later to
# return the proper session on the fly.
def make_engines():
    """Return a dictionary mapping hosts to database engines."""
    d = {}
    for host, conn in host_to_conns.iteritems():
        d[host] = create_engine(conn)
    return d

engines = make_engines()


class RoutingSession(Session):

    def get_bind(self, mapper=None, clause=None):
        return engines.get(request.host)



On Wed, Aug 21, 2013 at 9:20 AM, Matthew Hoopes <matthew.hoopes@gmail.com>wrote:

> Hi - I have an app that we've built for a single customer, and would like
> to expand and use the same app for others. Each client will have their own
> (set of) databases - for now assume that it's a single DB with the account
> table. So, if the browser is at client1.com, I'd like it to use db1, and
> if the browser is at client2.com, I'd like it to use db2, and so on.
>
> Our current strategy is to run a gunicorn instance per customer, and proxy
> nginx via hostname to the proper app server/port. This will keep us in good
> shape for a little bit, but I can see the future where we have 100 gunicorn
> instances running. I could spread them out over servers, and just keep the
> configuration books meticulously (and it could be argued that that's a good
> problem to have anyways), but that seems a little unwieldy.
>
> The other option would be a single gunicorn instance, and switching the
> config on the fly (maybe in a before request function?). We're using
> Flask-SQLAlchemy. However, we can't come up with a strategy for this, and
> aren't even sure if it's possible.
>
> (Yet another idea would be to put the client info in a single db, and
> figure this all out with app code, but that is a pretty serious refactor,
> and if I can get away with something easier, I'd really like to...)
>
> Can anyone help me out, even if it's to tell me this is a dumb idea?
>
> Thanks very much!
>



-- 
"Government does not solve problems; it subsidizes them."
Ronald Reagan