DBeaver Cheatsheet¶

DBeaver - Universal Database Tool

DBeaver is a free, open-source universal database tool for developers, database administrators, analysts, and anyone who needs to work with databases. It supports all major databases including MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, and more.

Installation¶

Windows Installation¶

# Download from official website
# https://dbeaver.io/download/

# Install via Chocolatey
choco install dbeaver

# Install via winget
winget install dbeaver.dbeaver

# Install specific version
choco install dbeaver --version 23.2.5

# Silent installation
dbeaver-ce-23.2.5-x86_64-setup.exe /S

# Portable version
# Download portable ZIP and extract
# No installation required

macOS Installation¶

# Download from official website
# https://dbeaver.io/download/

# Install via Homebrew
brew install --cask dbeaver-community

# Install specific version
brew install --cask dbeaver-community@23.2.5

# Manual installation
curl -O https://dbeaver.io/files/dbeaver-ce-23.2.5-macos-x86_64.dmg
open dbeaver-ce-23.2.5-macos-x86_64.dmg

Linux Installation¶

# Ubuntu/Debian
wget https://dbeaver.io/files/dbeaver-ce_23.2.5_amd64.deb
sudo dpkg -i dbeaver-ce_23.2.5_amd64.deb

# Fix dependencies if needed
sudo apt-get install -f

# CentOS/RHEL/Fedora
wget https://dbeaver.io/files/dbeaver-ce-23.2.5.x86_64.rpm
sudo rpm -i dbeaver-ce-23.2.5.x86_64.rpm

# Or using yum/dnf
sudo yum install dbeaver-ce-23.2.5.x86_64.rpm
sudo dnf install dbeaver-ce-23.2.5.x86_64.rpm

# Snap package
sudo snap install dbeaver-ce

# Flatpak
flatpak install flathub io.dbeaver.DBeaverCommunity

# AppImage
wget https://dbeaver.io/files/dbeaver-ce-23.2.5-linux.gtk.x86_64.tar.gz
tar -xzf dbeaver-ce-23.2.5-linux.gtk.x86_64.tar.gz
cd dbeaver
./dbeaver

Docker Installation¶

# Run DBeaver in Docker (with X11 forwarding)
docker run -it --rm \
  -e DISPLAY=$DISPLAY \
  -v /tmp/.X11-unix:/tmp/.X11-unix \
  -v $HOME/.dbeaver:/opt/dbeaver-data \
  --net=host \
  dbeaver/cloudbeaver

# CloudBeaver (web-based version)
docker run -d --name cloudbeaver \
  -p 8978:8978 \
  -v cloudbeaver-data:/opt/cloudbeaver/workspace \
  dbeaver/cloudbeaver:latest

# Access CloudBeaver at http://localhost:8978

System Requirements¶

Minimum Requirements:
- OS: Windows 7+, macOS 10.12+, Linux (any modern distribution)
- RAM: 512 MB
- Disk Space: 200 MB
- Java: 11+ (bundled with installer)

Recommended:
- RAM: 2 GB or more
- Disk Space: 1 GB or more
- Java: 17+ for best performance
- SSD for better performance with large databases

Database Connections¶

Connection Configuration¶

-- Connection parameters for different databases

-- MySQL
Host: localhost
Port: 3306
Database: myapp
Username: dbuser
Password: password
Driver: MySQL JDBC Driver

-- PostgreSQL
Host: localhost
Port: 5432
Database: myapp
Username: postgres
Password: password
Driver: PostgreSQL JDBC Driver

-- SQL Server
Host: localhost
Port: 1433
Database: myapp
Username: sa
Password: password
Driver: Microsoft SQL Server JDBC Driver

-- Oracle
Host: localhost
Port: 1521
SID/Service: XE
Username: hr
Password: password
Driver: Oracle JDBC Driver

-- SQLite
Database Path: /path/to/database.db
Driver: SQLite JDBC Driver

-- MongoDB
Host: localhost
Port: 27017
Database: myapp
Username: user
Password: password
Authentication Database: admin
Driver: MongoDB JDBC Driver

Advanced Connection Settings¶

# Connection properties file
# MySQL example
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/myapp
user=dbuser
password=password

# Connection pool settings
initialSize=5
maxActive=20
maxIdle=10
minIdle=2
maxWait=10000

# SSL settings
useSSL=true
requireSSL=true
verifyServerCertificate=true
trustCertificateKeyStoreUrl=/path/to/truststore.jks
trustCertificateKeyStorePassword=password

# Connection timeout settings
connectTimeout=30000
socketTimeout=60000
loginTimeout=30

# Additional parameters
characterEncoding=UTF-8
useUnicode=true
allowMultiQueries=true
rewriteBatchedStatements=true

SSH Tunnel Configuration¶

# SSH tunnel settings in DBeaver
SSH Host: ssh-server.example.com
SSH Port: 22
SSH Username: ssh-user
SSH Authentication: Password/Key

# Local port forwarding
Local Host: localhost
Local Port: 3307
Remote Host: database-server
Remote Port: 3306

# SSH key authentication
Private Key: /path/to/private-key.pem
Passphrase: key-passphrase

# SSH tunnel command equivalent
ssh -L 3307:database-server:3306 ssh-user@ssh-server.example.com

# Test SSH connection
ssh -T ssh-user@ssh-server.example.com

Connection Templates¶

{
  "templates": [
    {
      "name": "Local MySQL Development",
      "driver": "mysql8",
      "host": "localhost",
      "port": 3306,
      "database": "dev_db",
      "properties": {
        "useSSL": "false",
        "allowPublicKeyRetrieval": "true"
      }
    },
    {
      "name": "Production PostgreSQL",
      "driver": "postgresql",
      "host": "prod-db.example.com",
      "port": 5432,
      "database": "production",
      "properties": {
        "ssl": "true",
        "sslmode": "require"
      }
    },
    {
      "name": "AWS RDS MySQL",
      "driver": "mysql8",
      "host": "mydb.cluster-xyz.us-east-1.rds.amazonaws.com",
      "port": 3306,
      "database": "myapp",
      "properties": {
        "useSSL": "true",
        "requireSSL": "true"
      }
    }
  ]
}

SQL Editor¶

Basic SQL Operations¶

-- Data Definition Language (DDL)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

-- Data Manipulation Language (DML)
INSERT INTO users (username, email, password_hash) VALUES
('john_doe', 'john@example.com', 'hashed_password_1'),
('jane_smith', 'jane@example.com', 'hashed_password_2'),
('bob_wilson', 'bob@example.com', 'hashed_password_3');

UPDATE users 
SET email = 'john.doe@example.com' 
WHERE username = 'john_doe';

DELETE FROM users 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Data Query Language (DQL)
SELECT u.username, u.email, u.created_at
FROM users u
WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY u.created_at DESC
LIMIT 10;

-- Complex queries with joins
SELECT 
    u.username,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.username, u.email
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;

SQL Editor Features¶

-- Auto-completion and syntax highlighting
-- DBeaver provides intelligent code completion

-- Code formatting (Ctrl+Shift+F)
SELECT u.username,u.email,o.total FROM users u JOIN orders o ON u.id=o.user_id;

-- Formatted result:
SELECT 
    u.username,
    u.email,
    o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Code templates and snippets
-- Type 'sel' and press Tab for SELECT template
-- Type 'ins' and press Tab for INSERT template
-- Type 'upd' and press Tab for UPDATE template

-- SQL validation and error highlighting
-- DBeaver highlights syntax errors in real-time

-- Multiple query execution
-- Separate queries with semicolons
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM products;

-- Execute specific query (Ctrl+Enter)
-- Execute all queries (Ctrl+Alt+Shift+X)
-- Execute current statement (Alt+X)

Query History and Bookmarks¶

-- Query history is automatically saved
-- Access via Window -> Show View -> Query History

-- Bookmark frequently used queries
-- Right-click in SQL editor -> Add Bookmark

-- Saved queries examples:
-- Name: "Active Users Count"
SELECT COUNT(*) as active_users 
FROM users 
WHERE last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Name: "Top Products by Sales"
SELECT 
    p.name,
    SUM(oi.quantity) as total_sold,
    SUM(oi.quantity * oi.price) as total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY p.id, p.name
ORDER BY total_revenue DESC
LIMIT 10;

-- Name: "Database Size Analysis"
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) as size_mb,
    table_rows
FROM information_schema.tables 
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

SQL Script Execution¶

-- Execute SQL scripts from files
-- File -> Open SQL Script
-- Or drag and drop .sql files

-- Script execution options:
-- 1. Execute entire script
-- 2. Execute selected statements
-- 3. Execute statement at cursor
-- 4. Execute with transaction control

-- Transaction control in scripts
START TRANSACTION;

INSERT INTO users (username, email, password_hash) 
VALUES ('test_user', 'test@example.com', 'hash');

UPDATE users 
SET email = 'updated@example.com' 
WHERE username = 'test_user';

-- Commit or rollback based on results
COMMIT;
-- ROLLBACK;

-- Script with error handling
DELIMITER //
CREATE PROCEDURE safe_user_update(
    IN p_user_id INT,
    IN p_email VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE users 
    SET email = p_email, updated_at = NOW()
    WHERE id = p_user_id;

    COMMIT;
END //
DELIMITER ;

Data Viewer¶

Grid View Features¶

-- Data viewer provides multiple view modes:
-- 1. Grid view (default)
-- 2. Form view (record-by-record)
-- 3. JSON view (for JSON data)
-- 4. XML view (for XML data)
-- 5. Image view (for binary image data)

-- Grid view features:
-- - Sort by clicking column headers
-- - Filter data using column filters
-- - Resize columns
-- - Freeze columns
-- - Show/hide columns
-- - Export selected data

-- Column operations:
-- Right-click column header for options:
-- - Sort Ascending/Descending
-- - Filter
-- - Hide Column
-- - Resize to Fit
-- - Copy Column Name
-- - Set as Primary Key (for editing)

-- Row operations:
-- Right-click row for options:
-- - Edit Row
-- - Duplicate Row
-- - Delete Row
-- - Copy Row
-- - Export Row

Data Filtering¶

-- Column filters (click filter icon in column header)
-- Text filters:
-- - Contains
-- - Starts with
-- - Ends with
-- - Equals
-- - Not equals
-- - Regular expression

-- Numeric filters:
-- - Equals
-- - Not equals
-- - Greater than
-- - Less than
-- - Between
-- - Is null
-- - Is not null

-- Date filters:
-- - Equals
-- - Before
-- - After
-- - Between
-- - Today
-- - This week
-- - This month
-- - This year

-- Advanced filtering with SQL WHERE clause
-- Use the filter panel at bottom of data viewer
WHERE created_at >= '2023-01-01' 
  AND status = 'active' 
  AND email LIKE '%@gmail.com'

-- Multiple column filters are combined with AND
-- Use custom WHERE clause for OR conditions
WHERE (status = 'active' OR status = 'pending')
  AND created_at >= '2023-01-01'

Data Editing¶

-- Enable data editing:
-- 1. Ensure table has primary key
-- 2. Enable edit mode (pencil icon in toolbar)
-- 3. Double-click cell to edit

-- Inline editing features:
-- - Edit cells directly in grid
-- - Tab to move between cells
-- - Enter to confirm changes
-- - Escape to cancel changes
-- - Auto-save or manual save options

-- Bulk operations:
-- - Select multiple rows
-- - Delete selected rows
-- - Copy/paste between tables
-- - Duplicate rows

-- Data validation:
-- - Type checking (numeric, date, etc.)
-- - Length validation
-- - Foreign key constraints
-- - Custom validation rules

-- Transaction control:
-- - Auto-commit mode
-- - Manual transaction mode
-- - Rollback changes
-- - Save changes

-- Example: Bulk update using SQL
UPDATE users 
SET status = 'inactive' 
WHERE last_login < DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- Example: Safe delete with confirmation
DELETE FROM temp_data 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY)
LIMIT 1000;

-- Navigation features:
-- - Page through large result sets
-- - Jump to specific page
-- - Configure page size (100, 500, 1000, etc.)
-- - Fetch all data (for smaller result sets)

-- Keyboard shortcuts:
-- Ctrl+Home: Go to first row
-- Ctrl+End: Go to last row
-- Page Up/Down: Navigate pages
-- Ctrl+F: Find in data
-- F3: Find next
-- Shift+F3: Find previous

-- Search and find:
-- - Find text in current column
-- - Find text in all columns
-- - Case-sensitive search
-- - Regular expression search
-- - Replace functionality

-- Bookmarks and favorites:
-- - Bookmark specific rows
-- - Save filtered views
-- - Quick access to bookmarked data

-- Data refresh:
-- - Manual refresh (F5)
-- - Auto-refresh intervals
-- - Refresh on focus
-- - Show refresh timestamp

Schema Browser¶

-- Schema browser shows database structure:
-- - Databases/Schemas
-- - Tables
-- - Views
-- - Stored Procedures
-- - Functions
-- - Triggers
-- - Indexes
-- - Sequences
-- - User-defined types

-- Table information:
-- - Column definitions
-- - Primary keys
-- - Foreign keys
-- - Indexes
-- - Triggers
-- - Constraints
-- - Table statistics

-- View table DDL:
-- Right-click table -> View DDL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- View table properties:
-- - Row count
-- - Table size
-- - Index size
-- - Creation date
-- - Last modified date
-- - Engine type (MySQL)
-- - Character set
-- - Collation

Object Operations¶

-- Table operations (right-click table):
-- - Open in new tab
-- - View data
-- - Edit data
-- - Generate SQL
-- - Export data
-- - Import data
-- - Truncate table
-- - Drop table
-- - Rename table
-- - Copy table structure

-- Generate SQL scripts:
-- - CREATE TABLE statement
-- - INSERT statements (with data)
-- - SELECT statement
-- - UPDATE statement template
-- - DELETE statement template

-- Example generated INSERT:
INSERT INTO users (username, email, password_hash) VALUES
('john_doe', 'john@example.com', 'hash1'),
('jane_smith', 'jane@example.com', 'hash2');

-- Column operations:
-- - Add column
-- - Modify column
-- - Drop column
-- - Rename column
-- - Set as primary key
-- - Create index

-- Index operations:
-- - Create index
-- - Drop index
-- - Rebuild index
-- - View index usage statistics

CREATE INDEX idx_users_status_created 
ON users(status, created_at);

-- Constraint operations:
-- - Add foreign key
-- - Drop constraint
-- - Check constraint
-- - Unique constraint

ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

Schema Comparison¶

-- Compare database schemas:
-- Tools -> Compare -> Schemas

-- Comparison features:
-- - Structure differences
-- - Data differences
-- - Missing objects
-- - Different object definitions

-- Generate synchronization script:
-- - CREATE statements for missing objects
-- - ALTER statements for differences
-- - DROP statements for extra objects

-- Example sync script:
-- Add missing table
CREATE TABLE new_feature (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Modify existing table
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20),
ADD INDEX idx_phone (phone);

-- Update stored procedure
DROP PROCEDURE IF EXISTS get_user_stats;
DELIMITER //
CREATE PROCEDURE get_user_stats()
BEGIN
    SELECT 
        COUNT(*) as total_users,
        COUNT(CASE WHEN status = 'active' THEN 1 END) as active_users,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_users
    FROM users;
END //
DELIMITER ;

ER Diagrams¶

-- Generate Entity-Relationship diagrams:
-- Right-click database -> Generate ER Diagram

-- ER diagram features:
-- - Visual table relationships
-- - Primary/foreign key connections
-- - Column data types
-- - Index visualization
-- - Constraint display

-- Diagram customization:
-- - Layout algorithms (hierarchical, circular, etc.)
-- - Show/hide columns
-- - Show/hide data types
-- - Color coding
-- - Export as image (PNG, SVG, PDF)

-- Reverse engineering:
-- - Generate diagrams from existing database
-- - Include/exclude specific tables
-- - Filter by schema
-- - Group related tables

-- Forward engineering:
-- - Design database visually
-- - Generate DDL from diagram
-- - Validate relationships
-- - Export schema script

Query Execution¶

Execution Plans¶

-- View query execution plans:
-- Enable "Show execution plan" in SQL editor
-- Or use EXPLAIN statement

-- MySQL execution plan:
EXPLAIN SELECT 
    u.username, 
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.username;

-- PostgreSQL execution plan:
EXPLAIN (ANALYZE, BUFFERS) 
SELECT 
    u.username, 
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.username;

-- SQL Server execution plan:
SET SHOWPLAN_ALL ON;
SELECT 
    u.username, 
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.username;

-- Execution plan analysis:
-- - Table scans vs index seeks
-- - Join algorithms (nested loop, hash, merge)
-- - Sort operations
-- - Filter operations
-- - Cost estimates
-- - Row count estimates

Performance Monitoring¶

-- Query performance metrics:
-- - Execution time
-- - Rows affected
-- - Rows fetched
-- - Memory usage
-- - CPU usage

-- Enable query profiling:
-- Preferences -> Database -> SQL Editor -> Show query info

-- Performance optimization tips:
-- 1. Use appropriate indexes
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 2. Limit result sets
SELECT * FROM large_table LIMIT 1000;

-- 3. Use EXISTS instead of IN for subqueries
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

-- 4. Avoid SELECT *
SELECT id, username, email FROM users;

-- 5. Use UNION ALL instead of UNION when duplicates are acceptable
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

-- Query timeout settings:
-- Set query timeout in connection properties
-- Default: 20 seconds
-- Long-running queries: 300+ seconds
-- Batch operations: 3600+ seconds

Batch Operations¶

-- Execute multiple statements:
-- Separate with semicolons
-- Use transaction control

START TRANSACTION;

-- Batch insert
INSERT INTO users (username, email, password_hash) VALUES
('user1', 'user1@example.com', 'hash1'),
('user2', 'user2@example.com', 'hash2'),
('user3', 'user3@example.com', 'hash3');

-- Batch update
UPDATE products 
SET price = price * 1.1 
WHERE category = 'electronics';

-- Batch delete
DELETE FROM logs 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

COMMIT;

-- Bulk data operations:
-- Use LOAD DATA for large imports (MySQL)
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE temp_import
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Use COPY for bulk operations (PostgreSQL)
COPY users(username, email, password_hash)
FROM '/path/to/users.csv'
DELIMITER ','
CSV HEADER;

-- Use BULK INSERT for SQL Server
BULK INSERT temp_import
FROM '/path/to/data.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

Query Debugging¶

-- Debug complex queries step by step:

-- Step 1: Test base query
SELECT COUNT(*) FROM users WHERE created_at >= '2023-01-01';

-- Step 2: Add joins one by one
SELECT COUNT(*) 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01';

-- Step 3: Add grouping and aggregation
SELECT 
    u.username,
    COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.username;

-- Step 4: Add filtering and sorting
SELECT 
    u.username,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.username
HAVING COUNT(o.id) >= 5
ORDER BY total_spent DESC;

-- Use LIMIT for testing large queries
SELECT * FROM large_table LIMIT 10;

-- Check intermediate results
SELECT DISTINCT status FROM orders; -- Check available values
SELECT MIN(created_at), MAX(created_at) FROM orders; -- Check date range

Data Export/Import¶

Export Formats¶

-- Supported export formats:
-- - CSV (Comma-separated values)
-- - TSV (Tab-separated values)
-- - Excel (XLSX)
-- - JSON
-- - XML
-- - SQL INSERT statements
-- - HTML
-- - PDF (via print)

-- Export configuration:
-- File -> Export Data
-- Or right-click table -> Export Data

-- CSV export options:
{
  "delimiter": ",",
  "quote_char": "\"",
  "escape_char": "\\",
  "line_separator": "\n",
  "encoding": "UTF-8",
  "include_header": true,
  "quote_all_values": false,
  "null_value": "",
  "date_format": "yyyy-MM-dd",
  "timestamp_format": "yyyy-MM-dd HH:mm:ss"
}

-- JSON export options:
{
  "format": "array",  // array or object
  "pretty_print": true,
  "include_metadata": false,
  "date_format": "ISO",
  "encoding": "UTF-8"
}

-- SQL export options:
{
  "include_ddl": true,
  "include_data": true,
  "batch_size": 1000,
  "use_qualified_names": true,
  "include_comments": true
}

Import Data¶

-- Import data from various sources:
-- File -> Import Data
-- Or right-click table -> Import Data

-- CSV import configuration:
{
  "file_path": "/path/to/data.csv",
  "delimiter": ",",
  "quote_char": "\"",
  "escape_char": "\\",
  "encoding": "UTF-8",
  "header_row": 1,
  "skip_rows": 0,
  "null_values": ["", "NULL", "null"],
  "trim_values": true,
  "empty_string_as_null": true
}

-- Column mapping:
{
  "mappings": [
    {"source": "user_name", "target": "username"},
    {"source": "email_address", "target": "email"},
    {"source": "registration_date", "target": "created_at"}
  ],
  "type_conversions": [
    {"column": "created_at", "type": "timestamp", "format": "MM/dd/yyyy"},
    {"column": "age", "type": "integer"},
    {"column": "salary", "type": "decimal", "precision": 10, "scale": 2}
  ]
}

-- Import modes:
-- - INSERT: Add new records
-- - UPDATE: Update existing records
-- - UPSERT: Insert or update based on key
-- - REPLACE: Replace existing records

-- Error handling:
{
  "on_error": "skip",  // skip, stop, or log
  "max_errors": 100,
  "log_errors": true,
  "error_log_file": "/path/to/errors.log"
}

Bulk Data Operations¶

-- Large dataset import strategies:

-- 1. Batch processing
{
  "batch_size": 1000,
  "commit_frequency": 10000,
  "use_transactions": true,
  "parallel_threads": 4
}

-- 2. Disable constraints during import
ALTER TABLE target_table DISABLE KEYS;
-- Import data
ALTER TABLE target_table ENABLE KEYS;

-- 3. Use staging tables
CREATE TABLE staging_users LIKE users;
-- Import to staging table first
-- Validate and clean data
-- Transfer to production table
INSERT INTO users SELECT * FROM staging_users WHERE validation_check();

-- 4. Optimize for bulk operations
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- Perform bulk operations
SET foreign_key_checks = 1;
SET unique_checks = 1;
SET autocommit = 1;

-- Progress monitoring:
-- DBeaver shows import progress
-- - Records processed
-- - Processing speed (records/second)
-- - Estimated time remaining
-- - Error count
-- - Success rate

Data Synchronization¶

-- Synchronize data between databases:
-- Tools -> Database -> Compare/Synchronize

-- Sync configuration:
{
  "source": {
    "connection": "source_db",
    "schema": "production",
    "tables": ["users", "orders", "products"]
  },
  "target": {
    "connection": "target_db", 
    "schema": "staging",
    "tables": ["users", "orders", "products"]
  },
  "options": {
    "compare_data": true,
    "compare_structure": true,
    "generate_script": true,
    "execute_immediately": false
  }
}

-- Sync strategies:
-- 1. Full synchronization
-- 2. Incremental synchronization
-- 3. Bidirectional synchronization
-- 4. One-way synchronization

-- Generated sync script example:
-- Update existing records
UPDATE target.users t
JOIN source.users s ON t.id = s.id
SET t.email = s.email,
    t.updated_at = s.updated_at
WHERE t.email != s.email;

-- Insert new records
INSERT INTO target.users (id, username, email, created_at)
SELECT s.id, s.username, s.email, s.created_at
FROM source.users s
LEFT JOIN target.users t ON s.id = t.id
WHERE t.id IS NULL;

-- Delete removed records
DELETE t FROM target.users t
LEFT JOIN source.users s ON t.id = s.id
WHERE s.id IS NULL;

Database Administration¶

User Management¶

-- User and privilege management:
-- Navigate to Security -> Users in schema browser

-- Create new user (MySQL):
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';

-- Grant privileges:
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON myapp.* TO 'admin_user'@'localhost';

-- Create role and assign to user:
CREATE ROLE 'app_role';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_role';
GRANT 'app_role' TO 'app_user'@'localhost';

-- PostgreSQL user management:
CREATE USER app_user WITH PASSWORD 'secure_password';
CREATE ROLE app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT app_role TO app_user;

-- SQL Server user management:
CREATE LOGIN app_user WITH PASSWORD = 'SecurePassword123!';
USE myapp;
CREATE USER app_user FOR LOGIN app_user;
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;

-- View user privileges:
SHOW GRANTS FOR 'app_user'@'localhost';

-- Revoke privileges:
REVOKE INSERT, UPDATE ON myapp.* FROM 'app_user'@'localhost';

-- Drop user:
DROP USER 'app_user'@'localhost';

Backup and Restore¶

-- Database backup using DBeaver:
-- Right-click database -> Tools -> Backup

-- Backup options:
{
  "backup_type": "full",  // full, incremental, differential
  "include_data": true,
  "include_structure": true,
  "include_procedures": true,
  "include_triggers": true,
  "include_views": true,
  "compression": "gzip",
  "output_format": "sql"  // sql, binary, custom
}

-- MySQL backup (mysqldump):
mysqldump -u username -p --single-transaction --routines --triggers myapp > backup.sql

-- PostgreSQL backup (pg_dump):
pg_dump -U username -h localhost -d myapp -f backup.sql

-- SQL Server backup:
BACKUP DATABASE myapp 
TO DISK = 'C:\Backups\myapp_backup.bak'
WITH FORMAT, COMPRESSION;

-- Restore database:
-- Right-click database -> Tools -> Restore

-- MySQL restore:
mysql -u username -p myapp < backup.sql

-- PostgreSQL restore:
psql -U username -h localhost -d myapp -f backup.sql

-- SQL Server restore:
RESTORE DATABASE myapp 
FROM DISK = 'C:\Backups\myapp_backup.bak'
WITH REPLACE;

-- Automated backup script:
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"
DB_NAME="myapp"

mysqldump -u backup_user -p$BACKUP_PASSWORD \
  --single-transaction \
  --routines \
  --triggers \
  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Keep only last 7 days of backups
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

Performance Monitoring¶

-- Monitor database performance:
-- Window -> Show View -> Database Monitor

-- Key performance metrics:
-- - Active connections
-- - Query execution time
-- - Lock waits
-- - Buffer cache hit ratio
-- - Disk I/O statistics
-- - Memory usage

-- MySQL performance queries:
-- Show processlist
SHOW PROCESSLIST;

-- Show status variables
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Questions';

-- Show engine status
SHOW ENGINE INNODB STATUS;

-- Performance schema queries
SELECT 
    event_name,
    count_star,
    sum_timer_wait/1000000000 as total_time_sec,
    avg_timer_wait/1000000000 as avg_time_sec
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- PostgreSQL performance queries:
-- Active queries
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Database statistics
SELECT 
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database;

-- Table statistics
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Index Management¶

-- Index analysis and optimization:
-- Navigate to table -> Indexes tab

-- Create indexes:
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite indexes:
CREATE INDEX idx_products_category_price ON products(category, price);
CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

-- Partial indexes (PostgreSQL):
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Functional indexes:
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
CREATE INDEX idx_orders_year ON orders(YEAR(created_at));

-- Index usage analysis (MySQL):
SELECT 
    t.table_name,
    s.index_name,
    s.column_name,
    s.cardinality,
    ROUND(((s.cardinality / t.table_rows) * 100), 2) AS selectivity
FROM information_schema.statistics s
JOIN information_schema.tables t ON s.table_name = t.table_name
WHERE s.table_schema = DATABASE()
  AND t.table_schema = DATABASE()
ORDER BY selectivity DESC;

-- Unused indexes (PostgreSQL):
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

-- Index size analysis:
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
ORDER BY stat_value DESC;

-- Drop unused indexes:
DROP INDEX idx_unused_index ON table_name;

-- Rebuild indexes:
ALTER TABLE table_name ENGINE=InnoDB; -- MySQL
REINDEX TABLE table_name; -- PostgreSQL

Visual Query Builder¶

Query Builder Interface¶

-- Access Visual Query Builder:
-- SQL Editor -> Open Visual Query Builder
-- Or click "Design" tab in SQL Editor

-- Query builder components:
-- 1. Table/View selector
-- 2. Join configuration
-- 3. Column selection
-- 4. WHERE conditions
-- 5. GROUP BY clauses
-- 6. HAVING conditions
-- 7. ORDER BY specification
-- 8. LIMIT/OFFSET settings

-- Example query built visually:
-- Tables: users, orders, order_items, products
-- Joins: 
--   users.id = orders.user_id
--   orders.id = order_items.order_id
--   order_items.product_id = products.id

-- Generated SQL:
SELECT 
    u.username,
    u.email,
    COUNT(DISTINCT o.id) as order_count,
    SUM(oi.quantity * oi.price) as total_spent,
    AVG(oi.price) as avg_item_price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= '2023-01-01'
  AND o.status = 'completed'
GROUP BY u.id, u.username, u.email
HAVING COUNT(DISTINCT o.id) >= 5
ORDER BY total_spent DESC
LIMIT 100;

Join Configuration¶

-- Join types available in visual builder:
-- 1. INNER JOIN
-- 2. LEFT JOIN (LEFT OUTER JOIN)
-- 3. RIGHT JOIN (RIGHT OUTER JOIN)
-- 4. FULL JOIN (FULL OUTER JOIN)
-- 5. CROSS JOIN

-- Join condition types:
-- 1. Equality (=)
-- 2. Inequality (!=, <>, <, >, <=, >=)
-- 3. LIKE pattern matching
-- 4. IN/NOT IN
-- 5. EXISTS/NOT EXISTS

-- Complex join example:
SELECT 
    u.username,
    p.name as product_name,
    oi.quantity,
    oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
LEFT JOIN product_reviews pr ON p.id = pr.product_id AND pr.user_id = u.id
WHERE o.status = 'completed'
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Self-join example:
SELECT 
    e1.name as employee,
    e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Multiple table join with aggregation:
SELECT 
    c.name as category,
    COUNT(p.id) as product_count,
    AVG(p.price) as avg_price,
    SUM(oi.quantity) as total_sold
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed' OR o.status IS NULL
GROUP BY c.id, c.name
ORDER BY total_sold DESC NULLS LAST;

Condition Builder¶

-- WHERE condition builder supports:
-- 1. Simple conditions
-- 2. Complex conditions with AND/OR
-- 3. Nested conditions with parentheses
-- 4. Subqueries
-- 5. Functions and expressions

-- Simple conditions:
WHERE users.status = 'active'
  AND users.created_at >= '2023-01-01'
  AND users.email LIKE '%@gmail.com'

-- Complex conditions with grouping:
WHERE (users.status = 'active' OR users.status = 'premium')
  AND users.created_at >= '2023-01-01'
  AND (users.country = 'US' OR users.country = 'CA')

-- Subquery conditions:
WHERE users.id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total_amount > 1000
)

-- EXISTS conditions:
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.user_id = users.id 
      AND o.status = 'completed'
)

-- Function-based conditions:
WHERE YEAR(users.created_at) = 2023
  AND MONTH(users.created_at) >= 6
  AND LENGTH(users.username) > 5

-- NULL handling:
WHERE users.phone IS NOT NULL
  AND users.last_login IS NOT NULL
  AND COALESCE(users.middle_name, '') != ''

-- Date range conditions:
WHERE orders.created_at BETWEEN '2023-01-01' AND '2023-12-31'
  AND orders.updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)

-- Pattern matching:
WHERE products.name LIKE '%laptop%'
  AND products.description REGEXP 'gaming|performance'
  AND products.sku NOT LIKE 'DISC-%'

Aggregation and Grouping¶

-- GROUP BY builder supports:
-- 1. Single column grouping
-- 2. Multiple column grouping
-- 3. Expression-based grouping
-- 4. Date/time grouping functions

-- Basic grouping:
SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price
FROM products
GROUP BY category;

-- Multiple column grouping:
SELECT 
    category,
    brand,
    COUNT(*) as product_count,
    SUM(stock_quantity) as total_stock
FROM products
GROUP BY category, brand
ORDER BY category, brand;

-- Date-based grouping:
SELECT 
    YEAR(created_at) as year,
    MONTH(created_at) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as monthly_revenue
FROM orders
WHERE created_at >= '2023-01-01'
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY year, month;

-- Expression-based grouping:
SELECT 
    CASE 
        WHEN price < 100 THEN 'Budget'
        WHEN price < 500 THEN 'Mid-range'
        ELSE 'Premium'
    END as price_category,
    COUNT(*) as product_count,
    AVG(price) as avg_price
FROM products
GROUP BY CASE 
    WHEN price < 100 THEN 'Budget'
    WHEN price < 500 THEN 'Mid-range'
    ELSE 'Premium'
END;

-- HAVING conditions:
SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING COUNT(*) >= 5
   AND SUM(total_amount) > 1000
ORDER BY total_spent DESC;

-- Window functions (advanced):
SELECT 
    user_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) as order_sequence,
    SUM(total_amount) OVER (PARTITION BY user_id ORDER BY order_date) as running_total,
    LAG(total_amount) OVER (PARTITION BY user_id ORDER BY order_date) as previous_order_amount
FROM orders
WHERE status = 'completed'
ORDER BY user_id, order_date;

Data Transfer¶

Database Migration¶

-- Database-to-database transfer:
-- Tools -> Database -> Transfer Data

-- Transfer configuration:
{
  "source": {
    "connection": "source_mysql",
    "database": "production",
    "tables": ["users", "orders", "products"]
  },
  "target": {
    "connection": "target_postgresql",
    "database": "staging",
    "create_tables": true,
    "drop_existing": false
  },
  "options": {
    "transfer_data": true,
    "transfer_structure": true,
    "batch_size": 1000,
    "use_transactions": true,
    "parallel_jobs": 4
  }
}

-- Data type mapping (MySQL to PostgreSQL):
{
  "type_mappings": [
    {"source": "INT AUTO_INCREMENT", "target": "SERIAL"},
    {"source": "VARCHAR(255)", "target": "VARCHAR(255)"},
    {"source": "TEXT", "target": "TEXT"},
    {"source": "DATETIME", "target": "TIMESTAMP"},
    {"source": "TINYINT(1)", "target": "BOOLEAN"},
    {"source": "DECIMAL(10,2)", "target": "NUMERIC(10,2)"}
  ]
}

-- Generated migration script:
-- Create target tables
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Transfer data with transformation
INSERT INTO target.users (username, email, created_at)
SELECT 
    username,
    email,
    created_at
FROM source.users
WHERE status = 'active';

-- Handle data conflicts:
INSERT INTO target.users (username, email, created_at)
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM target.users WHERE username = s.username)
        THEN CONCAT(s.username, '_migrated')
        ELSE s.username
    END,
    s.email,
    s.created_at
FROM source.users s;

Cross-Platform Data Transfer¶

-- Transfer between different database systems:

-- Oracle to MySQL:
{
  "source": "Oracle 19c",
  "target": "MySQL 8.0",
  "challenges": [
    "Date format differences",
    "Sequence to AUTO_INCREMENT",
    "PL/SQL to MySQL procedures",
    "Data type compatibility"
  ],
  "solutions": [
    "Use DATE_FORMAT() for date conversion",
    "Convert sequences to AUTO_INCREMENT",
    "Rewrite stored procedures",
    "Map Oracle types to MySQL equivalents"
  ]
}

-- SQL Server to PostgreSQL:
{
  "source": "SQL Server 2019",
  "target": "PostgreSQL 15",
  "type_mappings": [
    {"source": "NVARCHAR(MAX)", "target": "TEXT"},
    {"source": "UNIQUEIDENTIFIER", "target": "UUID"},
    {"source": "DATETIME2", "target": "TIMESTAMP"},
    {"source": "BIT", "target": "BOOLEAN"},
    {"source": "MONEY", "target": "NUMERIC(19,4)"}
  ]
}

-- Data transformation during transfer:
-- Source (SQL Server):
SELECT 
    Id,
    FirstName + ' ' + LastName as FullName,
    CONVERT(VARCHAR, BirthDate, 120) as BirthDate,
    CASE WHEN IsActive = 1 THEN 'true' ELSE 'false' END as IsActive
FROM Users;

-- Target (PostgreSQL):
INSERT INTO users (id, full_name, birth_date, is_active)
VALUES (?, ?, ?::timestamp, ?::boolean);

-- Handle encoding issues:
{
  "encoding": {
    "source": "latin1",
    "target": "utf8",
    "conversion": "iconv -f latin1 -t utf8"
  }
}

ETL Operations¶

-- Extract, Transform, Load operations:

-- Extract phase:
SELECT 
    customer_id,
    order_date,
    product_id,
    quantity,
    unit_price,
    quantity * unit_price as line_total
FROM raw_orders
WHERE order_date >= '2023-01-01';

-- Transform phase:
SELECT 
    o.customer_id,
    c.customer_name,
    o.order_date,
    p.product_name,
    p.category,
    o.quantity,
    o.unit_price,
    o.line_total,
    CASE 
        WHEN o.line_total > 1000 THEN 'High Value'
        WHEN o.line_total > 100 THEN 'Medium Value'
        ELSE 'Low Value'
    END as order_category,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) as order_sequence
FROM (
    SELECT 
        customer_id,
        order_date,
        product_id,
        quantity,
        unit_price,
        quantity * unit_price as line_total
    FROM raw_orders
    WHERE order_date >= '2023-01-01'
) o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

-- Load phase:
INSERT INTO analytics.order_facts (
    customer_id,
    customer_name,
    order_date,
    product_name,
    category,
    quantity,
    unit_price,
    line_total,
    order_category,
    order_sequence
)
SELECT 
    customer_id,
    customer_name,
    order_date,
    product_name,
    category,
    quantity,
    unit_price,
    line_total,
    order_category,
    order_sequence
FROM transformed_orders;

-- Data quality checks:
-- Check for duplicates
SELECT customer_id, order_date, product_id, COUNT(*)
FROM order_facts
GROUP BY customer_id, order_date, product_id
HAVING COUNT(*) > 1;

-- Check for missing references
SELECT DISTINCT customer_id
FROM order_facts
WHERE customer_id NOT IN (SELECT id FROM customers);

-- Check data ranges
SELECT 
    MIN(order_date) as earliest_date,
    MAX(order_date) as latest_date,
    MIN(line_total) as min_total,
    MAX(line_total) as max_total,
    COUNT(*) as total_records
FROM order_facts;

Extensions and Plugins¶

Database Driver Management¶

-- DBeaver supports numerous database drivers:
-- - MySQL/MariaDB
-- - PostgreSQL
-- - SQLite
-- - Oracle
-- - SQL Server
-- - DB2
-- - Sybase
-- - Firebird
-- - H2
-- - HSQLDB
-- - Derby
-- - MongoDB
-- - Cassandra
-- - Redis
-- - ClickHouse
-- - Snowflake
-- - BigQuery
-- - And many more...

-- Driver installation:
-- Database -> Driver Manager
-- Download/Update drivers automatically
-- Or manually add JAR files

-- Custom driver configuration:
{
  "driver_name": "Custom MySQL",
  "driver_class": "com.mysql.cj.jdbc.Driver",
  "jar_files": [
    "/path/to/mysql-connector-java-8.0.33.jar"
  ],
  "url_template": "jdbc:mysql://{host}[:{port}]/[{database}]",
  "default_port": 3306,
  "supports_transactions": true,
  "supports_multiple_results": true
}

Plugin Ecosystem¶

-- Available DBeaver plugins:
-- 1. Git integration
-- 2. ERD (Entity Relationship Diagrams)
-- 3. Data visualization
-- 4. Query profiling
-- 5. Database documentation
-- 6. Code formatting
-- 7. Schema comparison
-- 8. Data masking
-- 9. Backup/restore tools
-- 10. Performance monitoring

-- Install plugins:
-- Help -> Install New Software
-- Or download from DBeaver marketplace

-- Popular plugins:

-- 1. DBeaver PRO features:
-- - NoSQL databases support
-- - Cloud databases integration
-- - Advanced data visualization
-- - Query profiling and optimization
-- - Data masking and anonymization
-- - Advanced export/import formats

-- 2. Git integration:
-- - Version control for SQL scripts
-- - Collaborative development
-- - Change tracking
-- - Branch management

-- 3. ERD plugin:
-- - Visual database design
-- - Automatic diagram generation
-- - Relationship visualization
-- - Export to various formats

-- 4. Documentation generator:
-- - Automatic schema documentation
-- - HTML/PDF reports
-- - Table and column descriptions
-- - Relationship documentation

Custom Extensions¶

// Create custom DBeaver extension:
// 1. Set up Eclipse plugin development environment
// 2. Create new plugin project
// 3. Implement DBeaver extension points

// Example: Custom data export extension
public class CustomExportExtension implements IDataExporter {

    @Override
    public void exportData(IDataExportSite site) throws DBException {
        // Custom export logic
        IResultSet resultSet = site.getResultSet();

        while (resultSet.nextRow()) {
            Object[] row = resultSet.getRowData();
            // Process row data
            processRow(row);
        }
    }

    private void processRow(Object[] row) {
        // Custom row processing
        for (Object value : row) {
            // Transform and export value
            String transformedValue = transformValue(value);
            writeToOutput(transformedValue);
        }
    }
}

// Plugin manifest (plugin.xml):
<?xml version="1.0" encoding="UTF-8"?>
<plugin>
   <extension point="org.jkiss.dbeaver.dataExporter">
      <exporter
            class="com.example.CustomExportExtension"
            description="Custom data export format"
            id="custom.exporter"
            label="Custom Export"
            order="100">
      </exporter>
   </extension>
</plugin>

Configuration and Customization¶

-- DBeaver configuration files:
-- Windows: %APPDATA%\DBeaverData\workspace6\.metadata
-- macOS: ~/Library/DBeaverData/workspace6/.metadata  
-- Linux: ~/.local/share/DBeaverData/workspace6/.metadata

-- Key configuration files:
-- - .metadata/.plugins/org.eclipse.core.runtime/.settings/
-- - connections.xml (connection configurations)
-- - drivers.xml (driver configurations)
-- - preferences.properties (user preferences)

-- Workspace customization:
{
  "sql_editor": {
    "auto_completion": true,
    "syntax_highlighting": true,
    "line_numbers": true,
    "word_wrap": false,
    "tab_size": 4,
    "font_size": 12,
    "theme": "dark"
  },
  "data_viewer": {
    "max_rows": 200,
    "auto_refresh": false,
    "show_row_numbers": true,
    "null_value_display": "<NULL>",
    "date_format": "yyyy-MM-dd HH:mm:ss"
  },
  "performance": {
    "result_set_max_size": 100000,
    "query_timeout": 20000,
    "connection_pool_size": 10,
    "memory_limit": "2G"
  }
}

-- Export/import configuration:
-- File -> Export -> General -> Preferences
-- File -> Import -> General -> Preferences

-- Team configuration sharing:
-- Export workspace settings
-- Share connection templates
-- Standardize formatting rules
-- Common driver configurations

Best Practices¶

Performance Optimization¶

-- 1. Connection management:
-- Use connection pooling
-- Close unused connections
-- Configure appropriate timeouts
-- Monitor connection usage

-- Connection pool configuration:
{
  "initial_size": 2,
  "max_active": 10,
  "max_idle": 5,
  "min_idle": 1,
  "max_wait": 10000,
  "validation_query": "SELECT 1",
  "test_on_borrow": true,
  "test_while_idle": true
}

-- 2. Query optimization:
-- Use LIMIT for large result sets
SELECT * FROM large_table LIMIT 1000;

-- Use appropriate indexes
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

-- Avoid SELECT * in production queries
SELECT id, name, email FROM users WHERE status = 'active';

-- Use EXISTS instead of IN for subqueries
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
      AND o.status = 'completed'
);

-- 3. Memory management:
-- Configure appropriate result set limits
-- Use streaming for large exports
-- Monitor memory usage
-- Close result sets properly

-- 4. Network optimization:
-- Use compression for remote connections
-- Minimize data transfer
-- Use appropriate fetch sizes
-- Enable connection keep-alive

Security Best Practices¶

-- 1. Connection security:
-- Use SSL/TLS for remote connections
-- Implement proper authentication
-- Use least privilege principle
-- Regular password rotation

-- SSL connection configuration:
{
  "ssl_mode": "require",
  "ssl_cert": "/path/to/client-cert.pem",
  "ssl_key": "/path/to/client-key.pem",
  "ssl_ca": "/path/to/ca-cert.pem",
  "ssl_verify_server_cert": true
}

-- 2. Access control:
-- Create application-specific users
-- Grant minimal required privileges
-- Use roles for permission management
-- Regular access reviews

-- Example secure user setup:
CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'SecurePassword123!';
GRANT SELECT ON myapp.* TO 'app_readonly'@'%';

CREATE USER 'app_writer'@'%' IDENTIFIED BY 'SecurePassword456!';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_writer'@'%';

-- 3. Data protection:
-- Mask sensitive data in development
-- Use parameterized queries
-- Implement audit logging
-- Regular security updates

-- Data masking example:
SELECT 
    id,
    username,
    CONCAT(LEFT(email, 3), '***@', SUBSTRING_INDEX(email, '@', -1)) as masked_email,
    '***-***-' + RIGHT(phone, 4) as masked_phone
FROM users;

-- 4. Backup security:
-- Encrypt backup files
-- Secure backup storage
-- Test restore procedures
-- Document recovery processes

Development Workflow¶

-- 1. Environment management:
-- Separate development, staging, production
-- Use consistent naming conventions
-- Version control for database schemas
-- Automated deployment processes

-- Environment configuration:
{
  "development": {
    "host": "dev-db.internal",
    "database": "myapp_dev",
    "auto_commit": true,
    "query_timeout": 30
  },
  "staging": {
    "host": "staging-db.internal", 
    "database": "myapp_staging",
    "auto_commit": false,
    "query_timeout": 60
  },
  "production": {
    "host": "prod-db.internal",
    "database": "myapp_prod",
    "auto_commit": false,
    "query_timeout": 120,
    "read_only": true
  }
}

-- 2. Code organization:
-- Use folders for script organization
-- Consistent naming conventions
-- Document complex queries
-- Version control integration

-- Script organization:
/sql-scripts/
  /migrations/
    001_create_users_table.sql
    002_add_email_index.sql
    003_create_orders_table.sql
  /queries/
    /reports/
      monthly_sales_report.sql
      user_activity_report.sql
    /maintenance/
      cleanup_old_logs.sql
      rebuild_indexes.sql
  /procedures/
    calculate_user_stats.sql
    process_orders.sql

-- 3. Testing practices:
-- Test queries on sample data
-- Validate performance on production-size data
-- Use transactions for testing
-- Document test cases

-- Testing template:
START TRANSACTION;

-- Test data setup
INSERT INTO test_users (username, email) VALUES
('test_user_1', 'test1@example.com'),
('test_user_2', 'test2@example.com');

-- Execute test query
SELECT COUNT(*) FROM test_users WHERE email LIKE '%@example.com';

-- Verify results
-- Expected: 2 rows

-- Cleanup
ROLLBACK;

-- 4. Documentation:
-- Document database schema
-- Explain complex business logic
-- Maintain change logs
-- Create user guides

-- Schema documentation template:
/*
Table: users
Purpose: Store user account information
Created: 2023-01-01
Last Modified: 2023-12-01

Columns:
- id: Primary key, auto-increment
- username: Unique username, 3-50 characters
- email: User email address, must be valid format
- password_hash: Bcrypt hashed password
- status: Account status (active, inactive, suspended)
- created_at: Account creation timestamp
- updated_at: Last modification timestamp

Indexes:
- PRIMARY KEY (id)
- UNIQUE KEY uk_username (username)
- KEY idx_email (email)
- KEY idx_status_created (status, created_at)

Related Tables:
- orders (user_id references users.id)
- user_profiles (user_id references users.id)
*/

Maintenance and Monitoring¶

-- 1. Regular maintenance tasks:
-- Monitor database growth
-- Analyze query performance
-- Update statistics
-- Rebuild indexes when needed

-- Database size monitoring:
SELECT 
    table_schema as database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

-- Table growth monitoring:
SELECT 
    table_name,
    table_rows,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) as size_mb,
    ROUND((data_length / 1024 / 1024), 2) as data_mb,
    ROUND((index_length / 1024 / 1024), 2) as index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

-- 2. Performance monitoring:
-- Track slow queries
-- Monitor connection usage
-- Analyze index effectiveness
-- Review execution plans

-- Slow query analysis:
SELECT 
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY query_time DESC
LIMIT 10;

-- 3. Backup verification:
-- Test restore procedures
-- Verify backup integrity
-- Monitor backup completion
-- Document recovery procedures

-- Backup verification script:
#!/bin/bash
BACKUP_FILE="/backups/myapp_$(date +%Y%m%d).sql"
TEST_DB="myapp_restore_test"

# Create test database
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS $TEST_DB;"

# Restore backup to test database
mysql -u root -p $TEST_DB < $BACKUP_FILE

# Verify restore
TABLES=$(mysql -u root -p -e "USE $TEST_DB; SHOW TABLES;" | wc -l)
echo "Restored $TABLES tables"

# Cleanup
mysql -u root -p -e "DROP DATABASE $TEST_DB;"

-- 4. Capacity planning:
-- Monitor growth trends
-- Plan for scaling
-- Optimize resource usage
-- Prepare for peak loads

-- Growth trend analysis:
SELECT 
    DATE(created_at) as date,
    COUNT(*) as daily_records,
    SUM(COUNT(*)) OVER (ORDER BY DATE(created_at)) as cumulative_records
FROM large_table
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date;

Summary¶

DBeaver is a comprehensive database management tool that provides a unified interface for working with multiple database systems. This cheatsheet covers the essential features and best practices for effective database development and administration.

Key Strengths: - Universal Support: Works with virtually all major database systems - Rich Feature Set: SQL editing, data visualization, schema management - Cross-Platform: Available on Windows, macOS, and Linux - Extensible: Plugin architecture for additional functionality - Free and Open Source: Community edition available at no cost

Best Use Cases: - Database development and administration - Data analysis and reporting - Schema design and migration - Multi-database environment management - Team collaboration on database projects

Important Considerations: - Performance can be impacted with very large result sets - Some advanced features require DBeaver PRO license - Memory usage should be monitored for large operations - Regular updates recommended for latest database driver support - Proper security configuration essential for production use

By following the practices and techniques outlined in this cheatsheet, you can effectively use DBeaver to manage databases while maintaining performance, security, and productivity in your database operations.

DBeaver Cheatsheet¶

DBeaver - Universal Database Tool

Table of Contents¶

Installation¶

Windows Installation¶

macOS Installation¶

Linux Installation¶

Docker Installation¶

System Requirements¶

Database Connections¶

Connection Configuration¶

Advanced Connection Settings¶

SSH Tunnel Configuration¶

Connection Templates¶

SQL Editor¶

Basic SQL Operations¶

SQL Editor Features¶

Query History and Bookmarks¶

SQL Script Execution¶

Data Viewer¶

Grid View Features¶

Data Filtering¶

Data Editing¶

Data Navigation¶

Schema Browser¶

Database Object Navigation¶

Object Operations¶

Schema Comparison¶

ER Diagrams¶

Query Execution¶

Execution Plans¶

Performance Monitoring¶

Batch Operations¶

Query Debugging¶

Data Export/Import¶

Export Formats¶

Import Data¶

Bulk Data Operations¶

Data Synchronization¶

Database Administration¶

User Management¶

Backup and Restore¶

Performance Monitoring¶

Index Management¶

Visual Query Builder¶

Query Builder Interface¶

Join Configuration¶

Condition Builder¶

Aggregation and Grouping¶

Data Transfer¶

Database Migration¶

Cross-Platform Data Transfer¶

ETL Operations¶

Extensions and Plugins¶

Database Driver Management¶

Plugin Ecosystem¶

Custom Extensions¶

Configuration and Customization¶

Best Practices¶

Performance Optimization¶

Security Best Practices¶

Development Workflow¶

Maintenance and Monitoring¶

Summary¶