HN Explorer

Ask HN: SQL ORM with nested atomic updates?

1 points by btdmaster ↗ HN
I have some data structures that look like this:

Car:

  Seats: [{ID: frontSeatId}, {ID: backSeatId}]

  Driver: {'Name':'Joe', 'SalaryUSD': 42000}

  SoundSystem: 

    {'TypeID': androidAutoId, Protocols: [{ID: radioProtocolId}, {ID: bluetoothProtocolId}]}
In this system, we often need to do partial updates, like:

Car:

  Driver: {'Name': 'Jack'}

  SoundSystem:

    {Protocols: [{ID: bluetoothProtocolId}]}
Meaning that the SoundSystemProtocols table should delete and create new entries such that the car only supports bluetooth, and the driver should get renamed from Joe to Jack.

Is there an ORM that lets you do these partial updates atomically, without writing custom controllers for each Car-like object?

I want ergonomics over performance. I don't mind the language. Ideally it should allow a simple interface where the model defines the controller.

I've tried SQLModel, which promises this, but ended up with confusing JSON validation, mixing between SQLModel types and the internal SQLAlchemy model, and still had to write session.add(seats) by hand anyway.

If this doesn't exist for SQL, does another database support ID-as-value like this, where passing in an ID changes a reference, and passing in a value changes the value at that reference?

5 comments

[ 5.1 ms ] story [ 18.1 ms ] thread
jayknight
Does this do what you are looking for?

https://docs.sqlalchemy.org/en/20/orm/session_transaction.ht...

It's the backing DBMS that will enforce atomicity. Postgres and sqlite allow the semantics of nested transactions with SAVEPOINTs (I don't know about other RDBMSes).

btdmaster
I see, that's very cool! It was more about getting it to do the nested update bit though, as in being able to do session.update on the Car with semantics that it will update the Driver connected to the Car, rather than point to a new driver.

It seems though this needs to be done by fiddling through ORM internals though, as I could not find any that would implement this kind of PATCH semantics.

malteg
something like https://gorm.io/docs/update.html#Update-from-SubQuery ?
btdmaster
Yup, but when I've tried gorm this is not generic enough to let me do this nested generically (with relations).

In gorm you can't do Car.update({'soundsystem': [{id: 'bluetoothProtocolId'}]}), you can only do something like Car.Association('soundsystem').update([{id:'bluetoothProtocolId'}]), fundamental difference being that the latter is dealing with Car internals and is not generic.