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

181–190 of 364 posts

Re: SQLite is not a toy database

#181

Post by jakoblorz »

gwd wrote: post #75
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.

I just looked, and the entire database for my webapp (which typically serves around ~50 people) is 139k. As you say, you can do a data dump quickly and safely; at that size, you could afford to just dump the entire DB every hour and keep the last year's worth of snapshots if you wanted.

https://litestream.io/ That's exactly what litestream accomplishes.

Re: SQLite is not a toy database

#183

Post by faizshah »

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…

There's also a pretty nice built-in sqlite extension for transitive closures that helps in searching hierarchical/tree structures in sqlite: https://web.archive.org/web/20141127001741/https://charlesle...

Re: SQLite is not a toy database

#184

Post by bogwog »

fabiospampinato wrote: post #130
haolez wrote: post #108

Earlier quoted context omitted.

Wow! The sql.js bundle is only 8kb. This is a hidden gem for sure. Thanks for pointing it out! EDIT: It's actually 1.2MB. Thanks for pointing it out :)

Nope, try using the demo website, it loads a 1.2mb wasm file ( https://sql.js.org/dist/sql-wasm.wasm ). SQLite might be impressively small but it's not _that_ small.

I was actually looking into options for storing some data for a game I'm working on a while back, and decided against using SQLite because it was too big of a dependency (project has a specific size limit/target).

Re: SQLite is not a toy database

#185

Post by jakoblorz »

crazygringo wrote: post #146
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.

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…

Depending on the use case (SaaS offerings with per customer shards) you can actually scale sqlite quite high. Expensify scaled to 4 mio qps (queries per second) [1], so if one of your customers is exceeding that, you better be looking at another DBMS but below that - well sqlite is an option.

[1] https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

Re: SQLite is not a toy database

#186

Post by benbjohnson »

jakoblorz wrote: post #181
gwd wrote: post #75

Earlier quoted context omitted.

I just looked, and the entire database for my webapp (which typically serves around ~50 people) is 139k. As you say, you can do a data dump quickly and safely; at that size, you could afford to just dump the entire DB every hour and keep the last year's worth of snapshots if you wanted.

https://litestream.io/ That's exactly what litestream accomplishes.

That's close. Litestream takes a snapshot of the database and then continuously replicates out WAL frames. On restore, it grabs the snapshot and replays all those WAL frames (or all the frames up to a certain time if you want point-in-time recovery).

Re: SQLite is not a toy database

#187

Post by jakoblorz »

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 can actually be scaled quite high: 4mio qps (queries per second) https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

Re: SQLite is not a toy database

#188

Post by dpix »

fouc wrote: post #84

PostgreSQL is not a toy database. MySQL is not a toy database. I feel like the "toy database" part is clickbait

I don't think the author is trying to call out SQLite as somehow better than others database tech - just that most people perceive SQLite to be a tool for development or exploration when in fact it much more powerful than most believe

Re: SQLite is not a toy database

#190

Post by doomleika »

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…

Yeah, still I would really wish SQLite is a feather weight RDBM system than almost-but-not-quite-your-typical-RDBMS the better `fopen`.

Having a tool like this would made my life whole lot easier, well, one can dream.

Post reply on HN