SQLite For Learning Examples

SQLite is a relational database stored in a single file, or entirely in memory. PHP can use it through PDO without installing a separate database server, which makes it excellent for learning SQL, writing small examples, and building fast tests.

SQLite is a real database, but it is not a perfect copy of MySQL or PostgreSQL. Use it to learn the core ideas, then check engine-specific behaviour before assuming production will behave exactly the same.

In-Memory Databases

An in-memory SQLite database exists only while the connection is open. It is useful for examples because every run starts clean.

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)');

$statement = $pdo->prepare('INSERT INTO users (email) VALUES (:email)');
$statement->execute(['email' => 'sam@example.com']);

$email = $pdo->query('SELECT email FROM users LIMIT 1')->fetchColumn();

echo $email . PHP_EOL;

// Prints:
// sam@example.com

This is real SQL, real tables, real constraints, and real prepared statements.

File Databases

SQLite can also store data in a file.

<?php

declare(strict_types=1);

$path = sys_get_temp_dir() . '/learning.sqlite';
$dsn = 'sqlite:' . $path;

echo $dsn . PHP_EOL;

// Output looks like:
// sqlite:/tmp/learning.sqlite

A file database is useful for small tools, local prototypes, command-line scripts, and some desktop-style applications. For a busy multi-user web application, a server database such as MySQL or PostgreSQL is usually a better fit.

Foreign Keys

SQLite supports foreign keys, but they must be enabled per connection.

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('PRAGMA foreign_keys = ON');

$enabled = $pdo->query('PRAGMA foreign_keys')->fetchColumn();

echo 'foreign_keys=' . $enabled . PHP_EOL;

// Prints:
// foreign_keys=1

This matters in tests. If production uses foreign keys but SQLite tests do not enable them, tests may allow invalid data that production would reject.

Type Flexibility

SQLite is more flexible with column types than MySQL and PostgreSQL. That can be convenient while learning, but it can hide mistakes.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price_cents INTEGER NOT NULL
);

Even if SQLite accepts a surprising value, your PHP application should still validate input before inserting it. Do not rely on SQLite's flexibility as your only safety net.

Useful Learning Pattern

A good SQLite learning example should:

1. Open a clean database.
2. Create the tables it needs.
3. Insert a few rows.
4. Run the query being taught.
5. Print a small result.

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL)');
$pdo->exec("INSERT INTO tasks (title, done) VALUES ('Write lesson', 1), ('Review example', 0)");

$statement = $pdo->prepare('SELECT title FROM tasks WHERE done = :done ORDER BY id');
$statement->execute(['done' => 0]);

foreach ($statement->fetchAll(PDO::FETCH_COLUMN) as $title) {
    echo $title . PHP_EOL;
}

// Prints:
// Review example

The example is self-contained. A beginner can run it, change one value, and immediately see how the query behaves.

When SQLite Is A Good Fit

SQLite is useful for:

• Learning SQL and PDO.
• Small command-line tools.
• Local development experiments.
• Tests that need a clean database quickly.
• Simple single-user or low-concurrency applications.

Use MySQL, MariaDB, or PostgreSQL when the application needs stronger concurrent writes, production operations tooling, replication, managed backups, larger datasets, or database-specific features.

What To Check

Before moving on, make sure you can:

• Create an in-memory SQLite database with PDO.
• Create a table and insert rows.
• Run a prepared statement.
• Explain why foreign keys need PRAGMA foreign_keys = ON.
• Name at least two differences between SQLite and server databases.