Hacker News Reader

Comment by workhere-io | Hacker News Reader

If you're making a web app, IMO you shouldn't use SQLite at all - not even for testing. In a live scenario SQLite has problems with multiple users. And in a test scenario SQLite is too forgiving when it comes to types - which means that once you switch to your live database, e.g. PostgreSQL, errors will occur that you hadn't foreseen because PostgreSQL isn't as forgiving as SQLite type-wise.

In short, use PostgreSQL both for testing and live. I recommend Postgres.app for Mac users: http://postgresapp.com/

View on HN

SQLite does not discourage using it for websites https://www.sqlite.org/whentouse.html . I use it for some low traffic web sites and I don't see any reason why I shouldn't. Benchmarking shows that my website can handle up to 400 req/s - that's not magic but it is far from my needs. As well it is convenient when memory usage of your web app is no more than 100Mb.

You should absolutely understand what you are doing but SQLite is good choice in many cases. Not everyone works on next Facebook.

I understand your point, but the problem with low-traffic websites is that they often suddenly - without warning - become high-traffic websites for a day or two when some larger website links to them. We've all seen it here on HN: Some article gets linked to, the server goes down, and people are forced to see the article on the Google cache.

So my point is this: Why not instead go for a database that was actually made to handle these kinds of situations? True, using PostgreSQL or MySQL won't in itself ensure that you'll survive a slashdotting/HN'ing, but your chances are better. (And obviously, your chances are even better if you use some sort of caching, but that's a different story).

Never used sqlite for websites, but wouldn't this problem be better handled by caching? I.e. completely bypass the database, especially for users who will never log in.

E.g. Varnish.

Sure, but you're paying a price in terms of complexity: Many simple sites with PostgreSQL/MySQL can handle being slashdotted without having a cache (unless you're using a heavy CMS such as WordPress/Drupal). If you use SQLite, you might be forced to use Varnish - and there goes the initial idea of simplicity that caused you to use SQLite in the first place.

Having said that, once your site goes truly high-traffic, you'll obviously need some caching. All I'm saying is that PostgreSQL/MySQL can easily handle moderately high traffic if your website is not a heavy CMS.

Some facts. I'm not sure how is that helpful in real life or how important but I'm adding them just for information:

* SQLite itself has configurable cache and some other caching options:

a) http://www.sqlite.org/pragma.html#pragma_cache_size

b) https://www.sqlite.org/sharedcache.html

* Node.js has SQLite library with caching option (matter of single line). https://github.com/mapbox/node-sqlite3/wiki/Caching . It might be possible that other languages has something similar as well.

P.S. Here is one of sites running on SQLite http://skaityta.lt/ with some caching.

I have no problems with multiple users and SQLite. A website with 1000 active users should run fine with just SQLite. Maybe you should start reading https://www.sqlite.org/lockingv3.html

I've addressed this here: https://news.ycombinator.com/item?id=7434651

I was testing an app I made in Django using sqlite. Virtualenv was complaining about an error. I don't remember what it was but it was completely irrelevant to the DB. Something like couldn't access the virtualenv's python bin or something. After spending days on it, I was like screw it, I'll come back to that error later and started moving the app towards production state. When I switched to postgresql the error resolved itself.

Yes its type bugs have hit me, as python is also dynamic. And I recently reported type bugs for sqlalchemy's Oracle mapping, so you can run I to problems even on the big DBs as there's a lot of software between you and the btree.

However, sqlalchemy is a godsend for testing. Tests that need a complicated server and are hard to run in a heterogenous dev team don't get run. It's much better to pick sqlite than to mock.

Reply on news.ycombinator.com