SQLite is not a toy database • Hacker News Board

Re: SQLite is not a toy database

#271

Post by pdimitar » Thu, Mar 25, 2021, 10:29 PM UTC

0xCMP wrote: post #253

crazygringo wrote: post #146

Earlier quoted context omitted.

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 wro…

The primary reason for me is that Postgres has stronger default constraints. If you care about keeping your data logically consistent then Postgres has more of that out of the box. SQLite just makes the tradeoff to be simpler since often it doesn't matter. But don't make the mistake that it doesn't matter. Since PG helps avoid data problems and you might need to scale out web servers that is why Django for instance r…

Not sure why you were downvoted because that is a legitimate concern and it's my only beef with sqlite3. I will kill for an embedded PostgreSQL. If sqlite3 becomes that I'll absolutely pay for a license if they require it.

Re: SQLite is not a toy database

#272

Post by pdimitar » Thu, Mar 25, 2021, 10:45 PM UTC

jyrkesh wrote: post #217

ben509 wrote: post #16

> 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 learned this the hard way when I stuck some app containers down on a few RPis and mapped the folder of stateful stuff (including a SQLite database) to an NFS share on my NAS. It's....not great.

Can't remember where did I read this now but about a year ago I have found material saying that sqlite3 over NFS is a very bad idea and even though it's supported it's also strongly advised against as a use-case.

Re: SQLite is not a toy database

#273

Post by mceachen » Thu, Mar 25, 2021, 10:51 PM UTC

simonw wrote: post #145

TheRealPomax wrote: post #117

Earlier quoted context omitted.

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.

Oh man, I didn't know about VACCUM INTO, thanks!

Re: SQLite is not a toy database

#274

Post by samatman » Thu, Mar 25, 2021, 10:55 PM UTC

wenc wrote: post #96

ben509 wrote: post #16

> 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…

It also doesn't have strong/static typing (it's dynamically typed) so you have to typecheck your inputs or do type coercion upon read. And it doesn't have a native date type. Date handling has to be handled at the application layer. It can be tricky to do massive time-series calculations or date-based aggregations. You can use integers or text types to represent dates, but this open-endedness means you can't share yo…

Datetimes really are the achilles heel of SQLite.

The JSON extension library is amazing and works well. If SQLite were to grow a first-rate RFC 3339 library, one which could read from tz when available and do the things which strftime cant, acting on your choice of Unix timestamp and valid RFC 3339 date string, this would be a real boon to the ecosystem.

I haven't found typechecking inputs to be a real barrier. Sure, `val INTEGER CHECK (val = 0 or val = 1)` is a long way to spell `val BOOLEAN` but `CHECK json(metadata)` is a reasonable way to spell `metadata JSON`, and a similar function would surely exist for a SQLite datetime extension. You can do it now by coercing the string through an expected format, but that doesn't generalize well.

Re: SQLite is not a toy database

#275

Post by lamelydiminish » Thu, Mar 25, 2021, 10:57 PM UTC

simonw wrote: post #248

lamelydiminish wrote: post #237

I love SQLite. Am building a webapp + mobile app that needs to sync user created content. Is there an easy way to sync SQLite DBs?

SQLite has a "session" extension which is designed to help handle the sync problem: https://sqlite.org/sessionintro.html

TIL. Can't find anything on sessions for sql.js though... Interested in using sql.js since I'm building a web PWA first, and eventually native desktop&mobile app. Perhaps this is overkill for SQLite at this point?

Re: SQLite is not a toy database

#276

Post by mikesabbagh » Thu, Mar 25, 2021, 10:57 PM UTC

nalgeon wrote: post #51

ben509 wrote: post #16

> 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.

I ran it once on an NFS disk. There must be some file locking happening I think. But I could read from multiple nodes, but for some reason the delete was failing (could not delete a database). Did not test the write as my app at that time was writing from one location only. Anyone tested such HA scenario?

Re: SQLite is not a toy database

#277

Post by dmurray » Thu, Mar 25, 2021, 11:03 PM UTC

msla wrote: post #234

dmurray wrote: post #180

Earlier quoted context omitted.

I thought the same, but maybe it's useful if you start with SQLite and decide to scale up to a distributed RDBMS without having to rewrite too much?

Two thoughts: SQLAlchemy, at least, lets you switch from SQLite to Postgres in a configuration file. Isn't there a useful SQL subset which allows you to switch from one database to another without rewriting? There seems to be such a subset for C, for example, which multiple compilers all interpret the same way, and SQL is a standardized language, too.

Yes, both of those are alternative solutions.

Using a wrapper like SQLAlchemy is probably an improvement for most medium and large projects, but it has costs of its own. Using portable ANSI SQL - above the Hello World level - is something basically nobody does unless they make a serious effort, lean on linting tools and forego some of the most useful features of their DBMS. sh is perhaps a closer comparison than C here: it's at least possible to write portable C accidentally.

And neither of these help you if you started with a SQLite database - an excellent choice for most - and decide that you need to support a dozen or a hundred concurrent users.

Re: SQLite is not a toy database

#278

Post by pdimitar » Thu, Mar 25, 2021, 11:09 PM UTC

haberman wrote: post #178

doomleika wrote: post #148

Two 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 solutio…

> SQLite doesn't really enforce column types[0], the choice is really puzzling to me. This is acknowledged as a likely mistake, but one that will never be fixed due to backward compatibility: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more…

Something like "PRAGMA STRICT = ON;" should be trivial to add after the strict mode is actually implemented.

But IMO that would just warrant an entirely separate software package. Shoving it inside sqlite3 is likely to make it much more complex.

Re: SQLite is not a toy database

#279

Post by valzam » Thu, Mar 25, 2021, 11:10 PM UTC

If I recall correctly https://www.foundationdb.org/ is also built on "distributed sqlite" at it's core.

Re: SQLite is not a toy database

#280

Post by samatman » Thu, Mar 25, 2021, 11:10 PM UTC

doomleika wrote: post #148

Two 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 solutio…

It's more accurate to phrase point 1. as SQLite column constraints are opt-in. If you explicitly add a check constraint on a column schema, SQLite will dutifully perform it for you. It's a little extra work, and as siblings have pointed out, a greenfield SQLite would probably not have done things this way. But it's also easy, I have check constraints on many columns and they serve the purpose.

Think of SQLie as having a weird dialect where `Col1 INTEGER` is spelled `Col1 INTEGER CHECK (typeof(Col1) IN ('integer', 'null'))`. Ideal? No, but also, not a showstopper.

There are a few good reasons not to use SQLite. Your point 2 is one of them, running the client on one machine and accessing the database file via a network file system is another. Although I've pushed write-heavy workloads pretty hard with some care, it's easy to create a situation where contention becomes untenable. You can really pound on it with one client, but with several it gets dicey.