Peewee: Allow to define database dynamically

[rudyryk]

Peewee is awesome :+1: :)

I'm using Peewee in my Tornado-powered project and I need to define database connection dynamically, like that:

import tornado
from peewee import *

class Application(tornado.web.Application):

    def __init__(self, **kwargs):
        # Some init stuff ...

        # Setup DB and Models

        self.database = PostgresqlDatabase('mydb', user='postgres')

        import myapp.users.models as users
        self.User = self._get_model(users.User, self.database)

        # etc...

    def _get_model(self, model, db):
        model._meta.database = db
        model.create_table(True)
        return model

So, I'm using private undocumented _meta property and I'm not feeling ok about that and I'm not sure what is the best design decision for that.

Anybody have ideas?

[coleifer]

See #210 and the docs on proxy:

http://peewee.readthedocs.org/en/latest/peewee/playhouse.html#proxy

This has come up a couple times so I will try to make the docs more obvious on this.

[rudyryk]

That's great! Yes, a short reference in main docs chapter about models would solve it, I think. Thank you! :)

[c0b]

a piggyback question: how can I dynamically use same models on multiple databases simultaneously?

like in many testing environment, for the same software we defined devint, qa, prod environment, those are running same model, just connection to same version of database-server running on different host, now for some cases, need to access both qa and prod database?

database_qa = db_url.connect('mysql://qa-database-host/...')
database_host = db_url.connect('mysql://prod-database-host/...')

class ModelSite(Model):
    Field1 = ...
    Field2 = ...
    class Meta:
        database_table = 'api_site'

class ModelB(Model):
    Field1 = ...
    Field2 = ...
    class Meta:
        database_table = 'api_task'

class ModelC(Model):
    ...

I have multiple models (mapping to multiple tables in a database) in qa environment, and have exact same models in prod database; for an analyzing program, it needs to connect both databases by the same set of models, what's the best practice to do so?

this question is not just dynamically switching database at program initialization time, but it want to keep both connections at runtime? I can think of keeping a copy of this model definition for every environment, but is there a better way to reuse same set of model class definition?

[coleifer]

So you literally, while your app is running, want it to be connected to both a QA environment and a Prod environment?

[c0b]

right, 'qa' and 'prod' are just examples here, it can be anything when users want to connect multiple database but using the same set of tables schema / models;

I know in some design, it's has advantage of create a separate database for each customer, in each database, use multiple tables as before, each table is mapping to a Peewee Model here; multiple database can be on a same mysql server or can be on different mysql server, that design also serves like manual data sharding;

so forgot about the qa/prod; I wonder how does peewee support multiple connection to different databases but using the same set of models ? all connection open / close are happening at runtime, depends on input

[coleifer]

I'm reopening as a reminder to respond. You've asked a good question and I'd like to write a thorough response.

[coleifer]

Docs: http://docs.peewee-orm.com/en/latest/peewee/database.html#using-multiple-databases

So, by default (unless you specify threadlocals=False when instantiating your database), peewee will maintain a connection-per-thread. The interfaces are:

• connect() -- create a connection
• get_conn() -- get the current thread's connection, creating one if it does not exist.
• close() -- close the connection for the current thread.

If you have two database instances, then they will each have their own connection.

Peewee models access their database via Model._meta.database. This attribute can be set or changed during run-time. So if you want to use the same models with different databases, it's literally as simple as setting MyModel._meta.database = some_db. While this isn't really a common pattern, or one peewee was originally designed to support, it's definitely possible.

I wrote a helper called Using that makes this a bit easier. Using takes a database and a list of models and acts as either a decorator or a context manager. For the duration of the wrapped block, the models will use a _separately managed_ connection to the specified database.

The key phrase is _separately managed_. This means that when you use the Using() helper, when you enter the wrapped block a new connection is opened to the given database (despite whether there's already one open for that thread). When you exit the block, that connection is closed. And for the duration of the block, the models you passed in will all be pointing at the database specified as the first parameter of Using.

Depending on your use-case, this may be all you need. Or you can write your own tooling that handles manipulating Model._meta.database.

To get a better understanding of peewee's connection management, you can read the documents linked below, or read the code -- the entire peewee module can be read in one sitting.

• http://charlesleifer.com/blog/managing-database-connections-with-peewee/
• http://docs.peewee-orm.com/en/latest/peewee/database.html#advanced-connection-management
• http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#connection-pool

[c0b]

that Using context manager is somewhat interesting, another use case I can think of is a data migration project I am working on, from the old mysql database, we want to migrate to the latest postgres to utilize the BinaryJSONField, because the old design in mysql was a LONGTEXT wrapper of JSONField, when this field grows into megabytes, to update a small portion of this json text isn't very efficient;
but it depends on the migration plan may run a couple of months or longer before we can fully shutdown the old service on mysql, this migration project is designed as a RESTful service for UI to call, for each Table entry individually; So in this migration service code, it has to maintain two connections, to mysql and to postgres at same time; while we'll still like to reuse the Model definition code as much as possible, because the biggest change is the old LONGTEXT based JSONField wrapper to postgres native BinaryJSONField; this part will really need a good design, to hold two pooled connection and gives models freedom to access either one

while the old design is on django on mysql, but django is really old, although 1.9 has postgres JSONField support, I feel new design should switch to peewee + flask (or some other light weight framework), what would you think @coleifer ? do you see anyone did some comparison ? performance / flexibility / ... ? thanks

1. http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#postgres-ext-api-notes
2. https://docs.djangoproject.com/en/1.9/ref/contrib/postgres/fields/#jsonfield

[wice90]

Hi,

Is Using helper still available in latest peewee?

I am trying to connect to different sqlite databases from different threads at same time, using same models.py (with Proxy)
Let me explain - A request is received on my backend and a thread is being opened. The thread accesses sqlite_1.db with models.py (proxy.initialize) , makes some python calculations for lets say 20 seconds, and then returns a data, selected from the sqlite_1.db.
In meantime another request comes from another user, that wants to operate on sqlite_2.db, but using the same models.py (and of course, proxy.initialize(sqlite_2.db)). New thread is opened and total operation time for this action is 1s. It finishes a lot before the first thread.
So when the first thread tries to execute a select query on a model from models.py, the DB has changed and it will be executed on sqlite_2.db instead of sqlite_1.db.

Any solution for this situation, or I should swtich from multiple DBs that I want to change per request to a one DB?

Thanks

[coleifer]

This is the new way: http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.bind_ctx

[eukreign]

Is bind_ctx thread safe? Can I bind the same model to different databases in separate threads?

If it's possible can you point me to how this works? From my investigation it seems that bind_ctx just monkey patches the class attributes with which database to use so if you set it in one thread you'd overwrite whatever it was set to in another thread, is my understanding correct?

[coleifer]

It is not thread-safe, no. Database connections are thread-safe, but the database attribute itself is not.

[alexpirine]

Would you consider a design change where you actually bind models to a database, instead of the other way around?

Being unable to use two or more databases simultaneously is limiting.¹ The workarounds to do it anyway are somewhat clumsy. And assuming by default that the developer will initialise his database before declaring his models, even if there are workarounds (like DatabaseProxy), is frankly limiting, and again, results in some weird code.

Also it doesn't make sense to import models to access the database (that has been bound to them beforehand, and we can only hope that this has been done: no way to type-check that), instead of actually importing the database object…

I know, Django does that, but it's a fully-fledged framework, a swiss-knife to do it all, and making bold assumptions on how developer should organise his code and initialise his application is therefore somewhat more "excusable"…

Now, for a tiny library (and being a tiny library is actually a nice thing), that is supposed to provide access to different SQL databases, I expect it to do just that for me, without forcing on me some strange design patterns.

¹ My simple use-case: I just created a local SQLite database with some data for analytics. Now that I tested my code and I am ready to deploy it, I would like to export the crafted data to a production-ready MySQL database for further analysis. Otherwise, it will take several hours to compute everything from scratch.

[coleifer]

I believe that the vast, vast majority of people use peewee with a single database. The exception is those who want to unit-test, and for their use-case the database proxy / deferred initialization is sufficient. There are ways to change database dynamically at run-time, which are well-documented.

I can't imagine penalizing the most common use-case to support a very niche usage of running the same models against multiple databases at the same time. Similarly, it seems your problem is you want to export sqlite and import into mysql, and have just assumed that you have to do it with peewee. There are many other ways to accomplish this, dumping to csv being a very good option.

[alexpirine]

I understand, my point is not only to suit other use-cases, but rather to come up with a better design, that would be nicer to use, and being suitable for less frequent use-cases would just be a side-effect.

This could be something similar to the sessions used in SQLAlchemy:

https://docs.sqlalchemy.org/en/13/orm/tutorial.html

(I actually used SQLAlchemy before, but this time, I got interested in peewee for its simplicity, SQLAlchemy is a bit of a labyrinth)

We could still come up with a simple API, something like:

db = connect("...")
# select query
db.select(User.id, User.name).from(User).where(User.email == 'admin@localhost')
# insert query
u = User(id=1, name="Alfred", email="alfred@localhost")
db.insert(u)

Another benefit of such API is that it will appear less "magic", things will look more straightforward and explicit.

Anyway, you wrote a great library, and I thank you for that (and for keeping the issues so tidy!), it's obviously your decision, you have a better view on your own project philosophy, and if I was able to communicate clearly my idea, I am happy with that, whatever path you take :)

[coleifer]

I appreciate that you mean well, but I think you are not considering:

• peewee has a lot of users, I will never break backwards compatibility in such a fundamental way.
• the vast majority of users will only ever use peewee with a single database, but even those who wish to unit-test with a test db will find the current APIs well-suited to the task

peewee was created because I dislike many aspects of sqlalchemy's design and find it unintuitive and magical - sessions especially.

The APIs you are suggesting can already be used with peewee's lower-level query builder interfaces, anyways. You are welcome to explore them -- see Table and related classes.

[jiamo]

"I believe that the vast, vast majority of people use peewee with a single database. "

Yes. But still some situation for multi database.

Some application create db for every user. All db have same table. When different user request api. Using bind_ctx in one thread the model will change at runtime. And it don't have solution but only use lock to bind model to one db.

If we have session concept , One thread have multi session. different session the model using different database. That will be very helpful.

[coleifer]

You can subclass the peewee.Metadata and make the database attribute thread-local. I believe this would address your issue.

class ThreadLocalDbMetadata(Metadata):
    ...

class BaseModel(Model):
    class Meta:
        model_metadata_class = ThreadLocalDbMetadata

[jiamo]

Thanks. I have two question on this:

1. It seem need change code to do "make the database attribute thread-local." I am not sure I can make it work.
   And it will not be such easy work like class _ConnectionLocal(_ConnectionState, threading.local): pass

2. For my understand. threading.local is for single thread. When using fastapi framework, the different requests will be handled in one thread. Is it still address my issue?

[coleifer]

Never heard of it, not sure if it's multi-threaded (which is what I thought you were asking about).

[rudyryk]

@jiamo fastapi is an async framework, and peewee was not initially designed to work in async app.

[qacollective]

Purely as an FYI - before this thread existed when doing data transformation work, I solved the problem of connecting to multiple databases simply by having my models in a module and then importing that same module multiple times into different namespaces, and using the DatabaseProxy() to delay connection timing so I can connect to two different databases.

So as an initial test with my models in MyApp.MyModels like:

database_connector = DatabaseProxy()

class BaseModel(Model):
    class Meta:
        database = database_connector

class MyModel(BaseModel):
    ...

In Main.py I had:

import MyApp.MyModels as SourceData
import MyApp.MyModels as DestinationData

db_source = ...
SourceData.database_connector.initialize(db_source)
db_destination = ...
DestinationData.database_connector.initialize(db_destination)

inputData = SourceData.MyModel.select(...).dicts()

for record in inputData:
    ...

This all works fine for me. I find that keeping the namespace prefixes of 'SourceData' and 'DestinationData' helped code clarity.

[fushihara]

I also found this issue by referring to how to write to another DB in multiple threads with the same model.
I think it's an uncommon usage, but I'd like you to add a statement to the Thread Safety section of the document that it doesn't support this case.
http://docs.peewee-orm.com/en/latest/peewee/database.html#thread-safety

[coleifer]

https://github.com/coleifer/peewee/issues/221#issuecomment-687625709 is probably the best way to do this in a thread-safe manner.