Familiarize yourself with the various open source database engines available, including their pros and cons, and make an informed decision on which to use for your project.

1. mySQL

Acquired by Oracle in January 2008, mySQL is the most widely used open source database engine in the world, only behind its commercial counterparts also developed by Oracle. Its relational structure, ability to handle hundreds of millions of records with efficiency, combined with its vast array of features make mySQL an excellent choice for the vast majority of SME operations.

With mySQL you must pre-define the schema, meaning the tables and columns your database will consist of, and the structure your data must conform to. Not only does this help provide structural integrity through the use of foreign key constraints and cascading, it also helps the database engine optimize queries for the greatest speed and efficiency.

Other developers who work with your software will also be grateful as they will easily be able to identify how the data is structured.

Utilizing the industry standard SQL language, communicating with mySQL databases somewhat resembles writing simple English phrases, plus learning SQL provides you the ability to easily work with various other database engines. Another excellent benefit of mySQL is its massive and active community with almost unlimited documentation, allowing you to quickly find the answers to any issues you may encounter.

Its full support for more advanced features including transactions, triggers, views, stored procedures, partitioning, replication, and more provide you with everything you need and beyond.

Check out the mySQL Community Edition site for further reading and information.

2. MongoDB

The world’s leading NoSQL database engine, MongoDB began its open source journey in 2009 and began seeing its massive rise in popularity starting around 2012. The flexibility offered by its schemaless structure, and use of JSON objects as records was positively embraced by developer communities across the internet.

Instead of a relational database where you must pre-define a schema of tables and columns, MongoDB is schemaless consisting of collections which contain documents as records. With no schema to conform to, you may store any data desired within documents without having to worry about structural constraints imposed by relational databases, providing benefits to AGILE development methodologies.

All documents are stored as JSON objects, providing familiarity and comfort to developers since JSON is already used in day-to-day software development. Along boasting a large and active online community, MongoDB contains full support for indexing, replication, sharding, and more.

Although the schemaless design of MongoDB comes with greater flexibility, it also comes at the cost of structural integrity since there are no data constraints to conform to. Without strict code controls within the software, this can lead to additional and unnecessary bugs, along with frustrations from other developers who work with the code.

The schemaless design also inhibits the engine from optimizing queries for the greatest speed as efficiently as its relational counterparts can.

However, NoSQL database engines like MongoDB are an excellent choice when the project necessitates the storage of arbitrary data into records such as biographies, historical timelines, educational records, and other such data sets.

For further reading, please head over to the MongoDB Home Page.

3. SQLite

A very lightweight and portable database, instead of the client server model of other database engines, SQLite is an excellent choice for managing localized data. This includes any embedded devices, mobile phones, the Internet of Things, and any other situations where you only need to manage data local for the specific device / user.

With the same relational structure of tables and columns, along with its utilization of the industry standard SQL language, developers can transition over to working on an SQLite project with ease. SQLite databases can be instantly created, dropped and transferred on the fly allowing for a great amount of flexibility.

Although not suitable for larger database systems, SQLite is a prime candidate where you need the power and flexibility of SQL, but with minimal overhead and usage.

For more details, check out the SQLite Home Page.

4. PostgreSQL

Another popular relational database engine similar to mySQL, and with a strong following amongst Python developers, PostgreSQL is known for being a more robust and enterprise level database engine.

Unlike mySQL, PostgreSQL is an object relational database engine, meaning along with structured schemas that consist of tables and columns, it also has native support for various NoSQL features including storing of JSON / XML objects. Like mySQL, it also contains full support for core features including transactions, views, triggers, stored procedures, partitioning, replication, and more.

There are several aspects of PostgreSQL that differentiate it from mySQL, one of the main ones being its powerful PL/SQL scripting language which provide a great amount of control and flexibility when writing triggers and stored procedures. Plus various others such as the ability to create functional indexes, but nothing we need to delve into for brevity sake.

In short, if you’re looking for more of an enterprise level database engine, then PostgreSQL may be for you.

You can read more details at PostgreSQL - The World’s Most Advanced Open Source Database.

First released in 2010, Elastic Search has quickly risen to be the most popular database engine for the searching and sorting of a large number of documents, even numbering in the billions. Its distributed architecture and inverted index make it an excellent choice to manage and search large number of documents, analytics, geospatial, infrastructure monitoring, and other data.

It comes packed with features including the ability to to gather incoming data from multiple streams simultaneously, provide data visualizations of indexed data, automated relevancy scoring of documents, full text search, and more. Importantly, it’s blazingly fast, and its distributed architecture ensures uptime even in the event of hardware failure.

If you have a need to store and search large amounts of data, make sure to check out Elastic Search home page.

6. redis / memcached

Although not database engines per se, these deserve a mention on this list nonetheless. Both redis and memcached are in-memory data stores, meaning the entire database is stored within memory making the storage and retrieval of data blazingly fast, even up to 10 million queries per second.

Due to the size limitations imposed by the purely in-memory storage, these should only be used to compliment other database engines such as mySQL or MongoDB, and not as a replacement.

Common uses for redis and memcached are for cached items, temporary / recent data that will expire in a short period of time, or small pieces of data that need to be frequently accessed. Although both are excellent data stores, memcache is mainly used for only a cache, whereas redis has support for eight different data types providing you more flexibility on how to store and manage your database.

A good majority of online operations can benefit from in-memory data stores to increase speed. For more reading, please check out the redis and memcached home pages.

You’re on Your Way!

Although not an exhaustive list, this article will have familiarized you with the most popular open source database engines that you will encounter during your work as a software developer.

Remember, especially when it comes to the widely used and time tested database engines, there is no right or wrong. It all depends on your project requirements.