SQLite, PostgreSQL, and MySQL
Finishing up my first six weeks of bootcamp, my only experience with relational database management systems has been with SQLite. Because there is a limited amount time to teach a large amount of information in a coding bootcamp, I wanted to briefly explore other RDBMS that couldn’t be covered.
SQLite, PostgreSQL, and MySQL are three of the most popular relational database management systems, that interact with a database. They allow you to read, write, and preform other tasks, as well as control access to a database. Relational database management systems use SQL to send queries to the database, but use their own versions of SQL.
“ SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.”
Most database management systems implement a separate server to communicate with the database server, through an interprocess communication. This is known as a client/server architecture. SQLite is described as “serverless”, meaning it does not require an additional server. Unfortunately this feature prevents SQLite doesn’t support multi-user management, meaning you can’t grant and control users access to the database. Applications communicate directly with the SQLite database server disk file. It being self contained, requires little support from an operating system and makes it easy to embed in devices like phones or game consoles.
Benefits:
Lightweight and Portable: In fact, the -lite in SQLite refers to it’s lightweight structure. It takes up little space and is notably -lite in it setup, administration, and it’s required resources. This can mostly be attributed the SQLite not requiring installation of additional dependencies. An SQLite database is stored as one single disk file. This single file is also “cross-platform”. This means you can create a SQL database on one machine and it can be copied and used on a different machine, even if that machine has a different environment or architecture.
Speed and Efficiency: Read and write requests are really fast with SQLite. This is because it only loads the data it needs instead of reading the whole file and storing it. When editing, it only overwrites the parts of the file you made changes too.
User-friendly: SQLite doesn’t require configuration and doesn’t need to be stopped, started, or restarted because it does’t have a server process. This makes installing and integrating SQLite pain-free.
“PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.”
— PostgreSQL Global Development Group
PostgreSQL has a strong reputation for its reliability, data integrity, extensive features. This gives developers the ability to better protect data and build secure environments and gives them the ability to manage data regardless of how large the database is. PostgreSQL allow you to define your own data types, build custom functions, and even write code in another programming language without refiguring the database. In a Stack Overflow survey in 2019, PostgreSQL came second in systems used by professionals (MySQL coming in first). Being such a strong database system, companies like Apple, Instagram, Twitch, and Reddit all use PostgreSQL.
Benefits:
SQL Standard: PostgreSQL follows more closely to the SQL standards and the other two relational database management systems mentioned in this blog. In fact, according to the official site, it meets 170 of the 179 standards of SQL
Integration: Because PostgreSQL is compatible with many programming languages, it makes it easier to migrate your database to another OS or integrate it with different tools. This also allows defining our own customized functions.
Big Community: PostgreSQL is a very old database management system. Because of that, it has had years to grow its community. This community has been able to deliver strong documentation and other online resources. And that makes troubleshooting easy.
“MySQL is the world’s most popular open source database. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more.
MySQL is an open-sourced software backed by Oracle Corporation. It is a scalable, fast and reliable database management system which can run on any operating system or server. Unlike SQLite, it follows a client/server architecture, meaning there is an additional server to communicate between applications and the database. MySQL databases work well for dynamic websites or as an embedded database for software, hardware, and appliances. It was designed to be easy to use and fast, rather than SQL compliant and this causes some limitations. MySQL also preforms well with read operations, but if you need heavy read-write operations you many want to use PostgreSQL instead. Despite this, MySQL is still the most popular RDBMS with companies like Shopify, Uber, and Amazon using it.
Benefits:
Security: Unlike SQLite, MySQL allow multi user management and allows you to specify users access. It comes with its own security script which grants you more control of access to your database and allows you to set password protection. Like SQLite and PostgreSQL, until every change operation of a transaction is successfully completed, that transaction isn’t cleared and the changes aren’t saved. This combined with the other layers of security MySQL provides, makes it a great choice for eCommerce businesses. This is because the money will not be deposited unless the entire process successfully completes. Upon failure all changes are reverted until the entire transaction is successful.
Easy to Use: Not only is it easy to install, but due to its popularity there is no shortage documentation and database administrators with experience using MySQL. There is also a plethora of third-party tools. And because MySQL started as an open-source software, there is a large community of developers to turn to for help.
