I added a new column to my plugin’s database table. Tested locally — everything worked. Deployed to the live site — plugin crashed with a “Column not found” error in front of actual users.
The column was sitting in my local database. It was not on the live server. Of course it wasn’t. I had added it by running ALTER TABLE directly in phpMyAdmin. That only touched my local machine. The live server had no idea a new column was supposed to exist.
This is the mistake I see most Moodle developers make once. You do it, the live site breaks, you manually run the SQL on the server at odd hours, and then you learn there’s a proper way to do this.
Why Manual SQL Doesn’t Scale
Running queries directly in phpMyAdmin works fine when you’re the only one touching the database. The moment you have a staging server, a live server, or other developers on the project, manual SQL becomes a liability. Someone always forgets to run it somewhere. And if you’re distributing a plugin to other Moodle sites, you obviously can’t SSH into every installation and run ALTER TABLE yourself.
Moodle solves this with an upgrade system that runs automatically. You describe the database change in PHP, bump a version number, and the next time an admin visits /admin/index.php, Moodle detects the version bump and runs your changes. On every server, every time, without anyone touching the database manually.
How the Upgrade System Works
Every Moodle plugin has a version.php file with a line like this:
$plugin->version = 2024042200;
That number is a 10-digit timestamp — year, month, day, and a two-digit sequence. Moodle stores the last-run version in its database. When you visit /admin/index.php, it compares the stored number to the one in version.php. If version.php is higher, Moodle looks in db/upgrade.php for blocks to run.
The upgrade file is a function that receives $oldversion — the version that was last saved. Each block checks whether $oldversion is lower than a specific number, and if so, runs the database change and saves a new savepoint. That’s the whole mechanism.
Adding a Column the Right Way
- First, bump the version in version.php:
// Before $plugin->version = 2024042200; // After $plugin->version = 2024042201;
Increment the last two digits by 1. If you run multiple changes on the same day, use 01, 02, 03, and so on. Never reuse a version number — Moodle silently skips blocks it has already run, so reusing one means your change never runs on existing sites.
- Then write the upgrade block in db/upgrade.php:
function xmldb_yourplugin_upgrade($oldversion) {
global $DB;
$dbman = $DB->get_manager();
if ($oldversion < 2024042201) {
$table = new xmldb_table('yourplugin');
$field = new xmldb_field(
'newcolumn',
XMLDB_TYPE_INTEGER,
'10',
null,
XMLDB_NOTNULL,
null,
'0', // default value
'existingcolumn' // add it after this column
);
if (!$dbman->field_exists($table, $field)) {
$dbman->add_field($table, $field);
}
upgrade_mod_savepoint(true, 2024042201, 'yourplugin');
}
return true;
}
A few things worth noting here.
The field_exists() check on line 16 is not optional — it’s the thing that stops your upgrade from crashing on servers that already have the column. Maybe a developer on your team added it manually before you wrote this upgrade. Maybe a previous version of the plugin created it under a different migration. Without field_exists(), running add_field() on an existing column throws a database error and the upgrade fails completely. With it, the block runs harmlessly and moves on.
Using $dbman->add_field() instead of a raw ALTER TABLE also matters. Moodle runs on both MySQL and PostgreSQL. Raw ALTER TABLE syntax differs between them. The $dbman methods generate the right SQL for whatever database the site is running on.
The upgrade_mod_savepoint() call at the end is what tells Moodle to record this version as complete. Forget it and Moodle will re-run the block on the next visit to /admin/index.php.
Don’t Forget install.xml
upgrade.php handles existing installations. Fresh installs — someone installing your plugin for the first time — use db/install.xml instead. Upgrade blocks never run on a clean install.
So you need to add the new field to install.xml as well:
<FIELD NAME="newcolumn" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false"/>
The easiest way to generate this correctly is through Moodle’s built-in XMLDB editor at Site Administration → Development → XMLDB editor. It writes the XML for you — you don’t have to get the attribute names and types right by hand.
Common Mistakes That Will Bite You
| Mistake | What happens |
|---|---|
| Reusing the same version number | Second block never runs on sites that already ran the first |
Missing upgrade_mod_savepoint() |
Block re-runs on every admin page visit |
Using ALTER TABLE instead of $dbman |
Breaks on PostgreSQL |
Updating version.php without visiting /admin/index.php |
Upgrade never triggers |
Not adding the field to install.xml |
Fresh installs don’t have the column |
Running the Upgrade
Once you have version.php bumped and upgrade.php written, deploy the files to the server and visit /admin/index.php. Moodle will detect the version difference and show an upgrade confirmation screen. Confirm it and the block runs. You can verify the column exists afterwards with a quick query in phpMyAdmin or the DB console:
SQL:
DESCRIBE mdl_yourplugin;
If the column is there, the upgrade ran. If not, check the Moodle error logs — either the version didn’t change or upgrade_mod_savepoint() is missing.
What I Do Now
I never touch the plugin database manually anymore — not even locally. If I need to add a column during development, I write the upgrade block first, bump the version, and run it through /admin/index.php the same way the live server will. That way, when I deploy, there’s nothing to remember and nothing to run manually.
The few minutes it takes to write an upgrade block have saved me from a lot of late-night SSH sessions.