Use SQLite For Your Next PHP Project

SQLite is one of those awesome pieces of software known for its "liteness". It is, as you can guess from the name, a database designed to have as small a footprint as possible. SQLite is a very popular database, in fact, it is believed to be the most widely deployed SQL database engine in the world.

SQLite has lately been a very good choice as a database to use for applications you write. If you don't plan on needing to scale up to the extremely high levels of applications that require something "heavier", you may want to give SQLite a try. And the SQLite libraries also come preinstalled with PHP, allowing you to get started using it right away.

Why Use SQLite?

SQLite is obviously best known for how lightweight it can be. Of course, they couldn't call it SQLite without it being at least lighter than most other databases.

The main difference between SQLite and other database systems, such as MySQL and PostgreSQL, is that it can be run completely without having to run the database on a separate server process. Instead, an entire SQLite database can be stored inside a single file, which can be accessed directly by an application.

SQLite can also be described as being "zero-configuration". SQLite doesn't require anything to be installed before it is used, and it does not rely on a separate server process that needs to be configured.

So, if this is enough to convince you to give SQLite a try, then let's get started, shall we?

Getting Started

The SQLite libraries included with PHP make it very easy to get started coding right away. Let's get started with some of the easier things.

Creating a Database

You can create a database and execute queries in SQLite just as you would in MySQL. The first thing to do to create your database is to open up a terminal and navigate to where you want your database to be located.

Then, start up the SQLite command-line interface with a command similar to this:

sqlite3 database.db

If everything goes fine, you should be greeted with a command prompt like this.

SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Here you can type in your SQL statements to build your database. For now, we'll just create a couple of tables to work with in PHP later.

CREATE TABLE posts (
    id INTEGER PRIMARY_KEY AUTO_INCREMENT,
    title TEXT,
    content TEXT
);

Connecting to a Database

There are some differences that I have noticed between SQLite 2 and SQLite 3, which can cause some small problems. Because of this, I've found that using PDO is probably the easiest way to connect to an SQLite database.

try {
    $db = new PDO('sqlite:/path/to/database.db');
} catch (Exception $e) {
    die($e);
}

In the PDO object we can specify the first parameter, which is the database driver and the path to the database itself, separated by a colon. If you want to write to the database at all, you need to first make sure that the user running the server has write permissions to the database file.

The code is wrapped in a try/catch statement as a good practice, just in case if we encounter any errors.

Executing Queries

Now you can use all of the standard PDO methods with SQLite to manipulate your database. This actually works in a very similar way to what you would do with a MySQL database. For example, to get your posts from the database:

$posts = $db->prepare('SELECT * FROM posts;');
$posts->execute();

And once the content being retrieved from the database is stored in $posts, it's a simple matter of displaying the data.

echo '<ul>';
while ($post = $posts->fetchObject()) {
    echo '<li>' . $post->content . '</li>';
}
echo '</ul>';

An Example

In this example, we have a simple form to insert data in to the database we created earlier. I've added some comments to show examples of both fetching data from the database, and inserting data using prepared statements.

< ?php
// PHP SQLite3 demo
 
// Connect to the database with PDO
try {
    $db = new PDO('sqlite:/var/www/sqlite/database.db');
} catch (Exception $e) {
    die ($e);
}
 
// Insert a post into the DB
if (isset($_POST['title']) && isset($_POST['content'])) {
    try {
        // Create a prepared statement
        $stmt = $db->prepare("INSERT INTO POSTS (title, content) VALUES (:title, :content);");
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
 
        // Fill in the values
        $title = $_POST['title'];
        $content = $_POST['content'];
        $stmt->execute();
    } catch (Exception $e) {
        die ($e);
    }
}
 
// Get posts from database
try {
    $posts = $db->prepare('SELECT * FROM posts;');
    $posts->execute();
} catch (Exception $e) {
    die ($e);
}
 
?>
 
<h1>Posts - SQLite Example</h1>
 
< ?php while ($post = $posts->fetchObject()): ?>
 
    <h2>< ?php echo $post->title ?></h2>
    < ?php echo $post->content ?>
 
< ?php endwhile; ?>
 
<hr />
 
<h2>Add new Post</h2>
<form action="" method="post">
 
    <p>
        <label for="title">Title:</label>
        <input type="text" name="title" />
    </p>
 
    <p>
        <textarea name="content" rows="8" cols="50"></textarea>
    </p>
 
    <p>
        <input type="submit" name="submit" value="Submit" />
    </p>
 
</form>

As always, if you have any questions, be sure to let us know in the comments section of this post.

Stay Updated

Did you enjoy this post? Don't miss a single post by getting free updates!

13 Comments

  1. August 8, 2010

    You can see a pile of SQLite related videos at YouTube which show how to use and program against SQLite. There is also more info at Squidoo about SQLite.

    • August 8, 2010

      Looks like you made some cool SQLite tutorials! I'll check it out ;)

  2. December 31, 2010

    You use SQLite for local development NEVER deployment, let me repeat NEVER use SQLite for deployment. The link you posted about it being "the most deployed" is the "most downloaded" if you read it. Because yes, it's what the industry uses for development. But it's a FAIL for deployment because it increases overhead and is not a service, doesn't store large data with good compression, etc, etc. I use MySQL for deployment, most of my mates use PG, which I just can't stand because the ORDER clause must have every value selected.

    • December 31, 2010

      Of course there are situations where other databases would be a better choice, especially for large applications. But what I'm talking about here is using SQLite for smaller applications which probably won't need to scale up to an extremely large demand.

      For example if you have a small to medium sized site or blog or whatever, SQLite will most likely be fine for your needs. It's when you get up to larger, more demanding applications which may have to deal with huge amounts of data that SQLite may not be the best choice.

    • Paul
      March 2, 2011

      This is wrong. SQLite *IS* the most deployed database engine. That is because practically any embedded device which needs a database uses it. Such as iPhones, set-top-boxes, routers, washing machines etc..etc... It's everywhere.

      It has it's place, it's place is not in a web application with many concurrent users so if this is what you are developing then yes, use something else.

      • March 2, 2011

        @Paul well put, I stand corrected. But this is indeed a post about PHP, so the post should perhaps be edited to reflect what Paul said.

        • Erik
          April 10, 2011

          I disagree with "the Empty". Sqlite is also good for deployment and its speed could be fater then Mysql, but only reading the database would be faster, writing to sqlite is not it`s best job. If the application is not writing all the time, but mostly reading, sqlite would be a right choice. A blog system is a good example of the right choice for sqlite, because a lot of people read the blog and updating is not handeled every second

      • Matt
        October 23, 2011

        Paul, you are incorrect. I work for a company (our main product, a large CMS, is powered by sqlite3) and we are supporting newspaper websites that get over 25000 visits a day. It's really not too hard to set a cron to vacuum the database daily to prevent integrity breakdowns. And like any other db driven system, always use prepared statements for the sake of security.

        Bottom line; never say never ;)

  3. April 30, 2011

    Hey Eric, thank you for the quick tutorial!

    It helped me implement SQLite in Libre Projects, the directory of free & open source web applications and alternatives to proprietary platforms.

  4. David
    July 31, 2011

    I have copied and tried your example, but it does not save the data to the database so I tried :$db -> beginTransaction(); and $db -> commit(); and it still doesn't save, can you help please.

  5. Querk McGoober
    January 17, 2012

    Is this why using SQLite on webservers crashes your SQLite db on a PC.

    Can they be updated,syc'd safely and or correctly?

    Me thinks that MySQL type is better all around

    Do not use SQLIte as production Db, it is not up to the good worky or as robusted as better ones is.

  6. JH
    January 27, 2012

    Hi Eric! Thanks for this. This has helped me a lot!

    I was wondering, using that example, how can I add a button or a link to SORT the data? I've tried lot of things, but none of them worked.

Trackbacks/Pingbacks