Skip to main content

Testing Database Triggers

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 triggers.

Triggers​

Database triggers are sets of automated instructions that execute in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table, to enforce business rules or maintain data integrity.

Atlas Pro Feature

Triggers 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 trigger:

schema.hcl
schema "public" {}
table "products" {
  schema = schema.public
  column "id" {
    null = false
    type = integer
  }
  column "price" {
    null = true
    type = numeric
  }
  primary_key {
    columns = [column.id]
  }
}
table "products_audit" {
  schema = schema.public
  column "product_id" {
    null = true
    type = integer
  }
  column "old_price" {
    null = true
    type = numeric
  }
  column "new_price" {
    null = true
    type = numeric
  }
  column "changed_at" {
    null    = true
    type    = timestamp
    default = sql("CURRENT_TIMESTAMP")
  }
}
function "log_price_changes" {
  schema = schema.public
  lang   = PLpgSQL
  return = trigger
  as     = <<-SQL
  BEGIN
      IF OLD.price IS DISTINCT FROM NEW.price THEN
          INSERT INTO products_audit (product_id, old_price, new_price, changed_at)
          VALUES (OLD.id, OLD.price, NEW.price, NOW());
  END IF;
  RETURN NEW;
  END;
  SQL
}
trigger "log_price_changes_trigger" {
  on = table.products
  after {
    update = true
  }
  for  = ROW
  when = "(old.price IS DISTINCT FROM new.price)"
  execute {
    function = function.log_price_changes
  }
}

In the schema above, the products table stores product details and prices, while the products_audit table logs any changes to product prices. The log_price_changes_trigger activates the log_price_changes function after an update to the price column in the products table, recording the old and new prices along with the timestamp of the change in the products_audit table.

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:

  1. Seed data into products.
  2. Verify that the products_audit table is empty.
  3. Update the price of a product in the products table.
  4. Check that producs_audit was updated with the new data.
schema.test.hcl
test "schema" "trigger" {
  # Seed data
  exec {
    sql = "INSERT INTO products (id, price) VALUES (1, 15.00), (2, 22.99), (3, 13.50);"
  }
  # Verify products_audit table empty
  exec {
    sql = "SELECT COUNT(*) FROM products_audit"
    output = "0"
  }
  exec {
    sql = "UPDATE products SET price = 19.99 WHERE id = 1;"
  }
  exec {
    sql = "SELECT product_id, old_price, new_price FROM products_audit"
    format = table
    output = <<TAB
 product_id | old_price | new_price
------------+-----------+-----------
 1          | 15        | 19.99
TAB
  }
}

Run the test by running:

atlas schema test --env dev

The output should look similar to:

Test Output
-- PASS: trigger (4ms)
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 test above, we will execute the following:

  1. Seed data into products.
  2. Verify that the products_audit table is empty.
  3. Update the price of two products in the products table, using the for_each meta-argument.
  4. Check that producs_audit was updated with the new data.
schema.test.hcl
test "schema" "trigger" {
  # Seed data
  exec {
    sql = "INSERT INTO products (id, price) VALUES (1, 15.00), (2, 22.99), (3, 13.50);"
  }
  # Verify products_audit table empty
  exec {
    sql = "SELECT COUNT(*) FROM products_audit"
    output = "0"
  }
  for_each = [
    {product_id: "1", new_price: "19.99"},
    {product_id: "2", new_price: "20"}
  ]
  exec {
    sql = "UPDATE products SET price = ${each.value.new_price} WHERE id = ${each.value.product_id};"
  }
  exec {
    sql = "SELECT new_price FROM products_audit where product_id = ${each.value.product_id}"
    output = each.value.new_price
  }
  log {
    message = "Testing for ID:  ${each.value.product_id} - New Price: ${each.value.new_price}"
  }
}

Run the test by running:

atlas schema test --env dev

The output should look similar to:

Test Output
-- PASS: trigger/1 (4ms)
    schema.test.hcl:50: Testing for ID:  1 - New Price: 19.99
-- PASS: trigger/2 (1ms)
    schema.test.hcl:50: Testing for ID:  2 - New Price: 20
PASS