Issue: Using attached databases with sqlite?
Author: kdart
State: closed
Created: 2017-04-08 00:58:46

Greetings,

This is more of a feature request or request for comment. 

SQlite has a feature to attach multiple database files: [ATTACH DATABASE](https://sqlite.org/lang_attach.html) that seems to enable queries across multiple database files. So I was wondering is there support for this in peewee already, or, if not, how one might accomplish this with peewee.

I'm especially interested in the case where the schema of the two files is the same, but contain different data. 

Would a new model definition be required?


It should work -- you'll need to run the "ATTACH" query at some point by calling database.execute_sql(). As for your model classes -- you may find it easiest to work with two sets of model classes, each of which defines a "schema" Meta option of "main" and whatever your attached db is. This will prefix all table accesses with the database name (equivalent of specifying the "schema").

So: it may not be easy, but it should be *possible*. Just experimenting in the shell, it looks like you could do something like this:

```python
class MyModel(Model):
    data = TextField()

    class Meta:
        schema = 'main'  # Or omit, but main is default sqlite db

class MyModelAttached(MyModel):
    class Meta:
        db_table = MyModel._meta.db_table
        schema = 'cache'  # Or whatever the attached db is named

MyModel.select()
# Outputs SELECT "t1"."id", "t1"."data" FROM "main"."mymodel" AS t1

MyModelAttached.select()
# Outputs SELECT "t1"."id", "t1"."data" FROM "cache"."mymodel" AS t1
```

Thanks for taking the time to investigate that. That can get me started. This method, however, is rather "static", as it's embedded in the code.  I'm trying to find a way to make the schema dynamic (provided by user). I suppose it's possible to update the meta attribute on the fly. 
I'll try that. 


You can update the "meta" attribute on the fly, but it will affect the class-as-a-whole.

OK.  sqlite allows you to mix schema per query (SELECT * from main.Table as t1, other.Table as t2 ...) and perform a cross-schema query. At least it appears that way, I haven't tried it yet. I don't see how to accomplish that with the peewee API.  It looks to me like that would be a big change. I'll probably have to work around it by using multiple queries to the different schemas. 

I've done this before. It's possible and not a complete nightmare. You need to specify the join condition since there are no foreign keys set in peewee in this case.

```python

import peewee

database = peewee.SqliteDatabase('main.db')

database.execute_sql('attach database "another.db" as other')

class Main(peewee.Model):
    name = peewee.CharField()

    class Meta:
        database = database
        schema = 'main'

class Other(peewee.Model):
    name = peewee.CharField()

    class Meta:
        database = database
        schema = 'other'

Main.create_table()
Other.create_table()

Main.create(name='tim')
Other.create(name='tim')
Other.create(name='john')

record = Main.select().join(Other, on=(Other.name == Main.name)).first()

print(record.name)
```

thx @timster. FYI, you don't need to update the schema name, as [sqlite will look for tables in attached databases anyway](https://www.sqlite.org/lang_naming.html).