Grdb.swift: How do I make a join from two different databases?

[ahmedk92]

Using raw sqlite I could open two database files at once, like: ATTACH DATABASE path/to/db.sqlite as seconddb

How can I achieve this with GRDB?

Thanks!

[groue]

Hi @ahmedk92,

The topic of attached database has never been addressed yet in GRDB, and it is the first time someone asks about it.

I can give quick advice, based on my limited knowledge of attached databases:

First, just run the raw ATTACH sql query in order to attach a database.

Second, use a single DatabaseQueue for the main database and the attached one(s). This means two things, practically speaking:

• Avoid using several database queues, since they won't be able to guarantee the serialization of database writes, and may throw SQLITE_BUSY errors. If you really need several database queues, set up a busy handler with Configuration.busyMode.

• Don't use a database pool, which run in the WAL mode, unless you want to give up atomic commits (see SQLite documentation).

Third, I don't know how well GRDB handles the disambiguation of database tables that have the same name in the main database and its attached databases. If you have such ambiguity, you'll enter uncharted territory.

Please share your experience when you have a working setup, will you?

[ahmedk92]

I did this and it worked!

try! q.write({ db in
  try! db.execute(sql: "attach database '\(OTHER_DB_PATH)' as otherTable")
})

Edit: Yes, I'm using a single DatabaseQueue not pools. If anything gone wrong I'll try to report it here. Thanks for your fast reply @groue !

[ahmedk92]

try! q.write({ db in
  try! db.execute(sql: "attach database '\(OTHER_DB_PATH)' as otherTable")
})

Executing the attach statement inside a write crashes on the iPad 12.9 2nd generation with error: cannot ATTACH database within transaction.

Doing it in a q.read seems to work:

q.read({ db in
  try! db.execute(sql: "attach database '\(OTHER_DB_PATH)' as otherTable")
})

@groue

[groue]

DatabaseQueue.write is documented to wrap statements in a transaction. If you want to avoid this transaction, check the Transactions and Savepoints chapter.

[ahmedk92]

Thanks! Didn't know about dbQueue.inDatabase. That's what I needed. Strange thing though is that crash only happened on iPads.

[groue]

Yes, it is strange. Maybe the SQLite version is different?