Why I Wish I Knew the Difference Between SQLite & MySQL Earlier…

Originally, I decided that I wanted to write about the differences between SQLite and MySQL, which are different variations of relational database management systems (RDBMSs). The topic was relevant to what we were working on in class, so why not give it a shot? But as I quickly found when I began researching, being a beginner programmer and all, I essentially brought nothing new to the table when it comes to understanding, let alone explaining, intricate technical differences between MySQL & SQLite.

I have no horror stories about how SQLite broke my heart, and why I’ll never use it again. But I do have an example of a time where I looked like a jerk in a professional environment because I made assumptions without understanding the basic differences between the two tools. So, I feel like the best way I can be helpful to someone else is to demonstrate a real-life example of why even non-programmers, which I was not when this occurred, should understand the difference between SQLite and MySQL, at least at a fundamental level.

Prior to arriving at Flatiron, I worked as a project manager at a software development company for almost two years, and before that was a co-founder of a company based on a iOS mobile application. When I was working on the mobile app in late 2016, I remember running into one occasion where understanding the difference between MySQL and SQLite would have allowed me to handle a situation better.

We were looking to hire a software development team and were reviewing a proposal from a company that we had liked. The company was based out Ahmedabad, India and despite being far away, had highly communicative and professional Project Managers. Objectively, and probably not-surprisingly, the cost of outsourcing software development to an Indian company was and is significantly cheaper than it is anywhere else in the world. That includes South America and Eastern Europe, which are also common places to outsource software development labor to. With that said, I was a bit skeptical when it came to reviewing their proposal, but they were undoubtedly the best company we found that fell within our price range.

Now fast-forward six months later to when we were 3 sprints (see below**) into our app development, with no way of turning back. It was at this point that my partner and I decided to hire a different professional developer (on Upwork — freelancer site that’s really really great for software dev projects, I’ve hired many people and companies, as well as interviewed dozens through it) to audit our code. As a part of the audit, we provided our new advisor, Scott, with all of the materials to access our source code and databases, as well as all of the associated documentation the company had created, including their methods, objects, API keys, etc.

Understandably, Scott had questions regarding the status of our database. The very first of which was “What database is your app using?”, which we confidently responded with “SQLite!” (duh, right?) because I had reviewed the original proposal recently. His response was “O.K. but I’m assuming that’s just for testing,” to which we assured him, without any idea of what we were talking about, that no, SQLite was the database our developers planned on using through production. We were quickly made aware that if this was really the case, it was going to be an expensive mistake and fix moving forward.

My partner and I frantically jumped on a call with our Project Manager (PM), fearing that we had been duped, or perhaps “gotten what we paid for”. When we asked why the developers were using SQLite instead of a “real database”, the PM was a bit confused by the question, combined with a genuine language barrier, responded with something along the lines of: “Of course the production database is going to be in MySQL and written in PHP, SQLite is just for testing…”. So I had come in really hot, guns-blazing ready to blame someone for a mistake based on a proposal I had committed to, without understanding how blatantly obvious it is that a company would not use SQLite for a production-level mobile application.

The fact that we were the paying clients was probably the only reason our Project Manager did not give us any crap for being so accusatory. But my partner and I felt sufficiently dumb afterwards, and rightfully so. Our intentions were good, which was to make sure that we were paying for a product that was being built within reasonable industry standards. But instead we felt the need to have to defend ourselves without knowing everything about the situation, and ultimately looked pretty dumb as a result. Now next time I’m in a position of evaluating a software development proposal, I’ll know that SQLite will obviously only be used during the development stage and ditched come go-live time.

With that said, check out some quick and important differences between MySQL & SQLite written by experts who know a lot more than I do. The below information comes directly from the best sources I could find about the comparisons.

Sources: Digital Ocean, Academind YouTube Video

MySQL

Advantages of MySQL

• Popularity and ease of use: As one of the world’s most popular database systems, there’s no shortage of database administrators who have experience working with MySQL. Likewise, there’s an abundance of documentation in print and online on how to install and manage a MySQL database, as well as a number of third-party tools — such as phpMyAdmin — that aim to simplify the process of getting started with the database.
• Security: MySQL comes installed with a script that helps you to improve the security of your database by setting the installation’s password security level, defining a password for the root user, removing anonymous accounts, and removing test databases that are, by default, accessible to all users. Also, unlike SQLite, MySQL does support user management and allows you to grant access privileges on a user-by-user basis.
• Speed: By choosing not to implement certain features of SQL, the MySQL developers were able to prioritize speed. While more recent benchmark tests show that other RDBMSs like PostgreSQL can match or at least come close to MySQL in terms of speed, MySQL still holds a reputation as an exceedingly fast database solution.
• Replication: MySQL supports a number of different types of replication, which is the practice of sharing information across two or more hosts to help improve reliability, availability, and fault-tolerance. This is helpful for setting up a database backup solution or horizontally scaling one’s database.

Disadvantages of MySQL

• Known limitations: Because MySQL was designed for speed and ease of use rather than full SQL compliance, it comes with certain functional limitations. For example, it lacks support for FULL JOIN clauses.
• Licensing and proprietary features: MySQL is dual-licensed software, with a free and open-source community edition licensed under GPLv2 and several paid commercial editions released under proprietary licenses. Because of this, some features and plugins are only available for the propriety editions.
• Slowed development: Since the MySQL project was acquired by Sun Microsystems in 2008, and later by Oracle Corporation in 2009, there have been complaints from users that the development process for the DBMS has slowed down significantly, as the community no longer has the agency to quickly react to problems and implement changes.

When To Use MySQL

• Distributed operations: MySQL’s replication support makes it a great choice for distributed database setups like primary-secondary or primary-primary architectures.
• Websites and web applications: MySQL powers many websites and applications across the internet. This is, in large part, thanks to how easy it is to install and set up a MySQL database, as well as its overall speed and scalability in the long run.
• Expected future growth: MySQL’s replication support can help facilitate horizontal scaling. Additionally, it’s a relatively straightforward process to upgrade to a commercial MySQL product, like MySQL Cluster, which supports automatic sharding, another horizontal scaling process.

When Not To Use MySQL

• SQL compliance is necessary: Since MySQL does not try to implement the full SQL standard, this tool is not completely SQL compliant. If complete or even near-complete SQL compliance is a must for your use case, you may want to use a more fully compliant DBMS, like PostgreSQL.
• Concurrency and large data volumes: Although MySQL generally performs well with read-heavy operations, concurrent read-writes can be problematic. If your application will have many users writing data to it at once, another RDBMS like PostgreSQL might be a better choice of database.

SQLite

Advantages of SQLite

• Small footprint: As its name implies, the SQLite library is very lightweight. Although the space it uses varies depending on the system where it’s installed, it can take up less than 600KiB of space. Additionally, it’s fully self-contained, meaning there aren’t any external dependencies you have to install on your system for SQLite to work.
• User-friendly: SQLite is sometimes described as a “zero-configuration” database that’s ready for use out of the box. SQLite doesn’t run as a server process, which means that it never needs to be stopped, started, or restarted and doesn’t come with any configuration files that need to be managed. These features help to streamline the path from installing SQLite to integrating it with an application.
• Portable: Unlike other database management systems, which typically store data as a large batch of separate files, an entire SQLite database is stored in a single file. This file can be located anywhere in a directory hierarchy, and can be shared via removable media or file transfer protocol.

Disadvantages of SQLite

• Limited concurrency: Although multiple processes can access and query an SQLite database at the same time, only one process can make changes to the database at any given time. This means SQLite supports greater concurrency than most other embedded database management systems, but not as much as client/server RDBMSs like MySQL or PostgreSQL.
• No user management: Database systems often come with support for users, or managed connections with predefined access privileges to the database and tables. Because SQLite reads and writes directly to an ordinary disk file, the only applicable access permissions are the typical access permissions of the underlying operating system. This makes SQLite a poor choice for applications that require multiple users with special access permissions.
• Security: A database engine that uses a server can, in some instances, provide better protection from bugs in the client application than a serverless database like SQLite. For example, stray pointers in a client cannot corrupt memory on the server. Also, because a server is a single persistent process, a client-server database cancontrol data access with more precision than a serverless database, allowing for more fine-grained locking and better concurrency.

When To Use SQLite

• Embedded applications: SQLite is a great choice of database for applications that need portability and don’t require future expansion. Examples include single-user local applications and mobile applications or games.
• Disk access replacement: In cases where an application needs to read and write files to disk directly, it can be beneficial to use SQLite for the additional functionality and simplicity that comes with using SQL.
• Testing: For many applications it can be overkill to test their functionality with a DBMS that uses an additional server process. SQLite has an in-memory mode which can be used to run tests quickly without the overhead of actual database operations, making it an ideal choice for testing.

When Not To Use SQLite

• Working with lots of data: SQLite can technically support a database up to 140TB in size, as long as the disk drive and filesystem also support the database’s size requirements. However, the SQLite website recommends that any database approaching 1TB be housed on a centralized client-server database, as an SQLite database of that size or larger would be difficult to manage.
• High write volumes: SQLite allows only one write operation to take place at any given time, which significantly limits its throughput. If your application requires lots of write operations or multiple concurrent writers, SQLite may not be adequate for your needs.
• Network access is required: Because SQLite is a server-less database, it doesn’t provide direct network access to its data. This access is built into the application, so if the data in SQLite is located on a separate machine from the application it will require a high bandwidth engine-to-disk link across the network. This is an expensive, inefficient solution, and in such cases a client-server DBMS may be a better choice.

**A “sprint” is a pre-defined set of functionality that is expected to be implemented into your software project by a particular date so that you and your team can anticipate testing and when certain features will be completed. Using Facebook as an example, “In Sprint 1, on June 2nd, we will have completed the login and create and edit profile features, and then in Sprint 2 , on August 3rd, we’ll implement posting on other users’ walls”.