Skip to content
Tutorials Center
My learning

Topics

PHP Reference ยท Databases (PDO)

PDO and SQLite in PHP

Use SQLite with PHP PDO: sqlite::memory: and file DSNs, foreign key and WAL pragmas, flexible typing, custom SQL functions and how it differs from MySQL.

SQLite is a complete SQL database stored in a single file, or entirely in memory, with no server to install. PHP's pdo_sqlite driver lets you use it with the same PDO API as MySQL or PostgreSQL. It is ideal for tests, small tools, caches, prototypes and applications with modest write traffic.

The DSN decides where the data lives. sqlite::memory: creates a private database that disappears when the connection closes, which makes it perfect for examples and automated tests.

PHP
$memory = new PDO('sqlite::memory:');
$file   = new PDO('sqlite:' . __DIR__ . '/app.db');

Try it

PHP An in-memory database and a file database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

PHP Foreign keys, flexible typing and a PHP function in SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Syntax

PHP
new PDO('sqlite::memory:');            // private in-memory database
new PDO('sqlite:/path/to/app.db');     // file (created if missing)
new PDO('sqlite:');                    // temporary on-disk database
PDO::connect('sqlite::memory:');       // PHP 8.4+: returns Pdo\Sqlite
Part Description
DSN sqlite: followed by :memory:, a file path, or nothing. No user name or password is used.
PDO::ATTR_ERRMODE Set to PDO::ERRMODE_EXCEPTION (the default since PHP 8.0) to get exceptions.
Pdo\Sqlite::createFunction() Registers a PHP function callable from SQL. Available on the Pdo\Sqlite object returned by PDO::connect() (PHP 8.4+).
PRAGMA statements SQLite settings sent as SQL through exec(): foreign_keys, journal_mode, busy_timeout.

Return value: a PDO (or Pdo\Sqlite) object.

Errors / Exceptions: PDOException if the file cannot be opened or created, for example when the folder is missing or not writable.

Usage notes

  • Foreign keys are off by default. Run PRAGMA foreign_keys = ON on every new connection, or constraints silently do nothing.
  • Flexible typing. SQLite stores values by their actual type and only uses the column type as an "affinity" hint. An INTEGER column happily keeps text it cannot convert. Add CHECK constraints or STRICT tables (SQLite 3.37+) if you need enforcement.
  • Native PHP types. INTEGER columns come back as int and REAL as float (PHP 8.1 and later).
  • Concurrency. Many readers can work at once but only one writer at a time. For web apps set PRAGMA journal_mode = WAL and PRAGMA busy_timeout = 5000 so writers wait instead of failing with "database is locked".
  • A private in-memory database cannot be shared between connections or requests. In tests keep one PDO object and pass it around.
  • File location. Store the database outside the web root, make the folder writable by PHP, and back it up by copying the file when no write is running.
  • Driver-specific methods such as PDO::sqliteCreateFunction() are deprecated since PHP 8.5; use the Pdo\Sqlite equivalents.
  • Missing MySQL features. There is no ALTER TABLE ... MODIFY, no stored procedures and no user accounts. AUTO_INCREMENT is replaced by INTEGER PRIMARY KEY, and lastInsertId() works the same way.
  • Switching to MySQL later is easy if you stay with standard SQL and use PDO's prepared statements throughout.