SQLite: Lean, Mean DB Machine
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 P. 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 good articles covering the SQLite 2 extension, so we are going to focus exclusively on PDO. This will also allow us to take advantage of SQLite 3's new features.
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 (see the SQLite PHP documentation for more details).
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.
Test Drive
Now that we have the configuration basics out of the way, let's connect to a new SQLite database and try it out. PDO provides a standard interface to supported databases, and connecting is no different. The only unique part of connecting to a database is the DSN, which for SQLite consists of the driver prefix sqlite and the path to the database file:
$dbh = new PDO('sqlite:/path/to/my/sqlite.db3');
It doesn't matter what the database file is called (or what extension it has), just as long as PHP/SQLite can find the file and open it. I use the db3 extension to differentiate that this is a SQLite 3 database. No username or password is required here since there is no server to log in to.
(SQLite 2 Users: PHP 5.1 provides a SQLite 2 driver for PDO; just use sqlite2 as the driver prefix instead. This is provided for backwards compatibility when you don't want to convert a database to SQLite 3)
From here you can do anything you would normally do with PDO. If you just created a new database, you probably should create a table to store data in:
$dbh->exec("CREATE TABLE table1 (id INTEGER PRIMARY KEY, col1 TEXT UNIQUE, col2 TEXT)");
Now that we have a table, we can insert some data into our table using a prepared statement:
$stmt = $dbh->prepare("INSERT INTO table1 (col1, col2) VALUES (:col1, :col2)"); $stmt->bindParam(':col1', $col1_val); $stmt->bindParam(':col2', $col2_val);
// insert our data $col1_val = "sqlite"; $col2_val = "rocks"; $stmt->execute();
$col1_val = "this needs to be unique"; $col2_val = "this doesn't!"; $stmt->execute();
You can check the results with a simple SELECT query:
foreach ($dbh->query('SELECT * FROM table1', PDO::FETCH_ASSOC) as $row) { print_r($row); }
We now have two rows in our database:
Id
col1
col2
1
sqlite
rocks
2
this needs to be unique
this doesn't
Two things you should notice here if you haven't picked them up already. First, the id column has incremented values. In SQLite, if you set a field to INTEGER PRIMARY KEY, it will automatically be an AUTO INCREMENET column as well. The second thing to notice is the apostrophe for the second row we inserted in col2. PDO automatically did the right thing and made sure the data inserted was properly escaped when the query was executed.
We've covered the basics on creating a SQLite database and how to do a few standard queries, so now let's go over some of the unique features that make SQLite fast...and behave in strange ways if you're not careful.
(Not So) Standard Features
One of the great features of using SQLite for storage instead of a flat file is that you can use standard SQL to manage your data as if you were using a more sophisticated database management system. However, there are some key features of SQL 92 that SQLite does not support. The most obvious is that SQLite lacks most of the standard SQL data types. To keep the library lean and mean, SQLite only supports the most basic data types that would cover all data you would see in a RDBMS. Here is the list of data types SQLite 3 uses:
- NULL: the values is simply a null value
- INTEGER: a signed integer; it is stored in 8, 16, 24, 32, 48 or 64 bits, depending on the magnitude of the number
- REAL: an 64 bit IEEE floating point number
- TEXT: any length text string; can be in ISO-8859-1, UTF-8 or UTF-16 encodings (UTF-16 will be either big or little endian, depending on your machine)
- BLOB: a standard binary object that is stored exactly as input
Even though SQLite doesn't support all of the standard SQL data types, it does support the syntax of making a column VARCHAR(18), BIGINT, or DOUBLE. Just be aware that internally, SQLite is using one of the five types above to store your data. A column that is VARCHAR(18) is not going to store 18 characters for each row of data, neither will it complain if you insert a 19+ character string. This will also affect how your data is returned in a SELECT query (like the lengths to VARCHAR or NUMERIC) you set will be ignored.
There are some important and powerful SQL commands and statements that aren't supported, either. ALTER TABLE is limited to renaming a table (RENAME TABLE) and adding a column (ADD COLUMN). RIGHT and FULL OUTER JOIN are not implemented, either. More advanced features that are either partially supported or completely missing are foreign key constraints, triggers, nested transactions, and writing to views. GRANT and REVOKE are also missing, since they are unnecessary given SQLite's current implementation (no login required!). For most work though, SQLite is more than powerful enough to handle whatever data you throw at it.
The last feature (or lack thereof) of note in SQLite is its use of manifest typing. Manifest typing keeps the data's type with the data, not with the table column (which is known as static typing). Just as PHP will try and convert values into other types when it is expecting a different type than given, SQLite will try and do the same thing. For example, if we run the insert query above again with the following data:
$col1_val = 50870; $col2_val = 2308.4237988920; $stmt->execute();
The values for $col1_val and $col2_val will be converted in to strings (TEXT type) and stored as such in SQLite. For a numeric column SQLite will try and convert the string into its numeric value. If there are ANY invalid characters in the string, the whole string will be stored as a string instead of a number (unlike PHP, which will convert "15abc" into 15 when expecting and integer). Finally, the only time SQLite will enforce a data type is when a column is declared as INTEGER PRIMARY KEY. Here an integer value is required.
Built for Speed
There are several techniques that you can use to boost the performance of a SQLite database. Some of these may apply to other DBMS' too.
Fire on All Cylinders
SQLite makes use of indexes (no, not indicies!), so use them! If you are going to run a SELECT query using a particular column value (WHERE clause) or are going to perform a JOIN on a specific column, then by all means add an index to that column:
$dbh->exec('CREATE INDEX col2 ON table1 (col2)');
As you can see, you don't need to alter a table to use an index. You can even create indexes on multiple columns if they are going to be used together in a WHERE or a JOIN. The only column you don't want to ever create an index on is a column that is declared INTEGER PRIMARY KEY (such as the column id above). SQLite automatically creates an index on those columns, and adding an additional index would just slow things down. In fact, this is a good rule of thumb for creating indexes in general. Don't use them unless you are going to perform a WHERE or JOIN on them. The overhead of keeping track to too many indexes on INSERT, UPDATE, or DELETE queries may offset the performance gain you would see on SELECT queries.
Give it Some Gas
If you are going to be performing several updates (INSERT, UPDATE, DELETE) to a table at once, bundling them in a transaction will significantly increase the speed of the updates. SQLite will perform a hard disk write for each query that modifies the database, whereas if several updates were performed in a transaction SQLite will perform a single write for the entire transaction. PDO also makes transactions painfully simple. Here is the insert we did before as a transaction:
// insert our data, but use a transaction $dbh->beginTransaction();
$col1_val = "sqlite"; $col2_val = "rocks"; $stmt->execute();
$col1_val = "this needs to be unique"; $col2_val = "this doesn't!"; $stmt->execute();
$dbh->commit();
Not much to it really. Be aware though that if something catastrophic happens before you commit your transaction you will lose all the data you didn't yet save in the database.
Pedal to the Metal
If you have read any of the other articles about PHP and SQLite you are probably familiar with the SQLite unbuffered query functions. Well, lucky for you, PDO uses unbuffered queries by default (just ask Wez). Next!
Get Under the Hood
SQLite supports the PRAGMA statement as a SQL command to set special parameters on the database. There is some great documentation on what you can do with PRAGMA, and there is one (safe) PRAGMA setting that will affect the speed of SQLite:
PRAGMA cache_size = <NUMBER>
If you are going to perform quite a bit of UPDATEs or DELETEs, and you don't mind using a bit more memory, increasing cache_size can help speed things up a bit. This setting will reset after the database is closed.
The PRAGMA commands are a great way to query and tweak the internals of SQLite, so check out the documentation if you'd like to learn more.
Reduce Drag
One final way you could potentially get greater speed out of SQLite is by creating an in-memory database. If you specify the path to your SQLite database in the PDO DSN as :memory: the SQLite driver will create an in-memory database instead of one in the file system. This database will be destroyed at the end of your PHP script, but may be useful in some situations where quick, temporary storage is necessary.
Routine Maintenance
Wash and Detailing
By default when you delete data from a SQLite database the space is kept and reused by SQLite, and thus is not available to use for other files. If you would like to free up this "reserved" space, then simply run the query VACUUM. If you desire to have this done for you by default you can set the PRAGMA value auto_vacuum if you haven't added any tables to a database yet.
Tune Up
If you can properly order tables in JOINs and index the proper columns you will get the most performance on your queries. For example, SQLite will translate a JOINs into extra WHERE clauses. For example, the following SELECT statement:
SELECT * from table1 JOIN table2 ON table1.common = table2.common;
Would be translated into this:
SELECT * from table1, table2 WHERE table1.common = table2.common;
You won't see a significant speed increase with simple queries like this one, but if you have several queries that are JOINing tables, then it may not be a bad idea to do this kind of translation yourself.
Also try to have at least one index available when you are performing any kind of JOIN or query on more than one table. The idea here is to reduce the time it takes to iterate through both tables to find the values that meet your JOIN criteria. Try to make sure that the second table you specify is indexed on the column you are JOINing on. If you'd like more details, you can find them in this SQLite Wiki article.
Empty Out the Trunk
Each time you access a SQLite database in your PHP scripts you are reading another file from the filesystem (well, almost every time, but for the sake of argument let's keep it simple, ok?). The more data that is stored in the database, the longer it is going to take to load. So, what can you do about it?
The first rule of thumb is to avoid storing large binary files in your database. Store the path to the file instead, and set up a special directory to hold the files you would have normally stored in the database. For example, if you are developing a photo management system and wanted to have the ability to tag photos and create comments a la Flickr you could store the tags and comments in a SQLite database along with a "link" to where the actual photo is on your local files system.
If you just have a lot of data in general and SQLite is slowing you down, you may want to think about compressing data before it is stored in your database. This would make searching harder, but for fields that contain a significant amount of data compression may be beneficial. What's more, you could define your compress/decompress functions as User Defined Functions and call them through SQL, making the process completely seamless. Before you run off and implement compression though, make sure you test and see if you gain any actual speed improvements since the actual compression/decompression does make a hit on the CPU.
Custom Parts
Two very cool ways to extend your use of SQLite are through the use of User Defined Functions (UDF) and User Defined Aggregate Functions. Both UDFs and aggregate functions allow you to create functions in PHP that you can then call through ordinary SQL statements. So, would you like an example?
User Defined Functions
The sqliteCreateFunction() method contains all the magic to attach a PHP function to SQLite. Let's create a compression function to dynamically compress a large data field, like possibly an article from that new blog system you're working on.
function compress_data($data_string) { return gzcompress($data_string, 6); }
$dbh->sqliteCreateFunction('compress', 'compress_data', 1);
We just registered our PHP function compress_data as a new SQLite function compress. We can now use compress in any query we'd like:
$stmt = $dbh->prepare("INSERT INTO articles (auth_id, date, article) VALUES (:auth_id, :date, compress(:article))");
I will leave you to implement a decompress function. :)
User Defined Aggregate Functions
Aggregate functions perform a function over a collection of data, such as the standard SQL functions avg() and stddev(). To operate on a collection of data you need to create two functions. The first function will be called for each individual piece of data. The other function will perform any other actions on the aggregated data and return the result. Here is an example that implements MySQL's GROUP_CONCAT function:
function group_concat_step(&$context, $string, $separator = ',') { $context .= $separator . $string; }
function group_concat_finalize(&$context) { return $context; }
$dbh->sqliteCreateAggregate('group_concat', 'group_concat_step', 'group_concat_finalize', 2);
To use it you can call group_concat(column, ' * '). This differs slightly from MySQL, where the same function would be called as GROUP_CONCAT(column SEPARATOR ' * ').
Check Engine Light
As great as SQLite is, it may not be the best choice for your needs. SQLite does not handle large numbers of users as efficiently as a client/server RDBMS does. It also doesn't play well with network filesystems. As with any other piece of technology, make sure you evaluate SQLite first to see if it is right for you. You may be surprised.
Finish Line
SQLite provides some very nice features as a zero configuration no overhead database system, and is a great addition to any PHP developer's toolbox. Yes, it may not always be the best tool for the job, but that's why PHP also supports MySQL, PostgreSQL, etc., etc. If you have any personal SQLite hints, tips, or gotchas please post them in the comments for all to share. Drive (and code) safely!
