2

I used to do Java development and with JPA, I don't really have to worry about the synchronicity between a table and the lists it owns. For those who don't understand what I mean, here's an example.

Suppose you have a Person table and an Address table. A person can have many addresses. So when you update the person's addresses and you're using JPA. Once you persist, the magic happens behind and everything is updated magically (addresses that should be deleted are deleted and those that are updated were updated).

Fast-forward to now, I'm doing NodeJS development and I notice that although we are using flex and some internal libraries to help with building the SQL queries, I can't do what's described above. Currently, there's 2 way that I can think of to do this.

  1. I can delete all addresses that belongs to the person and re-create them. However, doing this means I lose all the metadata that might be useful for logging.

  2. I need to perform extra query to the database to retrieve the previous list of addresses and then perform a for loop to check which address_id should be deleted, updated or created. This is not scalable if the list becomes too large.

So other than the 2 suggestions above, do you guys have any good methods? I can't find any good Persistence adapter for MySQL and NodeJS so far that doesn't have too much dependencies (npm nightmare right there) so it would be good if the solution could be implemented without a 3rd party library/framework.

5
  • 1
    The way it's typically done with Hibernate (Java) is by using a join table (AKA Associative Entity) under the covers. Any reason you can't do the same here? – Ordous Apr 9 '18 at 18:35
  • @Ordous: I don't understand how your comment applies to the question. There is a given 1:n relationship in a database, and the question is how to track necessary updates and deletes when manipulating the data in-memory. How does a join table fit into that? – Doc Brown Apr 10 '18 at 10:56
  • @DocBrown As I understood the question, the DB currently has a literal list for each user and that is giving OP a headache - as it's difficult to manipulate those. A join table would make things much simpler in that case. However, rereading the question, I may have been mistaken in that assumption. – Ordous Apr 10 '18 at 11:12
  • Well, Clara, a comment from your side if we got you right or wrong would be helpful. – Doc Brown Apr 10 '18 at 12:19
  • Are you aware that a delete query can have a join clause? – Caleth May 10 '18 at 14:41
1

So you have Person objects, each one holding a list of Address objects in your application's code, representing the records in the database. Just give the latter an additional (non-persistent) State attribute with possible values

  • New (for new records to be inserted into the DB),
  • Unchanged (for records which don't require an update)
  • Changed (for records to be updated)
  • Deleted (for record to be deleted)

which reflects the difference between the in-memory objects and the database records.

Now make sure this State attribute is maintained properly by your application's code. For example, make sure all attributes of an address are accessed only through setters, and each setter calls a method which changes the state to Changed when it was Unchanged before. Then you can easily write some code which INSERTs exactly the new records, DELETEs the deleted ones and UPDATEs exactly the ones which require a modification.

If you want to generalize this and don't want to introduce 3rd party libs, you could write your own micro ORM, or at least a small code generator, which provides this mechanics for all persistent objects in your system.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.