Comparison of lightweight databases: SQLite, H2, and MySQL Embedded

For PHP programmers, SQLite can quickly build a database development environment and provide a database environment with ease, self-container, no configuration, and no independent services. All data is stored in one file. When MySQL is used as the final production platform, SQLite is an irreplaceable Development Environment solution. But is there really no better choice for compatibility? Well, there are only a few reasons: MySQL compatibility and support for hash indexes!

When looking for an alternative to SQLite, there are two options: H2 and MySQL Embeded. I am concerned that it can be used as conveniently as SQLite, but must be compatible with MySQL.

The following is a simple comparison of the three databases:

Compare items	SQLite	H2 database engine	MySQL Embedded
Footprint	350KiB	~ 1 MB	<2 MB
Authorization Protocol	Public domain	Dual: Modified MPL 1.1/EPL 1.0 (commercial friendly)	GPL 2.0 (only required cial friendly if not redistributed)
Self-container
Single file
Serverless
Server Mode
Zero Configuration
Transaction Processing
Index	(B-tree, R-tree, full-text)	(B-tree, tree, hash, full-text)	(B-tree, R-tree, hash, full-text)
MySQL compatibility		(But not 100%)
Compatible with other databases		MySQL, PostgreSQL, Oracle, MSSQL, DB2, HSQLDB and Derby
Encryption
In-Memory Database			(MEMORY storage engine)

 

It seems that H2 management is the easiest, so I tried some of the limitations found after H2 in PHP: the MySQL driver of Quercus cannot work well with the MySQL compatibility mode of H2, I must use the PDO driver of quercuz instead.

MySQL Embedded is 100% compatible with MySQL. I have not started the test. But there are also some uncertain questions. I don't know if I can distribute applications that contain MySQL Embedded. This may require commercial authorization.