Creating and Managing Joomla Database Tables

In Joomla!, components usually manage content using the database. During the install, update or uninstall phase of a component, you can execute SQL queries through the use of SQL text files.

The install and uninstall SQL text files are stored in and as:

  • admin/sql/install.mysql.utf8.sql
  • admin/sql/uninstall.mysql.utf8.sql

The update SQL text files are stored in and as:

  • admin/sql/updates/mysql/1.0.0.sql
  • admin/sql/updates/mysql/1.0.1.sql
  • admin/sql/updates/mysql/1.0.2.sql

When Component is Installed

1. The file install.mysql.utf8.sql is executed when the component is installed for the first time. 

2. When the component is installed, the files in the SQL updates folder (admin/sql/updates/mysql) are read and the name of the last file alphabetically is used to populate the component's version number. The latest version number of the component installed is stored in the #__schemas table of the database.

For the automatic update to execute the update SQL files for future versions, this value must be present the #__schemas table. For this reason, it is good practice to create a SQL update file for each version (even if it is empty or just has a comment). This way the #__schemas version will always match the component version.

When Component is Updated

Joomla checks the #__schemas table for the current version of the component. Accordingly, it executes the SQL text files in admin/sql/updates/mysql folder. For example, if the current version is 1.0.0 and the new version of component is 1.0.2, then the Joomla will execute two SQL files: 1.0.1.sql and 1.0.2.sql.

Even if you don't require the use of database, you can add an empty file in admin/sql/updates/mysql/1.0.0.sql, just to initialize schema version. In future versions, if you plan to use database tables, the update can be executed automatically.

Joomla Manifest Files for Extensions

The SQL files are executed if you put in the component_name.xml manifest file. You can add this after metadata information and before files information.

<file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
<file driver="mysql" charset="utf8">sql/uninstall.mysql.utf8.sql</file>
<schemapath type="mysql">sql/updates/mysql</schemapath>

The update tag allows you to provide a series of SQL files to update the current schema. For example, in order to go from version 1.0.0 to version 1.0.1 in a MySQL database, a 1.0.1.sql file must be created inside the sql/updates/mysql folder and the <version> tag of the manifest must be updated to 1.0.1.