spanner-assert

spanner-assert

Validate Google Cloud Spanner emulator data against expectations written in JSON. Lightweight Node.js testing library for E2E workflows, fast feedback loops.

⚠️ This library only supports Cloud Spanner emulator - designed for testing environments, not production databases.

Install

npm install -D spanner-assert

Quick Start

• Start the Spanner emulator and note the connection settings.

• Create an expectations JSON file:

{
  "tables": {
    "Users": {
      "rows": [
        {
          "UserID": "user-001",
          "Name": "Alice Example",
          "Email": "[e-mail]",
          "Status": 1,
          "CreatedAt": "2024-01-01T00:00:00Z"
        }
      ]
    },
    "Products": {
      "rows": [
        {
          "ProductID": "product-001",
          "Name": "Example Product",
          "Price": 1999,
          "IsActive": true,
          "CategoryID": null,
          "CreatedAt": "2024-01-01T00:00:00Z"
        }
      ]
    },
    "Books": {
      "rows": [
        {
          "BookID": "book-001",
          "Title": "Example Book",
          "Author": "Jane Doe",
          "PublishedYear": 2024,
          "JSONData": "{\"genre\":\"Fiction\",\"rating\":4.5}"
        }
      ]
    }
  }
}

Each table lists expected rows as an array. Add an optional count field when you also want to assert the total number of rows returned.

• Run the assertion from a script:

import { createSpannerAssert } from "spanner-assert";
import expectations from "./expectations.json" with { type: "json" };

const spannerAssert = createSpannerAssert({
  connection: {
    projectId: "your-project-id",
    instanceId: "your-instance-id",
    databaseId: "your-database",
    emulatorHost: "127.0.0.1:9010",
  },
});

await spannerAssert.assert(expectations);

On success you get no output (or your own logging) because all tables matched.

If not successful

SpannerAssertionError: 1 expected row(s) not found in table "Users".
  - Expected
  + Actual

  Array [
    Object {
-     "Name": "Alice",
+     "Name": "Invalid Name",
    },
  ]

An error is thrown with a color-coded diff showing expected vs actual values (using jest-diff).

Example using Playwright

Here's a practical example of using spanner-assert in Playwright E2E tests to validate database state after user interactions:

import { test, expect } from "@playwright/test";
import { createSpannerAssert } from "spanner-assert";
import userCreatedExpectations from "./test/expectations/user-created.json" with { type: "json" };
import profileUpdatedExpectations from "./test/expectations/profile-updated.json" with { type: "json" };
import productInventoryExpectations from "./test/expectations/product-inventory.json" with { type: "json" };

test.describe("User Registration Flow", () => {
  let spannerAssert;

  test.beforeAll(async () => {
    spannerAssert = createSpannerAssert({
      connection: {
        projectId: "your-project-id",
        instanceId: "your-instance-id",
        databaseId: "your-database",
        emulatorHost: "127.0.0.1:9010",
      },
    });
  });

  test("should create user record after registration", async ({ page }) => {
    // 1. Perform UI actions
    await page.goto("https://your-app.com/register");
    await page.fill('[name="email"]', "[e-mail]");
    await page.fill('[name="name"]', "Alice Example");
    await page.click('button[type="submit"]');

    await expect(page.locator(".success-message")).toBeVisible();

    // 2. Validate database state with spanner-assert
    await spannerAssert.assert(userCreatedExpectations);
  });

  test("should update user profile", async ({ page }) => {
    // Navigate and update profile
    await page.goto("https://your-app.com/profile");
    await page.fill('[name="bio"]', "Software engineer");
    await page.click('button:has-text("Save")');

    await expect(page.locator(".success-notification")).toBeVisible();

    // Verify database was updated correctly
    await spannerAssert.assert(profileUpdatedExpectations);
  });

  test("should create product and verify inventory", async ({ page }) => {
    // Admin creates a new product
    await page.goto("https://your-app.com/admin/products");
    await page.fill('[name="productName"]', "Example Product");
    await page.fill('[name="price"]', "1999");
    await page.check('[name="isActive"]');
    await page.click('button:has-text("Create Product")');

    await expect(page.locator(".product-created")).toBeVisible();

    // Validate both Products and Inventory tables
    await spannerAssert.assert(productInventoryExpectations);
  });
});

Example expectation file (test/expectations/user-created.json):

{
  "tables": {
    "Users": {
      "count": 1,
      "rows": [
        {
          "Email": "[e-mail]",
          "Name": "Alice Example",
          "Status": 1
        }
      ]
    }
  }
}

Example with multiple tables (test/expectations/product-inventory.json):

{
  "tables": {
    "Products": {
      "count": 1,
      "rows": [
        {
          "Name": "Example Product",
          "Price": 1999,
          "IsActive": true
        }
      ]
    },
    "Inventory": {
      "count": 1,
      "rows": [
        {
          "ProductID": "product-001",
          "Quantity": 0,
          "LastUpdated": "2024-01-01T00:00:00Z"
        }
      ]
    }
  }
}

This pattern allows you to:

• Verify UI actions resulted in correct database changes
• Validate complex multi-table relationships
• Catch data integrity issues early in the development cycle
• Keep test expectations readable and version-controlled

Supported value types

spanner-assert compares column values using string, number, boolean, null, arrays, and JSON types.

Primitive types:

• string, number, boolean, null
• For Spanner types like TIMESTAMP or DATE, provide values as strings (e.g., "2024-01-01T00:00:00Z")

Array types (ARRAY columns):

• ARRAY<STRING>, ARRAY<INT64>, ARRAY<BOOL> are supported
• Arrays are compared with order-insensitive matching (element order does not matter)
• Empty arrays ([]) are supported

Array example:

{
  "tables": {
    "Articles": {
      "rows": [
        {
          "ArticleID": "article-001",
          "Tags": ["javascript", "typescript", "node"],
          "Scores": [100, 200, 300],
          "Flags": [true, false, true]
        },
        {
          "ArticleID": "article-002",
          "Tags": [],
          "Scores": [],
          "Flags": []
        }
      ]
    }
  }
}

JSON types:

• Spanner JSON columns are fully supported
• Subset matching: Only specified keys in expected JSON objects are compared (additional keys in actual data are ignored)
• Order-insensitive arrays: Arrays within JSON values can be in any order
• Nested structures: Unlimited nesting depth is supported

JSON example:

{
  "tables": {
    "Products": {
      "rows": [
        {
          "ProductID": "product-001",
          "Metadata": {
            "category": "electronics",
            "tags": ["laptop", "gaming"],
            "specs": {
              "cpu": "Intel i9",
              "ram": 32
            }
          }
        },
        {
          "ProductID": "product-002",
          "Reviews": [
            { "rating": 5, "comment": "Great!" },
            { "rating": 4, "comment": "Good" }
          ]
        }
      ]
    }
  }
}

JSON subset matching example:

// Expected (subset matching - only check specific keys)
{
  "tables": {
    "Products": {
      "rows": [
        {
          "ProductID": "product-001",
          "Metadata": {
            "category": "electronics"
          }
        }
      ]
    }
  }
}

// Actual database (matches even with extra keys)
{
  "ProductID": "product-001",
  "Metadata": {
    "category": "electronics",     ✅ matches
    "tags": ["laptop", "gaming"],   ⬜ ignored
    "specs": { "cpu": "Intel i9" }  ⬜ ignored
  }
}

JSON order-insensitive array example:

// Expected (arrays in any order)
{
  "tables": {
    "Articles": {
      "rows": [
        {
          "ArticleID": "article-001",
          "Tags": [
            { "id": 3, "name": "TypeScript" },
            { "id": 1, "name": "JavaScript" },
            { "id": 2, "name": "Node.js" }
          ]
        }
      ]
    }
  }
}

// Actual database (different order, still matches)
{
  "ArticleID": "article-001",
  "Tags": [
    { "id": 1, "name": "JavaScript" },  ✅ matches
    { "id": 2, "name": "Node.js" },     ✅ matches
    { "id": 3, "name": "TypeScript" }   ✅ matches
  ]
}

Connection Management

Each assert() call automatically manages its own database connection lifecycle:

• Auto-creation: A fresh connection is created when you call assert()
• Auto-cleanup: The connection is automatically closed when the assertion completes
• No manual management: No need to call close() or manage connection lifecycle

This design keeps the API simple and prevents connection leak issues. You can call assert() multiple times without worrying about resource management:

const spannerAssert = createSpannerAssert({
  connection: {
    projectId: "your-project-id",
    instanceId: "your-instance-id",
    databaseId: "your-database",
    emulatorHost: "127.0.0.1:9010",
  }
});

// Each call creates and closes its own connection
test("first assertion", async () => {
  await spannerAssert.assert(expectations1);
});

test("second assertion", async () => {
  await spannerAssert.assert(expectations2);
});

Database Cleanup with resetDatabase()

The resetDatabase() method deletes all data from specified tables, making it useful for cleaning up test data between tests. This ensures each test starts with a clean slate.

Basic Usage

await spannerAssert.resetDatabase(["Users", "Products", "Orders"]);

Playwright Example

Use test.afterEach() to automatically clean up after each test:

import { test } from "@playwright/test";
import { createSpannerAssert } from "spanner-assert";

const spannerAssert = createSpannerAssert({
  connection: {
    projectId: "your-project-id",
    instanceId: "your-instance-id",
    databaseId: "your-database",
    emulatorHost: "127.0.0.1:9010",
  },
});

test.describe("User Tests", () => {
  test.afterEach(async () => {
    // Clean up database after each test
    await spannerAssert.resetDatabase([
      "Users",
      "Products",
      "Orders",
    ]);
  });

  test("creates a new user", async ({ page }) => {
    // Test code...
    await spannerAssert.assert(expectations);
  });

  test("updates user profile", async ({ page }) => {
    // Test code...
    await spannerAssert.assert(expectations);
  });
});

Row Matching Algorithm

spanner-assert uses a greedy matching algorithm to compare expected rows against actual database rows. Understanding this behavior helps you write effective test expectations.

How It Works

The algorithm processes expected rows sequentially:

• For each expected row, it searches through the remaining actual rows
• When a match is found, that actual row is immediately consumed (removed from the pool)
• The algorithm moves to the next expected row
• Any expected rows that don't find a match are reported as missing

Key characteristics:

• Subset matching: Only columns specified in the expected row are compared. Additional columns in the database are ignored.
• Greedy selection: The first matching actual row is chosen—no backtracking or optimization.
• Order-dependent: The order of expected rows can affect the outcome if expectations are ambiguous.

Example: Subset Matching

// Expected (only 2 columns specified)
{
  "tables": {
    "Users": {
      "rows": [
        {
          "Email": "[e-mail]",
          "Status": 1
        }
      ]
    }
  }
}

This will match a database row even if it has additional columns:

Actual database row:
{
  UserID: "user-001",
  Name: "Alice Example",
  Email: "[e-mail]",  ✅ matches
  Status: 1,                    ✅ matches
  CreatedAt: "2024-01-01T..."  ⬜ ignored (not in expectation)
}

Best Practice: Use Unique Identifiers

To avoid ambiguity and ensure reliable matching, always include unique columns (like primary keys) in your expectations:

// ✅ Good: Specific and unambiguous
{
  "tables": {
    "Users": {
      "rows": [
        {
          "UserID": "user-001",
          "Email": "[e-mail]",
          "Status": 1
        },
        {
          "UserID": "user-002",
          "Email": "[e-mail]",
          "Status": 1
        }
      ]
    }
  }
}

// ❌ Risky: Ambiguous expectations
{
  "tables": {
    "Users": {
      "rows": [
        { "Status": 1 },
        { "Status": 1 }
      ]
    }
  }
}

When Greedy Matching Can Fail

Consider this scenario:

// Actual database has 3 rows:
// - { UserID: "A", Status: 1 }
// - { UserID: "B", Status: 1 }
// - { UserID: "C", Status: 2 }

// Expectations:
{
  "tables": {
    "Users": {
      "rows": [
        { "Status": 1 }, // ① Ambiguous - matches A or B
        { "UserID": "A" } // ② Specific - needs A
      ]
    }
  }
}

The greedy algorithm will:

• Process expectation ①, match it to the first row with Status: 1 (e.g., row A), and consume row A
• Process expectation ②, look for UserID: "A", but row A was already consumed
• Result: Assertion fails even though a valid assignment exists

Solution: Make expectations specific:

{
  "tables": {
    "Users": {
      "rows": [
        {
          "UserID": "A",
          "Status": 1
        },
        {
          "UserID": "B",
          "Status": 1
        }
      ]
    }
  }
}

Combining count and rows

Use both for comprehensive validation:

{
  "tables": {
    "Users": {
      "count": 10,
      "rows": [
        {
          "UserID": "admin-001",
          "Role": "admin"
        }
      ]
    }
  }
}

This ensures:

• Exactly 10 users exist (not 9 or 11)
• At least one admin user with ID "admin-001" exists

License

MIT