The environment system: Dev, Staging, Prod and their safeguards

Classifying your connections to avoid accidents

The main risk when working with databases day to day is not technical. It is human. A tab left open on the wrong connection, a DROP TABLE run too hastily, an UPDATE without a WHERE clause fired at production instead of staging. Traditional tools do not really distinguish between environments. The production connection looks just like the development one. The same "Execute" button produces the same effects everywhere.

QoreDB starts from a simple principle: every connection must declare its environment, and the application must adapt its behavior accordingly. This is not a cosmetic label. It is a piece of metadata stored in the vault, propagated to the Rust backend, and used by the rules engine to decide what is allowed.

Three levels, three behaviors

Every connection saved in QoreDB carries an environment field that takes one of three possible values: Development, Staging, or Production. This field is set when the connection is created and is part of the metadata persisted in the encrypted vault, on the same footing as the host, the port, or the driver.

In Development mode, no particular restriction applies. The user can freely run DROPs, TRUNCATEs, or UPDATEs without a WHERE clause. This is the everyday working environment, the one where you iterate quickly.

In Staging mode, QoreDB starts to step in. UPDATEs and DELETEs without a WHERE clause trigger a confirmation prompt. The interface displays a distinct visual indicator to remind you that you are no longer in development.

In Production mode, the safeguards are at their maximum. DROPs and TRUNCATEs are blocked by default. DELETEs require an explicit confirmation. ALTERs generate a warning. And read-only mode can be enabled to forbid any mutation.

A permanent visual signal

Identifying the environment does not rely solely on security rules. It also comes through a color system applied to the interface. Each environment is associated with a pair of CSS variables: a primary color and a softened variant for backgrounds.

Development uses a neutral theme. Staging stands out with a dedicated color. Production displays red borders and a "PROD" badge visible in the status bar and in the connection tree. The goal is that at no point can the user mistake a production tab for a development tab. The reminder is constant, not just at execution time.

SQL analysis before execution

When a query is submitted, QoreDB does not simply forward it to the database engine. It first passes through an analysis module, sql_safety, which uses sqlparser to parse the query according to the dialect of the active driver (PostgreSQL, MySQL, DuckDB, SQL Server, or a generic dialect).

The analysis produces two indicators: is_mutation and is_dangerous. The first identifies any query that modifies data (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP). The second targets high-risk cases: DROP, TRUNCATE, ALTER, and above all UPDATE or DELETE without a WHERE clause.

This analysis is syntactic, not heuristic. It relies on the syntax tree produced by the parser, which avoids false positives caused by keywords appearing in comments or string literals. The frontend also performs a pre-check with regexes to give immediate feedback, but it is the Rust backend that is authoritative.

The safety rules engine

The SQL analysis feeds a rules engine, the SafetyEngine, which decides on the action to take. This engine operates with two categories of rules: built-in rules and custom rules.

The built-in rules cover the most common cases. In production, a DROP is blocked. A TRUNCATE is blocked. A DELETE requires a confirmation. An UPDATE without a WHERE clause requires a confirmation (including in staging). An ALTER triggers a warning. These rules are active by default and can be disabled individually if needed.

The user can also define their own rules through the interface. Each rule specifies a target environment, an operation type, an action (block, warn, ask for confirmation), and an optional regex pattern to target specific queries. For example, you can create a rule that blocks any SELECT on a table containing sensitive data in production.

The engine evaluates the rules in order. The first rule that matches the query's context determines the action. The context includes the environment, the operation type, the query text, and the user's acknowledgment state.

A policy configurable per deployment

Beyond individual rules, QoreDB exposes a global policy through the config.json file and environment variables. The QOREDB_PROD_BLOCK_DANGEROUS variable forces all dangerous queries to be blocked in production. QOREDB_PROD_REQUIRE_CONFIRMATION enables mandatory confirmation.

Other variables let you bound execution: maximum query duration, maximum number of rows returned, maximum number of concurrent queries. These limits are not specific to an environment, but they are particularly useful in a shared production context.

This environment-variable system lets teams deploy QoreDB with a predefined security policy, without depending on each user's manual configuration.

Conclusion

QoreDB's environment system treats security as a property of the connection, not as a global option you turn on or forget about. The Dev/Staging/Prod classification runs through the entire application, from the encrypted vault to the rules engine, by way of the visual interface. The result is a tool that makes it hard to make a mistake in production, while staying non-intrusive in development.