Somewhere in the backstage of a web page, applications need to store large amounts of data. It could be product data for an e-commerce shop or user data about the registered users. All of the data should be well organized, highly secure, and always available, and most importantly, the storage cost should be reasonable. The obvious answer is a database, the perfect store for the application data. Then we will need a system that manages the database – which is also called a database! Keeping both meanings in mind, we will walk through the choices in finding a database that fits the needs of your application.
Database types
Databases are categorized into different types based on how they model data and how we query the data. The traditional choice is the \i relational database a.k.a. SQL database which accommodates relational data. The data items are organized into the rows and columns of the database tables which we query with SQL, the standard query language for databases. The most popular alternatives to SQL databases are identified as NoSQL databases which are not based on relational data models: A graph database organizes the data into nodes and edges whereas a document-oriented database stores the data as document objects. Several NoSQL query languages have been introduced, such as GraphQL for graph data and XQuery for documents, but none of them have become the de facto standard for their respective database type.
While the same application data fits in several types of databases, there are several differences that translate into one type being more suitable than the other. For example, creating a hierarchical view of relational data requires expensive join operations as the data is collected from multiple tables. Meanwhile, a NoSQL graph database allows us access to the same data much faster with a single operation. In contrast, defining an SQL integrity constraint for relational data is straightforward whereas a NoSQL database may not even support them. A common example is the unique constraint which requires that all values must be unique in a given context. SQL databases achieve this by adding the keyword UNIQUE to the table column definition but NoSQL databases have no standard way to do the same. We may have to define redundant proxy collections with a unique index or write custom application code to ensure the same level of integrity as a single SQL keyword.
Products
Two common choices for the relational database of a web application are MySql by Oracle, and MariaDB which is without a vendor lock-in. Their capabilities have been sufficient in the majority of Outsourcify web app projects. Both are open-source and free to install. Those few who need more than the standard features or just a better performance with high volumes of data should consider the DB products of the big players, e.g. Oracle 23c, Microsoft SQL Server, and IBM DB2, to mention a few. They are all based on the relational data model but they come with various non-standard NoSQL features built on top. Note that the commercial licenses for these enterprise-scale products also require an enterprise-scale budget.
NoSQL databases are a generation younger and much more diverse than their relational counterparts so it is harder to find a clear winner that would be the best for a specific web application. New players constantly emerge in the field with new technological ideas while older ones frequently lose their appeal and fall into oblivion. As of May 2024, a database engine ranking shows that MongoDB is the biggest native NoSQL database in terms of popularity. Both fame and popularity are important because a wide user base translates into abundant online resources and support which are essential to troubleshooting any issues. Performance-wise, MongoDB and other native NoSQL databases have the advantage over relational databases that have some NoSQL features enabled. As a downside, the younger generation products are without a proven track record in almost anything, including data security, making them more vulnerable targets for hackers.
Data access
Common questions asked by curious website owners are about the database: What’s the data in the database? Is it displayed correctly on the web page? We find the answers with database administration tools. PhpMyAdmin is the most used admin tool for MySQL and MariaDB. It runs in the browser where we get a table view of the data. Running custom SQL queries is also supported as well as updating the data in a web form view of the table. PhpMyAdmin is relatively easy to learn without an IT background or training. MySQL Workbench is a more advanced tool for database administrators and is commonly used by developers.
Web apps access the data through a query interface. Most commonly, the application sends an SQL query to the database and receives an array of data as a response. An alternative to SQL queries is to use one of the many NoSQL query languages such as GraphQL. This is not to say that the application developers ever need to write these queries. While they could write raw SQL to query and update the data, it is usually more convenient to read and manipulate the data as PHP or Javascript objects. This requires Object Relational Mapping (ORM) such as Doctrine ORM for PHP and Prisma for Javascript.
Hosting options
Once we know which database product will manage our data, we need to decide how it will be hosted and where the database will reside. The goal is to keep the data physically near the applications to avoid a lag in communication when the applications need data. A simple solution is to have both the database and the application code run on the same server. The caveat is that although we can keep the app and the data together, we will have challenges with scalability if the website grows in the amount of traffic and the amount of data. Therefore, it is common to host the database on a dedicated database server, located in the same geographical region or even in the same local network as the application server.
Cloud hosting services like Amazon AWS, Google GCP, and DigitalOcean, offer a wide range of options for hosting both web applications and databases. Choosing the same cloud service provider for both the application and the database is often rewarded with a discounted price. Using a single cloud platform also allows a closer proximity than for example, having DigitalOcean host the application that connects to the database hosted by Oracle OCI. Hosting the database in the cloud comes with several benefits:
- Software and hardware updates and other maintenance are included in the price
- Capacity upgrades with a click of a button
- Nightly backups keep your data safe
- Admin consoles for monitoring and managing the database
The decision often comes down to a cost consideration with the ease of operation and maintenance as well as website performance also weighing in.
Conclusion
Finding the best data storage option for a web application involves a complex equation of several factors that require a good understanding of the application data. Choosing practice is much easier, though, as there are many good databases available at a reasonable cost. The differences between the database types are significant. In short, relational SQL databases offer high data integrity with a powerful query language while the more flexible and scalable NoSQL databases take pride in their high performance. If we acknowledge the immaturity of the NoSQL technology, it will require an adventurous attitude to trust one with your data. For those who want to play it safe, a relational database is the right choice.
Don’t miss out on our latest articles:
- Agility Isn’t Dead. It’s Finally Becoming Real
- AI Is Reshaping Web Development — But Not in the Way People Think
- Will AI Replace Developers? A Reality Check From the Field
- Teaching AI and Software Development at Chulalongkorn University: A Two-Hour Conversation That Went Well Beyond the Slides
- Skipping Figma Doesn’t Mean Skipping Design
