Moodle Database and Data APIs: Developer Reference Guide

Moodle Core API Overview

Moodle Database API (DML)

Getting Started

phpglobal $DB;

Read Operations

Get a Single Record

php// Get user by ID
$user = $DB->get_record('user', ['id' => 5]);
echo $user->firstname; // "Alice"

// Get with specific fields only
$user = $DB->get_record('user', ['id' => 5], 'id, firstname, email');

// Throw exception if not found
$user = $DB->get_record('user', ['id' => 999], '*', MUST_EXIST);

Get Multiple Records

php// Get all users with a specific email domain
$users = $DB->get_records_select('user', "email LIKE ?", ['%@school.edu']);

// Get records with SQL
$users = $DB->get_records_sql(
    "SELECT * FROM {user} WHERE confirmed = ? AND deleted = ?",
    [1, 0]
);

// Get as menu (id => fullname) for dropdowns
$courses = $DB->get_records_menu('course', null, '', 'id, fullname');

Count Records

php$count = $DB->count_records('user', ['confirmed' => 1]);
$count = $DB->count_records_select('user', "email LIKE ?", ['%@school.edu']);

Check if Record Exists

phpif ($DB->record_exists('user', ['username' => 'jdoe'])) {
    // User exists
}

Write Operations

Insert a Record

php$record = new stdClass();
$record->username = 'jdoe';
$record->firstname = 'John';
$record->lastname = 'Doe';
$record->email = '[email protected]';

$id = $DB->insert_record('user', $record);

Update a Record

php$record = $DB->get_record('user', ['id' => 5]);
$record->firstname = 'Jane';
$DB->update_record('user', $record);

Delete Records

php// Delete by conditions
$DB->delete_records('user', ['id' => 5]);

// Delete with SQL
$DB->delete_records_select('log', "timecreated < ?", [strtotime('-1 year')]);

Transactions

php$transaction = $DB->start_delegated_transaction();

try {
    $DB->insert_record('course', $course1);
    $DB->insert_record('course', $course2);
    $transaction->allow_commit();
} catch (Exception $e) {
    $transaction->rollback($e);
}

Important Rules

• Always use placeholders (? or named :param) — never concatenate SQL
• Use {tablename} syntax in raw SQL — Moodle adds the table prefix automatically
• Never use mysql_* or PDO directly — always use $DB

Moodle Data Definition API (DDL)

Define Tables in db/install.xml

xml<TABLE NAME="local_myplugin" COMMENT="My plugin data">
  <FIELDS>
    <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
    <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true"/>
    <FIELD NAME="data" TYPE="text" NOTNULL="false"/>
    <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true"/>
  </FIELDS>
  <KEYS>
    <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
    <KEY NAME="userid" TYPE="foreign" FIELDS="userid" REFTABLE="user" REFFIELDS="id"/>
  </KEYS>
</TABLE>

Upgrade in db/upgrade.php

phpfunction xmldb_local_myplugin_upgrade($oldversion) {
    global $DB;
    $dbman = $DB->get_manager();

    if ($oldversion < 2026021500) {
        $table = new xmldb_table('local_myplugin');
        $field = new xmldb_field('status', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0');

        if (!$dbman->field_exists($table, $field)) {
            $dbman->add_field($table, $field);
        }

        upgrade_plugin_savepoint(true, 2026021500, 'local', 'myplugin');
    }
}

Moodle Cache API (MUC)

Cache Types

Define a Cache in db/caches.php

php$definitions = [
    'mycache' => [
        'mode' => cache_store::MODE_APPLICATION,
        'simplekeys' => true,
        'simpledata' => true,
    ],
];

Use the Cache

php$cache = cache::make('local_myplugin', 'mycache');

// Set
$cache->set('key1', 'value1');

// Get
$value = $cache->get('key1');

// Delete
$cache->delete('key1');

// Purge all
$cache->purge();

Moodle Calendar API

Create a Calendar Event

php$event = new stdClass();
$event->name = 'Quiz Due Date';
$event->description = 'Biology Quiz Chapter 5 is due';
$event->courseid = 5;
$event->userid = 0; // 0 = course event
$event->eventtype = 'course';
$event->timestart = strtotime('2026-03-01 23:59');
$event->timeduration = 0;
$event->visible = 1;

calendar_event::create($event);

Event Types

Moodle Course API

Get Course Information

php$course = get_course($courseid);
echo $course->fullname;

Get Course Modules

php$modinfo = get_fast_modinfo($courseid);
foreach ($modinfo->get_cms() as $cm) {
    echo $cm->name . ' (' . $cm->modname . ')';
}

Create a Course Module

php$moduleinfo = new stdClass();
$moduleinfo->modulename = 'quiz';
$moduleinfo->course = $courseid;
$moduleinfo->section = 1;
$moduleinfo->name = 'Chapter 5 Quiz';
$moduleinfo->intro = 'Test your knowledge of Chapter 5';
$moduleinfo->introformat = FORMAT_HTML;

$module = create_module($moduleinfo);

Moodle Analytics API

Built-in Models

• Students at risk of dropping out — predicts dropout based on engagement
• No teaching — detects courses with no teacher activity
• No recent access — flags inactive students

Creating Custom Analytics

phpclass target_low_quiz_score extends \core_analytics\local\target\binary {
    public static function get_name(): \lang_string {
        return new \lang_string('targetlowquizscore', 'local_myplugin');
    }

    protected function calculate_sample($sampleid, $sampleorigin, $starttime, $endtime) {
        // Return 1 if student scored below 60%, 0 otherwise
    }
}

Does Moodle Support xAPI?

• Logstore xAPI — sends Moodle events as xAPI statements to a Learning Record Store (LRS)
• H5P — H5P content generates xAPI statements natively
• SCORM — SCORM content can emit xAPI through conversion

What is xAPI?

Setting Up xAPI in Moodle

1. Install the Logstore xAPI plugin from moodle.org/plugins
2. Configure your LRS endpoint, username, and password
3. Select which events to track (course views, quiz attempts, assignment submissions)
4. xAPI statements are sent automatically as students interact with Moodle

Getting Content into Moodle

FAQ

What database does Moodle use?

Can I write raw SQL in Moodle?

What is MUC in Moodle?

Does Moodle support Redis?

Summary