Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite is not a toy database

antonz.org

Post reply on HN
Print view View original

111–120 of 364 posts

Re: SQLite is not a toy database

#111

Post by petercooper »

api wrote: post #105
petercooper wrote: post #30

With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further: https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten. https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replica…

SQLite is one of those things that also shows by contrast how bloated and inefficient much of modern software is. It accomplishes so much so efficiently with such a small footprint.

Agreed. There's a real beauty to it, to me, like looking at a Swiss watch or something. Redis gives me similar vibes though is a little more complex given its networked nature (although the C is nice to read).

Re: SQLite is not a toy database

#112

Post by megous »

I'd still rather use PostgreSQL for my data needs. psql is quite nice too as an interface, and "setup and forget" replication for everything in the cluster and remote access makes up for slightly harder deployment and upgrades.

Knowing that I can do anything and the change hits 3 hard drives in a span of few seconds on various machnies is nice. Tolerance for arbitrary down time of master/slave clusters is also nice.

But yeah, for quick one-off data munging, sqlite looks quite nice.

Re: SQLite is not a toy database

#113

Post by habibur »

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…

Not to misunderstand. It fully supports concurrent reads. And also concurrent reading while some other process is writing the same database.

What it doesn't support is concurrent writes only.

Re: SQLite is not a toy database

#114

Post by HelloNurse »

OldHand2018 wrote: post #58
Thaxll wrote: post #28

Earlier quoted context omitted.

That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.

Well, let me throw out a crazy idea: Nobody said you have to use a single database/file. Obviously, you are going to want to spend a couple minutes thinking about referential integrity. But how often do you delete records in your web app?

A big database id not an issue. Contention requires a large number of writers, not a large amount of cold data.

Re: SQLite is not a toy database

#115

Post by sangnoir »

tmountain wrote: post #103
nalgeon wrote: post #51

Earlier quoted context omitted.

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.

How do backups work? Is there a locking mechanism to prevent file corruption if the file is copied during writing?

It supports multiple readers, and writes are atomic - no reader should ever get corrupt data just because there is a write happening in parallel. You probably should not use the OS to copy the live db file, rather, have an in-process thread that reads the db and writes to a backup location periodically.

Re: SQLite is not a toy database

#116

Post by habibur »

antoinealb wrote: post #64
michaelmcmillan wrote: post #53

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 ?

Guess this daily 2 seconds of downtime is worth it, when that reduces cost say from $2000/month to $20/month.

Re: SQLite is not a toy database

#117

Post by TheRealPomax »

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…

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 copy initiated "when your health check sees there's a problem")

Re: SQLite is not a toy database

#118

Post by benbjohnson »

megous wrote: post #112

I'd still rather use PostgreSQL for my data needs. psql is quite nice too as an interface, and "setup and forget" replication for everything in the cluster and remote access makes up for slightly harder deployment and upgrades. Knowing that I can do anything and the change hits 3 hard drives in a span of few seconds on various machnies is nice. Tolerance for arbitrary down time of master/slave clusters is also nice.…

I agree that PostgreSQL has some great tooling around it. For SQLite replication, you might want to checkout Litestream: https://litestream.io/

Re: SQLite is not a toy database

#119

Post by WJW »

dheera wrote: post #63
petercooper wrote: post #30

With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further: https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten. https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replica…

Which if any of these is efficient enough to run on mobile and can cerealize an entire database to localStorage?

SQLite underpins large parts of both iOS and Android, so... all of them? Whether you can serialize "an entire database" just depends on the size of the database and whether it will fit in the local storage. SQLite scales up to a few TB at least, so that is unlikely to be the bottleneck.

Re: SQLite is not a toy database

#120

Post by rhombocombus »

api wrote: post #105
petercooper wrote: post #30

With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further: https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten. https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replica…

SQLite is one of those things that also shows by contrast how bloated and inefficient much of modern software is. It accomplishes so much so efficiently with such a small footprint.

I love it. SQLite is light, easy, and useful in a variety of situations.
Post reply on HN