Keep your CRUD off the Internet

Keep your CRUD off the Internet(ericsink.com)

25 points by io 13 years ago | 23 comments

Aside from being an advertisement, this does make a reasonably decent point for some use cases... though I don't believe most of them are ones that are commonly solved with web-based CRUD.

However, letting people do these things offline on mobile devices introduces non-trivial problems of its own.

You've now turned your presumably-consistent, probably-IT-department-managed central database into, at best, a hub that's only eventually consistent. Someone's on vacation and made some updates on their phone? Sure, it looks like it was applied. Better hope they get back in cell range or on a network again for long enough to update -- and that they have a way to monitor it and ensure it was successful -- and at this point you're losing at least part of the convenience this was supposed to enable.

You've also now created the necessity for more robust conflict resolution. When you get two people editing the same data in a live web form, you can at least invalidate the second person's update if you're keeping last-updated timestamps. This approach falls on its face in an edit-offline-then-upload model because you now either have to automatically merge changes (yikes), re-create deleted records that were edited (and deal with dead references, yikes), or implement a prompt to get the user to resolve it themselves (unhappy user and yikes).

Of course, you also have to decide who should get how much and which of your data synced, make sure it's secure, and supply some mechanism so that a careless user can't drop his phone while not locked and allow someone remote access. Making sure the data is irretrievable without the phone being unlocked and the user signed in to the app is the least of your worries, but still something to worry about. Plus you have the potential for sensitive user data being distributed to places it shouldn't be, e.g. PCI compliance issues...

I'm sure there are other issues here, but this is not a replacement; with just these issues it's a totally different paradigm. That doesn't mean it's a bad idea, but it definitely will not drop in nicely and take all that awful CRUD out of our internets.

ericsink 13 years ago | |

Developers used to be afraid of automatic merging and conflict resolution in version control tools. We evolved.

Many of those techniques also apply to merging and conflict resolution of database stuff. In fact, from a certain perspective, the screw cases are a bit easier.

http://zumero.com/docs/zumero_core.html#chapter_conflicts

chacham15 13 years ago | |

Their general idea is very similar to svn vs. git. That use case has proven to be very effective. Im sure that you can find use cases that this is more difficult to apply (where row conflicts cannot be automatically merged) but there are also use cases where this idea works like a gem.

camus 13 years ago | |

So why do you suggest ? dropping any offline/online sync feature because the implications (for crud apps)? i guess you could have a system checking what resource has been updated since last connection and explicitly alerting the user the commit has failed due to an update that happened between the offline edit and the connection, or keep track of all the versions ( which is hard to manage if resources have relationships and depend on other resources ) Anyway that's a subject i'm really into so any resource on the matter is welcomed , i'm really looking for the holy grail of data syncing.

obviouslygreen 13 years ago | | |

I think the viable scope of such a replacement is there but probably very limited. In my opinion it depends on the type, format, frequency of editing, sensitivity, and many other properties of the data you're working with.

Perhaps the solution isn't sending CRUD mobile, but rather replacing it in some circumstances with more limited, potentially check-in/check-out, simplified and abstracted workflows that make sense in specific situations. Trying to replace it like this in a general sense presents more problems than it solves.

dustingetz 13 years ago |

For apps that read a lot more than they write, there exist ACID databases in which reads don't have to touch network, while retaining consistent writes. Datomic is one; for those who have never studied datomic, the database storage is immutable in the same way that Git commits are immutable, which enables a lot of git-like things in your database, like queries without network. That way your apps can be fast without giving up ACID.

I don't know if anything like Datomic exists on mobile; and OP is talking about mobile apps not webapps; so OP may be kind of correct, at least today.

ericsink 13 years ago | |

I need to look at Datomic.

Zumero could also be described as having similar characteristics as Git. It's like a DVCS, except for stuff that looks like rows and columns instead of stuff that looks like directories and files.

I'm sure that any decentralized database violates ACID in some way, but Zumero is pretty darn ACIDic. All the local CRUD operations are ACID. And the sync to the server is ACID, or highly corrosive at minimum. But yes, the two operations are separate.

sc8 13 years ago |

With everything going mobile, and mobile not being so well connected, i'm looking at writing to local mobile storage, and then later syncing that storage to the server, when a connection is available.

This is perfect timing for me.

Thx.

camus 13 years ago | |

I'm looking for some articles about how to do syncing 101 by the way , i heard it's kind of hard. If anyone has good links about the subject ( on HTML5 offline/online sync <=> db server ) , it would be great.

troebr 13 years ago | | |

I made a kind of todo/list pet project with html5 local storage earlier. All the front end was based on that storage, then it was only a matter of syncing. To do that I kept a queue in local storage, that I only emptied after a successful sync. Then you need to define conflict rules based on what you synced, and that is case-by-case.

For instance for me, deleting a task meant anything else regarding that task later was disregarded, it's deleted. Updates to tasks were treated by timestamp. For creating tasks, I had to have a temporary id that I updated after syncing, to a permanent id.

tldr; queue, precedence rules and timestamps.

lawl 13 years ago |

I disagree. This surely depends on the type of crud application you're trying to build. We have a few in house business apps here, REST API and a JS frontend in the browser. Works fine, and since all our operations are atomic everything that doesn't come back with a 200 OK is fairly easy and generic to handle. The system also wouldn't be really usefull if the data weren't always up to date.

I get the point OP is trying to make but it really depends on what you are trying to build.

thatthatis 13 years ago |

It seems to me that sync is the last mile for a great write once deploy everywhere (Mobile + Web apps) framework.

What I really want is a meta framework that runs rails/django for backend + angular/ember for frontend + cordova/phonegap for mobile + responsive display with HTML/JS/CSS + something for syncing to allow offline mobile. Ideally this would be loosely coupled so you could choose your components (i.e. write a native mobile app if you want, or do the backend with Go, etc.)

The two pieces still missing are the sync component and the glue that keeps you from having to define the same thing multiple times. Unless there's something open source I'm unaware of.

davidwihl 13 years ago |

How is this going to sync just a slice of the database rather than everything in a given table? I would only want to publish one subset rather than all rows?

ericsink 13 years ago | |

At the present time, Zumero doesn't allow sync of a partial table. If we sync only part of a table down to a device, then SQLite doesn't have the ability to enforce a UNIQUE constraint.

SQLite does have excellent support for using multiple database files together (using ATTACH), so there is plenty of opportunity for setting things up to ensure that each device only syncs the data it needs.

ryderm 13 years ago | |

Just subscribe to the data that you care about. For example, Meteor.js does exactly this by giving each client a slice of the production db locally, but only the part that they subscribe to.

jordan_clark 13 years ago |

Like others have stated, I think it depends. Look at your target market. If the market is the USA, mobile connections are getting more reliable and really fast (LTE) and this isn't much of a problem.

OP should market this to devs focusing on countries where mobile networks stink.

GFischer 13 years ago | |

And where would that be? I thought it was the U.S. which was backwards !! :) (and I live in Uruguay, South America).

That said, I'd love it if more of my apps worked offline with sync.

jordan_clark 13 years ago | | |

Where? I don't know, but according to the speed test I just ran on my Verizon iPhone 5 I have 30 mbps down and 12 mbs upstream plus 70ms response time. With that speed/latency combo, poor network connection quality and offline syncing just isn't a problem that needs to be solved.

miljan 13 years ago |

Nice approach; I like going through the SQLite API.

EvanAnderson 13 years ago | |

cough WebSQL cough

So many apps, HTML5 or native, could benefit from an offline sync story. That wheel doesn't need to be re-invented over and over again.

This reminds me a lot of WebSQL, which I've always been sad was killed.

mitchell360 13 years ago | | |

The cool thing about using SQLite as a basis for sync is that it leverages all that we know re: RDBMS merging, syncing and conflict resolution at scale. And, from a skills perspective, leverages our team's existing knowledge in SQL.

We have use cases where mobile devices may be offline for minutes at a time (in hotels and conference halls where wireless is either sketchy or un-usably slow). I can definitely see us using this. I may check out the Couch thing as well, although we use Cassandra for NoSQL scenarios.