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

I think it is mainly a matter of keeping the friction level low on the tutorial. If you are a newby, I would recommend MySql only because it will be easier to do the tutorial. sqlite is a fine desktop solution otherwise.

Answer 3

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.

Answer 4

I always begin with SQLite. If I need to prototype a Rails project, I can be up and running in literally no time at all. Build some scaffolds, run my migrations, write some tests and I'm off to the races.

However, if and when a project gets to the point where you'll be deploying it, I would suggest MySQL or PostgreSQL for better performance.

SQLite also has its benefits for small embedded applications or if you don't have the overhead to run a database for a tool that only a few people will be using.

Answer 5

Note that Sqlite is the default engine for the Camping framework. Makes sense since both are aimed at small and fast, not big and enterprisey

Answer 6

Since Rails 2.0, sqlite3 is the default database, so there certainly isn't a bias against it. Many Rails tutorials predate Rails 2.0, however, when the default database was MySQL.

As several others have mentioned, sqlite3 is a fine database for quickly and easily getting an application up and running. For your Rails development environment, it has legs--it will likely work fine, even as your app gets complex. For the Rails test environment, it's also a very good choice--it's very fast and most tests have relatively simple database demands and tend to not require concurrency or otherwise run into sqlite3's limitations.

However, for many, if not most, production web sites, a DB designed for more concurrency may be called for--MySQL, Postgres, etc..

Answer 7

For something like a Rails blog engine SQLite will work very well even at pretty high volumes because its all reads and very few writes. In fact the majority of your hits will be cached pages - depending on if you allow comments and how active your comment threads are - you might get away with a single rails process because your web server will handle almost every request.

With a SQLite database each rails process has to fight over a file system lock on the file to do writes so you'll end up blocking a lot if you have a lot of processes writing. A way to think about this is to consider how many rails processes you will have...if its going to need more than 3-4 then probably SQLite is not a good choice.