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.
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.
Looks like you made some cool SQLite tutorials! I'll check it out ;)
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.
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.
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.
@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.
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
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.