Rails Database Default & Alternatives

 by abhishek  on   06 Aug 2010

This post is a part of the series of articles about choices available to a rails developer. It is inspired by a blog post by Josh Susser, where he asserts that there are too many choices available for a new ruby-on-rail developer now, which can get him scared/confused. This series is not about “choice or no choice?”, and not about “are the defaults correct?”. It just answers the newbie developer’s curiosity of what lies beyond the defaults. Lets begin with databases.

Database

Rails is used for writing database backed web applications using the MVC design pattern. The choice of database depends on the kind of capacity you expect from the database and the budget. If you are developing a large enterprise application, you might need to research Oracle/MS-SQL and the like, along with those described here. Another choice you might have to make is whether to go for a relational DBMS. The rails framework provides adapters (programs that connect your app to the database) for three popular open source relational database management systems – sqlite, mysql and postgresql. So if you are looking for an open source RDBMS, you have have the best to choose from.

Sqlite

sqlite for rails application Is the rails default. As the name suggests, sqlite is light-weight sql. Unlike the other two, it doesn’t work on the client-server model. It runs as a single library, attached to the executing rails app. Due to this, it saves the overhead of client-server communication, hence lite. A common perception is that it is suitable for development environments only and for live websites, it is a small tool (official perception). But lot of people swear by its usability in production – they say it is fine if your site receives up to 100k hits a day. Also, as it is light, it is the best suited database for/as embedded systems. Sqlite is used by some big names like Apple and Firefox. A more complete list can be seen on the sqlite website. More on sqlite performance -a stackoverflow question , official figures. However, as sqlite is not designed to handle heavy concurrency – it might not be a good choice if your app has 100 users per second all performing database operations.

mysql

mysql for rails applicationsMysql is client-server based, fast, easy to use and very popular. It is available for most platforms (look at this comparison on wikipedia – mysql is all green), and adapters and tools are widely available. Quite full featured, can be configured to run well for different goals and environments through its 9 storage engines. And works reasonably fast by default. Do look at this discussion on stackoverlow if you are torn between mysql and sqlite. The only catch with mysql is in the license. Its free and fine for use, but if you are planning to distribute mysql with your close-sourced product, you may have to pay license fee. Some useful information on the licensing can be found at stackoverflow, wikipedia and the official website

PostgreSQL

postgresql database for rails applications PostgresSQL is another great RDBMS (actually an O-RDBMS) available for rails developers. It is almost as good as mysql, with some differences in technology, and notable difference in license. Architecture is different (this has a single engine), so is ACID compliance (full here, partial in mysql), and there are some feature differences (partial indices are supported here, not in mysql) as well. The license in postgres is a much simpler MIT style ‘postgres’ license. So which one to go with – mysql or postgres ? Most people who have used both are inclined (see this stackoverflow discussion) towards postgres. Mysql has some limitations (like – no partial indices), which should be understood, especially if your application might need some hard-core database operations, like, say multiple level sub-querying. Plus, postgres is controlled/supported by the developer community, while mysql (now) belongs to Oracle. A few more good comparison resources – tarangana blog, wikivs. Some relevent stackoverlfow questions – pros/cons,  mysql-> postgres switch, reason for mysql popularity.

Our take

We prefer to use mysql in production and development. However for quick prototyping and for testing sqlite is great, because of the time it saves running the tests.

Conclusion

Rails is database agnostic. If you don’t write any native sql queries in the app, it can switch from one DBMS to another effortlessly. As a best practice, the production environment should not see a DBMS change, as this may increase the risk of unexpected bugs. Although for production use, Postgres looks like a winner in the above discussion, the others also stand tall based on what the utility is. The decision should really be taken based on the ease of use and the anticipated technical requirement.

5 comments

  1. I’m collecting a variety of Rails on PostgreSQL resources at http://railsonpostgresql.com/ ; enjoy!

  2. An Excellent Read

  3. Hi, This blog shows “3 Comments” in the title but only 2 comments are shown here. I guess something went wrong in the comment count :)

  4. Hey Ganesh, fixed the comments bug. There was a pingback that was accounting as a comment (we had recently disabled pingbacks).

  5. Hi Shalin, Now its perfect :)

Leave a comment