One of the things that we decided to do sooner, rather than later, was change our database library from mysqli to PDO. As we'll talk about later, PDO helps us write new database adapters more easily which becomes useful for supporting foreign databases for things like imports or, useful for third party developers who may wish to integrate with locally hosted foreign databases. PDO is also generally the de-facto standard for PHP at this point. Many hosts come with PDO enabled by default, whereas mysqli will often need to be enabled manually. We've seen this play out a couple of times when people change hosts or even change versions without ensuring the mysqli extension is enabled first.
To be able to install or upgrade to XenForo 2.4, you will need to ensure that PDO is available with the mysql driver in your hosting environment.
There are also some quality of life improvements included in PDO such as named parameter binding, for example:
Though, in retrospect, that may end up being less useful than you'd think... (more on that later).
Native support for SQLite
SQLite is at this point the most widely deployed database in the world (across phones, embedded devices, apps etc.) with some even using it in large scale, high performance, production environments. SQLite is also great for developers due to its prevalence, zero-configuration setup, and portability (it's just a single file) with excellent performance on local disks. Despite its simplicity, SQLite is fully ACID compliant and remarkably robust. It is not just convenient, it's enterprise-grade reliable.
For advanced developers, particularly those with dev ops pipelines, SQLite lends itself extremely well to automated testing - you can spin up a complete, isolated database instance instantly for each test run. While we will still officially recommend MySQL for production, SQLite for testing and development is hugely attractive and we're excited to put it in your hands starting with XenForo 2.4.
But what about all of the existing queries in the code?
Brand new fluent query builder
A new database means supporting a different database dialect. But no one wants to write database queries twice. In fact, it's a little bit annoying writing them once. The new query builder aims to make life easier. Let's rewrite the query I rewrote earlier, now using the new query builder:
This query builder instance automatically compiles to the right SQL dialect for the database in use. We have rewritten all existing queries in the software (and official add-ons) to utilise the new query builder, including those generated by the Finder and the SchemaManager to support SQLite.
Of course you don't have to use it. You will be able to write different queries directly to support both MySQL (as now) and SQLite or you will be able to forget about SQLite altogether (though this may affect people's ability to use your add-on if they are using SQLite).
This also opens the door for supporting other databases in the future. While there are no immediate plans, support for MS SQL and PostgreSQL is now a possibility. Anyone who may need that right now will be able to easily write your own adapters and grammar classes for the query builder in order to add support if you need to.
A PhpStorm plugin for developers
Ekli dosyayı görüntüle 322589-b08f75fa3cba8f63c60da2845e10201d.mov
Debugging queries
We have implemented a few methods to help developers check what query would be compiled by any particular query builder call for debugging purposes.
We support
Speaking of debugging queries - we've also given our debug output a refreshed look, now with better colour usage in both light and dark mode and syntax highlighting and formatting for database queries:
Built-in PHP web server support
Finding the right environment for development and testing can be challenging. There are a bunch of products available for different operating systems. Sometimes it can be difficult to find the right one for you, sometimes you might run into issues after upgrades, or you switch on your laptop one day and none of your databases are accessible. We've already talked about how SQLite can help with that - not having to manage your own MySQL or MariaDB instance is a huge benefit - but what if you didn't have to bother with Nginx or Apache, either?
Utilising the PHP built-in web server, you can quickly spin up a development server without ever having to touch web server configs or full environments like Herd, WAMP, MAMP or Docker.
XenForo Development REPL
A new
The REPL is preloaded with common variables, like
You may use the
The Psysh website and docs may be helpful references for getting started with it:
The Psysh website and docs may be helpful references for getting started with it:
To be able to install or upgrade to XenForo 2.4, you will need to ensure that PDO is available with the mysql driver in your hosting environment.
There are also some quality of life improvements included in PDO such as named parameter binding, for example:
PHP:
$activeUsers = \XF::db()->fetchAll("
SELECT *
FROM xf_user AS user
INNER JOIN xf_user_profile AS profile ON
(user.user_id = profile.user_id)
WHERE user.user_state = :user_state
AND user.last_activity > :min_activity
ORDER BY user.last_activity DESC
LIMIT 10
", ['user_state' => 'valid', 'min_activity' => time()]);Though, in retrospect, that may end up being less useful than you'd think... (more on that later).
Native support for SQLite
SQLite is at this point the most widely deployed database in the world (across phones, embedded devices, apps etc.) with some even using it in large scale, high performance, production environments. SQLite is also great for developers due to its prevalence, zero-configuration setup, and portability (it's just a single file) with excellent performance on local disks. Despite its simplicity, SQLite is fully ACID compliant and remarkably robust. It is not just convenient, it's enterprise-grade reliable.
For advanced developers, particularly those with dev ops pipelines, SQLite lends itself extremely well to automated testing - you can spin up a complete, isolated database instance instantly for each test run. While we will still officially recommend MySQL for production, SQLite for testing and development is hugely attractive and we're excited to put it in your hands starting with XenForo 2.4.
But what about all of the existing queries in the code?
Brand new fluent query builder
A new database means supporting a different database dialect. But no one wants to write database queries twice. In fact, it's a little bit annoying writing them once. The new query builder aims to make life easier. Let's rewrite the query I rewrote earlier, now using the new query builder:
PHP:
$activeUsers = \XF::query('xf_user AS user')
->join('xf_user_profile AS profile', 'user.user_id', '=', 'profile.user_id')
->where('user.user_state', 'valid')
->where('user.last_activity', '>', time() - 86400)
->orderByDesc('user.last_activity')
->limit(10)
->fetchAll();This query builder instance automatically compiles to the right SQL dialect for the database in use. We have rewritten all existing queries in the software (and official add-ons) to utilise the new query builder, including those generated by the Finder and the SchemaManager to support SQLite.
Of course you don't have to use it. You will be able to write different queries directly to support both MySQL (as now) and SQLite or you will be able to forget about SQLite altogether (though this may affect people's ability to use your add-on if they are using SQLite).
This also opens the door for supporting other databases in the future. While there are no immediate plans, support for MS SQL and PostgreSQL is now a possibility. Anyone who may need that right now will be able to easily write your own adapters and grammar classes for the query builder in order to add support if you need to.
A PhpStorm plugin for developers
Ekli dosyayı görüntüle 322589-b08f75fa3cba8f63c60da2845e10201d.mov
Debugging queries
We have implemented a few methods to help developers check what query would be compiled by any particular query builder call for debugging purposes.
PHP:
$pairs = \XF::query('xf_user')
->dump()
->select('user_id', 'username')
->limit(5)
->fetchPairs();We support
dump, dd, dumpSimple, ddSimple, dumpToFile, dumpToFileNamed methods anywhere in the query builder chain. These methods register an event that fires before the final query executes, meaning it also works with aggregate methods such as count and sum.
SQL:
SELECT "user_id", "username" FROM "xf_user" LIMIT 5Speaking of debugging queries - we've also given our debug output a refreshed look, now with better colour usage in both light and dark mode and syntax highlighting and formatting for database queries:
Built-in PHP web server support
Finding the right environment for development and testing can be challenging. There are a bunch of products available for different operating systems. Sometimes it can be difficult to find the right one for you, sometimes you might run into issues after upgrades, or you switch on your laptop one day and none of your databases are accessible. We've already talked about how SQLite can help with that - not having to manage your own MySQL or MariaDB instance is a huge benefit - but what if you didn't have to bother with Nginx or Apache, either?
Kod:
# php cmd.php xf:serve
Starting XenForo development server...
XenForo development server started: http://localhost:8080
Document root is: /Users/chrisdeeming/Herd/24x
Press Ctrl-C to quit.
13:26:20 GET 200 / (432.84ms)
13:26:21 GET 200 /css.php?css=public%3Anormalize.css%2Cpublic%3Afa.css%2Cpublic%3Avariations.less%2Cpublic%3Acore.less%2Cpublic%3Aapp.less&s=1&l=1&d=1747965566&k=975f8f0746a2cbf3a7f3ee7c96f538e9a2bf61e6 (488.67ms)
13:26:21 GET 200 /css.php?css=public%3Anode_list.less%2Cpublic%3Ashare_controls.less%2Cpublic%3Aextra.less&s=1&l=1&d=1747965566&k=b21bffa3ee848ff6a7384493bd4c8b387d4a511e (58.2ms)
13:26:21 GET 200 /js/xf/preamble.js?_v=e361daff
...Utilising the PHP built-in web server, you can quickly spin up a development server without ever having to touch web server configs or full environments like Herd, WAMP, MAMP or Docker.
XenForo Development REPL
A new
xf-dev:tinker CLI command has been added, providing a REPL (read–eval–print loop) for XenForo development. It is built on the excellent Psysh library, and provides an easy playground for running code snippets, testing services, and inspecting the environment interactively.The REPL is preloaded with common variables, like
$app, $db, $em, and many others you might (not) expect. You can view the full list with the ls -l --vars command, or disable this behavior with the --without-vars flag.You may use the
--execute option to pass code to run directly, which can be useful for running quick one-off scripts, or pass file paths as arguments to include them when the REPL starts, which can be useful for shared set-up code. The REPL also includes a --with-commands flag to allow calling other XenForo CLI commands within the REPL.The Psysh website and docs may be helpful references for getting started with it:
The Psysh website and docs may be helpful references for getting started with it: