7 ms·
The main problem that I see for using sqlite is exactly for it being 'Classic Serverless'. Because how does one keep an up te date backup? Deploying to Heroku,
by Gys 7y ago
The main problem that I see for using sqlite is exactly for it being 'Classic Serverless'. Because how does one keep an up te date backup? Deploying to Heroku, Dokku, AWS Lambda and such means the sqlite file will be lost on a crash or new deploy. Even a VM can crash. Export to S3 on every write? Maybe if changes do not happen often, so only for specific use cases (and actually I think you should just generate static html in that case).
I use sqlite for local tests for cases where the live app has a 'neo-serverless' database. It is very, very fast so the tests run almost instantly.
- coleifer 7y ago>how do you back up a file
- wongarsu 7y agoIt's a valid concern in so far as that any standalone database setup you would use at a serverless provider has backups already taken care of (for example AWS Lambda with AWS RDS has backups built into RDS). If I deploy SQLite instead I have to take care of backups myself, and that might not be trivial to get right.
- hashhar 7y agoIt's just a single file. Easier to backup than any other database. Also I don't think any cloud provider/database provides an always up to date backup other than a standby replica (which isn't also a backup exactly).
- wongarsu 7y agoIf I remember the "ways to corrupt SQLite databases" page discussed a few days ago, backing up SQLite isn't entirely trivial. By default there are up to three files that have to be copied simultaneously, else you risk corruption. The optimum is to run the backup command to create a copy, but that requires realizing this exists.
- hashhar 7y agoIf you do find the link could you please share it here. I think I've been hit by it once before. Also, it's comparatively simpler than other DBMS's like Postgres or MySQL.
- wongarsu 7y agoLink: https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html Discussion: https://news.ycombinator.com/item?id=22098832 https://news.ycombinator.com/item?id=22098832
- hashhar 7y agoThanks. Useful information. But I've been copying after taking a shared lock on the sqlite db and I think that's supported as mentioned on https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html. The online backup API is nice but it has to be done in-process or through a dedicated application. While the lock and file copy is very easy to do using whatever shell the OS provides.
- throw0101a 7y ago> Because how does one keep an up te date backup? https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html There's a ".backup" command: * https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_ https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_... Alternatively, given that it's ACID, you could just take a snapshot of the file system/volume in question, and do a recovery on restore. Edit: SQLite also has WAL files, so presumably one could just use tar/rsync to create the backup, and only the last file would be 'corrupted', so you'd lose the last (few) transaction(s): * https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- bsenftner 7y agoDing! correct answer. In addition, the backups can run in the background, simultaneous to active use in other threads. When the backup is initiated, it is a transaction, so it maintains knowledge of what portion of the database is new since the backup began and does not include that data. Which is what you want for backup integrity.