I have been reading about all the difficulties with SQLite and wanted to share another approach I have used successfully that relies on nothing other than native NSB.
I use csv text files and this seems to work well and avoid all the problems.
These are read into 2d arrays (using readfile() command) at application start up and data is searched using for loops.
I have one application that uses a 14Mb csv file with 36 fields and 105,000 records. Performance is good on web and mobile even a pwa update of the file is reasonable and the app works for users with poor internet connections.
I find it hard to believe you’re for looping thru each of the 105000 items sequentially and it’s quick. Did you create an index, like a binary tree search or other similar method?
I also have avoided sql on devices and on aws I use json in txt files with optimized txt file indexes. I even cache the data locally for faster lookups, and send it up the line to the server. I do not keep the entire db locally, but only download the necessary segments.
Definitely would like to hear about you’re solution.
Hi Gary,
Your comment made me curious, as I had a similar feeling. So I wrote a little program in one of the Basic implementations available on Android devices, filled an array with 105000 rows of 36 fields per row with string values, then ran a simple sequential search over it, looking for the value in the last row, first field. On my Android phone (OnePlus Open) this took roundabout 150 milliseconds - so a delay hardly noticeable.
I also ran the search over all fields in all lines - that took 1661 ms, so still a very reasonable time. This was simply using 2 nested for loops, nothing sophisticated at all.
I‘d like to try something similar using SQLite just to see if it‘s significantly faster. But the CSV file approach seems feasable when dealing with numbers given.
Kind regards
Thomas
Most interesting. These little phones are quite powerful.
I have another interest in this. Originally (5 years ago), I was looking at writing a service worker to synchronize my local “DB” with the server “DB”. It never happened. It’s currently a one-way cache, if not on the client, it’s fetched from the server. All new data is sent via Ajax (hoping you have a network connection) to the server, so offline causes issues. I’d like to cache the new updates locally and when a connection occurs, have the service worker update the server.
I have done a lot of work with service workers recently, and feel it is quite possible to write the two-way cache to hold updates until the server comes online.
Why isn’t anyone considering indexeddb? It is the planned replacement for sqlite and has been out for several years. I ported one of my db’s from sqlite to indexeddb back in 2020 and it worked quite well.
Anyway, it’s quite robust, it’s available in service workers and heck, it even has cursors.
I’m a PouchDB/CouchDB advocate as they form the perfect pair for offline-first apps.
PouchDB is a CouchDB-compatible JS implementation of CouchDB that runs in the browser, leveraging several storage backend options, defaulting to indexeddb.
It is very fast and if you pair with a CouchDB on your backend server, you get the data replication for free, since it is built-in to CouchDB/PouchDB.
If you don’t want the hassle of running your CouchDB server, IBM offers a free-tier Cloudant, which is basically their CouchDB implementation, that you can use as your backend DB.
I run my own Ubuntu servers with CouchDB on Oracle Cloud (they also offer a free tier with 2 VMs), as I enjoy playing around with different configuration options. These servers also run my Apache+PHP for backend processing.
I’m more than glad to review your database needs and help with PouchDB/CouchDB design. It would be great to be able to share ideas around its use, if someone else finds it a valuable DB solution for their apps.