Nodesail Shift 3

What is Shift 3?

Nodesail Shift 3 is a next-generation schema migration platform purpose-built for PostgreSQL. Unlike traditional migration tools that require developers to author imperative, sequential ALTER or CREATE scripts by hand, Shift 3 takes a fundamentally different approach: declarative state management.

You describe what your database should look like by uploading a standard SQL dump of your desired schema. Shift 3 handles the rest — it computes exactly what needs to change, generates the migration SQL automatically, and applies it with full transaction safety.

Think of it as the difference between telling someone how to rearrange a room versus simply showing them a photo of how it should look. Shift 3 figures out the steps for you.

Shift 3 is part of the Nodesail platform, an integrated developer infrastructure service offering databases, authentication, storage, and now schema management as unified, cloud-native services.

Core Architecture

Shift 3 is built around three primary subsystems that work in concert:

2.1 — The Diff Engine

The Diff Engine reads the structural metadata of two PostgreSQL databases directly from information_schema.columns, information_schema.tables, and related catalog views. This means comparisons are based on the actual structure of a database, not a textual or heuristic-based analysis of SQL files. The result is a deterministic, ordered changeset with no ambiguity.

2.2 — The Execution Engine

Once a changeset is computed, the Execution Engine generates a well-formed, ordered SQL migration script. This script is then applied to your target database within a single atomic transaction. If any statement fails, the entire transaction is rolled back automatically.

2.3 — The Isolation Layer

Before any comparison is made, Shift 3 spins up an ephemeral PostgreSQL instance in an isolated cloud environment. Your uploaded SQL is executed here. This prevents your local SQL from ever touching production during the analysis phase. Once the diff is computed, the temporary database is destroyed.

User uploads local.sql
       ↓
Isolation Layer spins up temp DB
       ↓
local.sql is executed in temp DB
       ↓
Diff Engine reads information_schema from both temp DB and production
       ↓
Deterministic changeset is computed
       ↓
Execution Engine wraps changeset in BEGIN...COMMIT block
       ↓
Migration is applied to production
       ↓
Temp DB is destroyed

How It Works — Step by Step

Here is a complete walkthrough of a typical migration workflow from start to finish.

Key Features

Migration Safety & Transaction Model

Data safety is the foundational design constraint of Shift 3. Every decision in the system — from the ephemeral sandbox to the transactional execution model — is made to minimize risk to your production data.

5.1 — The Atomic Transaction Guarantee

All generated migration SQL is wrapped in a single PostgreSQL transaction block:

BEGIN;

  -- Shift 3 generated migration
  CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(100) UNIQUE NOT NULL,
    price NUMERIC(10,2) NOT NULL
  );

  ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

  ALTER TABLE orders ALTER COLUMN status TYPE VARCHAR(50);

COMMIT;

-- If any statement above fails, nothing is committed.

PostgreSQL guarantees that if any single statement within a transaction fails, the entire transaction is rolled back. This means it is physically impossible for Shift 3 to leave your database in a partially-migrated, corrupted state.

5.2 — Pre-flight Checks

Before presenting you with the option to apply a migration, Shift 3 performs an automated pre-flight analysis:

• Destructive operation detection: Any DROP TABLE or DROP COLUMN statements are flagged prominently with a warning.
• Lock analysis: Operations that require an AccessExclusiveLock (like altering column types) are identified along with their potential impact on concurrent queries.
• Transactional viability: Certain PostgreSQL operations cannot run within a transaction (e.g., CREATE INDEX CONCURRENTLY). These are flagged and handled separately.

5.3 — Ephemeral Isolation

Your uploaded SQL file is never executed directly against your production database. It is always executed in an isolated, temporary PostgreSQL instance first. This sandbox has no network access to your production systems and is destroyed within seconds of the diff being computed.

The Structural Diff Engine

The Diff Engine is the intellectual core of Shift 3. It operates by querying PostgreSQL's built-in system catalog views to derive a complete structural fingerprint of each database being compared.

The primary query used during structural extraction is:

SELECT
  t.table_name,
  c.column_name,
  c.data_type,
  c.character_maximum_length,
  c.numeric_precision,
  c.numeric_scale,
  c.is_nullable,
  c.column_default,
  c.ordinal_position
FROM
  information_schema.tables t
  JOIN information_schema.columns c
    ON t.table_name = c.table_name
    AND t.table_schema = c.table_schema
WHERE
  t.table_schema = 'public'
  AND t.table_type = 'BASE TABLE'
ORDER BY
  t.table_name, c.ordinal_position;

This produces a complete inventory of every column in every table in the public schema. The Diff Engine then compares two such inventories side-by-side and produces three categories of changes:

• Additions (+): Tables or columns present in the desired state but not in the current state. These result in CREATE TABLE or ALTER TABLE ... ADD COLUMN statements.
• Alterations (~): Columns present in both states but with differing types, defaults, or nullability constraints. These result in ALTER TABLE ... ALTER COLUMN or ALTER TABLE ... ALTER COLUMN ... SET DEFAULT statements.
• Removals (-): Tables or columns present in the current state but absent from the desired state. These result in DROP TABLE or ALTER TABLE ... DROP COLUMN statements.

Supported Operations

The following DDL operations are currently supported by the Shift 3 Diff Engine and Execution Engine:

Google Drive Backup System

Shift 3 includes a fully integrated automated backup system that exports your PostgreSQL database schemas and data directly to your personal or team Google Drive. Powered by native PostgreSQL 17 clients, these backups guarantee flawless restoration. Backups can be scheduled on a flexible frequency or at a specific date and time.

8.1 — Connecting Google Drive

Navigate to Backup in the dashboard. You will be prompted to authorize Shift 3 to access a specific folder in your Google Drive via standard OAuth 2.0. Shift 3 only requests the minimum permissions necessary: access to a single dedicated folder.

8.2 — Scheduling Options

You may schedule backups using two modes:

• Frequency-based: Run a backup every 15 minutes, hourly, every 6 hours, daily, weekly, or monthly.
• Custom schedule: Specify an exact date and time for a one-off or recurring backup at a precise moment.

8.3 — Manual Backups

In addition to scheduled backups, you can trigger a manual backup at any time with a single click. Simply select your target database from the dropdown and press Backup Now. The backup job runs asynchronously using memory-efficient streaming and the resulting file is deposited securely into your connected Google Drive folder.

8.4 — What gets backed up

Backups are a complete logical export of the selected database, generated using the native pg_dump binary. We utilize the Custom Format (-Fc) which provides a highly compressed, highly flexible output. This format is the gold standard for restoring complex schemas, views, sequences, and foreign keys flawlessly using pg_restore.

Audit Logs & Traceability

Every migration action performed through Shift 3 is permanently logged in an immutable audit trail. This includes successful migrations, failed migrations, and rollback events.

Each audit log entry contains:

• Timestamp of the migration (timezone-aware)
• Identity of the user who initiated the migration
• Target database name and connection identifier
• The full SQL that was applied (or attempted)
• Final status: SUCCESS, FAILED, or ROLLED BACK
• Duration of the migration execution
• PostgreSQL error message in the event of failure

Audit logs cannot be modified or deleted by users. They are retained for the lifetime of your Nodesail organization account, providing a permanent and accountable history of all schema changes.

Security & Data Privacy

Security is a core design principle — not an afterthought. Here is how Shift 3 handles your sensitive data:

• Encrypted credentials: All database connection strings are encrypted at rest using AES-256. They are decrypted only in memory at the moment they are needed for a connection.
• Zero persistent data retention: Your uploaded SQL files and all data extracted from temporary databases are destroyed immediately after the diff computation is complete. We do not store a copy of your schema or data on our servers.
• Network isolation: Ephemeral sandbox databases run in an isolated network environment with no outbound access to the public internet or your production systems.
• TLS in transit: All communications between your browser, the Shift 3 API, and your databases are encrypted via TLS 1.2 or higher.
• Nodesail SSO: Authentication is handled exclusively through the Nodesail identity platform. Shift 3 itself never stores passwords.
• Google Drive OAuth: Shift 3 requests only the minimum OAuth scopes necessary for backup operations. We request access to a single application-specific folder, not your entire Drive.

Why use Shift 3 over alternatives?

There are several schema migration tools available to PostgreSQL developers. Here is how Shift 3 compares to the most common approaches:

Frequently Asked Questions