Database for Cloud and Offline

[chkaufmann 17]

Hi,

 

I have an application that still uses an MS Access as database. With the library I have I can run it on other databases as well since all requests are pure SQL (no TTable or things like that).

Now I'm looking for a replacement and I wonder, if there is a solution that could be used for cloud and offline.

 

The databases are not that big (about 25 tables, maximum 25'000 records each). But each customer may have up to 50-100 databases.

 

Ideal would be a solution where the database could be in the cloud, but there is an offline copy in case the internet connection is broken, so I can continue to work. When I'm back online, all changes would be synchronized to the server.

 

Now I know there is probably no easy solutions, but I'm open for ideas on how to find a good solution.

 

Christian

[aehimself 388]

I was experimenting with this once but found that the merge when your app goes back online is not that easy if the data can be changed at multiple locations. Effectively you'll have to have a local database and keep your own transaction log (can be the raw SQL queries) of all the work which was done offline. When the connection is back up, replay that transaction log on the remote database. But if the database was changed by someone else on the remote location you'll have to automatically decide what to do with each failed query, and it will get annoying after a while. Furthermore, you already have a local database, which is only kept in-sync with the remote one... seems to be unnecessary.

At the end I created my own datastore with classes and simply stored everything in a local JSON file. It was even easier to merge the modification sessions, as I had a full log of what happened and I did not want to change a value which I knew was deleted two weeks ago.

 

If this is not an option for you, set up a local database with SQLite or alike... that will have the least overhead I can think of.

You can also use a more advanced local database engine and use it's built-in replication to get the dirty work done.

[ConstantGardener 31]

TMS Echo

[Guest]

This is not a full solution for your question but public information that might leads to solve similar situation, for all of us, simply by make them know about it ( DevArt, mORMot, Embarcadero..), 

 

CockroachDB , i saw it in work and it is brilliant, and it is exactly what many of us need in situation like yours when you can work offline and engine will synchronize automatically with others nodes when you are online, https://www.cockroachlabs.com/ , full cloud DB where any laptop/desktop/server.. can be added as node, as online server or just as backup, the data will be synchronized automatically, you don't need to worry about anything.

 

How much it is real to use it with Delphi, I tried it with my old Navicat (DB manager) and it did work and connect fine and i could browse the default tables, CockroachDB is using PostgreSQL protocol, and it did work as such with Navicat, but i couldn't manage to make UniDac to connect to it, my UniDac is not up to date and neither my Navicat, so it might easy to fix or a workaround and it might be ready with FireDac or mormot.

 

Really worth reading about, it server/node is one exe for Windows and it is portable, and it has very nice web interface for monitoring ( may be other stuff i can't recall now).

 

TMS had promised in a blog post about FaunaDB but seems like still underwork, CockroachDB is open source and free unlike FaunaDB, and i have no idea how the performance and functionality between these two differ, all what i know that i saw CockroachDB been used in blockchain project as a backend for transactions and DB storage, and i saw it on a Linux on friend laptop and it is brilliant.