News for hackers. Stuff that matters.

A comment on Show HN: Doculite – Use SQLite as a Document Database.

Re:Show HN: Doculite – Use SQLite as a Document Database by bastawhiz on Monday August 07, 2023 @05:42PM (#37041362)
The one feature that I'd want out of this is atomic writes. If I have a document and want to increment the value of a field in it by one, I'm not sure that's possible with Doculite today: if two requests read the same document at the same time and both write an incremented value, the value is incremented by one, not two.

The way _I_ would expect to do this is something like this:

  const ref = db.collection('page').doc('foo');
  do {
    const current = await ref.get();
    try {
      await ref.set({ likes: current.likes + 1 }, { when: { likes: current.likes } });
    } catch {
      continue;
    }
  } while (false);
If `set()` attempts to write to the ref when the conditions in `when` are not matched exactly, the write should fail and you should have to try the operation again. In this example, the `set()` call increments the like value by one, but specifies that the write is only valid if `likes` is equal to the value that the client read. In the scenario I provided, one of the two concurrent requests would fail and retry the write (and succeed on the second go).
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by thenorthbay on Monday August 07, 2023 @06:11PM (#37041651)
Interesting. Updating values via incrementing them is a use case I barely had in Firebase. I mostly only dealt with 1-time updates to values, e.g. by the user or scheduled jobs. In which scenario would the current design cause you problems?
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by winrid on Monday August 07, 2023 @07:07PM (#37042170)
Honestly, it's weird that you never ran into this. This is a requirement for any data store and I've never not used atomic updates at any company. Most basic example: what if two users load the same object at the same time and you want to increment a "seen" count...?
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by thenorthbay on Monday August 07, 2023 @07:38PM (#37042435)
I guess it was sufficient to not have that kind of accuracy in most of the application to deliver user value. There probably were parts of the application where we used transactions. Could be a cool feature to build for this, though.
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by bastawhiz on Monday August 07, 2023 @07:38PM (#37042438)
Incrementing is only one possible use case. Any time you read data and then write back based on that data, you need to ensure that nobody wrote to the document in the interim. RDBMS do this with transactions.

Consider the case where a user is submitting an e-commerce order. You want to mark their order as processed and submit it for fulfillment. If you read the order to check if it's already submitted, two requests to submit it made at almost the same time (e.g., hitting the button twice) will both read that it's unfulfilled and try to each submit it.

By doing an atomic write you can be sure that at most one request submits the order.

[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by thenorthbay on Monday August 07, 2023 @08:14PM (#37042768)
Thank you for the feedback. Seems this is important.
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by layer8 on Tuesday August 08, 2023 @06:22AM (#37046864)
I’m sorry to be this frank, but if you weren’t even aware of the importance of transactional safety for read-modify-write operations, you shouldn’t be in the business of creating such a library. This is really basic database stuff.
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by ndbebofienw on Tuesday August 08, 2023 @08:54AM (#37048289)
If we allow ignorance to be the gatekeeper of progress, we will stifle the growth of every individual around us.

No one person can know everything. Teaching how to solve these problems will produce better software and better people too.

https://xkcd.com/1053/

[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by layer8 on Tuesday August 08, 2023 @09:07AM (#37048429)
I have no issues with the OP having that library as a side project and learning experience. But at their experience level it’s not suitable to be promoted publicly. To be fair, they only asked for opinions on it.
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by jstarfish on Tuesday August 08, 2023 @01:12PM (#37052340)
If I had a time machine, I'd use it to identify who in my life gave me this same "advice" and gift them a copy of their obituary from the future.

For a commercial product, yours is honest feedback.

But this isn't that. You popped into a literal show-and-tell to heckle some kid proud of his macaroni art. You didn't even critique the product, just insulted its maker. What the hell is wrong with you to think this is appropriate, or even helpful?

[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by gerad on Monday August 07, 2023 @11:19PM (#37044105)
It’s possible to use optimistic locking and versioning to ensure that things haven’t changed underneath you unexpectedly.
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by DANmode on Monday August 07, 2023 @05:57PM (#37041508)
I'd like to enable the same in my startup.

What are you using for this today?

[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by bastawhiz on Monday August 07, 2023 @07:28PM (#37042345)
If you're using something like MySQL or Postgres, you can do locking with the built in transaction primitives (see, for instance, SELECT FOR UPDATE). Mongo has tools like findAndModify which can help.

If you're using SQLite you can use exclusive transactions to perform the read+write but I'm sure there's probably a more efficient way to go about it. You can craft an UPDATE that selects on the primary key and the condition and then use sqlite3_changes() to get back the number of records modified (and fail if it's zero), but that may not be possible with your setup.

[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by schemescape on Monday August 07, 2023 @08:23PM (#37042836)
What about FieldValue.increment()?

https://cloud.google.com/firestore/docs/samples/firestore-da...

[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by bastawhiz on Tuesday August 08, 2023 @12:13PM (#37051313)
That's just sugar on top of atomic writes
[ Parent | on HN ]
Re:Show HN: Doculite – Use SQLite as a Document Database by schemescape on Wednesday August 09, 2023 @03:02PM (#37067061)
Interesting! I thought it would be simpler to implement than full blown atomic writes, so I assumed it was different.
[ Parent | on HN ]