WireDatabasePDO / $database

• Prefer prepared statements for values; use quote() only when prepared statements are not practical.
• Use escapeTable(), escapeCol() and escapeTableCol() for identifiers only, not values.
• Do not concatenate unsanitized input into SQL.
• Use execute($query, false) only when you intend to handle query failure yourself.
• Close cursors or fully consume statements before running dependent queries when needed.
• Use transactions only when allowTransaction() returns true.
• Write SQL in MySQL syntax, even when the site uses SQLite or PostgreSQL (it is translated).
• To support every database, check capabilities with $database->dialect() rather than checking which database is in use.

$query = $database->prepare("SELECT id, name FROM pages WHERE templates_id=:template");
$query->bindValue(':template', $template->id, \PDO::PARAM_INT);
$database->execute($query);
foreach($query as $row) {
    // ...
}

Configuration

The database type is set in /site/config.php:

MySQL uses the dbName, dbUser, dbPass, dbHost, dbPort etc. settings. PostgreSQL uses the same settings. Set dbPort to 5432 (the installer does), since the default is MySQL's 3306, and use dbSocket for the directory that holds the server's socket file rather than the file itself:

// /site/config.php
$config->dbType = 'pgsql';
$config->dbHost = 'localhost';
$config->dbPort = '5432';
$config->dbName = 'mysite';
$config->dbUser = 'mysite';
$config->dbPass = 'secret';
$config->dbSocket = '/var/run/postgresql';  // optional: connect through the socket in this directory instead of dbHost
$config->dbOptions = ['pgsql' => ['schema' => 'processwire']];  // optional: schema other than public

SQLite uses only dbFile:

// /site/config.php
$config->dbType = 'sqlite';
$config->dbFile = '';                             // site/assets/database/site.sqlite (default)
$config->dbFile = 'mysite.sqlite';                // site/assets/database/mysite.sqlite
$config->dbFile = '/home/user/data/site.sqlite';  // absolute path (i.e. outside the web root)

$config->dbFile rules:

• Blank: site/assets/database/site.sqlite.
• Filename or relative path: relative to site/assets/database/. It may not contain ...
• Leading slash (or Windows drive letter): absolute path.
• A database file inside the web root must be in site/assets/database/, otherwise a WireException is thrown. That directory is blocked by the root .htaccess file, and ProcessWire adds a deny-all .htaccess file to it. The root .htaccess file also blocks .sqlite and .sqlite3 files (and their -wal, -shm and -journal files) anywhere.
• For additional protection, the database file may be placed outside the web root by using an absolute path.
• The installer names the database file randomly (i.e. site-3f9a2c7e.sqlite) unless another file is specified, so that its URL cannot be guessed on servers where .htaccess files do not apply. The web installer also checks that the file cannot be downloaded, and warns if it can.

dialect()

• Returns: WireDatabaseDialect (WireDatabaseDialectMySQL, WireDatabaseDialectSQLite or WireDatabaseDialectPgsql)
• Purpose: Database-specific behavior, including capability checks.

$dialect = $database->dialect();
echo $dialect->name(); // 'mysql', 'sqlite' or 'pgsql'

Capability methods (all return bool):

Two more dialect methods return a collation name (or a blank string when none is needed, as on MySQL and PostgreSQL): compareCollation($value) for text comparisons and sortCollation() for ORDER BY terms. See the SQLite section below.

SQL helpers

Where a construct differs between databases, the dialect can build the statement in its own syntax, so that nothing has to be translated (and nothing has to be guessed by the translator). Helper output is executed as-is by prepare(), query() and exec().

upsert()

• Signature: upsert(string $table, array $columns, array $update, array $options = []): string
• Purpose: An INSERT that updates the existing row when it would violate a unique key. MySQL: INSERT ... ON DUPLICATE KEY UPDATE. SQLite and PostgreSQL: INSERT ... ON CONFLICT (...) DO UPDATE SET.
• $columns: columns to insert, as a list of names (each bound as :name) and/or name => expression.
• $update: columns to update when the row exists, as a list of names (set to the inserted value; the column must be in $columns) and/or name => expression (i.e. 'NOW()', 'qty+1', ':bindName'). Expressions are MySQL syntax on every database, as elsewhere: in an update expression a bare column name is the existing row's value and VALUES(col) is the inserted value. PostgreSQL translates them when building the statement. Prefer bound values over quoted literals in expressions.
• $options['conflict']: column names of the primary or unique key the insert conflicts on. MySQL does not need it and SQLite can do without it, but PostgreSQL requires a conflict target: without this option it uses the table's primary key, and throws WireDatabaseException when the table has none. Pass it whenever you know it.
• $options['rows']: insert several rows at once, each a list of scalar values in $columns order. Numbers are inserted as given, strings are quoted, booleans become 1/0, null becomes NULL. Values are not SQL expressions: use $columns with bound parameters for anything else.
• Throws WireDatabaseException when there is nothing to insert or update, when a listed update column is not being inserted, when a row does not have one value per column, or when a row value is not a scalar.

$dialect = $database->dialect();

$sql = $dialect->upsert('caches', ['name', 'data', 'expires'], ['data', 'expires'], ['conflict' => ['name']]);
// MySQL:  INSERT INTO `caches` (`name`, `data`, `expires`) VALUES (:name, :data, :expires)
//         ON DUPLICATE KEY UPDATE `data`=VALUES(`data`), `expires`=VALUES(`expires`)
// SQLite: INSERT INTO `caches` (`name`, `data`, `expires`) VALUES (:name, :data, :expires)
//         ON CONFLICT (`name`) DO UPDATE SET `data`=excluded.`data`, `expires`=excluded.`expires`
// PostgreSQL: the same as SQLite, with "double-quoted" identifiers
$query = $database->prepare($sql);
$query->bindValue(':name', $name);
$query->bindValue(':data', $data);
$query->bindValue(':expires', $expires);
$query->execute();

// custom expressions, and several rows of literal values
$sql = $dialect->upsert('sessions', ['id', 'data'], ['data', 'ts' => 'NOW()'], ['conflict' => ['id']]);
$sql = $dialect->upsert('pages', ['id', 'sort'], ['sort'], ['conflict' => ['id'], 'rows' => 1001, 0], [1002, 1]);
$database->exec($sql);

Helper output goes through prepare(), query() and exec() like any other SQL. A translating dialect's translator recognizes statements that are already in its own syntax and leaves them unchanged.

Writing SQL that works with every database

Most MySQL syntax that ProcessWire and modules commonly use works on SQLite and PostgreSQL unchanged, including INSERT ... SET, INSERT IGNORE, ON DUPLICATE KEY UPDATE ... VALUES(), backtick identifiers, LIMIT offset,count, REGEXP/RLIKE, GROUP_CONCAT(... ORDER BY ... SEPARATOR ...), CREATE TABLE with KEY/UNIQUE KEY/FULLTEXT KEY definitions, most ALTER TABLE operations, SHOW TABLES/SHOW COLUMNS/SHOW INDEX/DESCRIBE (and SHOW CREATE TABLE on SQLite), and common functions such as NOW(), UNIX_TIMESTAMP(), FROM_UNIXTIME(), DATE_FORMAT(), DATE_ADD()/DATE_SUB(), IF(), FIELD(), CONCAT(), CONCAT_WS(), LOCATE(), SUBSTRING_INDEX(), GREATEST(), LEAST() and RAND().

// works on MySQL, SQLite and PostgreSQL
$query = $database->prepare(
    "INSERT INTO my_table SET name=:name, qty=:qty " .
    "ON DUPLICATE KEY UPDATE qty=VALUES(qty)"
);
$query->bindValue(':name', $name);
$query->bindValue(':qty', $qty, \PDO::PARAM_INT);
$database->execute($query);

// use a capability check where the databases differ
if($database->dialect()->supportsFulltext()) {
    $sql = "SELECT pages_id FROM field_body WHERE MATCH(data) AGAINST(:text)";
} else {
    $sql = "SELECT pages_id FROM field_body WHERE data LIKE :text";
}

Guidelines:

• Use bound values or quote()/escapeStr() for values. With SQLite and PostgreSQL these escape MySQL-style, which the translator expects. Do not escape values with '' quoting.
• Get row counts with COUNT(*) or by counting fetched rows. With SQLite, rowCount() after a SELECT runs an additional COUNT(*) query (PDO's SQLite driver does not provide it).
• Close cursors (closeCursor()) or fetch all rows before changing the schema of a table you just read from.
• Use $pages->find() and other ProcessWire APIs for selector-based queries: they handle the database differences for you (for example, fulltext operators on SQLite).
• Compare numbers with numbers. MySQL silently converts qty = '' or qty = 'abc' to qty = 0; the PostgreSQL translator does the same for columns it knows the type of, but a bound value compared to a column of a derived table or a subquery is passed through, and PostgreSQL then reports the type mismatch.

SQLite

SQLite support is experimental. The installer offers it as a database type when PHP's pdo_sqlite extension and SQLite 3.35+ are available (CLI installer: 'dbType' => 'sqlite', optionally 'dbFile').

• Requirements: PHP's pdo_sqlite extension and SQLite 3.35.0 or newer (checked on connect). On PHP 8.4+, connections use Pdo\Sqlite.
• How it works: ProcessWire's SQL (MySQL syntax) is translated by WireDatabaseSQLiteTranslator and MySQL-compatible functions are registered with SQLite. Schema introspection (getTables(), getColumns(), getIndexes(), tableExists(), columnExists(), indexExists()) queries SQLite's PRAGMA functions directly and returns the same values the MySQL dialect returns, while SHOW and DESCRIBE queries issued by module code continue to be emulated by the translator. Statements that translate to multiple SQLite statements (such as CREATE TABLE with indexes, or an ALTER TABLE that requires rebuilding the table) are executed atomically.
• Settings: $config->dbOptions may contain an array indexed by database type, holding PDO driver options and ProcessWire settings that apply only to that type. For SQLite the settings are currently unicodeSort (see the table below), fulltext (see Fulltext search below) and foldIndex (see Fold indexes below). Assign the whole array, as in $config->dbOptions = ['sqlite' => ['unicodeSort' => true]];
• Connection: WAL journal mode, 5 second busy timeout. Errors for unknown tables and columns use MySQL's SQLSTATE codes (42S02, 42S22) and are reported at execute() rather than prepare(), as with MySQL.
• Planner statistics: without them SQLite often leaves unused the index that would serve a query (i.e. a title= lookup scans the template's pages). A database that has none (one just installed, or made before ProcessWire gathered them) gets a full ANALYZE on connecting, once: about 0.7 seconds per 20,000 pages. So on a large site, the first request after upgrading takes that much longer. One connection runs it, in a write transaction; requests that arrive meanwhile skip it rather than wait, and until it finishes, their queries run without statistics, as before (and their writes wait for it, up to the busy timeout). After that, PRAGMA optimize runs when each request ends (normally a fraction of a millisecond), analyzing again tables whose size has changed a lot, which SQLite does from a sample of their rows. ANALYZE TABLE (MySQL syntax) gathers full statistics for the tables it names at any time, i.e. after a large import.

Behavior differences from MySQL:

Not supported (throws an exception):

• MATCH ... AGAINST without FTS5 (an exception says so): check supportsFulltext() and use LIKE or REGEXP instead, or let DatabaseQuerySelectFulltext handle it. With FTS5, MATCH on columns that no FULLTEXT key has exactly raises MySQL's error 1191, as MySQL does. SQLite's own x MATCH y operator is passed through.
• FOUND_ROWS() (SQL_CALC_FOUND_ROWS is ignored): use COUNT(*), or check supportsFoundRows().
• UPDATE with JOIN, and multi-table DELETE with more than one target table (DELETE t FROM t JOIN ... with one target is supported).
• SELECT ... FOR UPDATE / LOCK IN SHARE MODE, user variables (@var), stored procedures.
• ALTER TABLE ... ADD CONSTRAINT and RENAME INDEX.
• ALTER TABLE operations that require rebuilding a table (MODIFY, CHANGE, primary key changes) on tables with triggers (other than those of FULLTEXT keys), CHECK/FOREIGN KEY/UNIQUE constraints, or indexes not created through ProcessWire.

Fulltext search

When SQLite has FTS5 (standard in PHP's builds), each MySQL FULLTEXT KEY name (columns) on table t becomes an FTS5 table t__fts_name, holding the text and the row's primary key, kept in sync by the triggers t__fts_name_ai, _ad and _au. MATCH ... AGAINST is translated to a lookup in it (boolean and natural language mode, with MySQL's boolean syntax), so the selector fulltext operators work as on MySQL, with relevance ordering.

• Words are matched case- and accent-insensitively, and _ is part of a word (as InnoDB).
• getIndexes() and SHOW INDEX report the key as FULLTEXT, SHOW CREATE TABLE (and so backups) include it, and getTables()/SHOW TABLES do not list the FTS5 tables.
• Only databases created with FTS5 support use it: a new database gets a marker table, pw_fulltext_v1. Databases created before it keep using LIKE/REGEXP for the fulltext operators until reinstalled.
• To turn it off, set $config->dbOptions = ['sqlite' => ['fulltext' => false]]; before the database is created.
• Differences from MySQL are listed in the table above.

Fold indexes

An = (or !=, <, >...) comparison of text with a value that is not ASCII-only (i.e. title=Crème brûlée) is folded in PHP (COLLATE pw_ci), so it compares every row: about 17 ms rather than 0.3 ms at 20,000 pages. With $config->dbOptions = ['sqlite' => ['foldIndex' => true]]; each index on a single text column gets a companion index on pw_fold(column) (named table__index__fold, and not reported by getIndexes(), SHOW INDEX or SHOW CREATE TABLE), and such comparisons become pw_fold(column) = pw_fold(value), which can use it.

• The companion indexes are added on the next connection after turning it on, and dropped on the next one after turning it off (a marker table, pw_fold_index_v1, records that they are there).
• Off by default, because the database file then needs ProcessWire's pw_fold() function: other tools, i.e. the sqlite3 command line or a database browser, can still read it, but cannot insert, update or delete rows in those tables, VACUUM it or run PRAGMA integrity_check. Turn it off (and connect once) before doing those.

PostgreSQL

PostgreSQL support is experimental. The installer offers it as a database type when PHP's pdo_pgsql extension is available (CLI installer: 'dbType' => 'pgsql' with dbName, dbUser, dbPass, dbHost, dbPort or dbSocket). The installer creates the database when it does not exist and the user may do so. Text comparisons ignore case and accents as on MySQL, using the unaccent extension (see "Case- and accent-insensitive matching" below).

• Requirements: PHP's pdo_pgsql extension and PostgreSQL 16.0 or newer (checked on connect). The pg_trgm extension is recommended: the installer creates it when it can, and FULLTEXT indexes then also get trigram indexes that speed up LIKE/REGEXP searches. On PHP 8.4+, connections use Pdo\Pgsql.
• How it works: ProcessWire's SQL (MySQL syntax) is translated by WireDatabasePgsqlTranslator. The translator reads column types from the catalog (cached per connection) so that it can reproduce MySQL's loose comparisons, and every table's indexes are named table__index because PostgreSQL index names are unique per schema rather than per table. Schema introspection (getTables(), getColumns(), getIndexes(), tableExists(), columnExists(), indexExists()) queries information_schema and pg_catalog directly and returns the same values the MySQL dialect returns (MySQL type names, auto_increment for identity columns, PRIMARY for the primary key), while SHOW and DESCRIBE queries issued by module code are emulated by the translator. Statements that translate to several PostgreSQL statements (CREATE TABLE with indexes, RENAME TABLE, some ALTER TABLE forms) are executed together when prepared.
• Settings: $config->dbOptions['pgsql'] may contain schema (a schema other than public, added to search_path), trigram (set to false when pg_trgm cannot be installed, so FULLTEXT indexes are skipped rather than failing), savepoints, fold, fulltext, emulatePrepares, randomPageCost, translationCache and translationCacheMax (see the table below). Assign the whole array, as in $config->dbOptions = ['pgsql' => ['schema' => 'processwire']];
• Connection: the session time zone is set to PHP's, so NOW() matches PHP's time as it does with MySQL. Errors for unknown tables and columns and for duplicate keys use MySQL's SQLSTATE codes (42S02, 42S22, 23000) and are reported at execute() rather than prepare(), as with MySQL; errorInfo() carries MySQL's error numbers (1146, 1054, 1062) and the original PostgreSQL message. In debug mode, failed queries are logged with their translation to site/assets/logs/pgsql-errors.txt.
• Values: pdo_pgsql returns integer columns as PHP ints and everything else as strings, which is what pdo_mysql does on PHP 8.1+. Booleans come back as PHP true/false only from expressions that are boolean in PostgreSQL (i.e. SELECT 1=1); TINYINT(1) columns are smallint and return ints.

Behavior differences from MySQL:

Not supported (throws an exception):

• MATCH ... AGAINST when full text search is not set up (an exception says so): check supportsFulltext() and use LIKE or REGEXP instead, or let DatabaseQuerySelectFulltext handle it.

• FOUND_ROWS() (SQL_CALC_FOUND_ROWS is ignored): use COUNT(*), or check supportsFoundRows().

• SHOW CREATE TABLE. Backups and site profile exports (WireDatabaseBackup) take each table's CREATE TABLE from the schema log instead (see below), which keeps what translation loses. For a table's current structure use getColumns($table, 3) and getIndexes($table, true). A site installed on PostgreSQL before the schema log existed has no log entries for its tables, so its export stops with an error naming such a table: reinstall it.

• UPDATE with LEFT/RIGHT JOIN, UPDATE that sets columns of more than the first table, and multi-table DELETE with more than one target table. (UPDATE t JOIN u ON ... SET t.x=u.y and UPDATE t, u SET ... become UPDATE ... FROM; DELETE t FROM t JOIN ... with one target, UPDATE ... ORDER BY ... LIMIT and DELETE ... LIMIT are supported.)

• LOCK IN SHARE MODE, user variables (@var, @@var), stored procedures, SOUNDS LIKE, <=>, and MySQL collation names in COLLATE clauses. SELECT ... FOR UPDATE is passed through and works.

• Nothing in ALTER TABLE beyond these, which are supported: ADD/DROP/MODIFY/CHANGE COLUMN, ADD/DROP INDEX, ADD/DROP PRIMARY KEY, RENAME COLUMN, RENAME INDEX, ADD [CONSTRAINT name] FOREIGN KEY ... REFERENCES ..., ADD [CONSTRAINT name] CHECK (...), ADD CONSTRAINT name UNIQUE (a unique index, as MySQL makes it), DROP FOREIGN KEY, DROP CHECK and DROP CONSTRAINT. A foreign key violation reports MySQL's SQLSTATE (23000, error 1452). Unlike MySQL, PostgreSQL does not create an index for a foreign key's columns.

• MySQL functions without a translation are passed through and fail unless PostgreSQL has a function of the same name. Translated, with MySQL's results (checked against MariaDB):

  • times: NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME() (the local time in whole seconds, as MySQL gives it), CURDATE(), CURTIME(), UTC_TIMESTAMP(), UTC_DATE(), UNIX_TIMESTAMP(), FROM_UNIXTIME(), DATE_FORMAT() and STR_TO_DATE() (literal formats; STR_TO_DATE() gives a date or a datetime as the format has time parts), DATE_ADD()/DATE_SUB() with INTERVAL, DATE(), YEAR(), MONTH(), DAY(), DAYOFMONTH(), HOUR(), MINUTE(), SECOND(), QUARTER(), DAYOFYEAR(), DAYOFWEEK() (1 is Sunday), WEEKDAY() (0 is Monday), DAYNAME(), MONTHNAME(), LAST_DAY(), DATEDIFF(), TIMESTAMPDIFF() (SECOND to YEAR, truncated as MySQL does);
  • strings: CONCAT() (NULL when an argument is NULL, as in MySQL), LENGTH() (bytes, as in MySQL), FIND_IN_SET() and INSTR() (ignoring case), LOCATE(), SUBSTRING_INDEX() (literal count), MID(), LCASE(), UCASE(), SPACE(), GROUP_CONCAT(), CONVERT(x USING charset) and CONVERT(x, type);
  • others: IF(), IFNULL(), ISNULL(), FIELD(), GREATEST()/LEAST() (NULL when an argument is NULL), LOG() (natural logarithm, as in MySQL), LOG2(), TRUNCATE(), SHA2(), UUID(), RAND(), DATABASE(), LAST_INSERT_ID(), CAST() type names and the lock functions.

  CONCAT_WS(), COALESCE(), NULLIF(), CHAR_LENGTH(), LOWER(), UPPER(), TRIM(), REPLACE(), LEFT(), RIGHT(), SUBSTRING() (with a positive start), LPAD(), RPAD(), REPEAT(), REVERSE(), MD5(), ABS(), ROUND(), FLOOR(), CEIL(), MOD(), POW(), LN(), LOG10() and EXP() exist in PostgreSQL. Missing, for example: SHA1() (PostgreSQL has no built-in SHA-1; use SHA2() or MD5()), WEEK(), YEARWEEK(), ROW_COUNT(), SUBSTRING() with a negative start, and HOUR()/MINUTE() on a TIME value.

Schema log

Every change to the schema (CREATE, ALTER, DROP and RENAME of tables and indexes) made through $database is recorded in the schema_log table, on every database type and always in MySQL syntax. On SQLite and PostgreSQL that is the SQL as ProcessWire issued it, before translation, so the log keeps what translation loses (column types, ENUM values, UNSIGNED, index prefix lengths). Replaying it on an empty MySQL database reproduces the site's schema, i.e. when converting a site to MySQL.

• The first change on a site starts the log with a baseline: the CREATE TABLE of every existing table (from SHOW CREATE TABLE). After that, each successful change is added. Failed statements are not recorded.
• Dropping a table removes its entries. Renaming one moves its entries to the new name.
• $database->schemaLog()->getEntries() returns the entries in replay order.
• Restoring a backup with WireDatabaseBackup is not recorded, since the dump carries its own schema_log.
• Changes made outside $database (i.e. by a module using PDO directly, or in a database client) are not recorded, and neither are temporary tables.
• $database->schemaLog()->getCreateTables() replays the log into one MySQL CREATE TABLE per table (see WireDatabaseSchemaReplay). A table whose history has a statement the replay can't apply with certainty is listed under failed rather than guessed.
• On SQLite and PostgreSQL, WireDatabaseBackup (and so a site profile export) takes each table's CREATE TABLE from the replay, so the dump installs on MySQL as the original schema. A table the log doesn't cover, or whose columns no longer match it, falls back to SHOW CREATE TABLE, with index prefix lengths added for MySQL.
• On SQLite and PostgreSQL the installer sets $config->dbCharset = 'utf8mb4' and $config->dbEngine = 'InnoDB', which are the table options new tables are created with in the log.

Moving a site to another database

A site moves between MySQL, SQLite and PostgreSQL in either direction, since backups carry each table's MySQL CREATE TABLE (see Schema log). There are two ways:

1. Export a site profile and install it (the usual way): export the site with ProcessExportProfile 5.0.2 or newer, including users to keep them, and install that profile on the other database. The new site gets its own config.php and salts, and the installer keeps the profile's users, so existing accounts log in as before.
2. Convert the site in place, keeping its config.php settings and salts:
   1. Check the data (from MySQL to PostgreSQL, see preflight() below) and fix what it reports.
   2. Back up the database: $file = $database->backups()->backup();
   3. Install ProcessWire (any profile) into the new, empty database, which creates what that database type needs (i.e. the PostgreSQL functions).
   4. From that installation, restore the backup over it: $database->backups()->restore($file, ['dropAll' => true]);
   5. Change the site's config.php to the new database ($config->dbType and its connection settings), keeping $config->userAuthSalt and $config->tableSalt as they are, so that passwords still verify.
   6. Compare $database->backups()->getAllTables(true) (row counts per table) on both databases.

Restoring inserts rows one at a time, at roughly 1 MB of dump per second, so a large site takes a while either way.

preflight()

$database->backups()->preflight($dbType = 'pgsql', array $options = []) checks a MySQL database for data that the other database type would reject or change, before anything is copied, and returns what it finds. Each finding is an array with table, column, check, level (error or warning), rows (how many), example (the primary key of one of them, or null) and message (what is wrong and how to fix it).

• For 'sqlite' (or 'mysql') it returns no findings: SQLite stores all of these.
• Invalid UTF-8 is not checked: MySQL does not store it in UTF-8 columns (strict mode rejects it and other modes replace it), and other character sets are converted to valid UTF-8 when read.
• $options['tables'] limits it to some tables (default: all). It reads every row of the columns it checks, once.
• Throws an exception on a database that is not MySQL.

foreach($database->backups()->preflight('pgsql') as $finding) {
  echo "$finding[level]: $finding[message]\n";
}

pdo()

• Arguments: pdo($type = null)
• Returns: \PDO
• Purpose: Get the underlying PDO connection. Omit $type for the writer connection.

$pdo = $database->pdo();

The $database->pdo property is an alias for $database->pdo().

getAttribute() / setAttribute()

• Arguments: getAttribute($attribute), setAttribute($attribute, $value)
• Returns: mixed for getAttribute(), bool for setAttribute()
• Purpose: Proxy to PDO connection attributes.

$driver = $database->getAttribute(\PDO::ATTR_DRIVER_NAME);

errorCode() / errorInfo()

• Returns: Last PDO error code or error info from the last used connection.

reset() / close()

• Arguments: reset($type = null), close($type = null)
• Returns: $database
• Purpose: Close or re-create writer/reader PDO connections.
• Types: $type may be 'writer', 'reader', or omitted for both/current use.

$database->reset();
$database->close('writer');

prepare()

• Arguments: prepare($statement, $driver_options = [], $note = '')
• Returns: \PDOStatement|WireDatabasePDOStatement
• Purpose: Prepare SQL for bound parameters.

$query = $database->prepare("SELECT * FROM pages WHERE id=:id");
$query->bindValue(':id', 1234, \PDO::PARAM_INT);
$database->execute($query);

The $driver_options argument may be:

execute()

• Arguments: execute(\PDOStatement $query, $throw = true, $maxTries = 3)
• Returns: bool
• Purpose: Execute a prepared statement with ProcessWire retry/error handling.

$ok = $database->execute($query);
$ok = $database->execute($query, false); // return false rather than throw

query()

• Arguments: query($statement, $note = '')
• Returns: \PDOStatement
• Purpose: Execute SQL and return a result set.

$rows = $database->query("SELECT id FROM pages LIMIT 10");
foreach($rows as $row) {
    echo $row['id'];
}

exec()

• Arguments: exec($statement, $note = '')
• Returns: int|bool
• Purpose: Execute SQL and return the number of affected rows when available. If given a PDOStatement, it delegates to execute().

$n = $database->exec("UPDATE pages SET modified=modified WHERE id=1234");

lastInsertId()

• Arguments: lastInsertId($name = null)
• Returns: string
• Purpose: Return the last auto-increment ID from the writer connection.

Transactions are available when the current database engine/table supports them (MySQL InnoDB tables, and always with SQLite and PostgreSQL).

if($database->allowTransaction()) {
    $database->beginTransaction();
    try {
        $database->exec("UPDATE ...");
        $database->commit();
    } catch(\Exception $e) {
        $database->rollBack();
        throw $e;
    }
}

beginTransaction()

• Returns: bool
• Purpose: Begin a writer transaction and disable reader use while active.

inTransaction()

• Returns: bool
• Purpose: Check whether the writer connection is currently in a transaction.

commit()

• Returns: bool
• Purpose: Commit the active transaction. Returns false if not in a transaction.

rollBack()

• Returns: bool
• Purpose: Roll back the active transaction. Returns false if not in a transaction.

supportsTransaction() / allowTransaction()

• Arguments: supportsTransaction($table = ''), allowTransaction($table = '')
• Returns: bool
• Purpose: Determine whether transactions are supported and currently allowed.

savepointCreate() / savepointRelease() / savepointRollback()

• Arguments: savepointCreate($name), savepointRelease($name), savepointRollback($name); the name is letters, digits and underscores
• Returns: bool. False when not in a transaction; savepointRollback() also returns false when the savepoint is gone, i.e. the database already rolled back the whole transaction (as MySQL does for a deadlock).
• Purpose: Undo part of a transaction and continue with the rest. savepointRollback() undoes what was done since the savepoint and leaves the transaction open. Same SQL on MySQL (InnoDB), SQLite and PostgreSQL. 3.0.274.

$database->savepointCreate('import_row');
try {
    $database->exec("INSERT ...");
    $database->savepointRelease('import_row');
} catch(\PDOException $e) {
    if(!$database->savepointRollback('import_row')) throw $e; // the transaction itself has ended
}

A page saved while a transaction is open (i.e. your own) saves each field within a savepoint. A field that fails is rolled back and reported, and the rest of the save continues, as it does outside a transaction. An error that ends the transaction itself (a deadlock or a lost connection) is rethrown for its owner to handle.

Schema methods inspect database tables, columns and indexes.

getTables()

• Arguments: getTables($allowCache = true)
• Returns: array of table names

$tables = $database->getTables(false); // bypass cache

tableExists()

• Arguments: tableExists($table)
• Returns: bool

getColumns()

• Arguments: getColumns($table, $verbose = false)
• Returns: array
• Purpose: Get column names or verbose column info.

$names = $database->getColumns('pages');
$info  = $database->getColumns('pages', true);      // indexed by column name
$name  = $database->getColumns('pages', 'name');    // one column info

Verbose modes:

columnExists()

• Arguments: columnExists($table, $column = '', $getInfo = false)
• Returns: bool|array

$ok = $database->columnExists('pages', 'name');
$ok = $database->columnExists('pages.name');
$info = $database->columnExists('pages', 'name', true);

getIndexes()

• Arguments: getIndexes($table, $verbose = false)
• Returns: array

$indexes = $database->getIndexes('pages');
$info = $database->getIndexes('pages', true);
$primary = $database->getIndexes('pages.PRIMARY', true);

indexExists()

• Arguments: indexExists($table, $indexName, $getInfo = false)
• Returns: bool|array

getPrimaryKey()

• Arguments: getPrimaryKey($table, $verbose = false)
• Returns: string|array
• Purpose: Return primary key column(s), or verbose primary-key info.

renameColumn() / renameColumns()

• Arguments: renameColumn($table, $oldName, $newName), renameColumns($table, array $columns)
• Returns: bool for renameColumn(), int count for renameColumns()
• Purpose: Rename columns without changing type.

$database->renameColumn('my_table', 'old_name', 'new_name');
$database->renameColumns('my_table', [
    'old_a' => 'new_a',
    'old_b' => 'new_b',
]);

Use these helpers when building SQL identifiers or operator strings from dynamic values. They sanitize identifiers; they do not quote identifiers with backticks.

escapeTable() / escapeCol()

• Arguments: escapeTable($table), escapeCol($col)
• Returns: string containing only _a-zA-Z0-9

$table = $database->escapeTable($inputName);
$col = $database->escapeCol($inputColumn);

escapeTableCol()

• Arguments: escapeTableCol($str)
• Returns: sanitized table.column, table, or column string

$field = $database->escapeTableCol('pages.name');

isOperator()

• Arguments: isOperator($str, $operatorType = WireDatabasePDO::operatorTypeAny, $get = false)
• Returns: bool|string
• Purpose: Validate comparison or bitwise SQL operators.

if($database->isOperator($operator, WireDatabasePDO::operatorTypeComparison)) {
    // =, !=, <, <=, >, >=, <>
}

Operator type constants:

escapeOperator()

• Arguments: escapeOperator($operator, $operatorType = WireDatabasePDO::operatorTypeComparison, $default = '=')
• Returns: valid operator or fallback

quote()

• Arguments: quote($str)
• Returns: quoted and escaped string value, including surrounding quotes.

$sql = "name=" . $database->quote($name);

Prefer prepared statements for values whenever possible.

escapeStr()

• Arguments: escapeStr($str)
• Returns: escaped string without surrounding quotes.

escapeLike()

• Arguments: escapeLike($like)
• Returns: escaped string suitable for SQL LIKE values.

$like = '%' . $database->escapeLike($term) . '%';

getVersion()

• Arguments: getVersion($getNumberOnly = false)
• Returns: MySQL/MariaDB version string, or SQLite version string (i.e. 3.47.0).

getServerType()

• Returns: server type string such as MySQL, MariaDB, Percona, or SQLite.

getRegexEngine()

• Returns: ICU or HenrySpencer. With SQLite, ICU (REGEXP uses PHP's PCRE).

getEngine()

• Returns: current configured database engine in lowercase ($config->dbEngine, not meaningful with SQLite).

getCharset()

• Returns: current configured database charset in lowercase.

getVariable()

• Arguments: getVariable($name, $cache = true, $sub = true)
• Returns: string|null
• Purpose: Retrieve a MySQL/MariaDB variable. With SQLite, only version, version_comment and the fulltext word length variables return a value; others return null.

$version = $database->getVariable('version');

getMaxIndexLength()

• Returns: int max length allowed for a fully indexed varchar column.

getTime()

• Arguments: getTime($getTimestamp = false)
• Returns: ISO datetime string or UNIX timestamp.

$now = $database->getTime();
$ts = $database->getTime(true);

getStopwords() / isStopword()

• Arguments: getStopwords($engine = '', $flip = false), isStopword($word, $engine = '')
• Returns: array or bool
• Purpose: Get or check fulltext stopwords for MyISAM/InnoDB.

queryLog()

• Arguments: queryLog($sql = '', $note = '')
• Returns: array|bool
• Purpose: Start, stop, reset, retrieve, or append to the in-memory query log.

$database->queryLog(true);          // reset and start
$database->query("SELECT 1", "note");
$log = $database->queryLog();       // retrieve
$database->queryLog(false);         // stop

Argument behavior:

Core automatically populates this log when ProcessWire debug mode is active.

backups()

• Returns: WireDatabaseBackup
• Purpose: Create a backup helper instance configured for this database.

$backups = $database->backups();
$file = $backups->backup(['description' => 'Before upgrade']);
$backups->restore(basename($file));

See WireDatabaseBackup for backup and restore operations.

With SQLite, backups work the same way. Backup files are MySQL-syntax SQL, rebuilt from the SQLite schema (including indexes; FULLTEXT indexes are saved as regular indexes), and restore through the SQL translator. Backup filenames use the database file's name, i.e. site_2026-01-01_12-00-00.sql.

• $database->dialect() capability checks are preferred over checking $config->dbType or name().
• SQL with values embedded in it must be escaped with quote()/escapeStr(), never by hand.
• With SQLite, avoid rowCount() on SELECT statements where performance matters (it runs a second query).
• With SQLite, failed queries are logged to site/assets/logs/sqlite-errors.txt in debug mode, including the original and translated SQL.
• WireDatabaseSQLiteTranslator can also be used on its own (it has no ProcessWire dependencies), i.e. (new WireDatabaseSQLiteTranslator($pdo))->translate($sql).