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.
$memory = new PDO('sqlite::memory:'); $file = new PDO('sqlite:' . __DIR__ . '/app.db');
Try it
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.
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
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 = ONon 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
INTEGERcolumn happily keeps text it cannot convert. AddCHECKconstraints orSTRICTtables (SQLite 3.37+) if you need enforcement. - Native PHP types.
INTEGERcolumns come back asintandREALasfloat(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 = WALandPRAGMA busy_timeout = 5000so 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
PDOobject 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 thePdo\Sqliteequivalents. - Missing MySQL features. There is no
ALTER TABLE ... MODIFY, no stored procedures and no user accounts.AUTO_INCREMENTis replaced byINTEGER PRIMARY KEY, andlastInsertId()works the same way. - Switching to MySQL later is easy if you stay with standard SQL and use PDO's prepared statements throughout.