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

91–100 of 364 posts

Re: SQLite is not a toy database

#91

Post by gwd »

duskwuff wrote: post #73
eingaeKaiy8ujie wrote: post #66

Earlier quoted context omitted.

>That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where that number is actually "1". I can imagine a static website where the content is read-only for users and is only editable by admins/developers/content managers through some CMS.

That'd be a near-infinite ratio. The parent is discussing a site where the ratio is near 1 (i.e. roughly as many reads as writes).

It's not even the percentage of reads vs writes; it's about the total time spent writing. Suppose that while a single user was actively using the website, the time spent writing was 70%. That's a totally mad write load; but even then, if you had two concurrent users, things would probably still be quite useable -- you'd have to go up to 4 or 5 users before things really started to slow down noticeably.

Suppose, on the other hand, that a single user generated around a 1% write utilization when they were actively using the website (which still seems pretty high to me). You could probably go up to 120 concurrent users quite easily. And given that not all of your users are going to be online at exactly the same time, you could probably handle 500 or 1000 total users.

Re: SQLite is not a toy database

#92

Post by tomcam »

Derpdiherp wrote: post #76

---Was talking about a previous company who made steps to shift from it for pricing reasons. Seems they where misinformed.

How is it not free for commercial use? It’s public domain code. I just rechecked the license after reading your comment.

Re: SQLite is not a toy database

#93

Post by Derpdiherp »

eurleif wrote: post #86
Derpdiherp wrote: post #76

---Was talking about a previous company who made steps to shift from it for pricing reasons. Seems they where misinformed.

Huh? SQLite is public domain.[0] There are paid extensions for encrypted and compressed databases[1], but those cost a flat fee for unlimited devices:[2] "Your license is perpetual. You have paid a one-time fee that allows you to use and modify the software forever. You can ship as many copied of the software to your customers as you want so long as you ensure that only compiled binaries are shipped (you cannot distr…

Interesting - it's been a long time since I used / looked at it. There was a big uproar in the company though and a drive to move away from it due to licensing and cost from there. Maybe they where misinformed.

Re: SQLite is not a toy database

#94

Post by benbjohnson »

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 ?

I'm the author of Litestream, which is an open-source tool for streaming replication for SQLite. That could be a good option if you need to limit your window for data loss. We have a pretty active Slack if you need help getting up and running. https://litestream.io/

Re: SQLite is not a toy database

#95

Post by juancampa »

cute_boi wrote: post #34

Nobody seems to mention that SQlite is one of the secure database. Just look at its rigorous testing, fuzzing and everything.

nit: security is like the speed of light for a massive object, you can never reach it, you can only get closer and closer. I'd say instead "SQLite is one of the _most_ secure databases"

Re: SQLite is not a toy database

#96

Post by wenc »

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 your db because everyone implements their own datetime representation.

Re: SQLite is not a toy database

#98

Post by hsnewman »

Thaxll wrote: post #20
andrewmcwatters wrote: post #13

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html

SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html

I disagree. I wrote a multiplayer web based game using sqlite in go: http://decwars.com/

Re: SQLite is not a toy database

#99

Post by SQLite »

indigo945 wrote: post #47
myth2018 wrote: post #6

I'd also like to add the possibility of using SQLite databases as an application file format: https://news.ycombinator.com/item?id=23508923 I had to work on a data import/export tool some time ago and SQLite has simplified the design a lot.

While this makes things easy, you should not do this for any application file format where you except your users to share files. This is because opening a SQLite database file makes the SQLite library execute any arbitrary code that may be stored in that file. [0] Therefore, SQLite is really only suitable for local-only file formats, such as configuration files, and not for files that users will e-mail to each other.…

This is misinformation. SQLite does not execute arbitrary code found in the data file. There was a bug, long since fixed, that could be used by an attacker to cause arbitrary code execution upon opening the database file. The referenced video talks about it. It was a very clever attack. But the bug that enabled the attack was fixed even before the talk shown in the video was given.

Let me say that again: SQLite does NOT execute arbitrary code that it finds in the database file. To suggestion that it does is nonsense.

See https://www.sqlite.org/security.html for additional discussion of security precautions you can take when using SQLite with potentially hostile files. The latest SQLite's should be safe right out of the box, without having to do anything mentioned on that page. But defense in depth never hurts.

Re: SQLite is not a toy database

#100

Post by nalgeon »

blacksmith_tb wrote: post #81
tyingq wrote: post #70

Earlier quoted context omitted.

Someone did write a plugin to have wordpress use SQLite as the backend: https://wordpress.org/plugins/sqlite-integration/ Perhaps not great for production since Wordpress automatically updates itself, and you would have to keep up with any changes. And not just for wordpress, but for any other plugins that use the database. Edit: A single file fork (albeit 5k lines of PHP) of the plugin that looks interesting: https:…

I maintain a corp Ghost blog that's backed by SQLite, it's been solid for years.

Same thing here.
Post reply on HN