PHP Cheatsheet for SQLite

Getting Started Code Example

The following example creates a PDO connection to the database (a local file named test.db that it will create if it doesn't exist).

It then adds a user to that table using a prepared statement.

<?php

function getDb() : PDO
{
    $db = new PDO("sqlite:".__DIR__."/test.db"); // teeest.db just creates a new file named teeest.db
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );//Error Handling
    return $db;
}

function createTable()
{
    $db = getDb();

    $sql = "CREATE table user(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name VARCHAR(255) NOT NULL,
        last_name VARCHAR(255) NOT NULL,
        UNIQUE (first_name, last_name)
    );" ;

    try
    {
        $db->exec($sql);
        print("Created user table.\n");
    }
    catch (PDOException $e)
    {
        print ($e->getMessage());
    }
}

function addUser()
{
    $db = getDb();

    $data = [
        'first' => 'first',
        'last' => 'last',
    ];

    $sql = 'INSERT INTO user(first_name, last_name) VALUES(:first, :last)';
    $statement = $db->prepare($sql);
    $statement->execute($data);
    print "Added the user" . PHP_EOL;
}


function main()
{
    createTable();
    addUser();
    echo "success" . PHP_EOL;
}

main();

Reasons For Usage

• Good for truly "micro" services, whereby we want to create a tiny little service that has a database, but doesn't require a full blown MySQL or PostgreSQL server runnning all the time eating memory.

Reasons Not To Use

• In certain cases, it may be better to serialize data to a JSON/YAML file instead, so you can easily manipulate the data, or track it in git. E.g. an SSH login tool that tracks usernames/keys.
• Does not support concurrency.
• Does not have any concept of "users". Your code either has access to the file or not.
• Not suitable for larger databases.
• Far fewer types. E.g. there is no UUID, network, or GIS types like with PostgreSQL.

Required Extension

In order to use SQLite in PHP, you need to have the sqlite extension installed. On debian/ubuntu, this is as simple as something like:

sudo apt install hp8.4-sqlite3