Common problems when using SQLite and PHP

I'm currently developing a social media application using PHP and sqlite. I don't know if I'll deploy with sqlite, but for development, it works well. I have two CVS sandboxes that I work in for this project. One of these in on my macbook (which comes with sqlite-enabled PHP) and an Ubuntu virtual machine. There are a few gotchas to be aware of when using sqlite in this kind of environment.

Write-protected directories

In other RDBM systems like mysql or postgres, the is a server process that is responsible for reading and writing to the database disk files. With sqlite, this isn't the case. If you're using sqlite through PHP, then the process owner running the PHP must be able to read and write to the location of the sqlite database file. This requirement gets a little more complex when you are running PHP through apache which has its own ideas about directory security.

In must apache setups, your PHP scripts will not be able to write in the web-accessible "document root". You will not be able to keep your sqlite database file in the document root. If you try, you will find errors on INSERT and UPDATES about "database file is locked." However, it is foolish from a security point of view to keep your database file in a web-accessbile directory anyway.

This particular problem hit me hard on Mac OS X. Once user directories are enabled in the apache configuration, your Sites directory becomes a document root. You won't be able to keep your sqlite files in that directory or any subdirectory under it. Instead, created a ~/tmp directory and keep the sqlite database file there.

SQLite version skew: apache/shell

Because sqlite is an embedded system, it is compiled into program you are using. If you are using PHP, you can run into the following issue. I use PHP from the command line all the time. When I create the database, I run a PHP script from the shell to do this. Unfortunately, the command line PHP and the version of PHP compiled into apache may not be the same. Further, the apache PHP may not be compiled with the same version of sqlite. This is the case on Mac OS X. What a mess!

To get around this, always create your sqlite databases through apache/PHP. You will run into far fewer issues this way.

Changing the schema requires an apache restart

Recall that apache is a pre-forking server. If you change the schema of your sqlite database while apache's running, you could get an error in PHP that "schema has changed." Whatever SQL statement you were attempting to run will fail.

From the "don't do that" school of medicine comes this technical advice. If you need to change the schema of an sqlite database, shut down apache first, update the database and restart apache.

I hope this post helps others avoid the mistakes I made.