Using multiple databases with peewee

Question

I'm writing a "multi tenant" application. It's going to be hosted on different subdomains, and based on which subdomain it's hosted, it should use a different database.

Is it possible to define, in execution time, which database peewee should be using? If I were using django, I'd just write a router that takes care of it, but I haven't found anything similar on peewee.

Am I missing something?

Thanks!

PS: A hack like this How to query several similar databases using Peewee?, where you need to know beforehand which class to invoke wouldn't work fine in my scenario

Neela

Can you do this in flask instead of peewee? For example, using application factories with application dispatchers – Zoo

@Zoo that could certainly work :) please put it as an answer so that I can at least upvote it... I like it! – Neela

Answer 1

Instead of handling this in peewee, you could handle the db selection in flask using application factories with application dispatchers

Zoo

The links not exists, please fix this – Gracielagracile

Answer 2

Peewee provides also the possibility to use DB Proxy, where you can switch the DB easily. Peewee Documentation

database_proxy = Proxy()  # Create a proxy for our db.

class BaseModel(Model):
    class Meta:
        database = database_proxy  # Use proxy for our DB.

class User(BaseModel):
    username = CharField()

# Based on configuration, use a different database.
if app.config['DEBUG']:
    database = SqliteDatabase('local.db')
elif app.config['TESTING']:
    database = SqliteDatabase(':memory:')
else:
    database = PostgresqlDatabase('mega_production_db')

# Configure our proxy to use the db we specified in config.
database_proxy.initialize(database)

Chauffer

Answer 3

You can also take a look at the ReadSlave module for an example of changing databases at run-time.

Code:

class ReadSlaveModel(Model):
    @classmethod
    def _get_read_database(cls):
        if not getattr(cls._meta, 'read_slaves', None):
            return cls._meta.database
        current_idx = getattr(cls, '_read_slave_idx', -1)
        cls._read_slave_idx = (current_idx + 1) % len(cls._meta.read_slaves)
        return cls._meta.read_slaves[cls._read_slave_idx]

    @classmethod
    def select(cls, *args, **kwargs):
        query = super(ReadSlaveModel, cls).select(*args, **kwargs)
        query.database = cls._get_read_database()
        return query

    @classmethod
    def raw(cls, *args, **kwargs):
        query = super(ReadSlaveModel, cls).raw(*args, **kwargs)
        if query._sql.lower().startswith('select'):
            query.database = cls._get_read_database()
        return query

Aesthetically

Thanks for that @coleifer! Questions: would this affect only selects? also... is there a way to define the database that all my models use in a separate place instead of the model? Ideally, it would be a config I apply (using something like dependency injection) and that would make all my models use that database. In django's ORM, that would be done at a router level. – Neela

Yeah, it was just meant as an example of how I'd done something similar. – Aesthetically

Answer 4

I think the simplest solution is using a factory for the models.

def models_factory(database):
    class Model1(Model):
        class Meta:
            database = database
        ...

    ...

    return(locals().copy())

somewhere else:

models = models_factory(database)
models['Model1'].create_table()

The function creates all the models using the database passed as argument. Then returns locals() dict which contains all the models defined.

If you use ForeignKeyField then it is important that the referenced model is defined in the same function or that it is defined in the calling scope.

Alternatively you can use DeferredForeignKey if you want to define the models in different functions (factories):

def model1(database):

    class Model1(Model):
        ...

    return Model1


def model2(database)

   class Model2(Model):
       model1_id = DeferredForeignKey('Model1', field='id', ...)
       ...
   
   return Model2

somewhere else:

Model1 = model1(database)
Model2 = model2(database)

Also if you are using multiple databases with the same database structure you can create a Database class inherited from peewee database and define the factory/es as methods.

Then you could instantiate the database just as Database(filename) and you have all the models in there:

class Database(SqliteDatabase):
    def __init__(self, filename):
        super().__init__(filename, pragmas={'foreign_keys': 1})
        self.models = self.create_models()
        ...

    def create_models(self)

        class Model1(Model):
            class Meta:
                database = self
            ...

        ...

        return locals().copy()

somewhere else:

db = Database("mydatabase.db")
db.models['Model1'].create_table()

Macnamara