ormar - multiple DB

[avico78]

source db is distributed env ,
So in examole on DB_a i have table and table on DB_b,
I have relation between the two by somekey
ormar module on DB_A:

class Test(ormar.Model):
    class Meta(BaseMetaDB_a):
        tablename: str = "test"
    id: int = ormar.Integer(primary_key=True)
    some_key: int = ormar.Integer()

ormar module db_b

class Test(ormar.Model):
    class Meta(BaseMetaDB_b):
        tablename: str = "test"
    id: int = ormar.Integer(primary_key=True)
    some_key: int = ormar.Integer()

Target db is similar 2 db.

I'd like to join the 2 modules into single module so i could use for read/write data easily.
how can i achieve that?
this example for 2 tables on 2 different table but actually i have 30 tables spread over 3 different DBs.
Which need to move to target dbs(same configuration as source db)

[collerek]

I'm not sure if I follow, but if you ask what i think you ask you can:

either rename the models to reflect the module:

class TestA(ormar.Model): # different name
    class Meta(BaseMetaDB_a):
        tablename: str = "test"
    id: int = ormar.Integer(primary_key=True)
    some_key: int = ormar.Integer()

ormar module db_b

class TestB(ormar.Model): # different name
    class Meta(BaseMetaDB_b):
        tablename: str = "test"
    id: int = ormar.Integer(primary_key=True)
    some_key: int = ormar.Integer()

other app module:

from module_a import TestA
from module_b import TestB

# the names differ
await TestA().save()
await TestB().save()

or if you don't wont to rename the classes simply import the whole module and use qualified names:

other app module:

import module_a
import module_b

# use name with module prefix
await module_a.Test().save()
await module_b.Test().save()

BTW. can you use language setting when you paste code here - it's easier to read when github adjusts the colors etc.
To do that use python after the backticks that open the code snippet (like: ```python)

[avico78]

@collerek

I think you did understand it right ,
but let me explain the motivation behind this:

let say you have source DB which is distributed (db_a,db_b)
this data , for the sake of the explanation , is a banking system and db contain customers data .
Bank decided to upgrade to new DB but they will not take risk to do blind full copy ,
so they reasonable preferring copy customer by customer (or bulk of customer but each "select" will fetch data from all relevant tables in context/resolution of a single customer ).

So we want to copy Customer data from source to new target (new_db_a,new_db_b)
successfully copy means - only if in both DB data was inserted successfully ,
otherwise rollback this customer.

So as orm provide an abstract layer to DB modules,
we know "represent" a logical unit by relationship ,
i.e , we "represent" Customer as logical unit where BankAcount belong to it .

class Customer(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    customer_name: str = ormar.String(max_length=100)
    customer_id: str = ormar.Integer()

class BankAcount(ormar.Model):
    class Meta:
        database = database
        metadata = metadata

    id: int = ormar.Integer(primary_key=True)
    account_id: str = ormar.String(max_length=100)
    account_balance: float = ormar.Float()
    customer_id: str = ormar.Integer(index=True)
    customer: Optional[Customer] = ormar.ForeignKey(Customer , ,related_name="account_details")

as above sharing same database - no problem , we can easily save Customer in the DB:

Customer.save_related(follow=True, save_all=True)

or getting the specific Customer

customer= await Customer.objects.select_all(follow=True).filter(cutomer_id=1)

But if they are on different DB (without "real" pk/fk) -
<>

Is that mean , in case we have nested/data hierarchy (in this Customer)
which build from tables from different DB can't be refer to root/Main object with functionality ormar provide (follow=True)?so we must break it down by the different db
so we preform :

await Customer().save()
await BankAcount().save()

just to emphasize the tables on different DB but do have some relationship fields.

As the nature of ormar is given an abstract layer i thought there's must be way to simplify the process.
maybe by some virtual relation in each of modules lives in different DB ,
I could create a synonym over dblinks to childs tables(i.e on one of the DB where Customer table lives
so the definition of both Customer and BankAccount will use same database .
but this kind of very limited solution as inserts overs dblinks has limited support for some field types.

i will try to implement what u suggest as with some conditional logic .
hopefully this kind of functionality will be build in someday:)

import module_a
import module_b

# use name with module prefix
await module_a.Test().save()
await module_b.Test().save()

[collerek]

If you want to be sure that both saves or none use transactions.

Cause it some of it fails it will end in Exception (at least it should)

import module_a
import module_b

# use name with module prefix
db_a = module_a.Test.Meta.database
db_b = module_b.Test.Meta.database

transaction_a = await db_a.transaction()
transaction_b = await db_b.transaction()
try:
    await transaction_a.start()
    await transaction_b.start()
    await module_a.Test().save_related(follow=Ture, save_all=True)
    await module_b.Test().save_related(follow=Ture, save_all=True)
except:
    await transaction_a.rollback()
    await transaction_b.rollback()
else:
    await transaction_a.commit()
    await transaction_b.commit()
        

That way if any of them will fail both will be rollbacked, and you don't have to check anything.

I don't really understand what you mean by : "does it mean that abstract layer which provide an easy way represent a logical unit is lost", can you rephrase this and the later paragraph.

[avico78]

@collerek ,edited .

Not really familiar with transaction functionality,
I did pass on documentation :
https://collerek.github.io/ormar/transactions/
for ur example :

transaction_a = await db_a.transaction()
transaction_b = await db_b.transaction()

Is that working like kind of demon that "see" module initialized with data
and once it does it preform the functionality you describe in ur code?

I missing the part for how it get the data ?
is there a chance u have an example which based on fastapi ?

[collerek]

There is no way to query/join two different databases at once.

In sqlalchemy for some backends there is a way to join schemas (so for some backends this means different db's on one/same server). Otherwise you can specify multiple dbs in session and query them at the same time as long as you do not combine data from different dbs.

In ormar your models can belong to different databases and you can query multiple databases, but they cannot be related (so joined/ saved/ queried in one query). And it's not ormar specific it's most (all?) of orm out there (all I know at least).

There are some link servers etc. functionality for different backends (i.e. mssql and mysql) but it's not supported in ormar and most (all?) orms.

So you can use pydantic (or ormar) to combine data from different databases, but this needs to be split into calls to separate dbs. So either reading the data or saving the data needs to be done per database, and later the result can be combined in python.

Transaction is just a mechanizm do undo all changes to db if something goes wrong before all data is saved to db. So you get your data normally as in other fastapi endpoints - passed as json to api.