SQLite vs MySQL

Damir Bulic -

Introduction

SQLite and MySQL are both among the most popular databases in the world. MySQL is currently #2 on the DB Engines ranking chart and close to #1. On the other hand, SQLite is an integral part of all phones and browsers. Even though Oracle is officially the most popular database, one can argue that more people use SQLite.

Architecture

There is a significant difference in the way SQLite and MySQL are designed. SQLite is an embedded database, while MySQL is a more common client/server database.

SQLite history and architecture

SQLite is used by applications having its driver integral part of the application. The database is on the local machine. This approach allows for significant performance gains as no network calls or data are copied from one application to another.

SQLite is not a toy database, but it is straightforward. Very fast when reading data, the database can be simultaneously read by many applications with no locking. However, SQLite writes data by a single application at a time. This means if you share the database with several applications and all of them change data, there will be a lot of waiting. SQLite locks the whole database for writing, so the performance of multiple writers is pretty bad as writes are performed sequentially. Today, applications are often multithreaded, and care needs to be taken to avoid writing from various threads at the same time to prevent SQLite throwing errors that database is locked.

SQLite has an unusual feature - flexible typing. You can write data of varying types in the same column. For example, you can have a table column named salary and write a number in one row, text in another, binary data in the third one. Historically, SQLite was a TCL extension, and writing data of variables without caring about the variable type was very useful. Nowadays, one can use the strict option when creating tables to disable flexible typing - we had to rely on checking constraints before.

MySQL history and architecture

MySQL is an open-source database that originated back in 1995. It was fast and straightforward out of the box for most applications.

When Monty Widenius sold MySQL to Sun Microsystems in 2008, he didn't anticipate Oracle buying Sun and getting complete control over MySQL2 years later. This leads to Monty leaving Sun and forking MySQL into MariaDB. People who don't want to have any ties with Oracle can switch to MariaDB, which is almost fully compatible with MySQL. However, MariaDB still didn't reach even 10% of MySQL's market share.

One of the strengths of MySQL is the ability to optimize storage and access per table by using different storage engines for tables with specific access patterns. For example, you don't need transactions when writing log items, and you can use the high-speed MyISAM engine. For short-lived intermediate tables, you can use the even faster Memory engine. Storing seldom-used data can be done using the Archive storage engine, with very fast writes and automatic compression of data. The default storage engine is InnoDB, a performant transactional engine.

Data types

MySQL has rich data type support, allowing fine-tuning for optimal space and performance. SQLite is very limited in data types, which usually isn't a problem as it isn't designed to store vast amounts of data.

SQLite types

SQLite internally uses just 4 data types: INTEGER, REAL, TEXT, and BLOB.

When creating a table, you can specify ANSI data type (VARCHAR, INT, etc.), and SQLite will allow it (considering it will generally save anything in a table anyway). You can even omit the data type altogether, something no other database allows.

MySQL types

Numeric Data Types

Exact values: INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT, DECIMAL, NUMERIC, BIT

Approximate values: FLOAT, DOUBLE

Date and Time Data Types

DATE, DATETIME, TIMESTAMP, TIME, YEAR

String Data Types

CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET

Spatial Data Types

MySQL has spatial data types that correspond to OpenGIS classes

Single values: GEOMETRY, POINT, LINESTRING, POLYGON...

Collections of values: MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION...

To work with spatial types, queries can use both the Well-Known Text (WKT) and Well-Known Binary (WKB) format.

JSON Data Type

MySQL natively supports JSON. This allows efficient access, validation of JSON data, and optimized storage.

Portability

SQLite has a single-file database with a stable binary format. New features added to SQLite are not changing the underlying data format. This allows for effortless data portability simply by copying the data file.

MySQL databases are copied by using its mysqldump utility. When backing up large InnoDB tables, one should use a --single-transaction parameter to avoid locking the table for the backup duration. Additionally, one can set up a replication using binary logs, but that's a more involved process.

And, of course - we are a database migration company! You can use our tools, which are arguably very good. As databases SQLite works with are usually under 1TB in size, we recommend Full Convert to migrate SQLite to MySQL, MySQL to SQLite, or any other combination of over 40 supported databases. Our customers often use our software to copy from one MySQL database to another, as that's much faster than resorting to the standard backup and restore process.

Security

SQLite, by default, has no concept of security. It doesn't use a username/password to authenticate the user. It doesn't encrypt the data. As the database file is local and the local machine is usually firewalled from outside access, it isn't a problem in many cases. When it is, SQLite Encryption Extension is available for purchase from the SQLite team.

Feature set

SQLite features

SQLite has very limited programmability. It does support SQL views and triggers, though.

There is no concept of stored procedures or functions. One would use stored procedures to execute complex business logic on the database server and avoid multiple network hops and intermediate data transfer over the wire. SQLite runs on the same machine where the database is. Another reason to use procedures is to have a single source of truth for the business logic, avoiding multiple applications possibly using obsolete logic. SQLite is usually tied to a single application.

One can only create SQLite functions by programmatically using its C API. The advantage here is that you can write the code inside your application, but that's a lot of complexity if you need something simple.

One of the essential advantages of SQLite, something that is not generally expected with embedded databases, is bulletproof reliability. SQLite testing is incredible; the team has 640x more code invested in the testing than the actual database engine! SQLite is even used inside the aviation software. More details on their testing are available here.

MySQL features

Compared to SQLite, MySQL has a lot of features. Not so much compared to, say, PostgreSQL.

It's a proper client/server relational database with users, roles, and programmability. It supports enormous databases, while SQLite is not recommended for something much larger than 1TB in size.

Many simultaneous users can use it without worrying about writers blocking other writers.

There is significant availability of 3rd-party solutions to enhance its functionality - many of them open-source. Some examples: ProxySQL adds query caching and logging, or you can use Redis for a caching layer, or you can use Monyog to monitor MySQL performance.

MySQL database drivers are available for all popular programming languages and developers.

Scaling can be achieved by adding more read replicas and table sharding (partitioning table data).

When to use

Both SQLite and MySQL are very good, and each one has significant strengths. Of course, depending on what you need precisely, choosing the wrong database for the project will bite you.

Choose SQLite if

  • You are deploying your application and want to avoid asking the user to install a full-blown database server
  • You need a single state database for your simple application, to be used by a single user
  • The database size won't reach terabytes
  • You don't care about the security of the data - knowing someone could trivially copy and open that database anywhere is fine

Choose MySQL if

  • Your database will be accessed by multiple applications and users
  • You expect the database to grow and potentially reach terabytes in size continuously
  • You want to use SQL stored procedures and functions
  • You want to monitor the database state and load

Conclusion

I hope this article will help you decide between SQLite and MySQL for your project.

❤️ Love this post?

Follow me on Twitter and let me know if you would like me to write about something else you need or find some factual errors in my writing.