Schema Ops

Under Schema → Schema Ops, DB Studio keeps snapshots of a connection's schema and works from them: export a schema as DDL, detect drift from a baseline, plan the SQL that brings a database to a desired schema, lint SQL, and run pre-flight checks. Every action reads the engine and database from the connection you pick.

Who Can Do What

ActionRole
View snapshots and drift alerts, export, compare, plan, lintAny signed-in user
Capture or delete a snapshot, scan for drift, acknowledge an alertRelease Manager or admin
Run pre-flight checksRelease Manager or admin (the same role that can run a script)

Buttons for actions your role cannot take are not shown. Each connection picker lists only the connections whose engine that action supports; when none qualifies the picker says so and names the supported engines on hover. If something fails to load, the view shows why with a Retry button.

Snapshots and the Baseline

A snapshot records a connection's tables, columns, keys, indexes and views at a moment, with a label, who captured it and when. Enter a Schema to limit a snapshot to that schema (on MySQL / MariaDB, that database); drift and plans then compare the same schema only. One snapshot per connection is its baseline, marked in the Snapshots list: the first snapshot of a connection becomes the baseline, and a later one becomes it when you tick Use as the drift baseline while capturing or choose Set as Baseline on its row. Capturing, setting the baseline and deleting each say which connection and snapshot they changed; Delete asks first, and says when the snapshot is the baseline. Opening the Schema ERD never creates a snapshot.

Open on a snapshot shows its schemas with their tables, views, enum types and sequences, and offers Export DDL on engines that export (to copy or download in the engine's own script format: .sql, .js for MongoDB, .cql for Cassandra, .cypher for Neo4j, .json for Elasticsearch), Compare with Live (the differences between the snapshot and the connection now) and a comparison with another snapshot of the connection. Differences read as sentences, each with its severity.

Drift

Scan for Drift compares the connection's live schema with its baseline; the Drift tab names the baseline it uses. Each difference becomes an alert with a severity: Info for additions, Warning for changes, Error for anything destructive. Scan for Drift says on hover why it waits when the connection has no baseline yet. Alerts are listed as:

Export and Plans

Export renders a connection's live schema, or one schema of it, as DDL you can copy. On PostgreSQL the DDL recreates the schema in an empty database: enum types and sequences first, then tables with their identity columns and enum-typed columns, sequence ownership, foreign keys, indexes, and views in the order they depend on each other. Plan compares the Connection to Migrate with the Snapshot to Match and produces the SQL to get there, creating any schema, enum type or sequence it needs. Enum values can be added this way; removing or reordering them is flagged for you to write by hand, since PostgreSQL cannot do it in place.

Supported Engines

Two schemas can only be compared when they come from the same engine.

Lint

The Lint tab checks SQL against a fixed set of rules: destructive statements (DROP TABLE, DROP COLUMN, TRUNCATE, DELETE or UPDATE without WHERE), PostgreSQL locking (CREATE INDEX without CONCURRENTLY), ADD COLUMN ... NOT NULL without a default, tables without a primary key, hardcoded passwords, GRANT ALL and GRANT ... TO PUBLIC, and narrowing column types. Paste SQL or choose a Saved Script; choosing one also selects its connection's database system. Each finding gives its line and a suggested fix. The Rule Catalog lists every rule with its severity, the database systems it applies to and what it checks. Text inside comments, string literals and quoted names is ignored, so commented-out SQL raises nothing, and the last statement counts even without a closing semicolon. The rule catalog lists every rule with the engines it applies to.

The other engines are linted in their own language, against their own rules:

Pre-flight Checks

A pre-flight check is a line in a script of the form -- @check <label>: <boolean SQL expression>, for example -- @check no_null_emails: (SELECT count(*) FROM users WHERE email IS NULL) = 0. Checks run in order and stop at the first that fails. Each runs read-only: on PostgreSQL and MySQL in a read-only transaction, on SQLite on a read-only handle, and on SQL Server inside a transaction that is rolled back, so a check cannot change the database; an expression that tries to (for example by calling nextval()) fails with the engine's message. A check containing a semicolon is refused. On SQL Server a check must be a true/false condition, since it is evaluated as CASE WHEN <expression> THEN 1 ELSE 0 END.

The other engines write checks in their own language, with their own comment mark (--, // or # all start a check line), and the check lines are left out when the script itself runs:

A comparison is written after the query with a space before the operator; a single = is not a comparison there, so it cannot be mistaken for part of a query.

Checks also run whenever the script runs, alone or as part of a release: before any of its SQL, against the same connection and database. If a check fails, cannot be evaluated, or the connection's engine does not support checks, the script is not run and its run is recorded as failed with the reason (for example Pre-flight check 'no_null_emails' on line 1 failed); a release run stops at that script as it does at any failure. When every check passes, the run's output starts with Pre-flight checks passed and the label of each check. Use the Pre-flight tab to try a script's checks without running it.

Previous
← Notifications
Next
Metrics →