spannify

English | 日本語

An assertion utility for verifying Cloud Spanner test data against JSON expectations.

Installation

npm install spannify

or

pnpm add spannify

Usage Guide

Quick Start

1. Start the Spanner emulator and verify the connection settings.

2. Create an expectations JSON file:

{
  "tables": {
    "Users": {
      "rows": [
        {
          "UserID": "user-001",
          "Name": "Alice Example",
          "Email": "[email protected]",
          "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. If you want to verify the total number of rows returned, add an optional count field.

3. Run the assertion from your script:

import { createSpannerAssert } from "spannify";
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);

When It Fails

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.

Usage with Playwright

A practical example using spannify in Playwright E2E tests to verify database state after user interactions:

import { test, expect } from "@playwright/test";
import { createSpannerAssert } from "spannify";
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"]', "[email protected]");
    await page.fill('[name="name"]', "Alice Example");
    await page.click('button[type="submit"]');

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

    // 2. Verify database state
    await spannerAssert.assert(userCreatedExpectations);
  });

  test("should update user profile", async ({ page }) => {
    // Navigate to profile and update
    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 the 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();

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

You can also verify multiple databases by changing the connection information when creating instances.

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

{
  "tables": {
    "Users": {
      "count": 1,
      "rows": [
        {
          "Email": "[email protected]",
          "Name": "Alice Example",
          "Status": 1
        }
      ]
    }
  }
}

Multiple tables example (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"
        }
      ]
    }
  }
}

Supported Value Types

spannify 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):

• Supports ARRAY<STRING>, ARRAY<INT64>, ARRAY<BOOL>
• Arrays are compared with order-independent matching (element order doesn't 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 Type:

• Spanner JSON columns are fully supported
• Subset matching: Only specified keys in the expected JSON object are compared (extra keys in actual data are ignored)
• Order-independent 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 - check specific keys only)
{
  "tables": {
    "Products": {
      "rows": [
        {
          "ProductID": "product-001",
          "Metadata": {
            "category": "electronics"
          }
        }
      ]
    }
  }
}

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

JSON Order-Independent 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 (matches even with different order)
{
  "ArticleID": "article-001",
  "Tags": [
    { "id": 1, "name": "JavaScript" },  ✅ Match
    { "id": 2, "name": "Node.js" },     ✅ Match
    { "id": 3, "name": "TypeScript" }   ✅ Match
  ]
}