RT Cunningham


Setting up and using SQLite or SQLite3 with and without PHP

SQLite The current version of SQLite is version 3, sometimes labeled as SQLite3, both in text and file names. The Ubuntu repositories and the PPA are confusing. You can install both versions of the package by requesting “sqlite” but using “php7.0-sqlite” or “php7.0-sqlite3” will only fetch the SQLite3 version.

Why anyone would want anything less than version three? It’s been outdated for years.

What is SQLite?

Unless you’re familiar with everything in the world (and you’re not, of course), you may not know that it’s yet another database system. From the website itself:

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.

Some applications you may be familiar with use it instead of some other high-end database systems. If I’m not mistaken, it’s used by Firefox, Thunderbird and Chrome. It’s probably closer, in terms of how it’s used, to Microsoft Access than MySQL. Each database can be contained in a single flat file.

I wish I could use it with WordPress – there’s an unsupported plugin for it I won’t use. None of the other plugins are designed around anything other than MySQL, so why bother? It should be in the core itself, just like MySQL, so plugin authors won’t be afraid to support it.

I don’t have to tell you the merits of using it instead of MySQL (or any drop-in replacement). It works with a file and as such, it uses a lot less memory. With it instead of MySQL, you could run a large WordPress website (but not a network) on a five-dollar VPS (like what DigitalOcean offers).

Install It

If you install the PHP extension, you can work with a database through PHP functions and that’s about it. You still have to install the full package if you want to manipulate it outside of PHP.

If you already have PHP installed, you only have to fetch two packages:

apt install sqlite3 php7.0-sqlite3

Use It

The Adminer script makes working with databases easy. I use Adminer with my WordPress databases and I’ve tested it with SQLite. It works just as well.

Adminer is a web application. I’m not using it on my development machine (the laptop I’m using now) because I’m using a regular application.

If I want to design something that uses a database, I’ll do it with the regular application and then test it with the PHP extension. Of course, I’ll have to learn everything about it before I can work with it. The only thing I’ve done with it so far is build a few short lists.

LibreOffice Base can connect to a few different types of databases, but not this type. That’s a shame, because the interface for LibreOffice Base seems pretty solid.

Share: Facebook | Twitter

By RT Cunningham
August 13, 2016
Web Development