Joomla Database API

This content was generated with the assistance of AI and should be verified against the official Joomla documentation before being relied on in production.

This sub-section’s Joomla bibliography lists the reference material consulted while preparing these pages.

Every Joomla extension that stores its own data talks to the database through one abstraction layer: the Joomla Framework joomla/database package, wrapped by a few CMS classes. This page explains that layer for developers: how an extension obtains the database object, how to build and run queries safely, how to read the results, how to run several statements as one unit, and where the layer stops helping and you must write SQL for each database server yourself. It was checked on 2026-10-30 against Joomla 6.2 (PHP 8.3 or later), the Joomla Developer Manual at https://manual.joomla.org/docs/ (the pages under General Concepts and Security) and the Joomla CMS and Joomla Framework sources. The running example is the Tallpine Hiking Club site: the package pkg_tallpine contains the component com_tallpine (namespace Tallpine\Component\Tallpine) with a trail entity stored in the table #__tallpine_trails, the module mod_tallpine_nexthikes that lists upcoming hikes and the plugin plg_content_tallpine_conditions that reads trail closures.

The Manual shows the 6.1 documentation as "Current" and 6.2 as "Upcoming" on the database pages; the API described here is the same in both, and every code example below was written for the 6.2 line. PHP language features used here (typed properties, readonly, constructor promotion, match) are covered in PHP: classes and objects and are not explained again.

The pieces of the layer

Three kinds of object take part in every database operation, and mixing them up is the source of most beginner mistakes.

  • The driver implements Joomla\Database\DatabaseInterface. It owns the connection to MySQL, MariaDB or PostgreSQL, quotes and escapes values, runs a query and fetches results, and manages transactions. In Joomla 6 the supported drivers are mysqli and pdomysql for MySQL and MariaDB, and pgsql for PostgreSQL; the server family reported by $db->getServerType() is mysql or postgresql. Microsoft SQL Server has not been supported since Joomla 4.0.

  • The query object is built with $db->createQuery(). It is a plain PHP object that collects the clauses (select, from, join, where, order, …​) and the bound values, and renders them to SQL for the driver that created it. It does not talk to the database.

  • The table prefix placeholder #__ is replaced by the configured prefix (jos_, abc12_ or whatever the installer chose) when a query is handed to the driver with $db->setQuery().

sequenceDiagram participant E as Extension code participant Q as Query object participant D as Database driver participant S as Database server E->>D: createQuery() D-->>E: empty query object E->>Q: select, from, where with named placeholders E->>Q: bind placeholder to a variable and a type E->>D: setQuery(query) D->>D: replace the table prefix placeholder E->>D: loadObjectList() D->>S: prepare statement text D->>S: send bound values separately S-->>D: rows D-->>E: array of stdClass objects

The important property is that the statement text and the values travel to the server separately. Values never become part of the SQL string, so they cannot change its structure. That is what "prepared statement" means, and it is the reason the rest of this page insists on bind() and whereIn() rather than string concatenation.

Getting the database object

In a model, table, plugin or other database-aware class

Since Joomla 4 the database is a service in the dependency-injection container, and the CMS classes that need it receive it through a setter rather than reaching for a global. The mechanism is the interface Joomla\Database\DatabaseAwareInterface and the trait Joomla\Database\DatabaseAwareTrait. The trait stores the object in a private property and adds two methods: a public setDatabase(DatabaseInterface $db) that the framework calls, and a protected getDatabase() that returns the object or throws DatabaseNotFoundException when nothing was injected. A class that uses the trait therefore writes $this->getDatabase() wherever it needs a connection and never declares a $db property of its own.

<?php

namespace Tallpine\Component\Tallpine\Administrator\Model;

\defined('_JEXEC') or die;

use Joomla\CMS\MVC\Model\ListModel;
use Joomla\Database\ParameterType;
use Joomla\Database\QueryInterface;

/**
 * List of trails for the administrator.
 */
class TrailsModel extends ListModel
{
    /**
     * Build the query that ListModel executes for the list view.
     */
    protected function getListQuery(): QueryInterface
    {
        // ListModel extends BaseDatabaseModel, which already uses DatabaseAwareTrait.
        $db    = $this->getDatabase();
        $query = $db->createQuery();

        $query
            ->select($db->quoteName(['a.id', 'a.title', 'a.alias', 'a.distance_km', 'a.difficulty', 'a.state', 'a.catid']))
            ->from($db->quoteName('#__tallpine_trails', 'a'));

        $state = (int) $this->getState('filter.published', 1);
        $query
            ->where($db->quoteName('a.state') . ' = :state')
            ->bind(':state', $state, ParameterType::INTEGER);

        $query->order($db->quoteName('a.title') . ' ASC');

        return $query;
    }
}

Source: Joomla Manual: Select Data from the Database

The fragment shows the three habits that the whole page builds on: identifiers go through quoteName(), values go through a named placeholder and bind() with an explicit ParameterType, and the method returns the query object without executing it. ListModel runs it, applies the paging limits and counts the total.

In a service, a module helper or a CLI command

Code that is not a CMS model or plugin gets the connection from the container, or, better, has it passed to its constructor so that it can be tested. In a component the registration happens in services/provider.php:

<?php

\defined('_JEXEC') or die;

use Joomla\CMS\Dispatcher\ComponentDispatcherFactoryInterface;
use Joomla\CMS\Extension\ComponentInterface;
use Joomla\CMS\Extension\Service\Provider\ComponentDispatcherFactory;
use Joomla\CMS\Extension\Service\Provider\MVCFactory;
use Joomla\CMS\HTML\Registry;
use Joomla\CMS\MVC\Factory\MVCFactoryInterface;
use Joomla\Database\DatabaseInterface;
use Joomla\DI\Container;
use Joomla\DI\ServiceProviderInterface;
use Tallpine\Component\Tallpine\Administrator\Extension\TallpineComponent;
use Tallpine\Component\Tallpine\Administrator\Service\TrailRepository;

return new class () implements ServiceProviderInterface {
    public function register(Container $container): void
    {
        $container->registerServiceProvider(new MVCFactory('\\Tallpine\\Component\\Tallpine'));
        $container->registerServiceProvider(new ComponentDispatcherFactory('\\Tallpine\\Component\\Tallpine'));

        // A plain service that receives the database through its constructor.
        $container->share(
            TrailRepository::class,
            static fn (Container $c) => new TrailRepository($c->get(DatabaseInterface::class))
        );

        $container->set(
            ComponentInterface::class,
            static function (Container $container) {
                $component = new TallpineComponent($container->get(ComponentDispatcherFactoryInterface::class));
                $component->setRegistry($container->get(Registry::class));
                $component->setMVCFactory($container->get(MVCFactoryInterface::class));

                return $component;
            }
        );
    }
};
<?php

namespace Tallpine\Component\Tallpine\Administrator\Service;

\defined('_JEXEC') or die;

use Joomla\Database\DatabaseInterface;
use Joomla\Database\ParameterType;

/**
 * Read access to trails, usable from the site, administrator, API and CLI.
 */
final class TrailRepository
{
    public function __construct(private readonly DatabaseInterface $db)
    {
    }

    /**
     * @return  \stdClass[]
     */
    public function findPublishedByDifficulty(string $difficulty): array
    {
        $query = $this->db->createQuery()
            ->select($this->db->quoteName(['id', 'title', 'alias', 'distance_km']))
            ->from($this->db->quoteName('#__tallpine_trails'))
            ->where($this->db->quoteName('state') . ' = 1')
            ->where($this->db->quoteName('difficulty') . ' = :difficulty')
            ->order($this->db->quoteName('title') . ' ASC')
            ->bind(':difficulty', $difficulty);

        return $this->db->setQuery($query)->loadObjectList() ?: [];
    }
}

Source: Joomla Manual: Dependency Injection

How the container, service providers and namespaces fit together is the subject of Namespaces, autoloading and dependency injection. For this page the point is only that DatabaseInterface::class is the key to ask for.

Where no constructor can be changed, for example in a one-off script, fetch the service directly:

use Joomla\CMS\Factory;
use Joomla\Database\DatabaseInterface;

$db = Factory::getContainer()->get(DatabaseInterface::class);

Source: Joomla Manual: Select Data from the Database

The Manual states that the Joomla 3 call Factory::getDbo() is deprecated and must not be used in new code, and that DatabaseDriver::getQuery(true) has been replaced by DatabaseInterface::createQuery(). The Framework source carries the same deprecation on the $new argument of getQuery(). In a long-lived object do not call Factory::getContainer() inside methods: take the dependency in the constructor.

In a plugin

A plugin class extends CMSPlugin implements SubscriberInterface and, since the plugin no longer receives a $db property automatically, declares that it is database-aware and uses the trait. The services/provider.php of the plugin injects the connection, exactly as core plugins such as plg_content_joomla do:

<?php

namespace Tallpine\Plugin\Content\TallpineConditions\Extension;

\defined('_JEXEC') or die;

use Joomla\CMS\Event\Content\ContentPrepareEvent;
use Joomla\CMS\Plugin\CMSPlugin;
use Joomla\Database\DatabaseAwareInterface;
use Joomla\Database\DatabaseAwareTrait;
use Joomla\Database\ParameterType;
use Joomla\Event\SubscriberInterface;

final class TallpineConditions extends CMSPlugin implements SubscriberInterface, DatabaseAwareInterface
{
    use DatabaseAwareTrait;

    public static function getSubscribedEvents(): array
    {
        return ['onContentPrepare' => 'onContentPrepare'];
    }

    public function onContentPrepare(ContentPrepareEvent $event): void
    {
        if ($event->getContext() !== 'com_tallpine.trail') {
            return;
        }

        $item = $event->getItem();
        $id   = (int) ($item->id ?? 0);

        $db    = $this->getDatabase();
        $query = $db->createQuery()
            ->select($db->quoteName('closed_until'))
            ->from($db->quoteName('#__tallpine_closures'))
            ->where($db->quoteName('trail_id') . ' = :trail')
            ->bind(':trail', $id, ParameterType::INTEGER);

        $closedUntil = $db->setQuery($query)->loadResult();

        if ($closedUntil !== null) {
            $item->text .= '<p class="trail-closed">Closed until ' . htmlspecialchars((string) $closedUntil) . '</p>';
        }
    }
}
<?php

\defined('_JEXEC') or die;

use Joomla\CMS\Extension\PluginInterface;
use Joomla\CMS\Factory;
use Joomla\CMS\Plugin\PluginHelper;
use Joomla\Database\DatabaseInterface;
use Joomla\DI\Container;
use Joomla\DI\ServiceProviderInterface;
use Tallpine\Plugin\Content\TallpineConditions\Extension\TallpineConditions;

return new class () implements ServiceProviderInterface {
    public function register(Container $container): void
    {
        $container->set(
            PluginInterface::class,
            static function (Container $container) {
                $plugin = new TallpineConditions(
                    (array) PluginHelper::getPlugin('content', 'tallpine_conditions')
                );
                $plugin->setApplication(Factory::getApplication());
                $plugin->setDatabase($container->get(DatabaseInterface::class));

                return $plugin;
            }
        );
    }
};

Source: Joomla Manual: How plugins work

Plugin events and the concrete event classes are explained in Plugin development and events. The relevant database point is the Joomla 5.x and 6.x deprecation in CMSPlugin: the old automatic $db and $app property detection logs a deprecation ("The database should be injected through the DatabaseAwareInterface and trait") and is removed in 7.0. Plugins that still read $this->db work in 6.2 only because of that compatibility code.

Database injection in models since Joomla 5.0

Models are created by the MVCFactory, which calls setDatabase() on every model that implements DatabaseAwareInterface. BaseDatabaseModel, the base class of ListModel, AdminModel and FormModel, uses DatabaseAwareTrait, so a model written for Joomla 5 and 6 never creates its own connection.

This has one consequence that catches older code: the database is injected after the constructor has run. In 6.2 the BaseDatabaseModel constructor still falls back to Factory::getDbo() (or to a dbo entry in the $config array) and calls setDatabase() itself, but it triggers the deprecation "Database is not available in constructor in 7.0" when it does. The source comment is explicit: subclasses must not use the database in their constructor. The same holds for MVCFactory: when no database was injected into the factory it logs "Database must be set, this will not be caught anymore in 7.0". In practice:

  • do not run queries or call getDatabase() in a model’s __construct; do it in populateState(), getItem(), getListQuery() or any later method

  • do not pass 'dbo' => $db in the $config array of a model or table; rely on the factory

  • create models with the component’s MVCFactory ($this->getMVCFactory()->createModel('Trail', 'Administrator')), not with new TrailModel(), so the database is injected

  • tests that instantiate a model directly call setDatabase() with a mock or a test connection

Older books such as Developing Extensions for Joomla! 5 (Packt, 2023) show a plugin or model reading $db as an inherited property and obtaining a connection with get('DatabaseDriver'). Both still run in 6.2 through compatibility paths, but they are the deprecated forms; the Aware trait and the container service are the supported ones.

The query builder

Anatomy of a query

createQuery() returns an object implementing Joomla\Database\QueryInterface (concretely a driver-specific subclass of DatabaseQuery). Every clause method returns the query, so calls chain. Method names follow SQL:

Method Purpose

select($columns)

Adds columns. Accepts a string, an array of strings, or several calls. Repeated calls add columns.

from($table, $alias)

Sets the main table. The second argument is an alias that quoted names can use.

join($type, $table, $condition) and innerJoin(), leftJoin(), rightJoin(), outerJoin()

Adds a join. The condition is the ON expression.

where($conditions, $glue)

Adds a condition. Several calls combine with AND; an array is combined with the glue.

whereIn($key, $values, $type) and whereNotIn()

Adds key IN (...) with every value bound separately.

group(), having()

Grouping and filtering of groups.

order($columns)

Adds ordering, for example a.title ASC.

setLimit($limit, $offset)

Limits the number of rows and sets the start. This is the supported way to page results.

insert(), columns(), values(), set()

Build INSERT and UPDATE.

update(), delete()

Start data-changing statements.

union(), unionAll()

Combine selects.

bind(), bindArray()

Attach values to named placeholders.

quoteName() (alias qn()), quote() (alias q()), escape()

Quote identifiers, quote literals and escape text.

A complete select with a join, a conditional filter and paging, from the site model of the trail list:

<?php

namespace Tallpine\Component\Tallpine\Site\Model;

\defined('_JEXEC') or die;

use Joomla\CMS\Factory;
use Joomla\CMS\MVC\Model\ListModel;
use Joomla\Database\ParameterType;
use Joomla\Database\QueryInterface;

class TrailsModel extends ListModel
{
    protected function getListQuery(): QueryInterface
    {
        $db   = $this->getDatabase();
        $user = $this->getCurrentUser();

        $query = $db->createQuery()
            ->select(
                [
                    $db->quoteName('a.id'),
                    $db->quoteName('a.title'),
                    $db->quoteName('a.alias'),
                    $db->quoteName('a.distance_km'),
                    $db->quoteName('a.difficulty'),
                    $db->quoteName('c.title', 'category_title'),
                ]
            )
            ->from($db->quoteName('#__tallpine_trails', 'a'))
            ->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'))
            ->where($db->quoteName('a.state') . ' = 1');

        // Access levels the visitor may see, bound as an integer list.
        $query->whereIn($db->quoteName('a.access'), $user->getAuthorisedViewLevels());

        $difficulty = (string) $this->getState('filter.difficulty', '');

        if ($difficulty !== '') {
            $query
                ->where($db->quoteName('a.difficulty') . ' = :difficulty')
                ->bind(':difficulty', $difficulty);
        }

        $search = trim((string) $this->getState('filter.search', ''));

        if ($search !== '') {
            // Escape LIKE wildcards typed by the visitor, then add our own.
            $pattern = '%' . addcslashes($search, '\\%_') . '%';
            $query
                ->where($db->quoteName('a.title') . ' LIKE :search')
                ->bind(':search', $pattern);
        }

        $query->order($db->quoteName('a.title') . ' ASC');

        return $query;
    }
}

Source: Joomla Manual: Select Data from the Database

A few details of that listing are worth stating because they are easy to get wrong.

  • join() takes a third argument, the condition, so the ON text is added for you. The Manual shows both that form and a form that writes ' ON (...)' by hand inside the table argument; the three-argument form is clearer.

  • getCurrentUser() on a model is provided by CurrentUserTrait, which BaseModel uses. Do not call Factory::getUser(); see Error handling and legacy APIs.

  • In a LIKE search the visitor’s % and _ are still wildcards after binding. Binding protects the structure of the statement, not the meaning of the pattern. The code escapes backslash, percent and underscore with addcslashes() before adding its own wildcards. It deliberately does not call $db->escape() on a value that is also bound: escape() is for text that is concatenated into SQL, and applying it to a bound value would store extra backslashes.

quoteName and identifiers

quoteName($name, $as = null) wraps an identifier in the quote characters of the current driver: backticks for MySQL and MariaDB, double quotes for PostgreSQL. It accepts a string, a dotted name ('a.title' becomes quoted on both sides of the dot), or an array of names, and a second argument that is a single alias or an array of aliases of the same length. Three equivalent ways to select two aliased columns from a joined table:

$query
    ->select($db->quoteName('a.title', 'trail_title'))
    ->select($db->quoteName('c.title', 'category_title'));

$query->select($db->quoteName(['a.title', 'c.title'], ['trail_title', 'category_title']));

$query->select($db->quoteName('a.distance_km') . ' * 0.621371 AS ' . $db->quoteName('distance_mi'));

Source: Joomla Manual: Select Data from the Database

Why this matters beyond style: PostgreSQL folds unquoted identifiers to lower case, and a column named order or user is a reserved word on one server and not on the other. Quoting every table and column name through the driver is what keeps one code path working on both. quoteName() is not a defence for user input. An identifier that comes from a request, such as a sort column, must be validated against a list of allowed names before it is quoted.

// Sorting by a column chosen in the request: allow-list first, quote second.
$allowed = ['a.title', 'a.distance_km', 'a.difficulty'];
$column  = in_array($requested, $allowed, true) ? $requested : 'a.title';
$direction = strtoupper($requested_dir) === 'DESC' ? 'DESC' : 'ASC';

$query->order($db->quoteName($column) . ' ' . $direction);

Source: Joomla Manual: Secure DB Queries

The table prefix

Joomla stores a prefix in configuration.php (public $dbprefix = 'tp7x_';) and the installer lets the administrator choose it, because several sites can share one database. In SQL written by an extension the prefix is always written as #__: #__tallpine_trails, #__content. The driver replaces it when the query is set with setQuery(), and the installer replaces it in the SQL files of your package. Rules that follow:

  • never hard-code jos_ or any other prefix; a query that works on your test site will fail on a site installed with another prefix

  • a query built from a plain string and sent through setQuery() is prefix-replaced too, but a string passed to quote() is a value, so #__ in it is left alone

  • the prefix is also the way to tell which tables belong to the site: do not use SHOW TABLES patterns that assume it

  • table names themselves are not secrets; $db->getTableList() returns every table and $db->replacePrefix('#__tallpine_trails') returns the physical name when you need it for diagnostics

Prepared statements

bind with named placeholders

A named placeholder is a colon followed by a name (:difficulty) written where a value would stand in the SQL. bind($key, &$value, $dataType = ParameterType::STRING, $length = 0, $driverOptions = []) associates the name with a PHP variable. Three properties of bind() are easy to miss.

  1. The value is taken by reference. The query reads the variable when it is executed, not when you called bind(). A literal cannot be passed by reference, so ->bind(':id', 1001, ParameterType::INTEGER) raises a PHP error. The delete example in the Manual passes literals; assign the value to a variable first. The reference is also useful: bind once, change the variable, call setQuery() and load again.

  2. The default type is ParameterType::STRING. An integer bound as a string works on MySQL because the server converts it, but on PostgreSQL comparing an integer column with a string parameter can fail or skip an index. Pass ParameterType::INTEGER for numbers. The constants are STRING, INTEGER, BOOLEAN, LARGE_OBJECT and NULL.

  3. bind() accepts arrays. Parallel arrays of keys, values and types bind several placeholders in one call, which the Manual notes reduces call overhead; a single scalar value bound to an array of keys binds the same value to each.

use Joomla\Database\ParameterType;

$db = $this->getDatabase();

$username = 'warden';
$id       = 42;

$query = $db->createQuery()
    ->select($db->quoteName(['id', 'name']))
    ->from($db->quoteName('#__users'))
    ->where($db->quoteName('username') . ' = :username')
    ->where($db->quoteName('id') . ' = :id')
    ->bind(
        [':username', ':id'],
        [$username, $id],
        [ParameterType::STRING, ParameterType::INTEGER]
    );

$user = $db->setQuery($query)->loadObject();

Source: Joomla Manual: Select Data from the Database

Placeholders can stand only for values. They cannot replace a table name, a column name, a sort direction or the ASC/DESC keyword. Those parts need an allow-list (see above).

whereIn and bindArray

An IN (...) list needs one placeholder per element. Doing that by hand is where concatenation creeps back in, so the query object has two helpers. whereIn($keyName, array $keyValues, $dataType = ParameterType::INTEGER) adds the whole condition: it binds every element under generated names (:preparedArray1, :preparedArray2, …​) and appends key IN (:preparedArray1,:preparedArray2,...) to the WHERE clause. whereNotIn() does the same for NOT IN. bindArray() is the lower-level function that whereIn() uses: it binds the values and returns the placeholder names, for use in other positions such as a CASE expression or a join condition.

use Joomla\Database\ParameterType;

// Trails whose ids were ticked in the administrator list.
$ids = array_map('intval', $cid); // $cid: the ticked checkboxes passed in by the controller

$query = $db->createQuery()
    ->select($db->quoteName(['id', 'title']))
    ->from($db->quoteName('#__tallpine_trails'))
    ->whereIn($db->quoteName('id'), $ids);

// Strings need an explicit type: the default is INTEGER.
$difficulties = ['moderate', 'hard'];

$query2 = $db->createQuery()
    ->select($db->quoteName('title'))
    ->from($db->quoteName('#__tallpine_trails'))
    ->whereIn($db->quoteName('difficulty'), $difficulties, ParameterType::STRING);

// bindArray gives the placeholders back for a hand-built expression.
$query3 = $db->createQuery()
    ->select($db->quoteName('id'))
    ->from($db->quoteName('#__tallpine_trails'));
$names = $query3->bindArray($ids);
$query3->where($db->quoteName('id') . ' IN (' . implode(',', $names) . ')');

Source: Joomla Manual: Secure DB Queries

Pitfalls:

  • whereIn() assumes integers. Passing strings without ParameterType::STRING casts them to integers on some drivers, which silently matches 0.

  • An empty array produces IN (), which is a syntax error. Test if ($ids === []) first and skip the query or add a condition that is always false.

  • Every placeholder in an IN list is a real parameter, and servers cap the number of parameters per statement (tens of thousands, depending on the server and driver). Chunk very large lists with array_chunk().

  • bindArray() and whereIn() generate numbered names from a counter on the query. Do not reuse one query object for two statements; create a new one.

The classic mistake, and the fix

SQL injection is the case where a user-controlled value changes the structure of the query. The Manual’s example is an update that concatenates a username into the WHERE clause; a username of foobar" OR username="admin turns it into an update of two accounts. The Tallpine equivalent is a search form:

// WRONG: the request value becomes part of the SQL text.
$term  = $this->input->getString('q');
$query = $db->createQuery()
    ->select('*')
    ->from($db->quoteName('#__tallpine_trails'))
    ->where($db->quoteName('title') . " LIKE '%" . $term . "%'");

// RIGHT: the SQL text is fixed, the value is bound.
$term    = $this->input->getString('q', '');
$pattern = '%' . addcslashes($term, '\\%_') . '%';
$query   = $db->createQuery()
    ->select($db->quoteName(['id', 'title']))
    ->from($db->quoteName('#__tallpine_trails'))
    ->where($db->quoteName('title') . ' LIKE :term')
    ->bind(':term', $pattern);

Source: Joomla Manual: Secure DB Queries

The Manual describes two defences. Prepared statements are the first and the one to use; the second is to escape every value with $db->escape() or to run it through $db->quote() (both of which also escape, so the hand-written quotes around a value are dropped). Escaping is the fallback for the few situations where a value cannot be a parameter, for example when a driver-specific function is being built as text. Joomla 4.0 and later execute queries as prepared statements through the driver, so the escaping route is no longer "free" security: it is still a second path where one forgotten call is a vulnerability. The wider checklist (input filtering, output escaping, CSRF tokens, ACL checks) is in Security in extensions.

Reading results

After setQuery() call one of the load* methods. They differ only in the shape of what they return:

Method Returns

loadResult()

One value: the first column of the first row, or null when there is no row. Use for COUNT(*), MAX(), one field.

loadRow()

One row as a list indexed by position.

loadAssoc()

One row as an associative array.

loadObject()

One row as an object (stdClass by default; pass a class name to hydrate your own).

loadColumn($index = 0)

A list of the values of one column.

loadRowList($key = null)

A list of position-indexed rows.

loadAssocList($key = null, $column = null)

A list of associative rows; with $key the array is keyed by that column, with $column the values are reduced to that column.

loadObjectList($key = '', $class = stdClass::class)

A list of objects, optionally keyed by a column.

getNumRows()

Rows found by the last SELECT; call it after execute() and before fetching.

execute()

Runs a statement with no result set (INSERT, UPDATE, DELETE, DDL).

insertid()

Auto-increment value of the last insert.

getAffectedRows()

Rows changed by the last UPDATE or DELETE.

A query that finds nothing returns null from loadObject() and loadResult() and an empty array from the *List methods; code that dereferences the result without checking is the usual source of "attempt to read property on null". After a load* call the cursor is freed, so call setQuery() again before running the same query a second time.

use Joomla\Database\ParameterType;

$db = $this->getDatabase();

// How many published trails per difficulty?
$query = $db->createQuery()
    ->select([$db->quoteName('difficulty'), 'COUNT(*) AS ' . $db->quoteName('total')])
    ->from($db->quoteName('#__tallpine_trails'))
    ->where($db->quoteName('state') . ' = 1')
    ->group($db->quoteName('difficulty'));

$totals = $db->setQuery($query)->loadAssocList('difficulty', 'total');
// ['easy' => '12', 'moderate' => '9', 'hard' => '4']

// A single value.
$query = $db->createQuery()
    ->select('MAX(' . $db->quoteName('distance_km') . ')')
    ->from($db->quoteName('#__tallpine_trails'));

$longest = (float) $db->setQuery($query)->loadResult();

// First page of five rows, starting at the sixth.
$query = $db->createQuery()
    ->select('*')
    ->from($db->quoteName('#__tallpine_trails'))
    ->order($db->quoteName('title'))
    ->setLimit(5, 5);

$page = $db->setQuery($query)->loadObjectList('id');

Source: Joomla Manual: Query Results after Selecting Data from the Database

Drivers return numbers from loadResult() and the associative methods as strings; cast them. Use setLimit() and not the optional $offset/$limit arguments of setQuery() when you can, so the query object carries its own paging and union queries and ListModel stay predictable. For very large result sets, iterate with $db->getIterator() instead of loadObjectList() to avoid holding every row in memory.

Writing data

Insert, update and delete with the query object

use Joomla\Database\ParameterType;

$db = $this->getDatabase();

$title      = 'Hohe Rinne loop';
$alias      = 'hohe-rinne-loop';
$distanceKm = 11.4;
$catid      = 8;
$created    = Factory::getDate()->toSql();

// INSERT
$query = $db->createQuery()
    ->insert($db->quoteName('#__tallpine_trails'))
    ->columns($db->quoteName(['title', 'alias', 'distance_km', 'catid', 'state', 'created']))
    ->values(':title, :alias, :distance, :catid, 1, :created')
    ->bind(':title', $title)
    ->bind(':alias', $alias)
    ->bind(':distance', $distanceKm)
    ->bind(':catid', $catid, ParameterType::INTEGER)
    ->bind(':created', $created);

$db->setQuery($query)->execute();
$newId = $db->insertid();

// UPDATE
$newDistance = 11.9;
$query = $db->createQuery()
    ->update($db->quoteName('#__tallpine_trails'))
    ->set($db->quoteName('distance_km') . ' = :distance')
    ->where($db->quoteName('id') . ' = :id')
    ->bind(':distance', $newDistance)
    ->bind(':id', $newId, ParameterType::INTEGER);

$db->setQuery($query)->execute();
$changed = $db->getAffectedRows();

// DELETE
$query = $db->createQuery()
    ->delete($db->quoteName('#__tallpine_trails'))
    ->where($db->quoteName('id') . ' = :id')
    ->bind(':id', $newId, ParameterType::INTEGER);

$db->setQuery($query)->execute();

Source: Joomla Manual: Insert Data into the Database

A bound float travels as a string by default, which both servers convert to the column type; if exact types matter use a DECIMAL column and bind the value as a string. On PostgreSQL insertid() relies on the sequence of a serial column, so the column must be declared that way (see the SQL files below). getAffectedRows() reports rows changed: an UPDATE that sets a column to its current value reports zero on MySQL with default settings.

Insert and update from an object

For single rows insertObject() and updateObject() build the statement from the public properties of an object, bind them as parameters and quote the names. Properties that do not correspond to columns raise an error, so keep the object to the columns.

$row = new \stdClass();
$row->title       = 'Lake Tallpine circuit';
$row->alias       = 'lake-tallpine-circuit';
$row->distance_km = 6.2;
$row->catid       = 9;
$row->state       = 1;

// The third argument names the auto-increment key to be filled in.
$db->insertObject('#__tallpine_trails', $row, 'id');
echo $row->id;

$row->distance_km = 6.5;
$db->updateObject('#__tallpine_trails', $row, 'id');

// updateObject skips null properties unless the fourth argument is true.
$row->description = null;
$db->updateObject('#__tallpine_trails', $row, 'id', true);

Source: Joomla Manual: Update Data in the Database

Before inserting, the Manual says to set the primary key property to null or 0 so the server generates it. For anything more than a throw-away script, prefer the Table class below: it adds check(), the standard checked-out, published and modified fields, and the content events that other extensions rely on.

Transactions

A transaction makes several statements succeed or fail together. The driver exposes transactionStart(), transactionCommit() and transactionRollback(). The pattern is always a try block with the rollback in catch, because a statement that fails throws Joomla\Database\Exception\ExecutionFailureException, and an uncommitted transaction is rolled back by the server when the connection closes, which hides the bug until the next request.

use Joomla\CMS\Language\Text;
use Joomla\Database\ParameterType;

/**
 * Move every booking from one departure to another and close the old departure.
 */
public function moveBookings(int $fromId, int $toId): void
{
    $db = $this->getDatabase();

    $db->transactionStart();

    try {
        $query = $db->createQuery()
            ->update($db->quoteName('#__tallpine_bookings'))
            ->set($db->quoteName('departure_id') . ' = :to')
            ->where($db->quoteName('departure_id') . ' = :from')
            ->bind(':to', $toId, ParameterType::INTEGER)
            ->bind(':from', $fromId, ParameterType::INTEGER);
        $db->setQuery($query)->execute();

        $closed = 0;
        $query = $db->createQuery()
            ->update($db->quoteName('#__tallpine_departures'))
            ->set($db->quoteName('state') . ' = :closed')
            ->where($db->quoteName('id') . ' = :id')
            ->bind(':closed', $closed, ParameterType::INTEGER)
            ->bind(':id', $fromId, ParameterType::INTEGER);
        $db->setQuery($query)->execute();

        $db->transactionCommit();
    } catch (\Throwable $e) {
        $db->transactionRollback();

        throw $e;
    }
}

Source: Joomla Framework: DatabaseInterface

Notes:

  • Transactions require a transactional storage engine. Joomla creates MySQL and MariaDB tables as InnoDB; a table created as MyISAM by another extension ignores the transaction.

  • On MySQL and MariaDB, DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE) commit the current transaction implicitly. PostgreSQL supports transactional DDL. Never put schema changes inside a transaction and expect to roll them back portably.

  • Nesting: calling transactionStart() while a transaction is open only increases a depth counter unless you pass true (transactionStart(true)), which creates a named savepoint. transactionRollback(true) then rolls back to the last savepoint instead of the whole transaction. Pair every start with a commit or rollback of the same kind.

  • Keep transactions short. Long ones hold row locks and make the site wait.

  • The Table class and AdminModel::save() do not open a transaction for you. A save that must also touch another table wraps its own call in the pattern above.

The SQL-level concepts (isolation levels, locking, deadlocks) are covered in Transactions in SQL; the Joomla layer adds nothing to them except the method names.

The Table class and nested set tables

Table

Joomla\CMS\Table\Table is an active-record class: one object represents one row. Core components use it for the entity behind the edit form, because it standardises bind(), check(), store(), load(), delete(), publish(), checkOut() and checkIn() and fires the table events. Its constructor receives the table name, the primary key name, the database and (optionally) an event dispatcher; subclasses fix the first two:

<?php

namespace Tallpine\Component\Tallpine\Administrator\Table;

\defined('_JEXEC') or die;

use Joomla\CMS\Table\Table;
use Joomla\Database\DatabaseInterface;
use Joomla\Event\DispatcherInterface;

class TrailTable extends Table
{
    /**
     * Content type alias used by tags, versions and custom fields.
     */
    public $typeAlias = 'com_tallpine.trail';

    public function __construct(DatabaseInterface $db, ?DispatcherInterface $dispatcher = null)
    {
        parent::__construct('#__tallpine_trails', 'id', $db, $dispatcher);
    }

    public function check(): bool
    {
        $this->title = trim((string) $this->title);

        if ($this->title === '') {
            $this->setError('COM_TALLPINE_ERROR_TRAIL_TITLE_REQUIRED');

            return false;
        }

        if ($this->alias === '' || $this->alias === null) {
            $this->alias = \Joomla\CMS\Application\ApplicationHelper::stringURLSafe($this->title);
        }

        return parent::check();
    }
}
$table = $this->getMVCFactory()->createTable('Trail', 'Administrator');

if ($table->load(['alias' => 'lake-tallpine-circuit', 'catid' => 9])) {
    $table->bind(['distance_km' => 6.8]);

    if (!$table->check() || !$table->store()) {
        throw new \RuntimeException($table->getError());
    }
}

Source: Joomla Manual: Table

Table::load() accepts a primary key or an array of column/value pairs, and builds a prepared query for you. getInstance() still exists but the factory (createTable()) is the right way to create a table so that the database is injected. As the Manual notes, Table handles one record at a time and offers no multi-row operations; for bulk updates use the query builder. The error API (setError(), getError()) is on the 7.0 removal path: core is moving to exceptions, so new code throws or catches them, as described in Error handling and legacy APIs.

Nested set tables

Categories, tags and menu items are trees stored with the nested set model: every row has parent_id, lft, rgt, level and path columns in addition to its own data. A node’s lft and rgt numbers enclose those of all its descendants, so "all descendants of node N" is a single range query without recursion, at the price of renumbering many rows whenever the tree changes. Joomla\CMS\Table\Nested, which extends Table, keeps these columns consistent when you store(), delete() or move a node. If a table used by com_tallpine should be a tree (the trail regions Alps > Wetterstein > Zugspitze area, say), extend Nested and create the columns:

use Joomla\CMS\Table\Nested;
use Joomla\Database\DatabaseInterface;
use Joomla\Event\DispatcherInterface;

class RegionTable extends Nested
{
    public function __construct(DatabaseInterface $db, ?DispatcherInterface $dispatcher = null)
    {
        parent::__construct('#__tallpine_regions', 'id', $db, $dispatcher);
    }
}

// Insert "Zugspitze area" as the last child of region 3.
$region = $this->getMVCFactory()->createTable('Region', 'Administrator');
$region->setLocation(3, 'last-child');
$region->bind(['title' => 'Zugspitze area']);
$region->check() && $region->store();

// All descendants of region 3 in one query (never loop with parent_id).
$parent = $this->getMVCFactory()->createTable('Region', 'Administrator');
$parent->load(3);

$query = $db->createQuery()
    ->select($db->quoteName(['id', 'title', 'level']))
    ->from($db->quoteName('#__tallpine_regions'))
    ->where($db->quoteName('lft') . ' > :lft')
    ->where($db->quoteName('rgt') . ' < :rgt')
    ->order($db->quoteName('lft'))
    ->bind(':lft', $parent->lft, ParameterType::INTEGER)
    ->bind(':rgt', $parent->rgt, ParameterType::INTEGER);

Source: Joomla Manual: Table

setLocation($referenceId, $position) accepts before, after, first-child and last-child and throws InvalidArgumentException for anything else. Other useful methods are getPath(), getTree(), isLeaf(), getRootId(), moveByReference(), rebuild() and rebuildPath(). If the numbers ever become inconsistent (a manual SQL import, an interrupted save), rebuild() recomputes lft, rgt and level from parent_id, and rebuildPath() recomputes the path column. A tree must have a root row (the core tables have one with id = 1); inserting without one fails. The typeAlias, access, published and language columns are needed if the nested table takes part in the category, tag and custom-field machinery described in Categories, custom fields and workflow integration.

flowchart TD R["Root: lft 1, rgt 10"] --> A["Alps: lft 2, rgt 7"] R --> V["Valley: lft 8, rgt 9"] A --> W["Wetterstein: lft 3, rgt 6"] W --> Z["Zugspitze area: lft 4, rgt 5"]

Reading the diagram: Alps has lft 2 and rgt 7, so every node with lft between 2 and 7 is inside it, and a leaf always has rgt = lft + 1. That is why a subtree query needs only two comparisons.

MySQL and PostgreSQL: what the layer does not hide

Joomla 6.2 runs on MySQL, MariaDB and PostgreSQL, and the query builder emits the right quoting, limit syntax and parameter style for whichever driver created it. It does not translate arbitrary SQL, and it does not make schema scripts portable. Older books say otherwise: Developing Extensions for Joomla! 5 (Packt, 2023) describes the query builder as translating queries for every database. In practice the builder covers the common clauses; everything else is your responsibility.

What is portable

  • select, from, join, where, group, order, setLimit and the insert/update/delete builders

  • quoteName() and quote()

  • $db->getNullDate(), $db->getDateFormat() and Factory::getDate()->toSql() for date values

  • concatenate(), castAs() and $query->charLength() for a few functions that differ

  • transactionStart(), transactionCommit() and transactionRollback()

What is not

Area MySQL / MariaDB PostgreSQL

Auto-increment key

id int unsigned NOT NULL AUTO_INCREMENT

id serial NOT NULL or GENERATED ... AS IDENTITY

Quoting

Backticks

Double quotes

Unsigned integers

int unsigned

Not available; use bigint or a CHECK constraint

Boolean / tiny flags

tinyint

smallint or boolean

Date/time column

datetime

timestamp without time zone

Case sensitivity of LIKE

Depends on collation (usually insensitive)

Case sensitive; ILIKE is not portable

Upsert

INSERT ... ON DUPLICATE KEY UPDATE

INSERT ... ON CONFLICT

Index creation inside CREATE TABLE

Allowed

Separate CREATE INDEX statements

Identifier case

Table names case-sensitive on some operating systems

Folded to lower case unless quoted

Implicit type conversion

Lenient ('12' equals 12)

Strict; types must match

DDL in a transaction

Commits implicitly

Rolls back

Character set

utf8mb4 columns and collations

Database encoding UTF8

The core schema shows both dialects side by side. The nested columns of #__tags are declared as lft int NOT NULL DEFAULT 0 with backticks in installation/sql/mysql/base.sql, and as "lft" bigint DEFAULT 0 NOT NULL with double quotes in installation/sql/postgresql/base.sql; the id is AUTO_INCREMENT in one and serial in the other.

Separate SQL files in the extension

The extension manifest names a separate script for each server family. The installer compares the driver attribute with $db->getServerType() (accepting the aliases mysqli, pdomysql and pgsql) and runs only the matching file. A file without charset="utf8" is skipped, which is a common cause of "my tables were not created".

<extension type="component" method="upgrade">
    <name>com_tallpine</name>
    <namespace path="src">Tallpine\Component\Tallpine</namespace>
    <version>1.0.0</version>

    <install>
        <sql>
            <file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
            <file driver="postgresql" charset="utf8">sql/install.postgresql.utf8.sql</file>
        </sql>
    </install>

    <uninstall>
        <sql>
            <file driver="mysql" charset="utf8">sql/uninstall.mysql.utf8.sql</file>
            <file driver="postgresql" charset="utf8">sql/uninstall.postgresql.utf8.sql</file>
        </sql>
    </uninstall>

    <update>
        <schemas>
            <schemapath type="mysql">sql/updates/mysql</schemapath>
            <schemapath type="postgresql">sql/updates/postgresql</schemapath>
        </schemas>
    </update>

    <administration>
        <files folder="admin">
            <folder>sql</folder>
            <folder>src</folder>
            <folder>services</folder>
        </files>
    </administration>
</extension>

Source: Joomla Manual: Extension manifest

The two install scripts for the same table:

-- sql/install.mysql.utf8.sql
CREATE TABLE IF NOT EXISTS `#__tallpine_trails` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `catid` int unsigned NOT NULL DEFAULT 0,
  `title` varchar(255) NOT NULL,
  `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `distance_km` decimal(5,1) NOT NULL DEFAULT 0.0,
  `difficulty` varchar(16) NOT NULL DEFAULT 'moderate',
  `description` mediumtext,
  `state` tinyint NOT NULL DEFAULT 0,
  `access` int unsigned NOT NULL DEFAULT 1,
  `created` datetime NOT NULL,
  `checked_out` int unsigned,
  `checked_out_time` datetime,
  PRIMARY KEY (`id`),
  KEY `idx_state` (`state`),
  KEY `idx_catid` (`catid`),
  KEY `idx_access` (`access`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
-- sql/install.postgresql.utf8.sql
CREATE TABLE IF NOT EXISTS "#__tallpine_trails" (
  "id" serial NOT NULL,
  "catid" bigint DEFAULT 0 NOT NULL,
  "title" varchar(255) NOT NULL,
  "alias" varchar(255) DEFAULT '' NOT NULL,
  "distance_km" numeric(5,1) DEFAULT 0.0 NOT NULL,
  "difficulty" varchar(16) DEFAULT 'moderate' NOT NULL,
  "description" text,
  "state" smallint DEFAULT 0 NOT NULL,
  "access" bigint DEFAULT 1 NOT NULL,
  "created" timestamp without time zone NOT NULL,
  "checked_out" integer,
  "checked_out_time" timestamp without time zone,
  PRIMARY KEY ("id")
);

CREATE INDEX "#__tallpine_trails_idx_state" ON "#__tallpine_trails" ("state");
CREATE INDEX "#__tallpine_trails_idx_catid" ON "#__tallpine_trails" ("catid");
CREATE INDEX "#__tallpine_trails_idx_access" ON "#__tallpine_trails" ("access");

Source: Joomla Manual: Extension manifest

Update scripts are one file per version in each folder, named for the version they bring the schema to (sql/updates/mysql/1.1.0.sql and sql/updates/postgresql/1.1.0.sql). The installer records the last applied version in #__schemas and runs every file with a higher version. Write each statement so it can be repeated safely (IF NOT EXISTS, and a check before ADD COLUMN in the installer script when the server has no IF NOT EXISTS for columns), because an interrupted update is run again. Reasons to move complex or conditional changes from SQL files into the installer script’s update() method, which can branch on getServerType() and use the query builder, are in Installer scripts and SQL updates.

A typical conditional in PHP, when one body of code must run on both servers:

$db = $this->getDatabase();

if ($db->getServerType() === 'postgresql') {
    $db->setQuery('CREATE INDEX IF NOT EXISTS ' . $db->quoteName('#__tallpine_trails_idx_diff') . ' ON ' . $db->quoteName('#__tallpine_trails') . ' (' . $db->quoteName('difficulty') . ')');
} else {
    $db->setQuery('ALTER TABLE ' . $db->quoteName('#__tallpine_trails') . ' ADD INDEX ' . $db->quoteName('idx_difficulty') . ' (' . $db->quoteName('difficulty') . ')');
}

$db->execute();

Source: Joomla Manual: Installation process

Two platform facts matter here. Joomla does not support migrating a site between MySQL/MariaDB and PostgreSQL, and it does not support downgrading, so SQL files are only ever written to move forward. The minimum database versions for 6.x also differ between sources: the Developer Manual lists MariaDB 10.4 and PostgreSQL 12 as enforced minimums with 10.6 and 14 as supported, while the upgrade guide names 10.6 and 14. Write SQL that works on the higher pair and check the current numbers on Requirements and hosting.

Common errors and pitfalls

  • Using $db from an older tutorial. Factory::getDbo(), get('DatabaseDriver'), a $db plugin property and getQuery(true) all still appear in books and forum answers. They run in 6.2 only through deprecated paths and disappear on the schedule in Deprecations and preparing for Joomla 7.

  • Concatenating values into the WHERE clause even when "the value is an integer". Casting with (int) is safe for integers, but the habit leads to the string case. Bind everything.

  • Missing the type on bind. The default is a string; pass ParameterType::INTEGER for numeric columns, particularly on PostgreSQL.

  • Passing a literal to bind(). Values are bound by reference, so the argument must be a variable.

  • Reusing a placeholder name for different values in one query. A repeated name refers to the same bound variable. Use distinct names unless you mean the same value.

  • IN () with an empty array, and strings in whereIn() without ParameterType::STRING.

  • Quoting a value with quoteName() (it is for identifiers) or an identifier with quote() (it is for values).

  • Forgetting setQuery() before load*() or execute(): the previous query, or none, runs.

  • Running queries in a model constructor, which breaks with database injection (see above).

  • Hard-coding the prefix instead of #__.

  • Assuming MySQL behaviour on PostgreSQL: lenient type conversion, unsigned columns, case-insensitive LIKE, DDL inside transactions.

  • Catching nothing. ExecutionFailureException and QueryTypeAlreadyDefinedException are exceptions; do not swallow them, and never echo the SQL text (which can contain table names and, with debug on, values) to visitors. Joomla’s own debugging tools, including the database queries panel of the debug plugin, are covered in Debugging.

  • Selecting * in a list query and letting the template choose columns: fetch only what the layout shows.

  • Treating Table as a bulk tool. It works one row at a time.

Security summary

  • Every value comes through bind(), bindArray() or whereIn(). The SQL text is built only from literals in your source and from identifiers that passed quoteName() after an allow-list.

  • Input filtering ($input->getInt(), getCmd(), getString()) is a second layer for correctness, not a replacement for binding. A filtered string can still contain a quote.

  • LIKE patterns need their wildcards escaped by you.

  • The database account used by the site should have only the privileges the site needs and no FILE privilege on MySQL; an extension must never depend on GRANT or CREATE DATABASE at runtime.

  • Do not log or display raw SQL in production. The error pages in Error, offline and component pages hide technical detail unless error reporting is raised.

  • Extensions distributed through the Joomla Extensions Directory are reviewed before listing; the process is described in Update servers and the JED. Concatenated queries are the first thing a reviewer looks for.

Version notes and corrections of older material

  • Joomla 4.0 moved the CMS to the Joomla Framework database package with prepared statements, named placeholders and ParameterType, and began deprecating Factory::getDbo() and getQuery(true). The 6.2 source tree pins joomla/database 4.0.0.

  • Joomla 4.3 to 5.0 made DatabaseAwareTrait and DatabaseAwareInterface the standard route for models, plugins and other services (models receive the connection from the MVCFactory); the old $db properties still work but log deprecations.

  • Joomla 6.0 removed code that was only kept for 3.x/4.x compatibility, available through the "Behaviour - Backward Compatibility 6" plugin where noted; views lose $this->db, and AdminModel::getItem() returns stdClass.

  • Joomla 7.0 removes the automatic $db detection in plugins, the database access in model constructors and the setError() calls in core.

  • Developing Extensions for Joomla! 5 (Packt, 2023) uses DatabaseAwareTrait and so remains largely correct; check its $db plugin property, Factory::getLanguage()/get('DatabaseDriver') snippets and the statement that the query builder translates every query for every database against the points above.

  • Joomla! 4 Masterclass (Packt, 2023) is a user-level book and says little about the database layer; code snippets taken from the web for Joomla 3 (JFactory::getDbo(), $db->quote() concatenation, $db->setQuery($query, $start, $limit)) should be rewritten in the form on this page.

  • The Developer Manual pages are authoritative but are not free of slips: some examples bind literal values that must be variables, the whereIn() method has no example of its own, and the Manual has no dedicated page on transactions. The Framework interface documentation and the source files are the reference for those.

References