Today I decided to provide a brief introduction into using SQLite compared to mySQL for PHP and webserver environments.

Traditionally mySQL is first choice when developing a dynamic website with PHP. Recently SQLite is another database system (I intentionally don't call it a database server, see below for reasons why) getting very popular. SQLite has two main usage areas: Webservers (mainly with little performance requirements) and applications.

Introduction

The latter (using it as application datastorage engine) has in particular become so popular, since Google's Android plattform for mobile devices is the number 1 choice besides Apples iPhone OS. And there is a good reason for using SQLite as an application data storage: SQLite doesn't require a server in the way mySQL does. There is no service or daemon running in the background of SQLite. Instead, the SQLite library accesses its database files directly in the context of the calling process (or even thread).

Unfortunately (typically) accessing data files directly is very often subject of criticism. Some developers say this makes the database slow. Some say the SQLite is more like an SQL frontend to CSV-style data store. My hint for you as a SQLite developer is: don't believe them. There are too many great advantages not requiring a database server (=service) at all.

Advantages

For mobile or desktop applications the advantage of SQLite is obvious: You may provide the initial database side-by-side with the application files. No need to install or create the database server or initialize the data. All data is stored in your own data-directory and can be backed up and copied with ease by using file functions.

But the same advantage is very important when creating web applications like dynamic websites: No need to ask the user for database credentials, to create the tables on the fly, fill them with initial content and so on. This eases installation of PHP/SQLite applications so much.

And don't forget about backing up: Usually, mySQL admins have to plan backup procedures for mySQL and for the webserver files independently, having very high risk of partial failure. With SQLite, all to do is backup the website folder, containing the databases, the templates, the scripts and even the custom server configuration (.htaccess, php.ini) all together.

Disadvantages

To be fair, SQLite is not always the perfect solution. For enterprise databases with heavy load and lots of different clients, mySQL provides the better concepts. For example, load ballancing is no feature of SQLite. It will always open the database, find the information and return it. There is few caching, the file will in many cases be locked for other processes and in the end, the limits of the file-system apply to you SQLite databases. However, most dynamic websites I know will never have much more than 1000 users a day, and for these websites SQLite would be perfect.

Things to consider when moving to SQLite

SQLite has very lose typing. Nearly everything is a text. This is very nice from a PHP-coders view, as PHP works similar. The lose typing allows easy migration from mySQL to SQLite. But there are some differences regarding the SQL syntax. One example would be the "INSERT INTO table SET a=1, b=2"... this is fine for mySQL, but SQLite requires the columns/values approach like "INSERT INTO table (a,b) VALUES (1,2)".

Managing SQLite databases

If you're a Windows user, I recommend the SQLite Administrator, which is actually the first and only tool you should install (besides your local webserver like XAMPP of course). Download: SQLite Administrator

The SQLite Administrator works quite similar to my favourite local mySQL administration tool heidi-sql.

Other things to note

Time is handled very cool by SQLite. There are a bunch of function like

• date(timestring, modifier, modifier, ...)
• time(timestring, modifier, modifier, ...)
• datetime(timestring, modifier, modifier, ...)
• julianday(timestring, modifier, modifier, ...)
• strftime(format, timestring, modifier, modifier, ...)

The timestring is either an ISO kind datetime, date or time, may be "now" or even a Julian day number. But what is really cool are the modifiers. You may modify the timestring very comfortably:

• NNN days
• NNN hours
• NNN minutes
• NNN.NNNN seconds
• NNN months
• NNN years
• start of month
• start of year
• start of day
• weekday N
• unixepoch
• localtime
• utc

Compared to mySQL's time functions like add_date() this is such a huge advantage.

As you can see, SQLite is really worth playing around with it and is really different from mySQL. To get used to it quickly, my last but most important hint for you is: adhere to the Docs at http://www.sqlite.org/docs.html to get the most out of it.