Master SQLite3 in PHP: Connect, Create Tables, and Perform CRUD Operations

Introduction

SQLite is an embedded, file‑based relational database engine that requires no separate server. In PHP you can manage SQLite databases using the built‑in SQLite3 extension.

Installation

The SQLite3 extension is enabled by default. It can be disabled at compile time with --without-sqlite3. Windows users must ensure the php_sqlite3.dll file is present in the PHP distribution and that libsqlite3.dll is reachable via the system PATH.

Note: Since PHP 7.4.0 on Windows, the DLL must be located in a directory listed in PATH . Copying the DLL to the Windows system directory works but is not recommended.

Usage

Connecting

Instantiate a SQLite3 object with the path to the database file. If the file does not exist, SQLite creates it automatically.

$dbFile = runtime_path() . DIRECTORY_SEPARATOR . 'tinywan.db';
$database = new SQLite3($dbFile);
var_dump($database);

The example connects to tinywan.db . If the file is missing, an empty database is created.

Creating a Table

Use an SQL CREATE TABLE statement via the exec() method.

$database->exec('CREATE TABLE IF NOT EXISTS resty_user (id INTEGER PRIMARY KEY, username TEXT, age INTEGER)');

The table resty_user has three columns: id (auto‑increment primary key), username, and age.

Inserting Data

Insert rows with an INSERT INTO statement, also using exec().

$database->exec("INSERT INTO resty_user (username, age) VALUES ('Tinywan', 24)");

This adds a record with username Tinywan and age 24.

Querying Data

Retrieve rows using a SELECT statement via the query() method, then iterate the result set.

$result = $database->query('SELECT * FROM resty_user');
while ($row = $result->fetchArray()) {
    echo 'ID: ' . $row['id'] . ', username: ' . $row['name'] . ', age: ' . $row['age'] . PHP_EOL;
}

The loop prints each row’s ID, username, and age.

Updating Data

Modify existing rows with an UPDATE statement via exec().

$database->exec("UPDATE resty_user SET age = 25 WHERE id = 1");

This changes the age of the record with id = 1 to 25.

Deleting Data

Remove rows using a DELETE FROM statement through exec().

$database->exec('DELETE FROM resty_user WHERE id = 1');

Closing the Connection

After all operations, close the database to free resources.

$database->close();

Viewing Data with an IDE Database Manager

Conclusion

The guide demonstrates how to manage an SQLite database from PHP: installing the extension, connecting, creating tables, and performing full CRUD operations, then closing the connection. SQLite offers a lightweight, powerful solution ideal for small projects and rapid development.