The Most Commonly Used Databases for the Web

A website can be static or dynamic. For any web application with a minimum of complexity a database management system (abbreviated as DBMS) is required.

It’s important to note that I will talk about public web applications such as e-commerce sites, social networks, classified ad sites, blogs, and so on (the kind of projects I have worked on, talking from my experience). I will not talk about intranets or extranets, which are closed environments that do not take into consideration the same speed and security concerns that a public site must manage.

In my opinion (and what I have seen so far) there are 3 database management systems that are commonly used for web applications.

MySQL

The #1 in this top, the well-known and well-tested DBMS for the Internet: MySQL. An open-source DBMS for relational databases.

It’s been highly coupled with PHP, or at least all PHP projects I have worked on used it. It’s the LAMP stack, which stands for Linux (as the operating system for the server), Apache (as the web server), MySQL (as the DBMS) and PHP (as the programming language). A very popular web stack. Probably the biggest project that uses it nowadays is WordPress.

It started as a fast solution, since it did not support advanced (but useful) database functionalities such as foreign keys, check constraints and transactions.

For that reason in the past, for a new project, we analyzed the pros and cons of using it: what’s our main focus? data integrity or speed? Something that nowadays is absurd, because we have very advanced solutions that work insanely fast (in part because hardware has evolved and is reasonably cheap).

MySQL lets the user to choose from several data engines. There are 2 main options:

• MyISAM: the faster one due to the limitations I talked about in the previous paragraph.
• InnoDB: it was the engine that provided the missing pieces and became more popular over the time until it was acquired by Oracle.

And that’s the problem with MySQL: it’s a product now owned by the biggest company in the field, earning millions of dollars from corporates using their product.

It looks like Oracle wants to be part of the non-corporate world controlling MySQL. For that reason the original MySQL creators forked the project to create MariaDB, a GPL project that keeps compatibility with MySQL.

PostgreSQL

Many developers switched to PostgreSQL, the most advanced open-source relational DBMS. It provides all the functionalities you may need from a database while being a product maintained by the community. It’s like the Debian of DBMS.

I would say it became very popular since 2005, the year Ruby on Rails launched its first version using this DBMS by default. Rails has been supporting some useful features from PostgreSQL, making the development easier. For instance, Rails 4 brought support for PostgreSQL’s arrays and hashes, out of the box. Why creating intermediate tables for this when we can use this feature right from our DBMS?

If you are used to a GUI to work with your DBMS directly, you may be disappointed. MySQL has lots of excellent GUIs with no equivalents to PostgreSQL. It’s best to get used to the command line.

MongoDB

This is probably the most popular and open source NoSQL DBMS.

Instead of a relational design with tables, MongoDB stores data as collections of documents, being simpler in design to be easily scaled using clusters (in fact, this is automatic using auto-sharding).

It is very appreciated by JavaScript developers because tables are replaced by JSON-like documents. These documents do not need a structure definition, we can just add them with whatever structure, and its form will be adapted dynamically (this reminds me of dynamic languages like Ruby and its variables). Some complex data types can be store easily, such as arrays.

This modern approach to store data, more natural for programmers, made many to switch to this NoSQL DBMS, but in many cases this was a mistake because they want to simply replace one system with another. MongoDB is not made to replace relational databases directly.

Conclusion

This top of commonly used database management systems does not intend to say that one is better than the other. Developers have personal preferences, tools work better with a specific project, problems can be solved easily choosing one over the others, and so on.

So as a conclusion, we must think that it’s important to analyze the problem and choose the right tool for the job. I could say that in the majority of cases, for common web applications with a classic relational data model in mind, a relational database will suffice.

Here are some thoughts:

• If you are using a LAMP stack, you should be using MySQL right away.
• If you work with Ruby on Rails, PostgreSQL is probably a more natural decision.
• For many modern needs, MongoDB is more appropriate.

I encourage you to test them all, trying to use their unique features and read what they emphasize on their developer documentation sites. A good analysis will make your long-term development easier.