Cordova/PhoneGap sqlite plugins offer large db size, excellent reliability

The Cordova/PhoneGap sqlite plugins (PhoneGap-SQLitePlugin-Android & PhoneGap-SQLitePlugin-iOS) offer some major advantages over the built-in WebKit SQL library API including support for large database sizes (see sqlite.org/limits.html) and excellent reliability.

Database sizes

The HTML5/Web SQL API imposes an upper limit of 5MB for client-side databases, which is more than sufficient for some applications but is very small by current standards. When using the Cordova/PhoneGap sqlite plugins then multi-gigabyte databases should be no problem according to sqlite.org/limits.html.

By standard compilation the default limits include:

• 2 TB database:
• 2G (2^9) pages with default page size of 1KB
• 1 billion (1 000 000 000) bytes in a string or BLOB
• columns: 2000
• SQL statement length: 1 million (1 000 000) bytes

These should be more than sufficient to satisfy the needs most mobile apps that are published today.

Limits such as number of columns and SQL statement length can be changed at run-time.

Defaults and some other limits such as maximum number of pages can be changed at compile-time. This would require rebuilding the sqlite library itself, which will be covered in a future posting.

Reliability

Keeping the sqlite database in a known, configured location guarantees that the database will be kept up-to-date and backed up on the user's PC or iCloud for the iOS. Using the WebSQL library API does not provide these guarantees for iOS or Android.

There have been a number of reports of data loss problems with the WebSQL library, especially for iOS.

History of WebSQL problems on iOS

Starting in January 2012, this thread on the PhoneGap forum discussed the issue that the local storage or WebSQL persistent data would no longer be backed up as of iOS 5.0.1. Kerri Shotts was kind enough to post a workaround solution that backs the data up during an app shutdown and restores the data upon startup. She made very clear disclaimers that this was only a hack.

CB-330 was filed for this issue, and the solution was in fact to back the data up upon app shutdown and restore upon app startup. This is a pretty good solution but what would happen if an app or even a device would crash?

Some reports have been made recently including:

• CB-1561 Using Storage API - rejected by Apple
• another thread about data loss
• an issue with 2.2RC1, fixed in 2.2.0

WebSQL on Android

Fortunately the issues with the WebSQL on iOS have not (yet) shown up on the Android platform. The WebSQL database is kept currently in a location that will be backed up on the PC, however this can be changed in the future.

Also on the PhoneGap forum there was a recent posting about data not persisted on the Android version.

In comparison, these kinds of problems have never been reported for the Cordova/PhoneGap sqlite plugins.