#Which Database is best for your Project?

Starting a full-stack or backend application? In this post, we’ll discuss the popular systems MySQL, PostgreSQL, and SQLite. The mentioned systems are all database management systems using SQL. Database management systems (DBMS) are used to store, retrieve, and run queries on data. These systems allow the developer and user to go through the [CRUD](https://www.educative.io/blog/crud-operations) operations with their data. MySQL, PostgreSQL, and SQLite are each relational, open-source database systems using structured query language (SQL). This language is used to manage the data held in the database systems. SQL is the standard language for relational database systems. We will focus on these systems that range in features and size to give an idea of which you could use in your next project.

## Goalsss

Not all databases are built the same. Starting a project and integrating a database system is a decision that should take into account the application's language, storage requirements, and features. The decision is simplified when you break down the differences and acknowledge your project's goals.

Focusing on our three database systems, we’ll point out what makes up the system, some features to note, and which may be ideal for your project. Hopefully by the end, you will have an idea of which system you’d like to integrate for your project.

## Let's Discuss MySQL

First we will discuss [MySQL](https://www.mysql.com/). This program is an open source database program, any developer can use and adjust the software within their application. MySQL is purposed for extensive production applications built with simple actions. If you are getting started in the development process, this database system is beginner friendly. Features include an administrative client, MySQL workbench, and a backup program. MySQL server runs well on any device and web servers with little management. MySQL is compatible with many programming languages, and has extensions available if a specific language is required. MySQL stores each database in a subdirectory of its whole data directory. This method along with MySQL’s storage space allows larger databases to be stored and organized easily. There are multiple ways to view the data that is stored. When running a query, you have the option to save the query paramter into a view. The created view is stored in the database, available for reference to gather the same necessary information. This especially helps with complex queries developers frequent. MySQL uses B-tree index, meaning keys are sorted using a hierarchical index to minimize data read. MySQL is available to include alternative index types with extensions, or modifications. MySQL offers transactional and non-transactional storage engines. This allows one or more write operations to be rolled back if not completed, or performing rollback operations manually. MySQL uses MySQL server,

<table>
   <tr>
     <th>Database Type</th>
     <th>Relational Database</th>
   </tr>
   <tr>
     <td>Proprietor</td>
     <td>Oracle</td>
   </tr>
   <tr>
     <td>Language</td>
     <td>Written in C and C++</td>
   </tr>
   <tr>
       <td>Index</td>
       <td>B-tree disk tables with index compression</td>
   </tr>
   <tr>
       <td>Performance</td>
       <td>Capable of applications with high volume of reads</td>
 </table>

Within this discussion of database systems, MySQL is the most popular and well known. An advantage to working within MySQL is the documentation from the site as well as other developers. With its standing, there have been many contributions made to the community knowledge. Its range has made working in the system simple and approachable. However, if you are in need of complexity and customization then this program will lack in that regard. This database management system will work for those projects that require scalability and simplicity.

## Let's Discuss PostgreSQL

Secondly, we will discuss [PostgreSQL](https://www.postgresql.org/). This is an object-relational database. This system is similar to MySQL, but has additional features. PostgreSQL is more intricate and complex, capable of writing large amounts of data efficiently. Features will include table inheritance, adheres more closely to SQL standards, and extended data types available. PostgreSQL supports a range of languages, with extensions also available if a specific language is required. The developers in PostgreSQL have developed their own PL/pgSQL language for the PostgreSQL database system. The purpose of PL/pgSQL to create functions and trigger procedures, adds structure to SQL, and inherits all user-defined types, functions, and operators. Within PostgreSQL, data is stored in a specific directory folder made up of subdirectories and control files. The storage is extensive and allows for more complex data stored with less limitations. This system also utilizes the B-tree index by default, as well as partial, mulitcolumn, and expressions. Partial index is an index that contains entries only to table and rows that meet a set condition. Expression index is an index column of a function or expression determined from the columns of the table. Multicolumn index is an index that can define more than one column within a table. PostgreSQL also supports queries saved to database views, along with materialized views. Materialized views are views that save the query parameters, as well as the information determined from the query. This can be referenced for consistent reports. PostgreSQL offers transactional and nested transactional storage. This allows write operations to be rolled back if not completed, and start a transaction set by an instruction or condition within the scope of an active transaction.

<table>
   <tr>
     <th>Database Type</th>
     <th>Object-Relational Database</th>
   </tr>
   <tr>
     <td>Proprietor</td>
     <td>The PostgreSQL Global Development Group</td>
   </tr>
   <tr>
     <td>Language</td>
     <td>Written in C and C++</td>
   </tr>
   <tr>
       <td>Index</td>
       <td>B-tree indexes, Partial</td>
   </tr>
   <tr>
       <td>Performance</td>
       <td>Capable of applications with high volume of reads and writes</td>
 </table>

Within this discussion of database systems, PostgreSQL is the most complex built database system. An advantage to working within PostgreSQL is the customization. Built for larger projects, won't be worth the hassle dealing with if you are looking for a smaller database system, or simpler system. This database management system will work for those projects that require scalability and additional features.

## Let's Discuss SQLite

Now we’ll talk about our final database in question, [SQLite](https://www.sqlite.org/index.html). This database is an embedded relational database system. This means it requires less power to run and can be built with less powerful processors. Server-less databases eliminate the operational overhead of deployment, capacity planning, upgrading, and management. Serverless is great for smaller applications, and database maintenance is minimal. SQL stores a complete database in one file on a disk, making it simple to navigate but scalability limited. SQL supports many programming languages. The system utilizes R-tree index is an index that allows searches for data which are "at" or "near" a particular point or bounding box, making the query faster. If a specific index is needed, there are alternatives available.

<table>
   <tr>
     <th>Database Type</th>
     <th>Relational Database</th>
   </tr>
   <tr>
     <td>Proprietor</td>
     <td>Public Domain, SQLite Consortium</td>
   </tr>
   <tr>
     <td>Language</td>
     <td>Written in C</td>
   </tr>
   <tr>
       <td>Index</td>
       <td>Multi-column indexes, R-tree indexes</td>
   </tr>
   <tr>
       <td>Performance</td>
       <td>Capable of applications with medium volume of reads</td>
 </table>

Within this discussion of database systems, SQLite is the smaller database system. This database management system will work for those projects that don’t require extensive storage. The main benefit of choosing SQLite rather than those mentioned is the embedded database, if server-less is necessary this is your choice.

## Finale

Many of the core functions translate over the systems in our discussion, the differences are in the features, scalability, and servers. These systems have amazing features you may need in your project, but you still need to know enough to work with the data. So take on what you as a developer can work with and make it your own.

Hopefully with knowing more about these database systems specialties, you have an inkling of which would be necessary for your project. And with your decision about which database system to use, you’ll need to think about which [ORM](https://www.freecodecamp.org/news/what-is-an-orm-the-meaning-of-object-relational-mapping-database-tools/) you’ll use to interact with your database. I have an idea of what you could use, check it out [HERE](https://www.codingzeal.com/post/how-to-use-redwood-with-prisma).

Photo by AltumCode on Unsplash