Mean DB

Please be aware of the following cause. When most of us think of PHP, we think of developing for the web. In most cases we will be using a LAMP-based server setup, with our favorite web scripting language contributing the Person. In most cases, the M is filled in with MySQL, although PHP does include support for several other database systems. Is a full-blown database server even necessary for most PHP applications? What about a data-driven website like a blog or a simple family photo album or message board? In these and may other cases, the small, simple, and extremely powerful SQLite may be all you need. Let's take SQLite out for a test drive and see how it performs, find some of the quirks you might run in to, and how to get the most use out of it.

Warming It Up

SQLite has been a PHP staple since version 5, and is available as a PECL extension for PHP 4. There is also SQLite support in PDO; in fact, the PDO SQLite extension is the only way you can get support for SQLite 3. There are already some covering the SQLite 2 extension, so we are going to focus exclusively on PDO. This will also allow us to take advantage of SQLite.

Warning: SQLite 3 uses a new (and incompatible) file format to store its databases. You can read SQLite 2 databases with SQLite 3, but the file will be converted to the SQLite 3 format. Once this is done you can't undo it. You have been warned!

PDO is standard in PHP 5.1, so if you're using the latest PHP release you have everything you need. The extension will be built by default, so unless you want Unicode support no extra configure options are needed. There is one caveat to building SQLite with PHP. If you are building the standard SQLite extension alongside PDO SQLite, then you should build them both shared or both not shared since standard SQLite now depends on PDO SQLite for some functionality. You should also enable the PDO extension before the SQLite extension in php.ini

SQLite is also light on configuration options, because there aren't any! Two options that may affect you opening or creating a SQLite database file do exist, though. SQLite is safe_mode and open_basedir aware, which means if PHP is restricted by either option within the file system, you may get the following error:

safe_mode/open_basedir prohibits opening /tmp/litedb.sq3

If you ever see this error you will either have to adjust your safe_mode/open_basedir settings or use a different directory/filename for you SQLite database.