Benefits, etc of using mySQL over SQLite (RoR)

Question

I'm building a web application right now for my company and for whatever reason, can't get mySQL working on my Mac w/ my Ruby install (OSX 10.5). SQLite works fine though, so would it be a problem to use SQLite for now so I can get to work on this and then just change up my database.yml file to point to a mySQL database when I deploy (assuming I rerun migrations and such)?

Also, what are the benefits/drawbacks of using mySQL over SQLite in a RoR application? I've always used mySQL by default in the past, but never learned SQL directly (always through ActiveRecord) and never thought too much about the difference.

Answer 1

SQLite is perfect for a desktop or smartphone application ("embedded" usage). However, if you plan to build a web-application, you are highly encouraged to make use of a non-embedded DMS like MySQL. The benefits are countless, such as 3rd party design and analysis apps, performance etc ...

Answer 2

THe biggest performance issue you may run into is table locks. SQLite unfortunately does not have row level locking. So if your app is going to run multiple processes / threads (as with multiple web users) its likely some threads will not be able to perform an SQL op. For this reason i would go with MySQL - or perhaps Postgresql.

Answer 3

Should be no problems, as MySQL should have a superset of SQLite capabilities, and as @Sean pointed out, performance should only increase. Just try to make sure you're not using anything too SQLite specific (I'm mainly a SQL Server and Oracle guy, so don't know what that would be, if anything). Remember, the "S" in SQL stands for Structured, not Standard ;)

Paul.

Answer 4

Benefits of MySQL/PostrgreSQL/etc

Pros

• Stronger data typing, which means cleaner data
• Ability to store more data
• Scale better to larger data sets
• Spatial support (think GPS)
• Full Text Search (FTS)

Cons

• Stronger data typing means data will be validated, bad data will cause errors
• Not a good candidate (if even possible) for devices with limited resources (iPhone, Blackberry, iPad, etc)

I would pick PostgreSQL v8.4+ over MySQL given the choice. MySQL's features lag behind the rest of the major SQL database alternatives.