Databases for Joomla

1. The Basics

1.1 What is a Database?

A database is an organised store of information that other programs can read from and write to quickly. Joomla uses a relational database, which keeps data in tables. Each table has columns (the fields) and rows (the records). One article is one row in a table of articles; one user is one row in a table of users.

The software that manages these tables is the database engine (also called the database server or DBMS). Joomla does not store the data itself; it asks the engine to do it, using a language called SQL (Structured Query Language).

1.2 Why Joomla Needs One

A Joomla page is not a file on disk. When a visitor opens an article, Joomla runs PHP that sends an SQL query to the database, reads the result, and builds the HTML on the fly. Almost everything you manage in the backend lives in the database:

• Content: articles, categories, tags, contacts, and custom fields.
• Structure: menus, modules, and their positions.
• People: users, groups, and access levels.
• Configuration: most extension settings and the list of installed extensions.

A small number of things live as files instead, for example media uploads, the configuration.php file, and template code. Everything else is in the database.

1.3 Where the Database Lives

The database engine usually runs on the same server as your site, or on a dedicated database server next to it. Joomla connects to it over a network socket using a host name, a user name, a password, and a database name. You set those four values once, during installation, and Joomla stores them in configuration.php.

3. MySQL and MariaDB

3.1 The Default Choice

The large majority of Joomla sites run on MySQL or MariaDB. These are the databases that come with almost every shared hosting account and every cPanel server, and they are what a fresh Joomla install expects. If you do nothing special, this is what you get.

3.2 MariaDB Is a MySQL Fork

MariaDB began as a community fork of MySQL after Oracle bought MySQL, and the original MySQL developers built it. For Joomla they are close to interchangeable: the same SQL, the same table layout, the same Joomla driver. Joomla detects which one it is talking to and adjusts a few details automatically. Many hosts now ship MariaDB by default and simply call it "MySQL".

3.3 MySQLi Versus PDO MySQL

Joomla offers two ways to connect to a MySQL or MariaDB database, and you pick one during installation:

For nearly all sites the difference does not matter. MySQLi is the safe default. Both talk to the same database and store data in the same way, so you can move between them by changing one line in configuration.php, as long as the matching PHP extension is installed.

3.4 Minimum Versions

Joomla 6.x requires MySQL 8.0.13 or higher (recommended: MySQL 8.4), or MariaDB 10.4 or higher (recommended: MariaDB 12.0), see: Joomla! Programmers Documentation: Requirements for Joomla! 6.x. Older numbers you may see quoted (MySQL 5.6, MariaDB 10.0) are the floor of the underlying joomla/database library, not the requirement of the modern CMS; a Joomla 6 site will not run on them. Run a current, supported release for better speed, security, and full utf8mb4 support. A host still offering MySQL 5.7 or an old MariaDB is running software past its prime, and you should upgrade or move before installing Joomla 6.

5. SQLite and Microsoft SQL Server

5.1 SQLite: The File-Based Engine

SQLite is unusual: it is not a server at all. The whole database is a single file on disk, and the engine runs inside the application that opens it. There is nothing to install or connect to over a network, which makes it perfect for testing, automated test suites, and small embedded tools.

Joomla's framework ships an SQLite driver, and the Joomla project uses SQLite internally for some of its own automated tests. But the Joomla CMS does not ship an SQLite install schema, so you cannot run a normal Joomla website on it. Treat the SQLite driver as a developer and framework tool, not a hosting option.

5.2 Microsoft SQL Server and Azure SQL

The framework also ships drivers for Microsoft SQL Server (sqlsrv) and Azure SQL (sqlazure), both reported as the server type mssql. As with SQLite, the CMS has no MSSQL install or update schema, so these drivers exist for custom applications built on the Joomla framework, not for a standard Joomla site. If a host or article suggests installing Joomla on SQL Server, treat it with care: the supported path for a Microsoft environment is to run MySQL or MariaDB on Windows, or to use IIS in front of a MySQL database.

5.3 The Practical Takeaway

For a real Joomla website, your real choices are MySQL, MariaDB, or PostgreSQL. SQLite and SQL Server matter only if you are a developer building a custom application on top of the Joomla framework.

7. How Joomla Connects to the Database

7.1 The configuration.php File

Joomla stores the database connection details in the root file configuration.php, set during installation. Five settings define the connection:

public $dbtype = 'mysqli';        // the driver: mysqli, pdomysql, or pgsql
public $host = 'localhost';       // database server address
public $user = 'joomla_user';     // database account
public $password = 'secret';      // its password
public $db = 'joomla_site';       // the database name
public $dbprefix = 'abcd1_';      // the table prefix (see section 8)

The $dbtype value is the driver name from section 2. To switch a MySQL site from MySQLi to PDO, you change mysqli to pdomysql here, provided the pdo_mysql PHP extension is installed. Because this file holds the password in plain text, the web server must never serve it; Joomla and a correct server configuration keep it private.

7.2 The Host Value

The $host is usually localhost or 127.0.0.1 when the database runs on the same machine. Managed and cloud hosting often gives you a separate host name and sometimes a port, written as host:port. Some setups connect over a Unix socket instead of TCP. Your host tells you the correct value; a wrong host is the most common cause of "Could not connect to the database" during install.

7.3 What Happens on Each Request

When a request arrives, Joomla reads configuration.php, loads the matching driver, and opens a connection to the engine. Every query in that request travels over that one connection, and Joomla closes it when the request ends. This is why a slow or overloaded database makes the whole site feel slow: every page waits on it.

7.4 Connecting to a Remote Database

When you develop locally but want to work against a database on a remote server, you have two ways to reach it, and they have very different security profiles.

Direct connection with an IP allowlist. You point Joomla straight at the remote server by setting $host to its address and port, for example db.example.com:3306. For this to work the remote database must accept outside connections, the firewall must allow your IP address, and the database user must be permitted to connect from it. This is simple, but it exposes the database port to the internet and breaks whenever your home or office IP changes.

SSH tunnel (the safer option). Instead of opening the database to the world, you forward a local port through SSH to the remote server. Joomla then connects to that local port as if the database were on your own machine. You open the tunnel with one command:

ssh -N -L 3307:127.0.0.1:3306 This email address is being protected from spambots. You need JavaScript enabled to view it.

This maps local port 3307 to 127.0.0.1:3306 as seen from the remote server, which is its own MySQL. Now point Joomla at the local end of the tunnel:

public $host = '127.0.0.1:3307';   // local end of the SSH tunnel
public $user = 'remote_db_user';
public $password = 'remote_db_password';
public $db = 'remote_db_name';

Because the tunnel ends on the remote server, MySQL sees the connection as coming from its own localhost, so the database account can stay restricted to localhost and the database port never needs to face the internet. Only SSH (key-authenticated) is exposed, which is why a tunnel is safer than opening the port and allowlisting an IP.

Two important details:

• Use 127.0.0.1, not localhost. The MySQLi driver treats the literal word localhost as a request for a Unix socket and ignores the port, which bypasses the tunnel. 127.0.0.1 forces a real TCP connection to the forwarded port.
• The SSH credentials are not in Joomla. The SSH host, user, and key live in your SSH client (the command above or an ~/.ssh/config entry), never in configuration.php. Joomla only ever sees the database connection to 127.0.0.1:3307. These are two separate logins: SSH authenticates the tunnel, the database credentials authenticate Joomla.

Keep the tunnel open the whole time Joomla runs; if it drops, Joomla immediately reports "Could not connect to the database". A tool such as autossh reopens it automatically. Remember too that you are working on live data, so treat destructive actions with the same care as on production.

9. Character Sets and Collation

9.1 Why Encoding Matters

A character set decides which characters the database can store; a collation decides how it sorts and compares them. Get this wrong and accented letters, emoji, or non-Latin scripts turn into question marks or mojibake, and sorting goes strange. Joomla aims for full Unicode so a site can hold any language.

9.2 How Collation Changes Sorting

Collation is not only about storage; it decides the order of an alphabetical list, and that order depends on the language. The same words sort differently under a general (ASCII/English) collation than under a language-specific one. A classic example is the Swedish city Örebro:

• General (ASCII/English) collation: Ö is treated like a plain O, so "Örebro" is sorted as if it were "Orebro" and lands among the O entries.
• Swedish collation: Ö is a separate letter that comes after Z. The Swedish alphabet ends with X, Y, Z, Å, Ä, Ö, so "Örebro" sorts near the very end of the list.

The effect on a list of city names is easy to see:

This is why a multilingual Joomla site can show a category or tag list in an order that looks wrong to a native speaker: the database is sorting correctly, just under the wrong collation for that language. The fix is to match the collation to the content's language rather than leaving everything on a single general collation.

9.3 utf8mb4 on MySQL and MariaDB

On MySQL and MariaDB, Joomla uses utf8mb4, the four-byte UTF-8 encoding that can store every Unicode character, including emoji. The older utf8 in MySQL is really only three bytes and cannot store emoji or some Asian characters, which caused years of subtle bugs. Joomla checks the database version and switches utf8mb4 support on when the server is new enough (MySQL or MariaDB from the 5.5.3 generation onward). A typical Joomla table uses utf8mb4_unicode_ci collation, where ci means case-insensitive.

9.4 The Null Date Detail

Each engine represents an "empty" timestamp differently, and Joomla knows the right value per driver. On MySQL and MariaDB the historic null date is 0000-00-00 00:00:00; when the server runs in strict mode that forbids zero dates, Joomla uses 1000-01-01 00:00:00 instead. PostgreSQL uses 1970-01-01 00:00:00 and SQL Server uses 1900-01-01 00:00:00. This is invisible in daily use, but it explains why you should never hard-code 0000-00-00 in your own queries. Ask the driver for the null date instead, with $db->getNullDate().

Modern Joomla has moved away from the zero-date sentinel where it can. Many date columns are now nullable, and an empty date is stored as a real SQL NULL rather than 0000-00-00; a current Joomla 6 schema no longer defaults any column to a zero date. The null date returned by getNullDate() still matters for the columns that remain non-nullable, for example created on #__content. So the safe rule has two parts: compare against $db->getNullDate() for non-nullable date columns, and check for SQL NULL on the nullable ones, rather than ever writing 0000-00-00 by hand.

11. Writing Queries the Joomla Way

11.1 The Query Builder

Hand-written SQL strings are fragile and easy to get wrong, especially around quoting. Joomla provides a query builder that writes correct, portable SQL for the active engine. You get an empty query with $db->getQuery(true) and chain methods onto it:

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

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

$query->select($db->quoteName(['id', 'title']))
      ->from($db->quoteName('#__content'))
      ->where($db->quoteName('state') . ' = :state')
      ->order($db->quoteName('created') . ' DESC');

$query->bind(':state', 1, ParameterType::INTEGER);

$db->setQuery($query);
$articles = $db->loadObjectList();

11.2 Quote Names and Bind Values

Two rules keep your queries safe and portable:

• Quote identifiers (table and column names) with $db->quoteName(). It adds the correct quote character for the engine, backticks on MySQL and double quotes on PostgreSQL.
• Bind values with named parameters and bind(), or quote them with $db->quote(). Never paste a raw value from a form straight into SQL.

11.3 Why This Stops SQL Injection

SQL injection is the attack where a user types SQL into a form field and the server runs it. Binding parameters defeats it, because a bound value is always treated as data, never as code. The query builder is not just tidy; it is the front line of Joomla's database security. Most data-breach reports in Joomla extensions trace back to a query that skipped this step.

13. Schema Updates and Migrations

13.1 How Joomla Changes the Database

When Joomla or an extension updates, the new version often needs new columns or tables. Joomla applies these changes through versioned SQL update files. The core files live in administrator/components/com_admin/sql/updates/, split by engine into mysql/ and postgresql/ folders, with one file per change named by version and date.

13.2 The Database Fix Tool

Sometimes an update is interrupted, or a database drifts out of step with the version Joomla expects. Joomla has a built-in check at System → Maintenance → Database. It compares your actual schema to the schema the installed version should have and offers a Fix button that runs the missing changes. If you ever see "Database schema is not up to date", this is the page that repairs it.

13.3 The Command Line

The same maintenance exists on the command line, which is handy for scripted deployments:

php cli/joomla.php maintenance:database

This checks the schema and reports or fixes problems without opening the backend. It is the database equivalent of clearing a stuck update by hand.

13.4 Extensions Do the Same

Well-built extensions ship their own SQL update files and an install script, so installing a new version brings their tables up to date automatically. This is why you update extensions through the Joomla installer rather than copying files over the top: the installer runs the schema changes that loose files would skip.

15. Performance and Tuning

15.1 Indexes Are the Big Lever

An index is a lookup structure that lets the database find rows without scanning the whole table, like the index at the back of a book. Joomla's core tables come well indexed. The performance problems people hit usually come from large custom tables or extensions that query unindexed columns. If a specific page is slow, the cause is often a query missing an index. Indexes are not free, though: each one speeds up reads but adds a little cost to every insert and update, so index the columns you search and filter on, not every column.

15.2 Find the Slow Queries

Turn on Joomla's Debug mode in Global Configuration and the Debug plugin shows every query a page ran, with timings. The database engine can also log slow queries (MySQL's slow query log). Together they tell you exactly which query to fix, instead of guessing.

15.3 Read the Query Plan with EXPLAIN

Once you know which query is slow, ask the database why. Every engine has a query planner that decides how to run a query, and you can see its plan by putting EXPLAIN in front of the query:

EXPLAIN SELECT title FROM #__content WHERE state = 1;

The output tells you whether the database uses an index or scans the whole table (look for "ALL", which means a full table scan). On PostgreSQL, EXPLAIN ANALYZE goes further and actually runs the query to report real timings. This is the single most useful habit for diagnosing a slow query: do not guess whether an index helps, measure it.

15.4 Caching Saves the Database

The fastest query is the one you never run. Joomla's caching (the System - Page Cache plugin and view caching) stores built pages and query results so repeat visits skip the database entirely. On a busy site this takes huge load off the engine. Pair it with a persistent cache store such as Redis or Memcached for the best effect.

15.5 Give the Engine Room

A database engine is far faster when it can hold the working data in memory. On a managed host this is tuned for you; on your own server, settings like MariaDB's innodb_buffer_pool_size decide how much of the database stays cached in RAM. Enough memory for the engine often does more for speed than any single Joomla setting.

15.6 A Real Example: Avoiding DISTINCT

One lesson from Joomla core development itself shows why the shape of a query matters more than the engine under it. When I developed the Category Item Count feature for Joomla in 2015, my first version used SQL's DISTINCT keyword to remove duplicate rows. On small sites it worked fine. But people with very large sites, many categories each holding a huge number of articles, reported serious slowdowns.

The reason is that DISTINCT forces the database to work out which rows are duplicates before it can return a result, often with extra sorting, a temporary table, or hashing. On a large dataset that becomes expensive. The query was later rewritten to produce the same result without DISTINCT, and the slowdown went away.

A query that looks elegant is not always the fastest query.

When you write SQL for a Joomla extension, and you reach for DISTINCT to hide duplicates, stop and ask where the duplicates come from. Often a better JOIN, a more selective WHERE, a GROUP BY, or one more index removes the need for it entirely. And always confirm with EXPLAIN (MySQL and MariaDB) or EXPLAIN ANALYZE (PostgreSQL) instead of assuming the query is already optimal.

17. Common Mistakes and Pitfalls

17.1 Wrong Connection Details

Symptom: installation or the site fails with "Could not connect to the database".

Fix: check the four values in configuration.php against what your host gave you: host (often localhost or with a port), user, password, and database name. A wrong host or a database that does not exist yet is the usual cause.

17.2 Choosing PostgreSQL Without Checking Extensions

Symptom: core works, but a third-party extension throws SQL errors on PostgreSQL.

Fix: confirm every extension supports PostgreSQL before you commit. When in doubt, use MariaDB, which the whole ecosystem targets.

17.3 The utf8 Versus utf8mb4 Trap

Symptom: emoji or some characters save as question marks, or a migrated database shows mojibake.

Fix: make sure the database and tables use utf8mb4, not the old three-byte utf8. Convert an older database to utf8mb4 rather than leaving it on utf8.

17.4 Editing Tables by Hand

Symptom: data looks fine in phpMyAdmin but Joomla behaves oddly, loses access rights, or shows orphaned items.

Fix: avoid direct table edits. Joomla keeps related tables in step (assets, history, associations); changing one row by hand can break those links. Use the backend or the database layer.

17.5 No Database in the Backup

Symptom: a "backup" restores the files but the site is empty or stuck on an old version.

Fix: always back up files and the SQL dump together, and test a restore. A file-only copy is not a Joomla backup.

17.6 Hard-Coding the Table Prefix

Symptom: custom SQL works on one site but fails on another with a different prefix.

Fix: write #__tablename, never the literal prefix. Joomla substitutes the real prefix at run time.

19. Quick Reference

SUPPORTED (CMS)   MariaDB, MySQL, PostgreSQL
DRIVER-ONLY       SQLite, MS SQL Server, Azure SQL (framework apps)
DBTYPE VALUES     mysqli, pdomysql, pgsql
SERVER TYPES      mysql, postgresql, mssql, sqlite
REQUIREMENTS      Joomla 6.x: MySQL 8.0.13+, MariaDB 10.4+, PostgreSQL 12.0+
CONNECTION        configuration.php: dbtype, host, user, password, db, dbprefix
TABLE PREFIX      Write #__table; Joomla swaps in the real prefix
ENCODING          utf8mb4 on MySQL/MariaDB (not 3-byte utf8)
GET DRIVER        Factory::getContainer()->get(DatabaseInterface::class)
QUERY             $db->getQuery(true) + quoteName() + bind()
ENGINE CHECK      $db->getServerType()  // 'mysql' or 'postgresql'
STORAGE ENGINE    InnoDB for all core tables (not MyISAM)
TRANSACTIONS      $db->transactionStart() / transactionCommit() / Rollback()
INTEGRITY         Joomla uses no DB foreign keys; enforced in PHP
SCHEMA FILES      administrator/components/com_admin/sql/updates/{mysql,postgresql}
FIX SCHEMA        System → Maintenance → Database  (or CLI below)
CLI               php cli/joomla.php maintenance:database
BACKUP            Files + SQL dump together (mysqldump / pg_dump)
SPEED             Indexes + caching (Page Cache, Redis) + engine memory
DIAGNOSE          EXPLAIN (MySQL) / EXPLAIN ANALYZE (PostgreSQL); avoid DISTINCT