Earlier quoted context omitted.
Backups.
Does that mean it's okay for your application to loose transactions (which occured between the backup point and the failure point) or do you have other mitigations ?
[deleted]
SQLite is not a toy database
141–150 of 364 posts
Re: SQLite is not a toy database
#142For me, the main killer feature of sqlite is portability. (Like moving it around, not porting it to new systems).
Just write the code to create an empty database if the db file does not exist, move your code elsewhere, and the database will be created on the first run. No usernames, passwords, firewall rules, IP addresses, no nothing... just a single file, with all the data inside.
Miration? Copy the whole folder, code and the database. Clean install.. copy the folder, delete the database. Testing in production? Just backup the file, do whatever, then overwrite the file.
Re: SQLite is not a toy database
#143Earlier quoted context omitted.
Guess this daily 2 seconds of downtime is worth it, when that reduces cost say from $2000/month to $20/month.
Many banks still “shutdown” for hours every night to do backups.
I’m not anywhere near the banking industry but from HN alone I’ve been led to believe dailyish huge file transfers are also the norm in a variety of situations (aka SQLite’s backup strategy).
Re: SQLite is not a toy database
#144> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…
Check out rqlite (https://github.com/rqlite/rqlite) for that functionality - "rqlite is a lightweight, distributed relational database, which uses SQLite as its storage engine. Forming a cluster is very straightforward, it gracefully handles leader elections, and tolerates failures of machines, including the leader. rqlite is available for Linux, macOS, and Microsoft Windows."
Re: SQLite is not a toy database
#145> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…
Not sure I understand: sqlite is file based, so snapshots and concurrent backups are literally just file copies/backups. I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can. (Heck, even failover is just a file…
Unfortunately backing up with a simply file copy operation isn't guaranteed to work if you have write traffic at the same time.
Instead you need to use the .backup mechanism or the VACCUM INTO command, both of which safely create a backup copy of your database in another file - which you can then move anywhere you like.
Re: SQLite is not a toy database
#146> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…
I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.
So if you're saying everything does support a web database... then what's the reason people aren't using it for websites?
Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into?
Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wrong, or when it's right?
Re: SQLite is not a toy database
#147It seems as though there is a new article about this daily.
Re: SQLite is not a toy database
#148Two major gripe I had with SQlite
1. SQLite doesn't really enforce column types[0], the choice is really puzzling to me. Since schema enforced type check is one of the strong suit of SQL/RDMBS based data solution.
2. Whole database lock on write, this make it unsuitable to high write usages like logging and metric recording. WAL mode will help but it will only alleviate the issue, you will need row based lock solution eventually.
Just like the offical FAQ said, SQLite competes with fopen[1] instead of RDBMS systems.
--
Re: SQLite is not a toy database
#149Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.
Not "in lieu", but I have found SQLite to be a good companion to pandas. pandas is good when you want to lend some easily navigable structure to your data without leaving python, but becomes cumbersome (it feels cumbersome esp if you know SQL) when you want to do some serious processing. At such times, I just dump my data into a SQLite DB, and from then on my code has SQL queries to it, which I find are far more readable than corresponding pandas statements (also easier for the reviewer).
Another advantage you get is if you wanted to look at these intermediate data, you don't need to run your code in debug mode and view the dataframe at a breakpoint - you can use something like datasette[1] or a standard SQLite DB viewer.
So a function that does complex data processing has approximately this structure my code:
(1)
(2)
(3)
(4)
[1] https://github.com/simonw/datasette - datasette doesnt replace a standard DB IDE, but is a very good lightweight alternative to one if don't intend to perform updates/inserts directly on a table.
Re: SQLite is not a toy database
#150> There is nothing more convenient than SQLite for analyzing and transforming JSON. You can select data directly from a file as if it were a regular table. Personally I love jq[0] for this purpose. I haven't really used SQLite for working with JSON, but the examples given are very verbose. [0] https://stedolan.github.io/jq/
I wrote a plugin for Datasette that adds a jq() custom SQLite function, it's pretty fun: https://datasette.io/plugins/datasette-jq