Mastering SQL Scripting, Delimiters & Client Variables in DBeaver

While basic database tools treat SQL editors as mere text fields with a single "Run" button, DBeaver is an integrated development environment built for enterprise software engineering. Writing multi-file database migrations, complex procedural scripts, and automated batch jobs requires understanding how DBeaver parses, partitions, and dispatches statements to target database engines.

1. Statement Execution vs. Batch Script Execution

A frequent source of confusion among new DBeaver developers is the fundamental difference between executing a single SQL query and executing a batch script. DBeaver provides separate keybindings for each operational mode:

2. Controlling Statement Delimiters in DBeaver

By default, standard ANSI SQL engines and DBeaver recognize the semicolon (;) as the statement delimiter. However, when writing stored procedures, functions, or triggers containing internal BEGIN ... END; blocks, multiple semicolons exist inside a single procedural unit. If DBeaver parsed by standard semicolon, it would split the routine prematurely and cause database syntax errors.

DBeaver offers two verified methods to redefine delimiters inside your script:

Method A: The DBeaver Delimiter Directive (--delimiter)

This directive tells the DBeaver client parser to switch the delimiter string dynamically:

-- 1. Switch active delimiter to double dollar sign ($$)
--delimiter $$

CREATE OR REPLACE FUNCTION get_department_headcount(dept_id INT)
RETURNS INT AS $$
DECLARE
    v_total INT;
BEGIN
    SELECT COUNT(*) INTO v_total
    FROM employees
    WHERE department_id = dept_id;
    
    RETURN v_total;
END;
$$ LANGUAGE plpgsql$$

-- 2. Restore standard delimiter back to semicolon
--delimiter ;

SELECT get_department_headcount(101) AS it_headcount;

Method B: Standard MySQL / Oracle DELIMITER Syntax

For MySQL, MariaDB, and Oracle dialects, DBeaver natively recognizes the classic DELIMITER // directive:

DELIMITER //

CREATE PROCEDURE archive_inactive_users(IN cutoff_days INT)
BEGIN
    INSERT INTO users_archive
    SELECT * FROM users 
    WHERE last_login < DATE_SUB(NOW(), INTERVAL cutoff_days DAY);

    DELETE FROM users 
    WHERE last_login < DATE_SUB(NOW(), INTERVAL cutoff_days DAY);
END //

DELIMITER ;

3. Client-Side Scripting Variables (@set & ${var})

Often, developers need to repeat identical values (such as an analytical start date, tenant ID, or status code) across multiple queries without writing database-specific session variable statements (like PostgreSQL SET LOCAL or SQL Server DECLARE @var).

DBeaver provides a client-side variable engine using @set and ${var}:

-- Define variables at the client level before execution
@set start_date = '2026-01-01'
@set end_date = '2026-12-31'
@set min_order_total = 500.00

-- Reference variables seamlessly using ${variable_name}
SELECT 
    c.customer_name,
    o.order_id,
    o.total_amount,
    o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '${start_date}' AND '${end_date}'
  AND o.total_amount >= ${min_order_total}
ORDER BY o.order_date DESC;

4. Interactive Query Parameters (:param)

When building queries for ad-hoc reports or team distribution, you can use parameter placeholders prefixed with a colon (e.g. :param_name). Whenever the query is run, DBeaver halts execution and displays an intuitive modal prompting the developer for values:

SELECT 
    transaction_id, 
    account_number, 
    amount, 
    status, 
    created_at
FROM financial_transactions
WHERE branch_code = :branch_code
  AND created_at >= :filter_date;

DBeaver automatically remembers previously entered parameter values during your session, accelerating repetitive ad-hoc data analysis.

5. Modular Scripts with @include

In large enterprise systems, running database schema updates or test suites often involves dozens of SQL files. Rather than combining them into one unwieldy 50,000-line script, DBeaver allows orchestrating scripts via @include:

-- master_release_2026.sql
@include './schemas/01_create_tables.sql';
@include './schemas/02_create_indexes.sql';
@include './procedures/03_stored_routines.sql';
@include './seeds/04_seed_reference_data.sql';

6. Transactions, Auto-Commit & Isolation Levels

Accidental data loss is the developer's nightmare. DBeaver features deep transaction controls directly in the bottom status bar:

• Auto-commit (Default): Every SQL statement is immediately committed upon execution. Best for read-only queries and local development.
• Manual Commit Mode: Statements run within an open transaction block until explicitly committed or rolled back.

7. Production Connection Safety Configurations

One of DBeaver's most powerful safety mechanisms is Connection Types. Right-click any connection → Edit Connection → General → Connection Type:

• Development (Green): Free auto-commit, no confirmation prompts.
• Test / Staging (Yellow): Standard warnings for schema drops.
• Production (Red):
  • The entire editor tab turns bright red in the header.
  • Auto-commit is strictly disabled by default.
  • DBeaver displays an explicit confirmation dialog before executing any UPDATE, DELETE, or DROP command.

8. Summary & Recommended Next Steps

By adopting DBeaver's advanced scripting features—custom delimiters, parameter bindings, client variables, and transaction boundaries—you ensure your database code is safe, reusable, and maintainable.