SQLite is a popular choice for PHP programmers to kick-start their development. It's easy, self-contained, requires no configuration, no server and all the data is kept neatly in just one file. Then later on, the application is commonly migrated to a production DBMS (e.g. MySQL). There have not been any alternatives for SQLite when developing in PHP with MySQL as a target platform. But then again, why would you want an alternative for SQLite? Well, to name a few reasons: MySQL compatibility and support for hash indices. And that's not all! Keep reading to see why.
When looking for alternatives to SQLite, I came across two suitable candidates: H2 database engine and a MySQL embedded version. My criteria were that the alternative should be as easy to use as SQLite and compatible with MySQL, based on two reasons. First, I wanted it to be compatible with MySQL so that I can keep using that easy self-contained database for development even after the application has gone into production. Second, an easy to setup database is great for bringing new developers up to speed with development as fast as possible.
Let's start with the good stuff. Below is a comparison of SQLite, H2 database engine and MySQL embedded version.
When looking for alternatives to SQLite, I came across two suitable candidates: H2 database engine and a MySQL embedded version. My criteria were that the alternative should be as easy to use as SQLite and compatible with MySQL, based on two reasons. First, I wanted it to be compatible with MySQL so that I can keep using that easy self-contained database for development even after the application has gone into production. Second, an easy to setup database is great for bringing new developers up to speed with development as fast as possible.
Let's start with the good stuff. Below is a comparison of SQLite, H2 database engine and MySQL embedded version.
| Compare | SQLite | H2 database engine | MySQL Embedded |
|---|---|---|---|
| Footprint | 350KiB | ~1MB | <2MB |
| License | Public domain | Dual: Modified MPL 1.1 / EPL 1.0 (commercial friendly) | GPL 2.0 (only commercial friendly if not redistributed) |
| Self-contained | ✔ | ✔ | ✔ |
| Single file | ✔ | ✔ | ✖ |
| Serverless | ✔ | ✔ | ✖ |
| Server-mode | ✖ | ✔ | ✔ |
| Zero-configuration | ✔ | ✔ | ✔ |
| Transactions | ✔ | ✔ | ✔ |
| Indices | ✔ (B-tree, R-tree, full-text) | ✔ (B-tree, tree, hash, full-text) | ✔ (B-tree, R-tree, hash, full-text) |
| MySQL compatibility | ✖ | ✔ (but not 100%) | ✔ |
| Compatibility with other DBMS | ✖ | ✔ MySQL, PostgreSQL, Oracle, MSSQL, DB2, HSQLDB and Derby | ✖ |
| Encryption | ✖ | ✔ | ✖ |
| In-memory databases | ✔ | ✔ | ✔ (MEMORY storage engine) |
To me it looks like H2 database is the easiest to manage. It requires no server and everything is put neatly in one file, which makes back-upping and sharing databases among developers easier. It even has a nice extra feature over MySQL: encryption of data files (though I don't see a direct need for that during development for me personally).
I have experimented with PHP and H2 before and ran into some limitations (Quercus' MySQL driver doesn't play well yet with H2's MySQL compatibility mode so I have to use the Quercus' PDO driver instead). I haven't tried embedding MySQL yet, which is what I am going to try next.
The embedded version of MySQL has the big benefit of being 100% MySQL compatible, but it also has an uncertainty. I am not sure if it's okay to redistribute my application (containing embedded MySQL) among developers. Another limitation could be that I would have to buy a commercial license for MySQL should I choose to distribute embedded MySQL to customers (vs. customers setting up their own MySQL server). Though, it is okay to use MySQL embedded version for a website hosted by myself (it's not technically distributed in that case).
For now, I have not decided yet whether I will stick with H2 or switch to the embedded version of MySQL. I am going to give both a try and I will keep you updated via this blog!
I have experimented with PHP and H2 before and ran into some limitations (Quercus' MySQL driver doesn't play well yet with H2's MySQL compatibility mode so I have to use the Quercus' PDO driver instead). I haven't tried embedding MySQL yet, which is what I am going to try next.
The embedded version of MySQL has the big benefit of being 100% MySQL compatible, but it also has an uncertainty. I am not sure if it's okay to redistribute my application (containing embedded MySQL) among developers. Another limitation could be that I would have to buy a commercial license for MySQL should I choose to distribute embedded MySQL to customers (vs. customers setting up their own MySQL server). Though, it is okay to use MySQL embedded version for a website hosted by myself (it's not technically distributed in that case).
For now, I have not decided yet whether I will stick with H2 or switch to the embedded version of MySQL. I am going to give both a try and I will keep you updated via this blog!
RSS Feed
