Database

This SQLite database wrapper provides a simple-to-use interface using PDO's SQLite driver. It was designed to have method signatures very close to Laravel's Eloquent ORM. There are not, however, any ORM features in this wrapper. These must be handled at the model level.

By default, foreign keys are enabled and the database is in WAL mode. In general these defaults should be perfect for most use cases.

Usage

Connect

// __construct(string $path, array $opts = [])
$db = new Database('test.db');

This wrapper is non-static; create an instance and pass a path to where the SQLite database will reside. If the database does not already exist, it will be created. You can pass PDO options to the second parameter. By default the error mode is set to Exceptions and the fetch method is set to associative arrays.

The wrapper also enables foreign keys and WAL mode by default, and at the moment this cannot be changed. This configuration should work great for most use-cases anyway, as SQLite itself can handle WAL-related upkeep such as checkpointing.

Set Working Table

// table(string $name): Database
$db->table('foo');

Sets the current working table for the instance. Allows you to switch table contexts for operations. Can method chain from it. Since the current working table is persistent, you do not need to call this method before each operation if you do not want to.

Create Table

// create(array $columns): PDOStatement|false
$db->table('foo')->create([
    'id INTEGER PRIMARY KEY',
    'name TEXT NOT NULL',
    'age INTEGER DEFAULT 18'
]);

Creates a table where the name of the table is the current working directory and the columns are passed via an array. The underlying SQL uses CREATE TABLE IF NOT EXISTS and so this will error/not execute if the table already exists.

Drop Table

// drop(): PDOStatement|false
$db->table('foo')->drop();

Drops the current working table if it exists.

Table Schema

// schema(): array
$db->table('foo')->schema();

Returns a dump from PRAGMA table_info() on the current working table.

Insert

// insert(array $data): int|false
$db->table('foo')->insert(['name' => 'John', 'age' => 32]);
$db->table('foo')->insert([
    ['name' => 'Smith', 'age' => 22],
    ['name' => 'Jane', 'age' => 18]
]);

Inserts a record into the current working table. Can pass an array of arrays to insert multiple records.

Testing

I wrote a simple test file; run it with PHP directly. php test.php This test script covers almost every relevant method in the class.