Issue: Thread safe DB swapping at runtime
Author: induane
State: closed
Created: 2021-03-10 17:42:11

The use case I have is in using sqlite as an application file format. I need to be able to switch databases at runtime, preferably in a thread safe manner.

I wouldn't mind using a context manager but I've had some issues with the proxyobject. Namely that once I initialize it, it's done globally.

What I'd like to be able to do is something like:

with BackendPath("/foo/bar.udf"):
    # do blar
    
    
Or alternatively be able create multiple instances of the document abstraction:

d = Document("/foo/bar.udf")
d2 = Document("/nan/baz.udf")

and have operations on each run through the model layer

I haven't found a good way to do that yet. Can you suggest one? Or alternatively are you open to me digging around in peewee a bit and taking a stab at a way to do this?

- Brant 

There are two ways you might look at doing this:

1. re-bind the `SqliteDatabase` instance to a different database file
2. re-bind the models to a different `SqliteDatabase` instance

I'm not aware of any patterns for doing this an arbitrary number of times at run-time, so you'll have to piece it together for yourself a bit.

Probably the easiest will be #1 since there are fewer moving parts. If you're connecting from multiple threads, because Peewee stores connection state in a thread-local, you'll have to ensure all threads close their connections when you switch database. It seems unlikely you would have connections open across threads, because of sqlite's write locking, but something to watch out for.

The only thing that would make me not consider option 1 is if you need to be switching back and forth between databases. In that case you might want to keep the db instances around and rebind the models between them.

You can re-initialize the database instance like this (option 1):

```python

db = SqliteDatabase(None)  # database is not initialized.

# db is "initialized" - when you call connect() it will use
# /tmp/db1.db
db.init('/tmp/db1.db')
...
# re-initialize db. if the calling thread has an open connection,
# it is closed. subsequent connections will use /tmp/db2.db.
db.init('/tmp/db2.db')
```

Rebinding models to a different database instance, as well as general db configuration stuff, is covered in the following links:

* http://docs.peewee-orm.com/en/latest/peewee/database.html#setting-the-database-at-run-time
* http://docs.peewee-orm.com/en/latest/peewee/database.html#run-time-database-configuration
* http://docs.peewee-orm.com/en/latest/peewee/database.html#testing-peewee-applications - covers re-binding models for testing purposes

I'm not likely to make any changes in the database apis or connection handling implementation, for what it's worth. 

Dang you're quick on the response!

It has proved fairly tricky to have multiple active document instances and switching the models DB attribute.

My first approach was a decorator I applied to all methods in the document class that called db.init(self.file_path) but that was pretty cumbersome and I was able to break it with subprocesses where I was doing large numbers of updates to different documents in parallel (which seemed like a good approach as it was mostly io bound).

Maybe the right approach is a custom proxy object of some kind that pulls connections from storage on thread local or something. I'll tinker around further.