PHP 8.1: PDO SQLite:
file: URI support
The PDO SQLite extension in PHP 8.1 supports using SQLite's
file: URIs to establish a database connection. SQLite version 3.7.7 (released in 2011) added support for the
file: URIs, which allows passing additional query parameters to fine tune the connection parameters.
Traditionally in PHP, an SQLite PDO database connection is established using the
sqlite: DSN, followed by the path to the database file:
$conn = new PDO('sqlite:foo.db');
The above snippet establishes an SQLite database connection to a file name
foo.db, or automatically creates a database if the file does not exist.
It is also possible to use
:memory: as the file path, which creates an in-memory ephemeral database that is cleared when the
PDO object is destroyed:
$conn = new PDO('sqlite::memory:');
With the addition of
file: URI support, the same connection can be established with the
$conn = new PDO('sqlite:file:foo.db');
sqlite:file:foo.db DSNs are functionally identical, however, the
file: URIs can be used to pass additional query parameters, which are passed to SQLite as connection parameters.
SQLite supports several parameters to control the characteristics of database connection. This includes forcing a private or shared cache mods, finer control over the read, write, and create permissions of the database file, and possibly more in the future.
For example, the
mode=ro parameter makes the database connection read-only, effectively preventing all writes to the database. It also refuses to create an empty database a file with the given name does not exists:
$conn = new PDO('sqlite:file:foo.db?mode=ro'); $conn->exec('INSERT INTO users (uid) VALUES (1)');
PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in ...:...
mode parameter supports read-only (
ro), read-write (
rw), read-write-create (
rwc), and in-memory (
memory) values, which provides fine control that the traditional
sqlite:foo.db DSN does not provide.
sqlite:filename DSN is equivalent to
sqlite:file:filename, which implies
mode=rwc. This makes all the following PDO constructs functionally equivalent, but with increasing level of visibility of the connection characteristics.
$conn = new PDO('sqlite:foo.db'); $conn = new PDO('sqlite:file:foo.db'); $conn = new PDO('sqlite:file:foo.db?mode=rwc');
PHP continues to support the traditional
sqlite:filename DSN, and the support for
file: URIs are an addition in PHP 8.1. There are no plans to deprecate or drop support for the existing SQLite DSNs.
Backwards Compatibility Impact
file: URIs are not supported in older PHP versions, and it is not possible to natively back-port it to older PHP versions.
file: URIs are used in older PHP versions, it results in a PDOException at the
PDOException: could not find driver in ...:...
open_basedir restrictions are enabled, this feature will not be available.