There is a lot of weird-sounding language around databases.
Most of it centers on the names of the popular database software which is available. It can make it tough to choose.
Each strangely named database solution offers features making it suitable for different tasks. This article should shed some light on what’s what.
SQL Is a Language
SQL (pronounced ESS-QUE-ELL or sequel depending on which side of the raging debate you side with).
It stands for Structured Query Language.
It is not a piece of software – it’s the programming syntax used by many (but not all) database software platforms to input and retrieve information stored in a database.
While it’s mostly standard, each SQL server solution tends to have its own implementation with small differences and differentiating features, so you can’t just take SQL written for one platform and run it directly on another without adapting it.
Which One Do I Choose?
Which one of the following database packages you choose for your project falls largely on what you’re trying to accomplish.
I’ll provide some examples of what each is good for and who’s using them to give you an idea.
With that in mind, if you’re a newbie – the one you choose is probably going to be the one you can find the best tutorials for your favorite programming language for.
Everyone learns differently, so if you find good articles for one database, that’s a good choice to get started. If you’re a video learner, you might start with a different database system because you found some easy-to-follow videos.
All of the following database solutions will have good support in just about whatever programming environment you will be using – Be it PHP, JavaScript/NodeJS, Python, or Perl.
MS SQL is for Windows
I’ll get the black sheep out of the way first – MS SQL.
It’s Microsoft’s SQL database server. While it can be run on Linux, it’s best used when working within Microsoft’s ecosystem, like building on the Azure cloud platform or building applications to be run specifically on Windows servers.
This doesn’t necessarily make it a bad choice – but it’s probably not what you’re looking for if you’re building and deploying on Linux.
MySQL / MariaDB
MySQL is probably the most popular open-source database software.
It uses SQL syntax to read and write information to the databases.
It’s networked – available as a service from other computers to query databases hosted on another machine.
This means that the MySQL Server software must be installed to provide the database service.
What It’s Good For
- Popularity means lots of help online.
- Storing data in tables with rows and columns (think an excel spreadsheet)
- Rigid data structures (as tables must be predefined)
- Relational data
- For example, books with authors or users with blog posts.
- Linked and related data that needs to be rapidly queried.
- SQL Language Queries
- Traditional, reliable relational SQL database
Who Uses MySQL/MariaDB?
MySQL is probably the most popular database software for online platforms.
- WordPress
- It’s the default database for a lot of PHP frameworks like Laravel
MariaDB
MariaDB is a drop-in replacement for MySQL. It does everything MySQL does with the same syntax and features.
If you’re following a tutorial written for MySQL, it’ll work exactly the same for MariaDB.
This might seem weird – why would someone go to the effort to maintain two pieces of software that behave identically?
The answer is software licensing – to make a long story short, Oracle Corporation backs the development of MySQL. Many Open Source proponents do not like Oracle due to some historical actions regarding intellectual property and ownership.
MariaDB was created to make sure there was an alternative that is fully compatible with MySQL should something like this happen again.
SQLite
Unlike most of the other database systems on this page, SQLite does not run as a client/server on a network – databases are a simple file available on the file system.
No additional software is required to be running to provide the database service – include an SQLite library with your project point it to a local SQLite file on your disk.
This is perfect for smaller databases, local development, or as the data store for desktop applications.
What it’s Good For
- Small databases
- Local development and testing
- Situations where complex queries aren’t important
- Desktop applications
- Asking your customers to install a SQL server before installing your product may confuse them – SQLite removes the need for this.
- Having your customers’ data in a single file on their computer makes it really easy for them to find their data and back it up.
- It can also be used for web apps when you only need a simple database for blog posts or other activities where data isn’t constantly written.
Who Uses SQLite?
As I mentioned, SQLite is great for desktop apps.
- Adobe
- Microsoft
- Firefox
- Recent releases of the Python programming language all include SQLite support.
PostgreSQL
PostgreSQL is a networked SQL client/server database solution that offers more complex data structures than MySQL.
This doesn’t mean it’s more complex to use – you can do more stuff with it (if you need to).
PostgreSQL has increased greatly in popularity in recent years.
What It’s Good For
- Everything MySQL does
- Complex queries
- Business intelligence and data analysis
- Concurrency – multiple users updating the same data rapidly
Who Uses PostgreSQL
PostgreSQL is popular with organizations dealing with big data queries.
- Robinhood
- IMDB
- Due to its popularity with the Business Intelligence crowd, there is a lot of material online regarding using PostgreSQL with the Python programming language.
MongoDB
MongoDB isn’t an SQL database program – but it is a database program.
MongoDB does not use the SQL query language for reading and writing data – it has its own query language – MQL (Mongo Query Language).
Unlike the above database systems, MongoDB stores data in documents rather than tables.
The data structure is not rigidly enforced, and you can pretty much dump whatever data you want into a document without regard to its format (until you have to query it).
Data is stored in documents with a format similar to JSON, allowing for complex objects to be stored without splitting the data into rows and columns.
What It’s Good For
- Storing data where you may not know the structure in advance
- For example, storing the details of a modern art exhibit: You may not know what attributes each item will have in advance, so fixed table columns would not be appropriate.
- Less risk of attach from SQL injection exploits
- Dump data into it and deal with it later
- Easy to get started with as you don’t have to worry about your database structure in advance
- This makes it good for rapid development where you haven’t decided on a rigid data structure.
Who Uses MongoDB
MongoDB is popular with organizations that deal with user-submitted data that may not follow a set structure.
- SAP
- EA
- eBay
Conclusion
There are different database systems because there are different users with different requirements. If you’ve found a good book that uses one system – stick with it.
The concepts used for creating and managing data in one database platform will transfer to others. Understanding these concepts will also help you understand the differences between each and make the choice easier.
It’s impossible to recommend a specific database system without knowing what you want to achieve first. Planning is important!
