Easy Databasing with SQLite

Easy Databasing with SQLite

Editor’s Note: This is a guest tutorial from W. Jason Gilmore, a PHP developer and bestselling author with more than ten years of professional experience. He’s the author of the popular new book, “Easy PHP Websites with the Zend Framework”, which includes access to almost five hours of online video, a periodically updated e-book, and access to a reader-restricted online forum. You can buy the book through both Amazon.com and EasyPHPWebsites.com.

Perhaps because the Web still in some ways resembles the Wild West in terms of its anything-goes, feast-or-famine environment, we Web developers tend to view even our most modest projects with visions (some would say delusions) of grandeur. After all, who among us hasn’t fantasized over our new e-commerce store generating enormous revenues? Or took a moment to calculate the cost of hosting our new blog on a dedicated server because surely there are millions of visitors who will share our passion for the crocheted pot holder industry? Such ambitions tend to skew our ability to evaluate a project’s technology requirements, resulting not only in significantly increased monetary outlays, but also in increased time (and therefore, expense) maintaining an exorbitant infrastructure.

Historically, such overreaching has perhaps been no more evident than when gauging a project’s database requirements. Prior to, and even in the years following, the rise of open source database solutions, any attempts to suggest using a database other than Oracle, Microsoft SQL Server, or IBM DB2 were met with laughter. The result was typically a project incurring thousands of dollars in licensing fees, significant hardware costs, and often a dedicated individual knowledgeable in maintaining these complex database solutions.

In the years following MySQL’s first public release back in 1996, developers began to reconsider long-held beliefs pertaining to minimally acceptable database requirements. Uncompromising demands capabilities were supplanted by the desire for performance and low licensing fees, not to mention by realization that most applications didn’t actually require many advanced features in the first place! MySQL’s measured approach to eschewing features in exchange for performance, not to mention its’ open source licensing strategy, made it a rather attractive solution for millions of Web developers seeking a low-cost solution which also happens to not unreasonably stretch the developer’s limits of SQL administration capabilities.

Even in light of these advantages, you might be surprised to MySQL can be overkill for many projects. In fact, an even more efficient solution is available which you’re probably unknowingly already using every single day. This solution is SQLite, and by one reliable account is the most widely deployed database solution in the world [1].

Enter SQLite

SQLite (http://www.sqlite.org) is a lightweight yet extremely powerful open source database solution used within countless high profile technologies around the globe, including Firefox, Skype, McAfee, and the iPhone [2]. Capable of powering cellphones and Web sites alike, SQLite can be installed in minutes, and requires nothing of the developer in terms of configuration nor in terms of otherwise typical tasks such as creating user or administrator accounts; it’s quite literally a set-it-and-forget-it solution, making it the perfect database for many Web applications managed by resource strapped developers. Further, because SQLite databases are stored in a single file, the database can be backed up and moved just like any other operating system file.

But don’t presume SQLite’s no-nonsense installation process is indicative of an inferior product! SQLite supports all of the data types typically found in other database solutions, transactions, a command-line interface, ample support among many of the most popular programming languages, and can handle terabyte-sized databases. In fact, I’d venture SQLite will do the job just fine for the vast majority of your Web-based projects, whether it’s building a corporate Web site or running your blog. I’ll spend the remainder of this tutorial introducing you to SQLite, and in particular showing you how to integrate SQLite with PHP.

Installing SQLite

SQLite is supported on all major operating systems, offering precompiled binaries for Windows, Mac OS X, and Linux. As is standard practice you can also download and compile the software from source. To install SQLite, head over to the project’s download page and download the desired version.

Because SQLite is a self-contained solution, there is no need to think in terms of clients and servers per se. All you need to do is unzip the download, and move the lone file to a convenient location preferably within your system path. Next, open a command prompt and execute the following command:

Executing this command will create a database named blog.db, and subsequently enter the SQLite command-line console. From here you can begin creating database tables. The following example creates a table named entry which will be used to contain blog entries. If you’re familiar with standard SQL table creation syntax, this will look quite similar to what you’ve seen in the past:

You can view a database’s tables using the .tables command, like this:

You can view a table’s schema using the .schema command:

To view a list of all available console commands, execute .help.

You can use all of the usual SQL commands to insert, update, delete, and select data. For instance, the following INSERT statement can be used to add a few rows to the entry table:

From there you can select the data using the SELECT statement:

If you navigate to the directory where you executed the sqlite3 command you’ll see a file named blog.db. This contains your entire database, table schema, and data! To backup your data, just copy that file to a USB key or elsewhere as desired.

PHP and SQLite

Because SQLite is such a viable solution for Web developers, not only is PHP natively capable of talking to the database, but the SQLite extension is actually enabled by default as of PHP 5. If you’re running Windows you will need to enable the php_sqlite.dll extension within your php.ini configuration file. For PHP 5.1 and beyond you’ll also want to enable the php_pdo.dll and php_pdo_sqlite.dll extensions. On Linux depending upon your environment you may need to build PHP using the --with-sqlite=shared option.

Once PHP is properly configured, you can use PHP’s PDO extension to interact with SQLite. The following example will retrieve the previously inserted blog entry:

Executing this script returns the following output:

The blog entry First entry was created on 2009-10-19 14:04:23.

If you receive an error when attempting to open the SQLite database, chances are there is a conflict between the SQLite version you downloaded and that used by your PHP installation. If this is the case, either update your PHP installation or use PDO to create your database tables and data.

Conclusion

I hope this brief tutorial provides you with the incentive to consider using SQLite for a future project. I can guarantee it will save you significant administrative time without detracting from your Web site’s performance. Please contact me with your questions at jason@easyphpwebsites.com.

Footnotes

[1] According to detailed estimates compiled by the SQLite Project.

[2] http://sqlite.org/mostdeployed.html

Database Icon used in banner images from the MinIcons set.

Posted Tuesday, October 20th, 2009 · Back to Top

SPONSOR

Add Comment

13 Comments 4 Mentions

  1. webmasterdubai Author Editor

    sqlite is really nice, to manage sqlite database with GUI manager is one my favorite and best firefox extension SQLite manager

    https://addons.mozilla.org/en-US/firefox/addon/5817

    ·

  2. Karl Agius Author Editor

    Excellent primer :) One question though; since SQLite only supports one write operation at a time (http://www.sqlite.org/faq.html#q5), would it still be a viable option for blogs with high comment traffic or a lot of interaction?

    ·

  3. Muzzammil Author Editor

    Yep, awesome tutorial. Thumbs up !!!. SQLite indeed a great little thing to store data. Very fast and efficient.

    ·

  4. Joël Cox Author Editor

    I’ve been tempted to take a more in depth look at SQLite, but it didn’t make it on my to-do list. Thanks for the post!
    .-= Joël Cox´s last blog ..Review: De wet op internet =-.

    ·

  5. John Media @ server hosting Author Editor

    Nice tutorial on SQLite I haven’t used the database yet but I would surely try to have knowledge on it. It would likely take sometime to learn but I have some background on SQL’s so I think its not that hard to cope up w/ this.

    ·

  6. Siva Author Editor

    Nice article , There are also some ecommerce wordpress blogs :)

    ·

  7. Blog Bisnis Online Author Editor

    Maybe I’ll learn sql again

    ·

  8. Build Credit Fast Author Editor

    How would this SQLite enables faster connection? Does it really affect on browsers?

    ·

  9. Ron Author Editor

    Lets you have a look at what your sqlite database is up to.

    ·

  10. Chamber of Commerce Author Editor

    I was very pleased to find this site.I wanted to thank you for this great read I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you post. I’ve recently started a blog, the information you provide on this site has helped me tremendously. Thank you for all of your time & work.

    ·

  11. Quantum Energy Author Editor

    I haven’t used the database yet. I am still learning about it but got difficult for me. and I would surely try to have knowledge on it.
    Nice Website and Nice tutorial on SQLite …

    ·

  12. Boris Author Editor

    Telling your troubles is swelling your troubles.

    ·

  13. Peter Širka Author Editor

    SQLite eshop with node.js and web framework partial.js
    http://eshop.partialjs.com

    ·

 

Build Internet by One Mighty Roar. Since 2008.