Ledger – Open Support Framework
Log in Sign up
← Integrations & Technical
All categories

Using the Database

2026-02-02 · Updated 2026-02-02

Using the Database Manager

The Database Manager gives you direct access to your Ledger database for browsing data, viewing table structures, and executing SQL queries. This tool is designed for administrators who need to inspect data, run migrations, or perform advanced database operations.

⚠️ Important Safety Information

The SQL tab provides full, unrestricted access to your database. You can execute any SQL command including INSERT, UPDATE, DELETE, ALTER, CREATE, and DROP. This means you can permanently modify or delete data and table structures.

Before using the SQL features, understand the risks:

  • You can accidentally delete entire tables or databases
  • Incorrect queries can corrupt your data
  • There is no undo button for destructive operations
  • Always test queries on a development site first if possible

If you're unsure about SQL or database management, stick to the Browse and Structure tabs for viewing data only.

Accessing the Database Manager

  1. Log into your admin panel
  2. Navigate to Database Management from the admin menu
  3. You'll see a list of all database tables on the left

Browse Tab - Viewing Data

The Browse tab lets you view the contents of any table:

  1. Click a table name in the left sidebar
  2. The Browse tab shows you the table's data in a grid
  3. Use the search box to filter rows across the first few columns
  4. Navigate through pages if the table has many rows
  5. Column values longer than 300 characters are truncated for display

This is useful for checking what data exists, verifying records, or investigating issues without needing to write SQL.

Structure Tab - Table Information

The Structure tab shows you technical details about a table:

Columns section:

  • Field names and data types
  • Whether fields can be NULL
  • Default values
  • Keys (primary, foreign, unique)
  • Special attributes (auto_increment, etc.)

Indexes section:

  • What indexes exist on the table
  • Which columns are indexed
  • Index types (PRIMARY, UNIQUE, INDEX, FULLTEXT)

This information is helpful when writing queries or understanding how your data is organized.

SQL Tab - Running Queries

The SQL tab allows you to execute custom SQL queries. This is where the power (and danger) lies.

What you can do:

  • Run SELECT queries to fetch specific data
  • Execute INSERT to add new records
  • Run UPDATE to modify existing data
  • Use DELETE to remove records
  • ALTER tables to add/remove columns or change structure
  • CREATE new tables or indexes
  • Show database information with SHOW and DESCRIBE commands

Safety rules:

  • Only one query at a time (no multiple semicolons)
  • Results are limited to 500 rows maximum for SELECT queries
  • The query will show you how many rows were affected for write operations

Common Use Cases

Running a migration: If a Ledger update requires database changes, you might receive SQL like this:

ALTER TABLE users ADD COLUMN timezone VARCHAR(50) DEFAULT 'UTC'

Paste it into the SQL tab and click Execute Query. The system will show you "Rows affected: 0" (because it's a structure change) and the new column will be added.

Checking for specific data:

SELECT * FROM forum_posts WHERE created_by_user_id = 5 AND is_deleted = 0

This shows all active posts by user ID 5.

Bulk updating records:

UPDATE knowledge_documents SET is_published = 1 WHERE category_id = 3

This publishes all documents in category 3.

What If Something Goes Wrong

If you execute a query that breaks your database or deletes important data:

  1. Don't panic - your hosting provider likely has automatic backups
  2. Check if you have a recent backup in your /backups folder (from the update system)
  3. Download the fresh schema file from: https://github.com/venture-logistics/ledger/blob/main/install/schema.sql
  4. Import the schema via phpMyAdmin at your hosting provider to rebuild table structures
  5. Restore your data from backup

Note: The schema file only contains table structures, not your data. You'll need a proper backup to restore actual content.

Automatic Backups for Destructive Queries

The Database Manager includes an automatic safety feature that protects you from accidental data loss.

How it works:

When you execute a potentially destructive SQL query (DELETE, DROP, TRUNCATE, or ALTER...DROP), the system automatically creates a complete backup of your database before running the query.

What happens:

  1. You paste a destructive query like DELETE FROM old_table
  2. System detects it's dangerous and creates a backup
  3. Backup is saved to /backups/db_backup_YYYY-MM-DD_HH-MM-SS.sql
  4. Query executes normally
  5. Success message shows: "✓ Automatic backup created: db_backup_2026-02-02_14-30-45.sql"

SELECT and INSERT queries do not trigger backups - only operations that could delete or modify structure.

Accessing your backups:

Backups are stored in the /backups folder in your Ledger root directory. To access them:

  1. Connect via FTP or your hosting file manager
  2. Navigate to /backups
  3. Download the backup file you need
  4. Import via phpMyAdmin if you need to restore

Important notes:

  • Backups are NOT accessible via web browser (protected by .htaccess)
  • The backup contains your entire database, not just the affected table
  • Backup files are timestamped so you can identify when they were created
  • For very large databases, backup creation may take a few seconds

If backup fails:

In rare cases, the automatic backup might fail (server permissions, disk space, etc.). If this happens:

  • You'll see a warning message
  • The query will NOT be blocked
  • You can choose to proceed or cancel
  • Consider creating a manual backup via phpMyAdmin instead

This feature gives you a safety net while still allowing full database control. If you make a mistake, you have a recent backup to restore from.

Best Practices

  • Browse and Structure tabs are safe - use them freely
  • Test SQL queries on a development copy first when possible
  • Make backups before running UPDATE, DELETE, or ALTER commands
  • Start with SELECT to verify what data will be affected before running UPDATE or DELETE
  • Use WHERE clauses carefully to avoid affecting more rows than intended
  • Keep queries simple and focused on one task
  • If you're doing something complex, break it into smaller queries

Alternatives to the SQL Tab

For routine tasks, Ledger provides dedicated admin pages:

  • User management - use the Members admin page
  • Forum posts - use the forum moderation tools
  • Knowledge base - use the document editor

Reserve the SQL tab for:

  • Running database migrations
  • Advanced queries across multiple tables
  • Investigating complex data issues
  • Operations not covered by existing admin pages

Learn More

If you're new to SQL or databases:

  • Use online resources like w3schools.com for SQL tutorials
  • Test queries using SELECT before trying UPDATE or DELETE
  • Ask for help in the Ledger community forum
  • Consider hiring a developer for complex database work

Remember: with great power comes great responsibility. The Database Manager trusts you to know what you're doing.

Support

Any problems or questions visit the Environments forum.

Is this document high quality? Or does it need improvement? High ranking articles display in the footer, admin notified if improvements required.

Log in to rate this document
Rating: 0.0 / 5 (0)