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

261–270 of 364 posts

Re: SQLite is not a toy database

#261

Post by danielheath »

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…

I would use SQLite on a single-machine web server. However, I prefer to build my web stuff as stateless machine images that connect to a separate database instance, because it lets me scale them independently.

I should revisit this policy now that you can run a truly huge site off a 1U slot (I work for an alexa top10k, and our compute would fit comfortably in 1U); computers are so fast that vertical scaling is probably a viable option.

Re: SQLite is not a toy database

#262

Post by dnautics »

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.

It's not the worst thing in the world; you're validating on data ingest anyways to prevent sqli, for example, right?

Re: SQLite is not a toy database

#263

Post by hodgesrm »

pupdogg wrote: post #89
juancampa wrote: post #44

Earlier quoted context omitted.

How do you ensure data is not lost to oblivion if a catastrophic system failure occurs?

You put in-place a loss mitigation strategy. This strategy will vary by application. In my case, I have a similar setup where we write 25-30k records to SQLite daily. We start each day fresh with a new SQLite db file (named yyyy-mm-dd.db) and back it up to AWS S3 daily under the scheme /app_name/data/year/month/file. You could say that's 9 million records a year or 365 mini-sqlite dbs containing 25-30k records. Porta…

This sounds interesting. Have you thought of doing a talk or blog article about it?

p.s., I run the SF Bay Area ClickHouse meetup. Sounds like an interesting topic for a future meeting. https://www.meetup.com/San-Francisco-Bay-Area-ClickHouse-Mee...

Re: SQLite is not a toy database

#264

Post by names_are_hard »

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…

This. I'm working on a hobby project that is essentially a web app for personal use. I started with sqlite because it was the simplest to start with, but I'm about to reluctantly migrate to postgres. Why? Constraints, data types, and programmability.

In terms of scale, sqlite is just fine. But I am tired of fiddling with the dates, it's too easy for bugs to sneak into my code, and I want to use table valued functions to essentially parameterize views instead of having to build complex queries in the app layer.

If your web app is mostly reading and writing single rows, yeah, sqlite is just fine. But if there's substantial and complex logic involved, it has its limits.

Re: SQLite is not a toy database

#265

Post by pdimitar »

danielheath wrote: post #261
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…

I would use SQLite on a single-machine web server. However, I prefer to build my web stuff as stateless machine images that connect to a separate database instance, because it lets me scale them independently. I should revisit this policy now that you can run a truly huge site off a 1U slot (I work for an alexa top10k, and our compute would fit comfortably in 1U); computers are so fast that vertical scaling is probab…

That's exactly what I am leaning to in my current job. Looking at the load of our servers I can comfortably put all our software on my gaming machine (which is mid-range!) -- with the DB included -- and I bet no request ever will be above 100ms.

IMO a lot of organizations should start re-investing in on-premise as well. Having a mid-range AMD EPYC server can serve most businesses out there without ever having more than 40% CPU usage.

That, plus scaling down Kubernetes clusters. Most companies absolutely didn't need them in the first place.

Re: SQLite is not a toy database

#266

Post by zokier »

II2II wrote: post #260
zokier wrote: post #207

Earlier quoted context omitted.

> Another issue with Base is the required JRE I thought that they dropped JRE requirement when the engine was switched to Firebird? Googling around apparently the transition wasn't quite successful :( https://ask.libreoffice.org/en/question/279711/firebird-dead...

The only "embedded database" option (HSQLDB) requires the JRE. I don't know if this holds true for the "connect to an existing database" options, but I would rather throw together a Python script using SQLite when it reaches that level of complication. (It uses my existing knowledge.)

> The only "embedded database" option (HSQLDB) requires the JRE

Firebase is still available as an experimental option as another embedded database, just confirmed on my installation. Also while I was at it, tested it with Java disabled and HSQLDB predictably did not work, but Firebird did continue to work. Apparently it also opens an pre-existing Firebase odb file even without experimental flag being on.

https://ibb.co/jM6k07w

Re: SQLite is not a toy database

#267

Post by Sohcahtoa82 »

dnautics wrote: post #262
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. It's not the worst thing in the world; you're validating on data ingest anyways to prevent sqli, for example, right?

Data validation is a dangerous method to try to prevent SQL injection. The only surefire method is to used parameterized queries, which you should be doing anyways.

Re: SQLite is not a toy database

#268

Post by justin_oaks »

JimmaDaRustla wrote: post #195

Weird. They never mentioned the amazing power of inserting text and numbers into a boolean column.

I assume you're being sarcastic because I haven't yet thought of a reason why that'd be a good thing. Although I've never been bitten by SQLite's dynamic types, I wish they weren't a thing.

I'm genuinely curious if there are any real use-cases for this behavior.

Re: SQLite is not a toy database

#269

Post by pdimitar »

benbjohnson wrote: post #227
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…

I'm in the process of adding read replication to Litestream[1] so folks can scale out the read-side of their SQLite applications to multiple nodes (or replicate to edge nodes for low-latency). [1]: https://litestream.io/

Amazing. This was missing for my use-case and I gave up on LiteStream because of it but now it seems I have to revisit!

Wonderful job.

Re: SQLite is not a toy database

#270

Post by dnautics »

Sohcahtoa82 wrote: post #267
dnautics wrote: post #262

Earlier quoted context omitted.

> SQLite doesn't really enforce column types[0], the choice is really puzzling to me. It's not the worst thing in the world; you're validating on data ingest anyways to prevent sqli, for example, right?

Data validation is a dangerous method to try to prevent SQL injection. The only surefire method is to used parameterized queries, which you should be doing anyways.

huh? By data validation I mean a validation library in your surrounding PL, it's flowing through the types of that language, and at no point is unprepared SQL entering your system.
Post reply on HN