Rails 2.0: Why not use sqlite3?

Question

I've been reading some tutorials on how to get started using Rails 2.0.

(Time out: genius website name idea conceived from a typo I just made: "tutoRAILS." Sorry, back to my question.)

In most of the tutorials I've been reading, it seems to encourage using MySQL instead of sqlite3. Is there a reason for this, like, performance-wise or anything? I'm just testing out Rails on my PC using InstantRails at the moment, and they're nice enough to include MySQL in their setup, but I've been making my experimental applications using sqlite3. Am I missing some major caveat of sqlite3, or is this just a general preference that others have for MySQL?

Answer 1

SQLite is a good engine, but it's still an in process (or desktop) style engine. In process engines have inherent weaknesses in terms of concurrency that make them a fundamentally poor choice over server-based engines like MySQL for web sites or other scenarios with the potential for a lot of simultaneous write access.

See this page on the SQLite web site:
http://www.sqlite.org/whentouse.html

SQLite usually will work great as the database engine for low to medium traffic websites

and

if ... you are thinking of splitting the database component off onto a separate machine, then you should definitely consider using an enterprise-class client/server database engine instead of SQLite.

Answer 2

SQLite is awesome, but it has performance issues with multiple concurrent readers and writers.

Like Joel quoted, if your site is low traffic this will rarely be a problem, but on medium activity I sometimes get locked DBs (and hanged queries). In this case a DB with better support for multiple-concurrent-users is better.

Personally, if I use a DB-agnostic layer to access the DB then it's easy to switch from one to the other, so it's easy to start with SQLite and move to a different one when necessary.