SQL modules

SQL modules are plain .sql files that are applied to the database when you run dbtool update. They can insert new records, update existing data, or delete rows — anything you can express in SQL. Common uses:

Adjusting mob drop tables (add, remove, or reweight drops)
Relocating dangerous mob spawn points
Inserting custom NPC records
Modifying item stats or availability
Changing quest or ability data stored in the database

How SQL modules work

When dbtool update runs, it applies any .sql files that are listed (directly or via their parent folder) in modules/init.txt. The files are executed in the order they are listed. SQL modules run against the live database, so they must be written to be idempotent — safe to apply more than once.

Always write SQL modules so they can be re-run without causing errors or corrupting data. Use INSERT IGNORE, ON DUPLICATE KEY UPDATE, or WHERE clauses that target specific rows rather than bulk operations without guards.

Creating a SQL module

Create your SQL file

Place the file in modules/custom/sql/ (or any subfolder of modules/).

modules/custom/sql/safe_outposts.sql

-- Relocates dangerous monsters away from outposts
-- Valkurm Dunes
UPDATE mob_spawn_points
SET pos_x = 210.481, pos_y = -6.946, pos_z = 101.274
WHERE mobname = 'Goblin_Leecher'
  AND pos_x = 184.673
  AND pos_y = -7.710
  AND pos_z = 106.691;

-- Buburimu Peninsula
UPDATE mob_spawn_points
SET pos_x = -396.957, pos_y = -32.261, pos_z = 44.893
WHERE mobname = 'Goblin_Shepherd'
  AND pos_x = -166.429
  AND pos_y = 7.910
  AND pos_z = -105.478;

Use WHERE clauses that match the exact current position or values, so the update is a no-op if already applied.

init.txt

custom/sql/safe_outposts.sql

# Or load the entire folder
custom/sql/

Apply it by running dbtool update

python tools/dbtool.py update

In Docker:

docker exec <container> python /server/tools/dbtool.py update

Idempotency patterns

SQL modules may be applied every time dbtool update runs. Use these patterns to prevent duplicate inserts or errors on re-run.

-- Safe to run multiple times: duplicate rows are silently skipped
INSERT IGNORE INTO mob_droplist (mobid, itemid, droprate)
VALUES (12345, 1000, 100);

-- Upsert: insert if not present, update if it already exists
INSERT INTO mob_droplist (mobid, itemid, droprate)
VALUES (12345, 1000, 100)
ON DUPLICATE KEY UPDATE droprate = 100;

-- Only updates the row if it still has its original values
-- Once applied, the WHERE clause will no longer match
UPDATE mob_spawn_points
SET pos_x = 210.481, pos_y = -6.946, pos_z = 101.274
WHERE mobname = 'Goblin_Leecher'
  AND pos_x = 184.673;

Example: removing mob drops

The era accuracy modules in modules/toau/sql/ show a real pattern for removing items from the drop table:

modules/toau/sql/mob_droplist_removal.sql

-- Remove items that were not yet in the game during the ToAU era
-- ACP items
DELETE FROM mob_droplist WHERE itemId = 2741; -- Seedspall Luna
DELETE FROM mob_droplist WHERE itemId = 2758; -- Quadav Backscale
DELETE FROM mob_droplist WHERE itemId = 2778; -- Inferior Cocoon

-- WotG spell scrolls
DELETE FROM mob_droplist WHERE itemId = 4702; -- Scroll of Sacrifice
DELETE FROM mob_droplist WHERE itemId = 4726; -- Scroll of Enthunder II

DELETE statements are not idempotent in the strict sense — a second run is a no-op only because the row is already gone. They are safe to re-run as long as you do not need the data restored between runs.

Example: enabling a custom NPC

modules/custom/sql/lsb_mascot.sql

-- Enables the LandSandBoat mascot NPC
UPDATE npc_list SET name=CHAR(4), flag=1 WHERE name=CHAR(10);

Common tables

Table	What to modify
`mob_droplist`	Mob drop items and rates
`mob_spawn_points`	Mob spawn coordinates
`npc_list`	NPC records, names, and flags
`item_basic`	Item base stats
`item_equipment`	Equipment slot and level data
`abilities`	Job ability properties
`spell_list`	Spell availability by job

Module System

Era Accuracy Modules

How SQL modules work

Creating a SQL module

Idempotency patterns

Example: removing mob drops

Example: enabling a custom NPC

Common tables

​How SQL modules work

​Creating a SQL module

​Idempotency patterns

​Example: removing mob drops

​Example: enabling a custom NPC

​Common tables

How SQL modules work

Creating a SQL module

Idempotency patterns

Example: removing mob drops

Example: enabling a custom NPC

Common tables