Over a million developers have joined DZone.

SQLite Databases & Corruption: Why You Can't Please Everybody

DZone 's Guide to

SQLite Databases & Corruption: Why You Can't Please Everybody

· Java Zone ·
Free Resource

According to Eric Sink, the reason your SQLite database is dragging may not be your code or any of your design choices, but just because of Google or Apple. And Sink is confident in that argument:


No. Yes. Kinda. Not really.

Ultimately, Sink points to the fact that SQLite databases are fairly easily corruptible simply by connecting more than one to an application corruptible by linking multiple copies of the SQLite library - a "global list (mutex protected) of open SQLite database files," as sqlite.org defines it - to the same application. Once database connections are made using multiple SQLite libraries, work-arounds designed to avoid Posix Advisory Locking bugs will no longer work. [1]

C# developers, for example, run the risk of such library duplication because they access SQLite through a wrapper, which puts some distance between the developer and their database. Sometimes, mistakes are made.

The really interesting thing, though, is how Google and Apple push you into making this mistake. According to Sink, both major platforms ship with SQLite versions from 2012 - potentially even older for Android, given that so many users are still using old versions of the OS - which leads many developers to package apps and tools with their own versions of SQLite. When a library bundles its own SQLite, though, it may cause a problem when your app attempts to use the built-in OS SQLite.

Sink illustrates the severity of the problem with a list of requirements one might have for a SQLite database:

  • Up-to-date version
  • Encrypted data
  • Same version on all mobile platforms
  • No risk of data corruption
  • Compatibility with ORMs and sync tools
  • Not have to think about it

These are all fairly reasonable requirements, and according to Sink, the current state of SQLite adoption makes it so they cannot all be met.

For a complete look at Sink's argument and his conclusions, check out the full article.

[1] Thanks to D. Richard Hipp for corrections and clarifications!


Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}