SQLite and MySql are database technologies. They are used to store data, using a relational model, manipulated by the SQL language. SQLite and MySQL are very different technologies designed for different purposes, so comparing them is a bit like comparing oranges and apples. SQLite is great for embedded applications like mobile apps, and IoT edge, whereas MySQL is great for client server and multiuser, high transaction systems.
Here's a summary table showing how SQLite and MySQL compare at a high level.
| Feature | SQLite | MySQL |
|---|---|---|
| Architecture | Embedded Architecture. SQLite is one of the most widely used software systems in the world due to its suitability in embedded, mobile and IoT applications. SQLite is very compact and resource efficient. It is also built using ubiquitous and generic C libraries, making it highly portable across OS platforms. The entire database is stored in a single file. | Server Architecture. MySQL and it's Open Source offshoot MariaDB use the 'server' architecture similar to traditional databases like Oracle and SQLServer. The database runs in a data center on a server machine and is accessed via the network from the client application. |
| SQL92 Support | Excellent | Excellent |
| License Cost | SQLite is fully Open Source and can be used at no cost. | MySQL is licensed by Oracle. MariaDB is a highly compatible Open Source fork of MySQL and can be used at no cost. |
| Multi User | SQLite can support multiple threads and processes and therefore multiple users accessing the database. It uses a single write, multi read concurrency model, so only one database write can be in progress at a time. These transactions occur very quickly so it works for small and medium transaction loads. SQLite supports atomic transactions. | For high transaction loads with thousands of writes per second MySQL is a good choice. MySQL supports transactions. |
| Management Tools | SQLite provides a simple command line tool to interact with a SQLite database using SQL statements. SQLite Workbench is an Open Source project that provides a visual GUI experience to manage SQLite databases. | MySQL also provides a fully feature command line to manage databases and execute SQL statements. MySQL Workbench is a GUI based management tool with a visual approach to database design. |
In this SQLite vs MySQL comparison I dig into 9 different aspects of these technologies.
When trying to determine whether to choose MySQL, MariaDB, MySQL embedded or SQLite, consider the following,
The following diagram is a slide taken from Dr. Richard Hipp's SQLite presentation from the "Databaseology Lectures" at Carngie Melon University in 2015. Note that SQLite has a query optimizer implemented in the Next Generation Query Planner.
MySQL allows you to swap storage engines which enables specific functionality. Here is a list of storage engines
The following diagram shows the major components involved in handling SQL statements.
The query cache is deprecated as of version 5.7 and removed in version 8. The query cache does not scale well with high throughput workloads on multi core machines.
SQLite and MySQL are both very capable technologies for the purposes they were created to achieve. Their limitations are unlikely to ever be exceeded as the system hardware and OS limitations will likely be hit first.
This table summarizes how SQLite and MySQL compare on DB features.
| DB Feature | SQLite | MySQL |
|---|---|---|
| Licensing | Open Source | MySQL is licensed by Oracle, but MariaDB which is a forked version of MySQL is open source |
| Maximum Database Size | 140TB | With partitioning the database size is virtually unlimited. Tables can be a maximum of 64TB without partitioning and rows can be up to 4GB in size. |
| Stored Procedures | SQLite is an embedded database so it doesn't make sense for it to have a server side execution language. | MySQL includes a DB server execution language called MySQLScript. |
| Database Encryption | Database encryption is not supported out of the box, but it is possible to extend SQLite functionality. SQLCipher is a SQLite extension that will encrypt your database with a 256bit AES key. | MySQL fields can be encrypted via the AES_ENCRYPT and AES_DECRYPT functions |
| Concurrency | SQLite only allows a single database write at a time. It fully supports multithreaded applications and if 2 threads are attempting to write at the same time, one will have to wait until the other is completed. This is typically not an issue, but if you need more advanced concurrency you look into addons like SQLite.swift, FMDB or GRDB. | MySQL concurrency adds a level of sophistication. MySQL allows write locks at a finer grained levels like at the table and even row level. |
| Full Text Search | SQLite support full text search through the fts5 module. | MySQL provides full text search features natively. The InnoDB and MyISAM storage engines support full text search, but it is not supported by all storage engines. |
Both MySQL and SQLite strive to support the ANSI SQL92 standard but neither fully implements. The following table shows the SQL92 language features that may be missing from these database technologies.
| SQL Command | SQLite | MySQL |
|---|---|---|
| CREATE TABLE CHECK constraint | Allowed but not enforced | Allowed but not enforced |
| CREATE TABLE 'FOREIGN KEY' | Allowed but ignored | Supported |
| FOR EACH STATEMENT | Will be executed as FOR EACH ROW | Only supports row level triggers |
| Updateable Views | Views are read only but INSTEAD OF can be used | Updateable views are supported |
| INSTEAD OF trigger on tables | Not supported | MySQL does not support INSTEAD OF triggers |
| RIGHT OUTER JOIN | Not supported | MySQL supports RIGHT OUTER JOIN |
| FULL OUTER JOIN | Not supported | MySQL supports FULL OUTER JOIN |
| ALTER TABLE | SQLite supports the RENAME TABLE, ADD COLUMN, RENAME COLUMN, and DROP COLUMN variants of the ALTER TABLE command. The ALTER TABLE operations such as ALTER COLUMN, ADD CONSTRAINT, are omitted. | MySQL allows column changes through the 'ALTER TABLE - MODIFY' SQL statement. MySQL also supports 'ALTER TABLE - ADD CONSTRAINT'. |
| GRANT and REVOKE | Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine. See sqlite.org SQL Features | MySQL supports GRANT and REVOKE |
Both SQLite and MariaDB have a long community history of contribution. The following table compares their statistics on OpenHub.
| OpenHub Stat | SQLite | MySQL |
|---|---|---|
| Number of Commits | 46,884 | 191,868 |
| Lines of Code | 670,122 | 2,245834 |
| Language | C | C++ |
| First Commit | May 2000 | July 2000 |
| More info Link | SQLite OpenHub | MariaDB OpenHub |
Because SQLite is embedded into an application it is up to the application to determine what can be accessed by the user of the application. It is still important for the SQLite database to protect itself from exploits, especially if it is being used to open databases that have been shared from external parties.
Overall SQLite has very few known vulnerabilities. These are generally issues that result in buffer overflows and segmentation faults. These vulnerabilities leave SQLite open to denial of service attacks.
MySQL supports the features you would expect from any Server based database system. Including Users and Groups, passwords and access logging, account limits and locking. Authentication plugins can be used to integrate with identity services.
Upon installing MySQL it is advisable to harden the installation by setting a password for the root account, disallowing remoted connections via the root account, removing anonymous accounts and removing the test database and related privileges.
There is a command line tool that will apply all these changes on a MySQL installation, the command to run this tool is as follows:
sudo mysql_secure_installationSQLite and MySQL are very different technologies and while they are both relational database solutions based on SQL that is where the similarities end.
| Use Case | SQLite | MySQL |
|---|---|---|
| Embedded System | Well suited | MySQL is poorly suited to embedded use, look at MySQL Embedded instead. |
| Web Sites | Well suited for lower traffic sites. Remember that sqlite.org uses Sqlite to serve its dynamic content and this site serves 400K pageview per day. | MySQL was designed for web site content and is a great choice for this use case. |
| High Transaction | SQLite only allows a single database write at a time and uses file system level locking to prevent contention. SQLite can handle hundreds of transactions per minute, but if you have a high number of connections all trying to write many table inserts and updates you might want to consider a server database. | MySQL is very well suited to high transaction loads. |
| Large Datasets | Since the entire SQLite database is stored in one file, a database that goes beyond a gigabyte is hitting the limit for SQLite. | MySQL can handle very large datasets. |
SQLite isn't so much scalable as it is tuneable. You can't create SQLite clusters and scale them according to demand. There are a few tweaks that you can make to improve the performance of your SQLite database like using Write-ahead logging.
MySQL provides high availability features like clustering which enables horizontal scalability by increasing or decreasing the size of the cluster.
Database replication can be used to balance the load across multiple servers.
SQLite is very easy to install and maintain. There are a number of tools that assist with DB tasks, the are documented on the sqlite.org site but in summary they are:
There are 2 key areas of concern when upgrading SQLite. Library changes and SQLite database file format changes. Upgrading involves replacing the SQLite library and updating function call to compile against it. SQLite will automatically update the database file format so it is advisable to make a backup before upgrading.
Backing up a SQLite database is a simple as backing up a file.
SQLite is very reliable, but data corruption is still a possibility. There are a number of tools that can help if a SQLite database has been corrupted. These tools can easily be used by novice users and are straightforward. Typically they will repair a corrupted database and allow you to view and export data.
Some tools include:
Managing a MySQL database is like administering any server based DBMS. There is a lot to it, but MySQL provides many features and tools to make this easier.
Preparing for a MySQL upgrade is the most involved part of an upgrade. It requires ensuring that no obsolete or deprecated features are being used by any schemas. The MySQL documentation outlines all of the checks needed to perform before advancing to the actual upgrade.
Once you database is ready to upgrade it is a matter of running the appropriate installer for your platform. Docker containers running MySQL are easy to upgrade and allow you to upgrade into a new container with the new version of MySQL and mounting the storage into the new container.
Linux installations can be upgrade using package managers like apt and yum, and windows installations are upgraded via a Windows installer.
Backing up a MySQL database can be a simple as copying files, per the following steps
There are a number of tools that can be used to backup MySQL databases as well. Options include:
SQLite is compact and performs well in embedded environments and systems with limited processing power. The compiled SQLite library is a tiny 700kb and runs in 4MB of memory. You can use SQLite on a controller board like an Arduino.
The system requirements to run MySQL vary based on how it is configured, how big the database is and how much demand is put on the server.
You will need at least 1GB of RAM to run MySQL and about 5MB per connected user.
SQLite is free to use and the forum provides access to an active community if you need assistance. However an annual maintenance subscription is available at $1500/year. The AMS allows you to have questions answered privately instead of on a public forum. Access to high priority support is available and runs between 8K to 50K per year depending on your needs.
A SQLite Warranty of title and perpetual right-to-use for the source code is available for a one time $6000.
MySQL Standard is licensed by Oracle at $2000 per year. This included 24x7 support, unlimited support tickets, access to the knowledge base and maintenance updates.
MySQL Enterprise is licensed at $5000 per year and MySQL Cluster Carrier Grade at $10,000 yearly. While the InnoDB and MyISAM storage engines are available to standard and enterprise customers. The NDB storage engine is only available to Cluster Carrier Grade customers.
MariaDB is the Open Source equivalent to MySQL and is free to user. MariaDB no longer supports the NDB storage engine and therefore does not support clustering.
SQLite and MySQL are relational database technologies supporting SQL. SQLite is suited to mobile and IoT applications where hardware resources like CPU and memory are limited. MySQL is a server based technology suited to high transaction client-server systems. MySQL was designed to support web sites with dynamic content and is the content database supporting millions of Wordpress sites.
There are situations where either can be used and both would be suitable. The sqlite.org site uses SQLite for its dynamic content and it serves 400K pageviews per day.
See my other DB content articles:
Photo by Philipp Berndt on Unsplash