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

51–60 of 364 posts

Re: SQLite is not a toy database

#51

Post by nalgeon »

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

Re: SQLite is not a toy database

#52

Post by _0w8t »

unnouinceput wrote: post #29
petercooper wrote: post #22

Earlier quoted context omitted.

I ran a niche community social bookmarking site (around 100-200k pageviews per month) on SQLite for several years and it was no problem at all. If a write was occurring, having a simultaneous request wait 100 milliseconds was no big deal. It only became a problem when I got tired of ops and wanted to put it on Heroku at which time I had to migrate to Postgres. I've always been surprised WordPress didn't go with SQLit…

At the time when WordPress rolled out SQLite was still in its infancy while MySQL was already a mature DB, hence the choice. These days however they could, at least, do a nice wizard to ask "do you want to run a blog only?" and deploy SQLite instead.

I looked at using SQLite with Wordpress for sites I maintain. In theory Wordpress core is quite DB-agnostic and according to random blogs MySQL could work. But plugins use so much MySQL-specific features that it was hopeless.

Re: SQLite is not a toy database

#53

Post by michaelmcmillan »

juancampa wrote: post #44
michaelmcmillan wrote: post #19

I've built a complex CRM that handles 2.1 million USD in transactions every year. It is running sqlite with a simple in-memory lru cache (just a dict) that gets purged when a mutating query (INSERT, UPDATE or DELETE) is executed. It is very simple and more than fast enough. Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s before making money.

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

Backups.

Re: SQLite is not a toy database

#54

Post by jacurtis »

unnouinceput wrote: post #29
petercooper wrote: post #22

Earlier quoted context omitted.

I ran a niche community social bookmarking site (around 100-200k pageviews per month) on SQLite for several years and it was no problem at all. If a write was occurring, having a simultaneous request wait 100 milliseconds was no big deal. It only became a problem when I got tired of ops and wanted to put it on Heroku at which time I had to migrate to Postgres. I've always been surprised WordPress didn't go with SQLit…

At the time when WordPress rolled out SQLite was still in its infancy while MySQL was already a mature DB, hence the choice. These days however they could, at least, do a nice wizard to ask "do you want to run a blog only?" and deploy SQLite instead.

I don't have any data on this, but I do know that a significant (if not majority) of Wordpress sites now aren't even blogs. They are small websites for things like restaurants, hair salons, construction companies, photographers, and personal portfolios. Especially since most of these sites are also running on $3/mo shared hosts, switching to SQLite would be a substantial improvement.

Most these sites are running a homepage, an about page, a contact page, and maybe one or two misc pages. They might have a blog that has two blog posts on it from nine years ago. But that is really it. MySQL is really overkill considering the scenario. SQLite's big "limitation" is non-concurrent writes. But this is rarely a problem with most Wordpress sites because they are single-author and they aren't updated very often. SQLite can handle plenty of reads to support even heavily trafficked websites.

Not to mention, SQLite's greatest advantage is portability. A single file contains your entire database. A non-technical user could transfer hosts or backup their data by copying their database file like it was a photo or an excel document. That's pretty incredible when you think about it.

Re: SQLite is not a toy database

#55

Post by stjohnswarts »

hackeraccount wrote: post #35

Is it the unix version of MS Access? It's sort of interesting to compare the two.

Well the database part maybe, but it's not going to help you generate forms and reports or build small guis around it. You'll have to find those tools for yourself or build a web app around your sqlite db. I haven't done a lot with Access though, mostly help build small database interfaces/reports for their inventory systems (whether a lab or comic book collection).

Re: SQLite is not a toy database

#56

Post by voxic11 »

nalgeon wrote: post #51
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 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.

Also if you really need HA there are nice projects like https://dqlite.io/

Re: SQLite is not a toy database

#57

Post by dwohnitmok »

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

As far as I'm aware that threading information is on a per-connection basis.

SQLite is perfectly capable of supporting multiple, parallel reads.

SQLite must serialize writes, which makes a highly parallel write-heavy workload not good for it. However, with WAL enabled writes do not block reads.

Basically highly-parallel read loads with low write counts (low-enough that serializing them doesn't lead to unacceptable slow down of writes) or with loads where latency is acceptable in writes (but not reads) is a perfect use case for SQLite. And it turns out that a lot of web services are heavily asymmetrically biased towards reads.

Re: SQLite is not a toy database

#58

Post by OldHand2018 »

Thaxll wrote: post #28
busterarm wrote: post #25

Earlier quoted context omitted.

Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)

That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.

Well, let me throw out a crazy idea:

Nobody said you have to use a single database/file. Obviously, you are going to want to spend a couple minutes thinking about referential integrity. But how often do you delete records in your web app?

Re: SQLite is not a toy database

#59

Post by myth2018 »

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

Thanks for the heads-up!

In my use case, files are shared between different instances of the application, usually without user intervention, but there's an attack vector to be addressed here.

Re: SQLite is not a toy database

#60

Post by _0w8t »

II2II wrote: post #42
snicker7 wrote: post #2

> SQLite is serverless. Maybe if you use SQLite as a file format. But if you use it like an actual database (e.g. in a web application), I find that one is best off setting up a daemon thread to queue/batch transactions.

One of the disappointing developments of the past quarter century is the near demise of general purpose databases as an end user application. Yes, client/server models are useful when dealing with a large number of transactions. On the other hand, it is usually too complex to justify for personal or small office use. I miss the days when databases were included in office suites or could be purchased as relatively ine…

Access DB is still included in Microsoft Office
Post reply on HN