undefined | Better HN

0 pointscyberax1y ago0 comments

In my current company, we're using a similar approach: just shove everything into a JSON blob. If you need a constraint or an index, you can create a computed column (in PostgreSQL) that pulls out a field from JSON.

For the data schema, we're using Protobufs with buf validate. This works surprisingly well, you can use the same types in the backend API and on the frontend. We even have a cron job that reads all the data periodically and verifies that the JSON blobs conform to the schema. Our next intern is going to write a PostgreSQL extension to do that on commit/update :)

One real advantage of this approach is that it's easy to do stuff like "search everywhere".

0 comments

kubectl_h1y ago

Do you go to the trouble of updating individual values in objects using some kind of deep/partial updating function or do you just accept race conditions that come with updating full objects?

cyberaxOP1y ago

We use optimistic versioning, with a dedicated "version" field (that is actually always pulled out of the blob in all tables).

Classic fine-grained schemas are not that much different. A lot of high-level ORM frameworks simply save all the objects' fields on update, without doing fine-grained diffs.

In addition, our frontend apps also support offline mode. They can get all the relevant objects, and then operate on them locally. So our API was designed from the start to deal with conflicts.

kubectl_h1y ago

> We use optimistic versioning, with a dedicated "version" field (that is actually always pulled out of the blob in all tables).

All well and good but you do need to handle failures elegantly in code. The nice thing about flat DB tables and SQL is you don't really have to care if your goal is to update a single column authoritatively. The state of the other values in the table are, often, immaterial. It gets even more complicated reconciling deeply nested conflicts in open schemas.

Not knocking your approach, it's just a trade-off I guess.

kubectl_h1y ago

> A lot of high-level ORM frameworks simply save all the objects' fields on update, without doing fine-grained diffs.

Perhaps that is true... I'm a Rails dev and ActiveRecord definitely tracks mutations on the object and selectively updates.

I would assume this is consistent with Django and I'd assume a policy is probably required to be specified by SQLAlchemy.

duskwuff1y ago

A lot of databases include functions for manipulating JSON which can be used for atomic updates. SQLite has JSON_PATCH(), for instance.

kubectl_h1y ago

Yeah, makes sense. To write safe code though at that point don't you have to enforce rigid schemas?

An example would be updating Kubernetes resources, the admission controller will verify the correctness of the change you are trying to make and reject the patch if it's non-comformant. Nested values have... value... in terms of contextualizing and isolating/localizing data leaves but at the end of the day aren't you still dealing with strict schemas and, when those schemas change you have to reconcile schema migrations?

duskwuff1y ago

> To write safe code though at that point don't you have to enforce rigid schemas?

Certainly. But you have to do that with SQLite anyway, because (by default, without strict mode [1] enabled) it won't stop you from putting the wrong type of data into a column.

[1]: https://www.sqlite.org/stricttables.html

j / k navigate · click thread line to collapse

0 comments

kubectl_h1y ago

Do you go to the trouble of updating individual values in objects using some kind of deep/partial updating function or do you just accept race conditions that come with updating full objects?

cyberaxOP1y ago

We use optimistic versioning, with a dedicated "version" field (that is actually always pulled out of the blob in all tables).

Classic fine-grained schemas are not that much different. A lot of high-level ORM frameworks simply save all the objects' fields on update, without doing fine-grained diffs.

In addition, our frontend apps also support offline mode. They can get all the relevant objects, and then operate on them locally. So our API was designed from the start to deal with conflicts.

kubectl_h1y ago

> We use optimistic versioning, with a dedicated "version" field (that is actually always pulled out of the blob in all tables).

Not knocking your approach, it's just a trade-off I guess.

kubectl_h1y ago

> A lot of high-level ORM frameworks simply save all the objects' fields on update, without doing fine-grained diffs.

Perhaps that is true... I'm a Rails dev and ActiveRecord definitely tracks mutations on the object and selectively updates.

I would assume this is consistent with Django and I'd assume a policy is probably required to be specified by SQLAlchemy.

duskwuff1y ago

A lot of databases include functions for manipulating JSON which can be used for atomic updates. SQLite has JSON_PATCH(), for instance.

kubectl_h1y ago

Yeah, makes sense. To write safe code though at that point don't you have to enforce rigid schemas?

duskwuff1y ago

> To write safe code though at that point don't you have to enforce rigid schemas?

Certainly. But you have to do that with SQLite anyway, because (by default, without strict mode [1] enabled) it won't stop you from putting the wrong type of data into a column.

[1]: https://www.sqlite.org/stricttables.html

j / k navigate · click thread line to collapse