0

I have a multi-tenant DB architecture. Is there any way to use one active model for multiple identical databases?

A little clarification:

I have a Foo exdends Model class that works with a foo_table. That foo_table is identical in schemaA, schemaB, schameC etc. Can I use the same Foo class to do operations on each of these tables from different schemas?

dkb
  • 4,389
  • 4
  • 36
  • 54
Norbert Bicsi
  • 1,562
  • 2
  • 19
  • 33

1 Answers1

2

If you have databases with identical schemas, you can totally use the same model across multiple databases. In fact, it is typical to do this when people do ETL from one database to another.

A typical program looks like this: 

Base.open(/*connection params*/);
List<Person> johns = Person.where("first_name = ?", "John");
Base.close();
  • The Base.open() opens a connection and attaches it to the current thread.
  • Next line Person.where(..) finds a connection on the thread, and uses it to select records.
  • Base.close() finds a connection on the current thread and closes it.

Also, the ActiveJDBC models when first used in the JVM, will expect a connection on the current thread, and will pull metadata from the current schema. This will ensure that:

This means that at any given time, a model assumes that it is connected to a database with the same structure that was used to get the metadata.

Next, this is probably what you want: 

Base.open(/*connection params for DB1*/);
List<Person> johns = Person.where("first_name = ?", "John");
Base.close();

Base.open(/*connection params for DB2*/);
for(Person person:johns){
    person.set("first_name", "Bill").setId(null).saveIt();
}
Base.close();

Essentially, you will read data from one database, but will save it to another.

The call to person.setId(null) is needed to ensure the framework generates insert, and not update, see http://javalite.io/surrogate_primary_keys.

ipolevoy
  • 5,432
  • 2
  • 31
  • 46
  • So if I understand correctly, my problem is that the first time I connect to a schema, that schema does not contain all the tables my models reference. That is because the first schema I access is my schema_auth which I use to log in all users. I imagine if i would create all my tables in the schema_auth as well then it would work. Is there a more elegant solution? Could I perhaps reload the DB metadata based on the currently attached connection whenever needed? – Norbert Bicsi Dec 23 '16 at 17:36
  • you did not mention anything about any problems in your original question. The metadata is read only once, and cannot be reloaded after that. All your schemas need to have identical tables for the models you want to operate. – ipolevoy Dec 23 '16 at 18:09
  • I didn't mention it because I didn't know the metadata only gets read once so I didn't realize it was relevant info. Thank you for your help. I understand now, I will update my schemas and it shall work! – Norbert Bicsi Dec 23 '16 at 19:46
  • 1
    Just occurred to me that this page is relevant for your understanding: http://javalite.io/runtime_discovery – ipolevoy Dec 28 '16 at 03:20