4 ms·

Is SQLite a good choice for a user facing file format? I.e. were the user can access the database file in Finder/Explorer and make a copy at any point in time

by ricg 5y ago

Is SQLite a good choice for a user facing file format? I.e. were the user can access the database file in Finder/Explorer and make a copy at any point in time to share, backup, etc. Would that copy be consistent?

snuxoll 5y ago
As long as it’s not actively being written to, yes. Even enabling WAL won’t save you from inconsistent backups without use of the backup API or atomic file system snapshots when writers are active.
- ricg 5y ago
  Well, I'm thinking of an app that lets the user edit some kind of document. At some point the changes have to be saved. Could this happen to the "live" file or would the workaround be to keep a temporary copy somewhere away from the user and copy that back and forth?
  - snuxoll 5y ago
    Yes, using the backup API you could load on on-disk database into memory and then do the reverse to a temp file and atomically move it to replace the saved copy. There’s even an example of using the backup API for this on the SQLite website https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html This wouldn’t be a great solution for large databases, but if you’re using it as a document format up to the couple dozen MB range it’s the perfectly acceptable. Alternatively you could just go the working copy route - make a copy of the database on open and move it back in place when closed. This would also let you recover unsaved changes if you crash or are terminated before the user hits save, as well as the obvious benefit of not needing to keep the entire database loaded in memory and needing to write the entire thing out on save.
    - ricg 5y ago
      Thanks for the details. I'll take a closer look. The documents of my app can get quite large, up to 300 to 400 MB (lots of small images). I was thinking to use SQLite as an alternative to the ZIP based format that I'm using at the moment, hoping to avoid the working copy route that I use today, but looks like that would be tricky.
GormanFletcher 5y ago
Using SQLite databases as an application file format is encouraged by the SQLite team: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html

by ricg 5y ago

snuxoll 5y ago

As long as it’s not actively being written to, yes. Even enabling WAL won’t save you from inconsistent backups without use of the backup API or atomic file system snapshots when writers are active.

ricg 5y ago
Well, I'm thinking of an app that lets the user edit some kind of document. At some point the changes have to be saved. Could this happen to the "live" file or would the workaround be to keep a temporary copy somewhere away from the user and copy that back and forth?
- snuxoll 5y ago
  Yes, using the backup API you could load on on-disk database into memory and then do the reverse to a temp file and atomically move it to replace the saved copy. There’s even an example of using the backup API for this on the SQLite website https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html This wouldn’t be a great solution for large databases, but if you’re using it as a document format up to the couple dozen MB range it’s the perfectly acceptable. Alternatively you could just go the working copy route - make a copy of the database on open and move it back in place when closed. This would also let you recover unsaved changes if you crash or are terminated before the user hits save, as well as the obvious benefit of not needing to keep the entire database loaded in memory and needing to write the entire thing out on save.
  - ricg 5y ago
    Thanks for the details. I'll take a closer look. The documents of my app can get quite large, up to 300 to 400 MB (lots of small images). I was thinking to use SQLite as an alternative to the ZIP based format that I'm using at the moment, hoping to avoid the working copy route that I use today, but looks like that would be tricky.

GormanFletcher 5y ago

Using SQLite databases as an application file format is encouraged by the SQLite team: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html