Validate Database Schema Integrity

When teams first run atlas schema validate, they often discover issues that have been silently lurking in their database for years. These are objects that were created in the past and never updated, or were created with database-specific settings that allowed invalid definitions (e.g., check_function_bodies = off in PostgreSQL). These problems surface when you try to recreate the schema from scratch or migrate to a new environment.

Atlas treats your database schema like application code: version controlled in git, reviewed in pull requests, validated for semantic correctness, and reproducible across environments. This guide focuses on the validation part - catching issues that traditional migration tools miss.

The Problem: "It Works in Production"

Database schemas, like any software, accumulate technical debt over time. But unlike application code, databases are forgiving - they let you create objects that reference other objects, then let you modify those dependencies without re-validating the entire schema graph. This hidden debt stays invisible until something breaks - either when you try to recreate the schema from scratch, or when you interact with those objects at runtime, like calling a function or querying a view.

Atlas catches these issues because it validates your schema semantically - not just syntactically. When you inspect a database and try to apply that schema to a fresh database, invalid objects fail loudly.

Why Databases Allow Invalid Objects

Most databases defer validation to runtime. This is by design - it allows flexibility, circular references, and dynamic SQL. But it also means invalid objects accumulate silently:

Database	Behavior
SQL Server	Deferred name resolution - procedures validated at execution, not creation
MySQL	Validates syntax only, not object references
PostgreSQL	Validates function bodies at creation, but often disabled via check_function_bodies = off

The result: schemas that "work" in production only because no one has called that broken function or queried that stale view recently.

Running Validation

# Export your schema
atlas schema inspect -u "$DB_URL" --format '{{ sql . }}' > schema.sql

# Validate against a clean database
atlas schema validate \
  --url file://schema.sql \
  --dev-url "docker://postgres/16/dev"

The --dev-url spins up a clean database to test recreation. See Dev Database for connection strings for MySQL, SQL Server, and others.

What Atlas Catches

Stale Views

Views using SELECT * don't update when underlying tables change:

CREATE VIEW active_users AS SELECT * FROM users WHERE active = true;

-- Later:
ALTER TABLE users ADD COLUMN email VARCHAR(255);

The view still returns the old column set. Queries expecting email fail or return wrong data. SQL Server caches view metadata at creation - the view literally doesn't know about new columns.

Fix: Explicit column lists. Always.

CREATE VIEW active_users AS
  SELECT id, name, email, active FROM users WHERE active = true;

Invalid Functions

Functions that reference non-existent tables, columns, or other functions fail validation. If a function body refers to an object that doesn't exist, Atlas catches it:

CREATE FUNCTION get_active_users() RETURNS SETOF users AS $$
  SELECT * FROM users WHERE status = 'active';
$$ LANGUAGE sql;

-- Later:
ALTER TABLE users RENAME COLUMN status TO user_status;

The function body still references status, which no longer exists. PostgreSQL won't catch this if check_function_bodies is off (common in migration scripts). Atlas validates the function can actually be created - if the referenced column doesn't exist, validation fails.

Orphaned Foreign Keys

Foreign keys pointing to renamed columns:

ALTER TABLE orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Later:
ALTER TABLE customers RENAME COLUMN id TO customer_id;

Depending on the database, this silently breaks the constraint or blocks the rename. Either way, your schema state is inconsistent with what you think it is.

After Validation

Once atlas schema validate passes, apply the cleaned schema:

Versioned Migrations

Generate migration files with atlas migrate diff, apply with atlas migrate apply. Full audit trail, CI/CD integration, rollback support.

Declarative Migrations

Define desired state, let Atlas compute the diff. Apply with atlas schema apply, or pre-plan with atlas schema plan for review.

Continuous Validation

Running validation once finds existing debt. Running Atlas continuously prevents new debt from accumulating. Every schema apply or migrate diff validates the entire schema graph - broken objects can't be introduced because Atlas won't generate migrations for invalid states.

This is the difference between periodic audits and continuous enforcement. The issues described above simply don't happen when Atlas manages your schema.

Have questions? Find us on Discord.