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 file: URI:
$conn = new PDO('sqlite:file:foo.db');sqlite:foo.db and 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 ...:...The 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.
The traditional 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
The new file: URIs are not supported in older PHP versions, and it is not possible to natively back-port it to older PHP versions.
If the file: URIs are used in older PHP versions, it results in a PDOException at the PDO constructor:
PDOException: could not find driver in ...:...If open_basedir restrictions are enabled, this feature will not be available.