SQLite vs MySQL - Comparing 2 Popular Databases

When it comes to databases, there are a variety of options to choose from. Take this list of 8 popular databases for example. Each one has its own set of pros and cons and it is based on your requirements and preferences that will determine which one you choose. However, when it comes to two databases in particular: SQLite and MySQL; it's interesting to know what differences exist before choosing one.

In this guide, we're going to explore the differences between both of these databases so that you can make an informed decision over which one you should choose for your next project.

Relational database management systems (RDBMS)

SQLite and MySQL are both based on the relational database management system model. RDBMS are comprised of columns and rows to store data within a structured format. This subset of DBMS are deemed relational since the values within the table are related to one another. Furthermore, tables themselves can also be related.

These types of database systems are commonly used for storing financial records, manufacturing and logistical information, personnel data, and much more.

SQLite pros and cons

SQLite is a serverless solution that's self-contained, highly reliable, and full of features. As a self-contained SQL database engine, it can handle all sorts of data in a relatively simple manner. This is because it integrates directly with the application instead of having to install it somewhere and then have it connect with your app.

Pros

• Serverless which means it is simple to set up and zero configuration is required
• File-based system makes it very portable
• Great for development and testing

Cons

• Doesn't provide network access (i.e. accessing it from another machine) as it is serverless
• Not built for large-scale applications
• No user management

MySQL pros and cons

MySQL, on the other hand, is a database server. Therefore, you can't just directly integrate it with your app like SQLite, it requires that you install it and then connect it to your app. Your application then uses the TCP/IP protocol to send and receive data from the database. Below is a diagram (thanks to sqlitetutorials) which demonstrates the architectural differences between MySQL and SQLite.

Pros

• Powerful and able to handle high traffic sites
• Feature rich
• Many security features built-in
• User management capabilities

Cons

• Not as portable as SQLite
• Reliability issues
• Since being acquired by Oracle, development has slowed down

When to use SQLite vs MySQL

There are some pretty major differences between SQLite and MySQL. As we mentioned above, SQLite is serverless whereas MySQL is not. Therefore, if portability is of a concern, go with SQLite. This makes the transfer of all your database data much more efficient as it is stored in a single file. Furthermore, if your application requires that you write to disk locally you may also want to use SQLite.

However, if you require scalability in terms of the number of database queries required, MySQL is the better choice. If you want any real degree of concurrency or require higher levels of security as well as user permissions management, MySQL wins over SQLite.

Summary

Both SQLite and MySQL are highly used and tested relational database management systems. As stated in the name, SQLite is many times great as a lightweight solution for applications which do not require a full set of features, user permissions, etc. That being said, it's still a fantastic database system and holds its own for small-scale, development, and testing purposes. MySQL, on the other hand, is a more powerful database capable of handling much higher loads.