Database Migrations¶

Overview¶

StarkOne uses plain SQL migration files to evolve the SQLite database schema. Migrations run automatically on panel startup.

File Naming¶

Migration files must end with .up.sql:

✅ Correct	❌ Wrong
`025_replication.up.sql`	`025_replication.sql`
`026_plugins.up.sql`	`026_plugins.down.sql`

The isUpMigration() function in internal/db/db.go checks for the .up.sql suffix. Files without it are silently ignored.

Location¶

/usr/share/starkone/migrations/*.up.sql

Naming Convention¶

NNN_description.up.sql

Where NNN is a zero-padded 3-digit sequence number. Migrations run in alphabetical (numeric) order.

Writing Idempotent Migrations¶

Critical Rule

Migrations run on every server that upgrades - including servers with very old schemas and brand-new installations. Every migration must be safe to run regardless of the server's current state.

Always Use `IF NOT EXISTS`¶

-- ✅ Good
CREATE TABLE IF NOT EXISTS my_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL DEFAULT '',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ❌ Bad - fails on servers where the table already exists
CREATE TABLE my_table ( ... );

Adding Columns¶

ALTER TABLE ADD COLUMN is safe because the panel auto-handles "duplicate column" errors:

ALTER TABLE webapps ADD COLUMN php_version TEXT DEFAULT '8.2';

If the column already exists, the error is caught and the migration continues.

Errors Caught Automatically¶

The migration runner in db.go silently skips statements that fail with:

Error substring	Meaning
`duplicate column`	Column already exists - safe to skip
`already exists`	Table or index already exists - safe to skip
`no such column`	`INSERT ... SELECT` references a missing column
`no such table`	`INSERT ... SELECT` references a missing source table

Inserting Data from Existing Tables¶

-- ✅ Good - won't fail if old_table doesn't exist
INSERT OR IGNORE INTO new_table (id, name)
SELECT id, name FROM old_table;

-- ❌ Bad - fails if old_table doesn't exist or is missing columns
INSERT INTO new_table SELECT id, created_at FROM old_table;

Example: Safe Migration¶

-- 027_website_limits.up.sql

-- Create new table (safe on all servers)
CREATE TABLE IF NOT EXISTS webapp_limits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    webapp_id INTEGER NOT NULL,
    cpu_limit INTEGER DEFAULT 0,
    memory_limit INTEGER DEFAULT 0,
    io_limit INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Add index (safe - "already exists" is caught)
CREATE INDEX IF NOT EXISTS idx_webapp_limits_webapp_id
    ON webapp_limits(webapp_id);

-- Add a column to an existing table (safe - "duplicate column" is caught)
ALTER TABLE webapps ADD COLUMN suspended INTEGER DEFAULT 0;

Testing a Migration¶

Before releasing:

Apply the migration to a clean (new) installation to verify it works
Apply it to an old installation to verify it doesn't break existing data
Check: "What happens if this migration runs twice?"

Manual Migration¶

In emergencies, apply a migration manually:

sqlite3 /etc/starkone/starkone.db < /usr/share/starkone/migrations/027_website_limits.up.sql

Or via the SQLite shell:

sqlite3 /etc/starkone/starkone.db
.read /usr/share/starkone/migrations/027_website_limits.up.sql