Testing PostgreSQL Schemas with pgTAP and Atlas

Testing your database schema shouldn't be an afterthought. Automating checks on your objects, constraints, and functions is critical to catching breaking changes before they hit production.

Enter pgTAP—which relies on traditional runtime assertions in an ephemeral database—and Atlas—which leverages a modern, declarative approach. This post breaks down how both tools handle schema testing and explains when you should swap out heavy runtime assertions for Atlas's lightweight lint rules.

What is TAP?

TAP (Test Anything Protocol) is a text-based interface used to communicate the results of unit tests to a test harness. It standardizes test outputs into a stream of sequential, readable lines that denote whether a test passed or failed, along with the test count and optional diagnostics. This protocol decouples the execution of a database's test logic from the reporting tool, allowing developers to use familiar TAP-compliant harnesses (like Perl's prove) to aggregate and analyze test results.

A typical TAP stream looks like this:

1..3
ok 1 - users table should exist
ok 2 - email should not be null
not ok 3 - name should not be null
# Failed test 3: "name should not be null"
#     Column public.users.name may be null

The first line declares how many tests ran (1..3). Each ok or not ok line reports one result. Lines starting with # carry optional diagnostics when a test fails.

What is pgTAP?

pgTAP is a PostgreSQL extension that wraps schema checks in SQL-callable assertion functions for structures, permissions, and business logic, and reports results using the TAP format.

For example:

SELECT plan(1);
SELECT has_table('public', 'users', 'users table should exist');
SELECT * FROM finish();

Running this against a database with a users table prints:

ok 1 - users table should exist

What is Atlas?

Atlas treats your database schema as code. Its testing framework uses .test.hcl files to manage test suites, allowing you to write assertions in plain SQL while using the Atlas DDL (HCL) to handle seed data, cleanup, parallelism, and table-driven runs.

For example:

test "schema" "users_email" {
  catch {
    sql   = "INSERT INTO users (email) VALUES (NULL)"
    error = "null value in column \"email\""
  }
}

Running atlas schema test --env local reports PASS or FAIL for each case:

-- PASS: users_email (12ms)
PASS

How pgTAP and Atlas Compare

Test Environment and Scope

pgTAP runs assertions inside the database session you connect to. By default, tests execute inside a transaction that rolls back when finished, so your real data is never modified. You are responsible for making sure the connected database reflects the schema you want to test, typically by spinning up a Docker container or restoring from a dump.

Atlas takes a different starting point. Before each test case, it provisions a fresh schema on a dev database, applies the declared schema source (an HCL or SQL schema file, a migration directory, or a composite of sources), runs the assertions, and cleans up afterward. This means Atlas tests are always tied to a specific schema definition, not just whatever happens to be in a running instance.

Writing and Organizing Tests

pgTAP's strength is its library of pre-written assertion functions. Checking that a table exists, a column has the right type, or a query throws the expected error requires only a single function call rather than hand-written catalog queries.

Atlas tests use familiar SQL for the assertions themselves, but the structure around them (test cases, seed data, cleanup, parallelism, and table-driven patterns) is defined in HCL. This is to support the use of Atlas for testing business logic, such as validating migration behavior, asserting function and trigger output, and verifying views and queries return expected results. Atlas supports for_each for table-driven cases, parallel execution for independent tests, and an external command to invoke programs outside the database when SQL alone is not enough.

Shared Capabilities

Despite their different architectures, pgTAP and Atlas cover much of the same ground:

Area	What you can verify
Values and data	Queries return expected values, match regular expressions, or compare numbers
Errors	A statement fails with a specific error message or code, and queries run without crashing
Query results	Two result sets match, a query is empty, or a specific row exists in a larger dataset
Database structure	Tables, schemas, views, and indexes exist; columns have the expected types, defaults, and nullability
Constraints and keys	Primary keys, foreign keys, unique constraints, and check constraints behave correctly
Security	Ownership, grants, RLS policies, and function behavior under different roles

Where they diverge is less about what you can assert and more about how you set up the environment, organize tests, and integrate with schema-as-code and migration workflows.

When to Reach for Lint Rules Instead

pgTAP and Atlas lint rules can assert many of the same things, but they operate at different layers. Lint rules are static analysis: Atlas reads your schema source defined in HCL, SQL, or migration files, and evaluates it against policies you define in .rule.hcl files. pgTAP is runtime verification: assertions run inside a live PostgreSQL session after the schema has been applied to a database.

That distinction matters for day-to-day workflow. A lint check does not need the pgTAP extension or Docker. It runs in milliseconds and fits naturally into atlas schema lint during local development and atlas migrate lint in CI, where it can flag violations introduced by a specific migration without re-scanning the entire schema history.

Check	pgTAP function	Lint rule alternative
Table exists	has_table()	Assert on table objects in a rule block
Primary key	has_pk()	Assert on primary_key blocks in your schema
Column type and nullability	col_type_is(), col_not_null()	Assert on column attributes
Role properties	role_ok()	Assert on role attributes (e.g., superuser, login)
Table grants	table_privs_are()	Assert on permission blocks declared in your schema
Grantable privileges	table_privs_are() with grant option checks	permission.grantable predicates

For policy-style checks—"every table must have a primary key," "identifier columns must be not null"—lint rules are the better default. For example, instead of provisioning a database and calling col_not_null(), you can express the constraint in a rule:

schema.rule.hcl

predicate "column" "not_null_or_have_default" {
  or {
    default {
      ne = null
    }
    null {
      eq = false
    }
  }
}

rule "schema" "column-notnull" {
  description = "require columns to be not null or have a default value"
  table {
    column {
      assert {
        predicate = predicate.column.not_null_or_have_default
        message   = "column ${self.name} must be not null or have a default value"
      }
    }
  }
}

Reach for pgTAP in Atlas schema tests when you need to verify post-apply behavior, want TAP-formatted diagnostics in CI, or the check is easier to express as SQL than as a declarative rule.

Conclusion

pgTAP and Atlas both help you test PostgreSQL schemas, but they solve different parts of the problem.

pgTAP excels at SQL-native assertions with rich TAP diagnostics. Functions like table_privs_are() make privilege checks concise, and TAP output shows exactly which grant is missing when a test fails.

Atlas excels at tying tests to a declared schema. It provisions isolated environments, orchestrates test cases in HCL, and fits naturally into schema-as-code and CI/CD workflows. For many structural checks, Atlas lint rules are the better fit; pgTAP in schema tests is useful when you want TAP-formatted runtime verification on a provisioned database.

Using them together, you can run pgTAP assertions directly from Atlas schema tests without a separate test runner.

Try It Yourself

Our pgTAP with Atlas guide walks through a complete project setup: declaring the pgTAP extension, configuring a dev database with the extension installed, writing exec blocks that call pgTAP functions like table_privs_are(), and catching privilege regressions in CI with TAP-formatted failure output.