Shipping SQL with a plugin

The model

A plugin never knows which database engine the server runs on. LonelyIce uses SQLite files by default and MySQL through a storage plugin; the same package must work on both. So:

• SQL files are written once, in the MySQL dialect AzerothCore's SQL has always used. The core translates each statement for the active backend when it applies the file.
• Plugin code reaches the databases only through the core's database interfaces. It never includes a driver.
• Backend-specific files and overrides/ folders are not allowed in plugins. The one exception is a database the plugin owns and updates itself (below).

The reference is Databases.

Declare the folders

"databases": {
  "world": "data/sql/db-world",
  "characters": "data/sql/db-characters"
}

Each value is a folder in the plugin, relative to plugin.json. The core's updater reads every *.sql file in it, in subfolders too (so a module's base/ and updates/ subfolders work as they are). Put the folder under data/ or sql/: AddPlugin lays out only data, sql, conf, lua and client.

A real example is lonelyice.waystones:

data/sql/db-characters/2026_09_25_00_character_waystone.sql
data/sql/db-world/2026_09_25_01_waystones.sql

Name the files

• Files are applied in file-name order and tracked by file name in the database's updates table (state MODULE).
• A file name must be unique among all update folders of that database: the core's, every module's and every plugin's. A duplicate stops the updater with Duplicate filename.
• Use YYYY_MM_DD_NN_<plugin>_<what>.sql. The date orders your files; the plugin name keeps them apart from other plugins' files of the same day.

Write SQL that can run again

The updater stores a hash of each file. A file whose content changed is applied again. Write every file so that a second run does no harm:

-- characters: never drop player data
CREATE TABLE IF NOT EXISTS `character_waystone` (
  `guid` INT UNSIGNED NOT NULL,
  `waystone` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`guid`, `waystone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Attuned Kirin Tor waycrystals';

-- world: content you own can be replaced wholesale
DELETE FROM `creature_template` WHERE `entry` = 9100001;
INSERT INTO `creature_template` (`entry`, `name`, `subname`, `minlevel`, `maxlevel`, `faction`, `npcflag`, `ScriptName`) VALUES
(9100001, 'Waycrystal', 'Kirin Tor Translocation Network', 80, 80, 35, 1, 'npc_custom_waystone');

To change a table later, add a new file instead of editing an applied one.

When the SQL runs

In LonelyIce. The setup wizard applies the core's and all installed plugins' SQL once. After that the server starts with database updates off. On every start it computes a stamp from the core database connections and, for each loaded plugin, the id, version, database, relative path and SHA-256 of the contents of every .sql file in its auth, characters and world folders. If the stamp differs from the one saved in plugins/.cache/sql.stamp, the server runs the updater over the loaded plugins' folders only. A plugin installed, updated or edited later therefore gets its tables on the next start.

While developing: any edit of a file's contents changes the stamp, so the next start runs the updater again and it applies the changed file as described above. There is no need to delete sql.stamp. A database the plugin owns is not part of the stamp (see below).

In a plain worldserver of the fork. Plugin folders are added to the updater's list, so the SQL is applied at start together with module SQL whenever Updates.EnableDatabases includes that database.

A plugin whose library does not load contributes no SQL.

Removing a plugin

Nothing undoes an update file. Tables and rows a plugin created stay in the database when it is disabled or removed, and the updates table keeps its entries. Design for that: the server must run without the plugin even though its tables exist.

SQL that must be undone on removal (for example rows that point at a spell id of the plugin) belongs in the install and uninstall lists of a patch recipe, which run when the plugin is installed and removed. See Client patches.

SQL the translation does not support

For SQLite each statement is translated. These are rejected there, so do not use them in plugin SQL:

ENGINE=, CHARSET, COMMENT, backquoted names, INSERT IGNORE, REPLACE and multi-row INSERT are translated. Test every file on SQLite (the default storage) and, when you can, on MySQL.

Reaching the databases from code

Use the pools the core provides: LoginDatabase, CharacterDatabase, WorldDatabase (DatabaseEnv.h). Queries are written in the same MySQL dialect and translated on the way:

#include "DatabaseEnv.h"

// asynchronous write
CharacterDatabase.Execute("INSERT IGNORE INTO `character_waystone` (`guid`, `waystone`) VALUES ({}, {})", guid, id);

// synchronous read
if (QueryResult result = CharacterDatabase.Query("SELECT `waystone` FROM `character_waystone` WHERE `guid` = {}", guid))
{
    do
    {
        uint32 waystone = result->Fetch()[0].Get<uint32>();
        // ...
    } while (result->NextRow());
}

• Escape strings from players with EscapeString before putting them into a query.
• The databases are not open while scripts are created (AddScripts, constructors). Query them from hooks that run later, such as WorldScript::OnStartup.
• The core's prepared statements belong to the core; a plugin cannot add statements to the core pools.

A database of your own

A plugin can own a whole database, as playerbots does. The core does not open it for you: your code derives a pool from ModuleDatabasePool (with its own DatabaseConnection class and prepared statements), opens it in a DatabaseScript hook OnModuleDatabasesLoading, and creates, populates and updates it with ModuleDBUpdater. databases in the manifest may describe it as an object; the server and the launcher ignore that form:

"playerbots": { "config": "PlayerbotsDatabaseInfo", "base": "data/sql/playerbots/base", "updates": "data/sql/playerbots/updates" }

Your code runs this on every start, so the SQL stamp above does not cover it; playerbots does it under its own switch Playerbots.Updates.EnableDatabases. With your plugin folder as the updater's source directory, a file of that database that cannot be translated may get a replacement of the same name in data/sql/overrides/<backend>/ (playerbots keeps overrides/sqlite/2025_04_26_00.sql). This is the only place a plugin may use overrides; its auth, characters and world SQL must translate as written.

Mind that the LonelyIce launcher sets the connections of auth, characters, world and playerbots only, both for SQLite files and for a database server. Another plugin-owned database gets no connection string from the launcher; it has to come from your plugin's config. Read src/Script/Playerbots.cpp of mod-playerbots before taking this route.