SQLite MySQL

SQLite vs MySQL

Introduction

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

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 there are no network calls, no data being copied from one application to another.

SQLite is not a toy database, but it is very simple. 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 by 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 multiple threads from the same time to avoid SQLite throwing errors that database is locked.

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

MySQL history and architecture

MySQL is an open-source database originated back in 1995. It was simple and, for vast majority of applications, fast out of the box.

When Monty Widenius sold MySQL to Sun Microsystems in 2008, he didn't anticipate Oracle will buy Sun and get full control over MySQL just 2 years later. This lead 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 at this time still didn't reach even 10% of the 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, when writing log items, you don't need transactions, and you can use the very fast 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 huge amounts of data.

SQLite types

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

When creating a table, you can specify any 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...

Queries can use both Well-Known Text (WKT) and Well-Known Binary (WKB) format to work with spatial types.

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 done so in such a way not to change the underlying data format. This allows for very simple 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 --single-transaction parameter to avoid locking the table for the duration of the backup. 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, or 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 thatt'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 username/password to authenticate user. It doesn't encrypt the data. As the database file is local and the local machine usually is firewalled from outside access, for many cases it isn't a problem. 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. A reason one would use stored procedures is to execute complex business logic on the database server and avoid multiple network hops and transfer of intermediate data over the wire. SQLite runs on the same machine where 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 programatically using its C API. The advantage here is that your can write the code inside your application, but that's a lot of complexity if one needs something simple.

One of the important 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 to the testing than the actual database engine! SQLite is even used inside the aviation software. More details on their testing is 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 huge databases, while SQLite is not recommended for something much larger than 1TB in size.

It can be used by many simultaneous users without worrying about writers blocking other writers.

There is a 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 my adding more read replicas, and by use of table sharding (partitioning table data).

When to use

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

Choose SQLite if

Choose MySQL if

Conclusion

I hope this article makes it crystal clear if you should use SQLite or MySQL for your project.

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

Damir
Damir Bulic
Spectral Core CEO
On this page: