3 ms·

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 tes

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/ http://postgresapp.com/

olavgg 13y ago
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 https://www.sqlite.org/lockingv3.html
- workhere-io 13y ago
  I've addressed this here: https://news.ycombinator.com/item?id=7434651 https://news.ycombinator.com/item?id=7434651
philtar 13y ago
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.
willvarfar 13y ago
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.
daliusd 13y ago
SQLite does not discourage using it for websites https://www.sqlite.org/whentouse.html 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.
- workhere-io 13y ago
  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).
  - LordIllidan 13y ago
    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.
    - workhere-io 13y ago
      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.

olavgg 13y ago

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 https://www.sqlite.org/lockingv3.html

workhere-io 13y ago
I've addressed this here: https://news.ycombinator.com/item?id=7434651 https://news.ycombinator.com/item?id=7434651

philtar 13y ago

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.

willvarfar 13y ago

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.

daliusd 13y ago

SQLite does not discourage using it for websites https://www.sqlite.org/whentouse.html 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.

workhere-io 13y ago
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).
- LordIllidan 13y ago
  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.
  - workhere-io 13y ago
    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.