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

311–320 of 364 posts

Re: SQLite is not a toy database

#311

Post by lmm »

nijave wrote: post #230
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 the biggest issue can be distilled to "lack of concurrent network access". You start getting into a lot hairy management problems with distributed file systems/copying files around. Imo part of that is just web applications tend to be not very efficient when compared to something like unix cli utilities so to get performance you end up with potentially massive amounts of horizontal scaling On the other hand,…

> Imo part of that is just web applications tend to be not very efficient when compared to something like unix cli utilities so to get performance you end up with potentially massive amounts of horizontal scaling

It's less about performance than about resilience IMO. Of course if you don't have a proper HA datastore (master-master) then you kind of undermine that, but a datastore SPOF is better than the whole application being a SPOF.

Re: SQLite is not a toy database

#313

Post by warmwaffles »

isoprophlex wrote: post #196
mastre_ wrote: post #192

Earlier quoted context omitted.

> but one that will never be fixed due to backward compatibility I wonder if a fork/"new version" could address this. Like, sqlite2 (v1.0, etc.).

Considering that we're on sqlite3 already, it'd probably be something for the v4 ;)

There is already an sqlite4 but they haven't done any work with it for a long time.

Re: SQLite is not a toy database

#314

Post by warmwaffles »

bob1029 wrote: post #177
Thaxll wrote: post #173

Earlier quoted context omitted.

"The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you." It's not the right approach because it's hard to get right, you want to offload that to the DB.

It's actually not that hard to get right. You can put a simple lock statement around a SQLiteConnection instance to achieve the same effect with 100% reliability, but with dramatically lower latency than what hosted SQL offerings can provide. Also, the only reason we ever want to lock a SQLiteConnection is to obtain a consistent LastInsertRowId. With the latest changes to SQLite, we don't even have to do this anymore…

You won't have to worry about the last insert row id anymore. `RETURNING` was added recently. Enjoy the speeeed.

Re: SQLite is not a toy database

#315

Post by breck »

What really blew me away years ago: SQLite is public domain! (https://www.sqlite.org/copyright.html)

I recommend getting a book on it. I have the O’Reilly one. (https://sqlite.org/books.html). I’ve used it so much in so many different scenarios. Such a great tool to have on the toolbelt.

Re: SQLite is not a toy database

#317

Post by snicker7 »

the_duke wrote: post #208
smulc wrote: post #144

Earlier quoted context omitted.

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

rqlite is a separate daemon written in Go, negating most of the reasons to choose Sqlite in the first place. It absolutely has good use cases, but those are rather niche. You'll mostly be better off with the traditional postgres etc.

For web applications, having a separate daemon for sqlite is best practice anyway. SQLite doesn't play nice with multiple concurrent connections (costly locking, the possibility of data corruption). You typically need to offload transactions to a queue in a daemon thread.

Re: SQLite is not a toy database

#318

Post by mikedilger »

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…

Maybe there could be a fork called SQLITE, an acronym for SQLite Including Type Enforcement

Re: SQLite is not a toy database

#320

Post by anonymousiam »

gm3dmo wrote: post #236
yellowapple wrote: post #168

Earlier quoted context omitted.

> then what's the reason people aren't using it for websites? I'd guess the reason to be that people keep hearing things like "don't use SQLite for websites" and thus don't even try. > Why do you say it works for "small" websites but presumably not large ones? Not the GP, but the main reason I wouldn't use SQLite for a large website is that SQLite itself doesn't offer much re: failover/replication (i.e. multiple serv…

Crazy web scale folks flip from Mongo (NoSQL) to Postgres: https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-h...

Great article. It took them 10 months to successfully migrate! No mention of how many man-hours, but probably a lot.
Post reply on HN