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

191–200 of 364 posts

Re: SQLite is not a toy database

#191

Post by napsterbr »

faizshah wrote: post #183
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...

Clicking the above link takes me to https://imgur.com/32R3qLv.

I fail to understand why have a blog at all if its author don't like people linking to it.

Re: SQLite is not a toy database

#192

Post by mastre_ »

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…

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

Re: SQLite is not a toy database

#193

Post by durkie »

incanus77 wrote: post #165

I had seen Dr. Hipp speak at a conference[1] around 2009 and it really put SQLite on my radar as a (then) Mac developer. It ended up surfacing from the back of my mind around then at Mapbox when I put together the prototype of the MBTiles file format[2] which is still in use today. The idea of an on-disk relational database was new to me and just a perfect fit for where mobile devices were at that point in time, and…

thanks for mbtiles! I spend lots of time using and abusing that format every day :)

Re: SQLite is not a toy database

#194

Post by pbowyer »

yellowapple wrote: post #168
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…

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

> That being said, if your website has crazy "web scale" FAANGesque needs and you're at the point where you need to write your own replicated datastore, using SQLite as a base and building your own replication layer on top of it (or using RQLite and maybe adjusting it for your needs) seems like a reasonable way to go.

Exactly what Bloomberg did with Comdb2: https://github.com/bloomberg/comdb2

Re: SQLite is not a toy database

#196

Post by isoprophlex »

mastre_ wrote: post #192
haberman wrote: post #178

Earlier quoted context omitted.

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

> 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 ;)

Re: SQLite is not a toy database

#197

Post by yrrr »

WJW wrote: post #119
dheera wrote: post #63

Earlier quoted context omitted.

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.

localStorage is a web API, the storage limit is ~5MB.

However it can only store strings so it's pretty taxing.

Chrome and Safari actually embed SQLite directly as WebSQL, however it's not becoming standardized because Mozilla doesnt consider "add SQLite" as a sensible web standard.

Re: SQLite is not a toy database

#198

Post by hinkley »

benbjohnson wrote: post #164
hinkley wrote: post #135

Earlier quoted context omitted.

One could, for instance, run “static” sites with the data stored in a SQLite database. Update on write apps often can do okay with 90% uptime. It’s the “generate everything in every request” crowd that needs an HA solution and why do we keep doing this to ourselves ?

Yes, static generation is a great way to go if that works for your use case. Throw it up on a CDN and you'll likely have five 9s of uptime. I don't think that's a viable solution for a lot of applications though.

Half of arguments on the Internet are someone trying to win an argument they lost elsewhere. If I could get some people I work with to actually run the numbers on how much it costs us to make everything dynamic, versus how much we earn versus static, I think we’d quickly arrive at an agreement that most of it should be static with a little AJAX thrown in for the 10% that needs to be special.

Re: SQLite is not a toy database

#199

Post by megous »

benbjohnson wrote: post #139
megous wrote: post #122

Earlier quoted context omitted.

I know about litestream, it's not the kind of "setup and forget" I have in mind. But it's nice that it exists.

That's good to know. I'm always trying to improve the developer ergonomics. What would you want to see added to make it "setup and forget"?

I don't think there's much to be done. I just like the fact that I don't have to think about replicating individual databases, regardless if I rename, delete, re-create them.

It's being done at cluster level, it's one time setup and I can forget about it.

Re: SQLite is not a toy database

#200

Post by pbowyer »

kroggen wrote: post #172
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…

For replication of SQLite there are some options: http://litereplica.io - single-master replication http://litesync.io - multi-master replication https://aergolite.aergo.io - highest security replication, using a small footprint blockchain

Have you used litereplica or litesync? It doesn't inspire confidence that their websites haven't been updated since 2016 and 2017 respectively.
Post reply on HN