Testing Database Views

Testing your database schema and migrations is crucial to ensure code behaves as expected, catch bugs early, and prevent regressions. Databases enforce logic, constraints, and complex relationships, so testing ensures these elements work correctly and remain intact after changes.

In this guide we will learn how to use Atlas's schema test command to test database views.

Database Views

A view is a virtual table in the database, defined by a statement that queries rows from one or more existing tables or views.

Atlas Pro Feature

Views are currently available only to Atlas Pro users. To use this feature, run:

atlas login

Project Setup

Schema File

For this example, let's assume we have the following schema, including a view:

schema.hcl

schema "public" {}

table "users" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "name" {
    type = text
  }
  column "ssn" {
    type = varchar(9)
  }
}

view "clean_users" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "name" {
    type = text
  }
  as         = <<-SQL
  SELECT u.id, u.name
    FROM ${table.users.name} AS u
  SQL
  depends_on = [table.users]
  comment    = "A view to select active users without sensitive data"
}

The schema has a simple users table, with id, name, and ssn as columns. The view, clean_users, selects the id and name columns from users, ensuring we aren't selecting any sensitive data (SSNs).

Config File

Before we begin testing, create a config file named atlas.hcl.

In this file we will create an environment, specify the source of our schema, and a URL for our dev database.

We will also create a file named schema.test.hcl to write our tests, and add it to the atlas.hcl file in the test block.

atlas.hcl

env "dev" {
  src = "file://schema.hcl"
  dev = "docker://postgres/15/dev?search_path=public"

  # Test configuration for local development.
  test {
    schema {
      src = ["schema.test.hcl"]
    }
  }
}

Writing Tests

Simple Test

Let's start off with a simple test that will do the following:

1. Seed data into the users table.
2. Run a query on the clean_users view.
3. Ensure the data is retrieved as expected.

schema.test.hcl

test "schema" "view" {
  # Seeding to test view.
  exec {
    sql = "INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');"
  }
  log {
    message = "Seeded the user's table"
  }

  # Expected exec to pass.
  exec {
    sql = <<SQL
    SELECT id, name
    FROM clean_users;
    SQL
  }
  log {
    message = "Tested the view"
  }

  # Validates data.
  exec {
    sql = "SELECT id, name FROM clean_users;"
    format = table
    output = <<TAB
 id |  name
----+---------
 1  | Alice
 2  | Bob
 3  | Charlie
TAB
  }
  log {
    message = "Table is as expected"
  }
}

info

To learn more about how to write tests, view the testing docs.

To run this test, run:

atlas schema test --env dev

The output should be similar to:

Test Output

-- PASS: view (1ms)
    schema.test.hcl:6: Seeded the user's table
    schema.test.hcl:17: Tested the view
    schema.test.hcl:33: Table is as expected
PASS

Table Driven Test

Another alternative is to write a table driven test. This test uses the for_each meta-argument, which accepts a map or a set of values and is used to generate a test case for each item in the set or map.

Following similar logic to the example above, our table driven test will:

1. Seed data into the users table.
2. Run a query on the clean_users view.
3. Ensure the data is retrieved as expected.

schema.test.hcl

test "schema" "view" {
  for_each = [
    {id = 1, name = "Alice"},
    {id = 2, name = "Bob"},
    {id = 3, name = "Charlie"}
  ]
  # Seed the `users` table.
  exec {
    sql = "INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');"
  }
  # Query the `clean_users` view.
  exec {
    sql = "SELECT id, name FROM clean_users WHERE id IN (1, 2, 3);"
  }
  # Check each ID returns the right user.
  log {
    message = "Testing ${each.value.id} -> ${each.value.name}"
  }
}

To run this test, run:

atlas schema test --env dev

The output should be similar to:

Test Output

-- PASS: view/1 (3ms)
    schema.test.hcl:62: Testing 1 -> Alice
-- PASS: view/2 (633µs)
    schema.test.hcl:62: Testing 2 -> Bob
-- PASS: view/3 (643µs)
    schema.test.hcl:62: Testing 3 -> Charlie
PASS